-- ============================================================
-- Online Job Portal System for Narowal City
-- Database: job_portal_narowal
-- Import this file in phpMyAdmin (XAMPP)
-- ============================================================

CREATE DATABASE IF NOT EXISTS job_portal_narowal
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE job_portal_narowal;

-- ------------------------------------------------------------
-- Table: categories
-- ------------------------------------------------------------
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: jobs
-- ------------------------------------------------------------
CREATE TABLE jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employer_id INT NOT NULL,
  category_id INT NOT NULL,
  title VARCHAR(150) NOT NULL,
  description TEXT NOT NULL,
  salary VARCHAR(100) NOT NULL,
  location VARCHAR(150) NOT NULL,
  job_type ENUM('Full Time','Part Time','Internship','Contract') NOT NULL,
  contact_email VARCHAR(150) NOT NULL,
  status ENUM('pending','approved','rejected','closed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: applications
-- ------------------------------------------------------------
CREATE TABLE applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  seeker_id INT NOT NULL,
  resume VARCHAR(255) NOT NULL,
  cover_letter TEXT NOT NULL,
  status ENUM('pending','reviewed','accepted','rejected') NOT NULL DEFAULT 'pending',
  applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
  FOREIGN KEY (seeker_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 8 job categories
-- ------------------------------------------------------------
INSERT INTO categories (name, status) VALUES
('IT', 'active'),
('Teaching', 'active'),
('Office', 'active'),
('Shop', 'active'),
('Labor', 'active'),
('Healthcare', 'active'),
('Sales', 'active'),
('Driver', 'active');

-- ------------------------------------------------------------
-- 10 approved sample jobs in Narowal City
-- employer_id 2 = Narowal Textile Mills, 3 = City Hospital
-- ------------------------------------------------------------
-- category_id: 1=IT, 2=Teaching, 3=Office, 4=Shop, 5=Labor, 6=Healthcare, 7=Sales, 8=Driver
INSERT INTO jobs (employer_id, category_id, title, description, salary, location, job_type, contact_email, status) VALUES
(2, 1, 'Junior Web Developer',
 'Looking for a beginner PHP developer to maintain company website and basic database tasks. Training will be provided at Narowal office.',
 'Rs. 35,000 - 45,000', 'Narowal City', 'Full Time', 'employer1@gmail.com', 'approved'),

(2, 7, 'Sales Officer',
 'Responsible for local sales visits, customer follow-up, and monthly sales reports in Narowal and nearby areas.',
 'Rs. 30,000 + Commission', 'Narowal City', 'Full Time', 'employer1@gmail.com', 'approved'),

(3, 6, 'Staff Nurse',
 'Registered staff nurse required for day and night shifts at Narowal City Hospital emergency ward.',
 'Rs. 40,000 - 55,000', 'Hospital Road, Narowal', 'Full Time', 'employer2@gmail.com', 'approved'),

(3, 6, 'Lab Technician',
 'Perform routine lab tests, maintain equipment records, and assist senior doctors.',
 'Rs. 28,000 - 38,000', 'Narowal City', 'Full Time', 'employer2@gmail.com', 'approved'),

(2, 5, 'Electrical Supervisor',
 'Supervise electrical maintenance team in textile unit. Diploma in electrical engineering required.',
 'Rs. 50,000 - 65,000', 'Industrial Area, Narowal', 'Full Time', 'employer1@gmail.com', 'approved'),

(3, 2, 'School Lab Assistant',
 'Assist science teachers in school lab, prepare experiments, and maintain lab inventory.',
 'Rs. 22,000 - 28,000', 'Narowal City', 'Part Time', 'employer2@gmail.com', 'approved'),

(2, 3, 'Accounts Assistant',
 'Handle daily vouchers, petty cash, and basic bookkeeping using Excel.',
 'Rs. 32,000 - 40,000', 'Narowal City', 'Full Time', 'employer1@gmail.com', 'approved'),

(2, 1, 'IT Intern',
 '3-month internship for CS students. Learn PHP, MySQL, and basic web hosting on localhost/XAMPP.',
 'Stipend Rs. 15,000', 'Narowal City', 'Internship', 'employer1@gmail.com', 'approved'),

(3, 3, 'Receptionist',
 'Manage front desk, patient registration, and phone calls with good communication skills.',
 'Rs. 25,000 - 30,000', 'Narowal City Hospital', 'Full Time', 'employer2@gmail.com', 'approved'),

(2, 7, 'Marketing Intern',
 'Support social media posts and local promotion campaigns for Narowal textile products.',
 'Stipend Rs. 12,000', 'Narowal City', 'Internship', 'employer1@gmail.com', 'approved');

-- ------------------------------------------------------------
-- 3 sample applications
-- ------------------------------------------------------------
INSERT INTO applications (job_id, seeker_id, resume, cover_letter, status) VALUES
(1, 4, 'sample_resume_ahmed.pdf',
 'I am a BS Computer Science student interested in web development. I have basic PHP and MySQL knowledge.',
 'pending'),

(3, 5, 'sample_resume_sana.pdf',
 'I have completed nursing diploma and want to serve at Narowal City Hospital.',
 'reviewed'),

(8, 6, 'sample_resume_usman.pdf',
 'I am looking for an IT internship to complete my FYP and gain practical experience.',
 'pending');

-- ------------------------------------------------------------
-- Table: interview_requests
-- ------------------------------------------------------------
CREATE TABLE interview_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  employer_id INT NOT NULL,
  seeker_id INT NOT NULL,
  job_id INT NOT NULL,
  interview_datetime DATETIME NOT NULL,
  message TEXT,
  location_name VARCHAR(255) NOT NULL,
  location_lat DECIMAL(10,8) NULL,
  location_lng DECIMAL(11,8) NULL,
  map_link TEXT NULL,
  status ENUM('pending','accepted','rejected') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
  FOREIGN KEY (employer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (seeker_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Table: reports
-- ------------------------------------------------------------
CREATE TABLE reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reporter_id INT NOT NULL,
  reported_user_id INT NULL,
  reported_job_id INT NULL,
  report_type ENUM('user','job','application','other') NOT NULL DEFAULT 'other',
  reason VARCHAR(255) NOT NULL,
  details TEXT NOT NULL,
  status ENUM('pending','reviewed','dismissed','action_taken') NOT NULL DEFAULT 'pending',
  admin_action TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (reported_job_id) REFERENCES jobs(id) ON DELETE SET NULL
) ENGINE=InnoDB;
