CREATE DATABASE IF NOT EXISTS hrms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hrms;


CREATE TABLE branches (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_code VARCHAR(50) NOT NULL UNIQUE,
  branch_name VARCHAR(150) NOT NULL,
  address VARCHAR(500) DEFAULT NULL,
  city VARCHAR(100) DEFAULT NULL,
  state VARCHAR(100) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  email VARCHAR(190) DEFAULT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_branches (
  user_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(user_id,branch_id),
  INDEX idx_user_branch_branch(branch_id),
  CONSTRAINT fk_user_branch_user FOREIGN KEY(user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_branch_branch FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employee_code_sequence (
  id TINYINT UNSIGNED PRIMARY KEY,
  next_number INT UNSIGNED NOT NULL DEFAULT 1000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO employee_code_sequence(id,next_number) VALUES(1,1000);

CREATE TABLE employees (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED DEFAULT NULL,
  employee_code VARCHAR(50) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) DEFAULT NULL,
  date_of_birth DATE DEFAULT NULL,
  gender VARCHAR(30) DEFAULT NULL,
  email VARCHAR(190) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(500) DEFAULT NULL,
  aadhaar_number VARCHAR(20) DEFAULT NULL,
  pan_number VARCHAR(20) DEFAULT NULL,
  company_document VARCHAR(255) DEFAULT NULL,
  certificate_document VARCHAR(255) DEFAULT NULL,
  aadhaar_document VARCHAR(255) DEFAULT NULL,
  pan_document VARCHAR(255) DEFAULT NULL,
  bank_book_document VARCHAR(255) DEFAULT NULL,
  passport_photo VARCHAR(255) DEFAULT NULL,
  education_qualification VARCHAR(180) DEFAULT NULL,
  education_institution VARCHAR(180) DEFAULT NULL,
  education_year VARCHAR(10) DEFAULT NULL,
  next_promotion VARCHAR(120) DEFAULT NULL,
  next_promotion_date DATE DEFAULT NULL,
  department VARCHAR(120) DEFAULT NULL,
  designation VARCHAR(120) NOT NULL DEFAULT 'Team Member',
  joining_date DATE DEFAULT NULL,
  status ENUM('active','inactive','terminated') NOT NULL DEFAULT 'active',
  status_reason VARCHAR(500) DEFAULT NULL,
  status_changed_at DATETIME DEFAULT NULL,
  status_changed_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_employee_branch(branch_id),
  INDEX idx_employee_status(status),
  UNIQUE KEY uq_employee_aadhaar(aadhaar_number),
  CONSTRAINT fk_employee_branch FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employee_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(180) NOT NULL,
  date_of_birth DATE DEFAULT NULL,
  gender VARCHAR(30) DEFAULT NULL,
  email VARCHAR(190) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(500) NOT NULL,
  aadhaar_number VARCHAR(20) NOT NULL,
  pan_number VARCHAR(20) NOT NULL,
  designation VARCHAR(120) NOT NULL DEFAULT 'Team Member',
  status ENUM('pending','approved','rejected','docs_submitted','completed') NOT NULL DEFAULT 'pending',
  submitted_by INT UNSIGNED DEFAULT NULL,
  reviewed_by INT UNSIGNED DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  documents_submitted_at DATETIME DEFAULT NULL,
  employee_id INT UNSIGNED DEFAULT NULL,
  company_document VARCHAR(255) DEFAULT NULL,
  certificate_document VARCHAR(255) DEFAULT NULL,
  aadhaar_document VARCHAR(255) DEFAULT NULL,
  pan_document VARCHAR(255) DEFAULT NULL,
  bank_book_document VARCHAR(255) DEFAULT NULL,
  passport_photo VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_request_branch_status(branch_id,status),
  INDEX idx_request_status(status),
  INDEX idx_request_aadhaar(aadhaar_number),
  CONSTRAINT fk_request_branch FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  CONSTRAINT fk_request_submitter FOREIGN KEY(submitted_by) REFERENCES admin_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_request_reviewer FOREIGN KEY(reviewed_by) REFERENCES admin_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_request_employee FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE employees
  ADD COLUMN salary_type VARCHAR(30) NOT NULL DEFAULT 'Monthly',
  ADD COLUMN payroll_plan VARCHAR(50) NOT NULL DEFAULT 'Standard Payroll',
  ADD COLUMN pf_option ENUM('PF','Non-PF') NOT NULL DEFAULT 'PF',
  ADD COLUMN joining_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN hra DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN hcs DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN pf_percent DECIMAL(5,2) NOT NULL DEFAULT 12.00,
  ADD COLUMN esi_percent DECIMAL(5,2) NOT NULL DEFAULT 0.75,
  ADD COLUMN pf_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN esi_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN net_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN increment_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN payroll_notes VARCHAR(1000) DEFAULT NULL;

ALTER TABLE employee_requests
  ADD COLUMN salary_type VARCHAR(30) NOT NULL DEFAULT 'Monthly',
  ADD COLUMN payroll_plan VARCHAR(50) NOT NULL DEFAULT 'Standard Payroll',
  ADD COLUMN pf_option ENUM('PF','Non-PF') NOT NULL DEFAULT 'PF',
  ADD COLUMN joining_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN hra DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN hcs DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN pf_percent DECIMAL(5,2) NOT NULL DEFAULT 12.00,
  ADD COLUMN esi_percent DECIMAL(5,2) NOT NULL DEFAULT 0.75,
  ADD COLUMN pf_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN esi_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN net_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN increment_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ADD COLUMN payroll_notes VARCHAR(1000) DEFAULT NULL,
  ADD COLUMN final_reviewed_by INT UNSIGNED DEFAULT NULL,
  ADD COLUMN final_reviewed_at DATETIME DEFAULT NULL,
  ADD COLUMN offer_letter_path VARCHAR(255) DEFAULT NULL;

CREATE TABLE employee_increments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  change_type VARCHAR(40) NOT NULL DEFAULT 'salary_increment',
  increment_amount DECIMAL(12,2) NOT NULL,
  previous_gross DECIMAL(12,2) NOT NULL DEFAULT 0,
  new_gross DECIMAL(12,2) NOT NULL DEFAULT 0,
  previous_branch_id INT UNSIGNED DEFAULT NULL,
  new_branch_id INT UNSIGNED DEFAULT NULL,
  previous_designation VARCHAR(120) DEFAULT NULL,
  new_designation VARCHAR(120) DEFAULT NULL,
  reason VARCHAR(500) DEFAULT NULL,
  effective_date DATE NOT NULL DEFAULT '2000-01-01',
  created_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_inc_employee(employee_id),
  CONSTRAINT fk_inc_employee FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employee_resignations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  reason VARCHAR(1000) NOT NULL,
  resignation_date DATE NOT NULL,
  notice_period_days INT UNSIGNED NOT NULL DEFAULT 15,
  requested_last_working_date DATE NOT NULL,
  approved_last_working_date DATE DEFAULT NULL,
  status ENUM('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending',
  submitted_by INT UNSIGNED DEFAULT NULL,
  reviewed_by INT UNSIGNED DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_res_employee(employee_id),
  INDEX idx_res_status(status),
  INDEX idx_res_last_working(approved_last_working_date),
  CONSTRAINT fk_res_employee FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_res_submitter FOREIGN KEY(submitted_by) REFERENCES admin_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_res_reviewer FOREIGN KEY(reviewed_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status VARCHAR(30) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_attendance_employee_date(employee_id,attendance_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE leave_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_leave_employee_dates(employee_id,start_date,end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branch_manpower_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  designation VARCHAR(120) NOT NULL,
  planned_count INT UNSIGNED NOT NULL DEFAULT 0,
  monthly_budget_per_employee DECIMAL(12,2) NOT NULL DEFAULT 0,
  notes VARCHAR(500) DEFAULT NULL,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_branch_designation(branch_id,designation),
  INDEX idx_plan_branch(branch_id),
  CONSTRAINT fk_plan_branch FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_positions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  openings INT UNSIGNED NOT NULL DEFAULT 1,
  status VARCHAR(30) NOT NULL DEFAULT 'open',
  closing_date DATE DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recruitment_questions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  position_id INT UNSIGNED NOT NULL,
  question_text TEXT NOT NULL,
  option_a VARCHAR(500) NOT NULL,
  option_b VARCHAR(500) NOT NULL,
  option_c VARCHAR(500) DEFAULT NULL,
  option_d VARCHAR(500) DEFAULT NULL,
  correct_option CHAR(1) NOT NULL,
  marks DECIMAL(6,2) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_rq_position(position_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Employee code sequence is application-controlled: EMP1001, EMP1002, EMP1003... */


-- ===== AREA MANAGER FIELD TRACKING ADD-ON =====
-- HRMS Area Manager Field Tracking
-- Run this once in phpMyAdmin on the existing `hrms` database.
-- Safe additive migration: it does NOT drop or replace existing HRMS tables.

USE hrms;

CREATE TABLE IF NOT EXISTS field_tracking_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id INT UNSIGNED NOT NULL,
  employee_code VARCHAR(50) NOT NULL,
  start_time DATETIME NOT NULL,
  start_latitude DECIMAL(10,7) NOT NULL,
  start_longitude DECIMAL(10,7) NOT NULL,
  start_accuracy DECIMAL(10,2) DEFAULT NULL,
  start_selfie VARCHAR(255) DEFAULT NULL,
  end_time DATETIME DEFAULT NULL,
  end_latitude DECIMAL(10,7) DEFAULT NULL,
  end_longitude DECIMAL(10,7) DEFAULT NULL,
  end_accuracy DECIMAL(10,2) DEFAULT NULL,
  end_selfie VARCHAR(255) DEFAULT NULL,
  total_distance_km DECIMAL(12,3) NOT NULL DEFAULT 0,
  status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_fts_employee_date(employee_id,start_time),
  INDEX idx_fts_code_date(employee_code,start_time),
  INDEX idx_fts_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create a mobile PIN for an employee using PHP's password_hash(), not a plaintext value.
-- Example after opening the supplied setup page:
-- /field_mobile_access.php
