-- ============================================================
-- 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: users
-- ------------------------------------------------------------
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  role ENUM('admin','employer','seeker') NOT NULL,
  company_name VARCHAR(150) NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 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;

-- ============================================================
-- Default users (passwords hashed with password_hash / bcrypt)
-- admin@gmail.com     -> admin123
-- employer1@gmail.com -> employer123
-- employer2@gmail.com -> employer123
-- seeker1@gmail.com   -> seeker123
-- seeker2@gmail.com   -> seeker123
-- seeker3@gmail.com   -> seeker123
-- ============================================================

INSERT INTO users (name, email, password, phone, role, company_name, status) VALUES
('System Admin', 'admin@gmail.com',
 '$2b$10$plOmRF4FP194gcxgBuLSaOKM7sdFf8rk1YrdwJMTNVHMMwRtdJllW',
 '03001234567', 'admin', NULL, 'active'),

('Ali Textiles', 'employer1@gmail.com',
 '$2b$10$zE69RVN5vm/s1H5frLifOOeI4ohSwr1McUuQ4sGcG7wiqWomV9Cse',
 '03011112222', 'employer', 'Narowal Textile Mills', 'active'),

('City Care Hospital', 'employer2@gmail.com',
 '$2b$10$zE69RVN5vm/s1H5frLifOOeI4ohSwr1McUuQ4sGcG7wiqWomV9Cse',
 '03013334444', 'employer', 'Narowal City Hospital', 'active'),

('Ahmed Khan', 'seeker1@gmail.com',
 '$2b$10$p8BZUBwoAr2mA34zrCuom.XAyBoKS6Z/kP7nrb3RHOR5wKjHOi/Xe',
 '03015556666', 'seeker', NULL, 'active'),

('Sana Malik', 'seeker2@gmail.com',
 '$2b$10$p8BZUBwoAr2mA34zrCuom.XAyBoKS6Z/kP7nrb3RHOR5wKjHOi/Xe',
 '03017778888', 'seeker', NULL, 'active'),

('Usman Raza', 'seeker3@gmail.com',
 '$2b$10$p8BZUBwoAr2mA34zrCuom.XAyBoKS6Z/kP7nrb3RHOR5wKjHOi/Xe',
 '03019990000', 'seeker', NULL, 'active');

-- ------------------------------------------------------------
-- 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;