-- ============================================================
-- PEOPLECORE HR SYSTEM (Seyalix HRMS) — LIVE INSTALL SCHEMA
-- Consolidated from full migration history — 04 Aug 2026
-- ============================================================
-- This is ONE clean file that replaces running 25+ separate
-- migration_*.sql / fix_*.sql files one by one. It contains the
-- FINAL state of every table after all patches — duplicate
-- CREATE TABLE attempts, superseded fixes, and one-off data
-- repairs (that only mattered for an already-broken existing DB)
-- have been removed.
--
-- HOW TO USE ON CPANEL:
--  1. cPanel → MySQL Databases → create a NEW, EMPTY database
--     (cPanel will name it something like cpaneluser_hr — note
--     the exact name it gives you).
--  2. Create a MySQL user, set a strong password, ADD that user
--     to the database with ALL PRIVILEGES.
--  3. phpMyAdmin → select that empty database → Import tab →
--     choose this file → Go. (If your host limits upload size,
--     paste the file content into the SQL tab instead.)
--  4. Update config/config.php on the server with the REAL
--     values cPanel gave you:
--       DB_NAME  -> cpaneluser_hr        (NOT peoplecore_hr)
--       DB_USER  -> cpaneluser_hruser    (NOT root)
--       DB_PASS  -> the password you set (NOT Gmhpl@2025)
--       DB_HOST  -> usually still 'localhost' on cPanel
--       BASE_URL -> your real domain, e.g. https://yourdomain.com/hr_system
--  5. Log in as admin / Gmhpl_2025 and vendor / Gmhpl_2025 —
--     both are forced to change password on first login.
--  6. CHANGE BOTH PASSWORDS IMMEDIATELY (see note above the
--     seed data below) — this file, with these exact hashes, has
--     been shared over chat, so treat them as already public.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. MASTER / CONFIGURATION TABLES
-- ============================================================

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    head_employee_id INT NULL,
    parent_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    pincode VARCHAR(10),
    latitude DECIMAL(10,8) NULL COMMENT 'Office latitude for geofencing',
    longitude DECIMAL(11,8) NULL COMMENT 'Office longitude for geofencing',
    geofence_radius INT DEFAULT 25 COMMENT 'Geofence radius in meters for mobile check-in/out',
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    location_id INT,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (location_id) REFERENCES locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE designations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    department_id INT,
    grade_id INT,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (grade_id) REFERENCES grades(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    ifsc_prefix VARCHAR(10),
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE document_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    is_mandatory TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE skills_master (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leaving_reasons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reason VARCHAR(100) NOT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shifts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    grace_in_minutes INT DEFAULT 15,
    break_minutes INT DEFAULT 30,
    work_hours DECIMAL(4,2) DEFAULT 8.00,
    is_night_shift TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE muster_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE off_day_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sunday TINYINT(1) DEFAULT 1,
    monday TINYINT(1) DEFAULT 0,
    tuesday TINYINT(1) DEFAULT 0,
    wednesday TINYINT(1) DEFAULT 0,
    thursday TINYINT(1) DEFAULT 0,
    friday TINYINT(1) DEFAULT 0,
    saturday TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE holiday_calendar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    holiday_date DATE NOT NULL,
    location_id INT NULL,
    is_national TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (location_id) REFERENCES locations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 2. SALARY / LEAVE / ADVANCE / TAX CONFIG
-- ============================================================

CREATE TABLE salary_heads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(30) UNIQUE NOT NULL,
    type ENUM('earning','deduction','employer_contribution') NOT NULL,
    calculation_type ENUM('fixed','percentage','formula') DEFAULT 'fixed',
    percentage_of VARCHAR(50) NULL,
    formula TEXT NULL,
    is_taxable TINYINT(1) DEFAULT 0,
    is_pf_applicable TINYINT(1) DEFAULT 0,
    is_esi_applicable TINYINT(1) DEFAULT 0,
    display_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO salary_heads (name, code, type, calculation_type, is_taxable, display_order) VALUES
('Basic Salary', 'BASIC', 'earning', 'fixed', 1, 1),
('House Rent Allowance', 'HRA', 'earning', 'percentage', 1, 2),
('Dearness Allowance', 'DA', 'earning', 'percentage', 1, 3),
('Conveyance Allowance', 'CONV', 'earning', 'fixed', 0, 4),
('Medical Allowance', 'MED', 'earning', 'fixed', 0, 5),
('Special Allowance', 'SPEC', 'earning', 'fixed', 1, 6),
('Incentive', 'INCV', 'earning', 'fixed', 1, 7),
('Bonus', 'BONUS', 'earning', 'fixed', 1, 8),
('LTA', 'LTA', 'earning', 'fixed', 0, 9),
('Arrears', 'ARR', 'earning', 'fixed', 1, 10),
('PF Employee', 'PF_EMP', 'deduction', 'percentage', 0, 20),
('PF Employer', 'PF_ER', 'employer_contribution', 'percentage', 0, 21),
('ESI Employee', 'ESI_EMP', 'deduction', 'percentage', 0, 22),
('ESI Employer', 'ESI_ER', 'employer_contribution', 'percentage', 0, 23),
('Professional Tax', 'PT', 'deduction', 'fixed', 0, 24),
('Income Tax', 'TDS', 'deduction', 'fixed', 0, 25),
('Advance Recovery', 'ADV_REC', 'deduction', 'fixed', 0, 26),
('Loan Recovery', 'LOAN_REC', 'deduction', 'fixed', 0, 27);

CREATE TABLE leave_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    days_per_year INT DEFAULT 0,
    carry_forward TINYINT(1) DEFAULT 0,
    max_carry_forward INT DEFAULT 0,
    encashable TINYINT(1) DEFAULT 0,
    half_day_allowed TINYINT(1) DEFAULT 1,
    min_notice_days INT DEFAULT 0,
    is_paid TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO leave_types (name, code, days_per_year, carry_forward, encashable, is_paid) VALUES
('Casual Leave', 'CL', 12, 0, 0, 1),
('Sick Leave', 'SL', 12, 0, 0, 1),
('Annual Leave', 'AL', 24, 1, 1, 1),
('Maternity Leave', 'ML', 180, 0, 0, 1),
('Paternity Leave', 'PL', 15, 0, 0, 1),
('Compensatory Off', 'CO', 0, 0, 0, 1),
('Loss of Pay', 'LOP', 0, 0, 0, 0),
('Marriage Leave', 'MAR', 3, 0, 0, 1);

CREATE TABLE advance_heads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    max_amount DECIMAL(12,2) DEFAULT 0,
    max_installments INT DEFAULT 12,
    interest_rate DECIMAL(5,2) DEFAULT 0,
    interest_type ENUM('simple','compound') DEFAULT 'simple',
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO advance_heads (name, code, max_amount, max_installments, interest_rate) VALUES
('Salary Advance', 'SAL_ADV', 100000.00, 12, 0),
('Medical Advance', 'MED_ADV', 50000.00, 6, 0),
('Education Advance', 'EDU_ADV', 200000.00, 24, 8),
('Vehicle Advance', 'VEH_ADV', 500000.00, 36, 10),
('Home Loan Advance', 'HOME_ADV', 1000000.00, 60, 12);

CREATE TABLE tax_slabs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    financial_year VARCHAR(10) NOT NULL,
    regime ENUM('new','old') DEFAULT 'new',
    income_from DECIMAL(12,2) DEFAULT 0,
    income_to DECIMAL(12,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tax_slabs (financial_year, regime, income_from, income_to, tax_rate) VALUES
('2025-26','new',       0,   300000,  0),
('2025-26','new',  300001,   700000,  5),
('2025-26','new',  700001,  1000000, 10),
('2025-26','new', 1000001,  1200000, 15),
('2025-26','new', 1200001,  1500000, 20),
('2025-26','new', 1500001, 99999999, 30),
('2025-26','old',       0,   250000,  0),
('2025-26','old',  250001,   500000,  5),
('2025-26','old',  500001,  1000000, 20),
('2025-26','old', 1000001, 99999999, 30),
('2026-27','new',       0,   300000,  0),
('2026-27','new',  300001,   700000,  5),
('2026-27','new',  700001,  1000000, 10),
('2026-27','new', 1000001,  1200000, 15),
('2026-27','new', 1200001,  1500000, 20),
('2026-27','new', 1500001, 99999999, 30),
('2026-27','old',       0,   250000,  0),
('2026-27','old',  250001,   500000,  5),
('2026-27','old',  500001,  1000000, 20),
('2026-27','old', 1000001, 99999999, 30);

CREATE TABLE tds_heads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    max_limit DECIMAL(12,2) DEFAULT 0,
    is_old_regime TINYINT(1) DEFAULT 1,
    is_new_regime TINYINT(1) DEFAULT 0,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tds_heads (section, name, max_limit, is_old_regime, is_new_regime) VALUES
('80C',                'LIC / PPF / ELSS / EPF / NSC / Tax Saver FD', 150000, 1, 0),
('80CCD(1B)',          'NPS Additional Contribution',                  50000, 1, 0),
('80D',                'Medical Insurance Premium',                    25000, 1, 0),
('80E',                'Education Loan Interest',                          0, 1, 0),
('24(b)',              'Home Loan Interest (Self Occupied)',          200000, 1, 0),
('80G',                'Donations to Charitable Institutions',             0, 1, 0),
('HRA',                'House Rent Allowance Exemption',                   0, 1, 0),
('LTA',                'Leave Travel Allowance Exemption',                 0, 1, 0),
('80TTA',              'Savings Account Interest',                     10000, 1, 0),
('Standard Deduction', 'Standard Deduction',                           50000, 1, 1);

CREATE TABLE pf_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    establishment_id VARCHAR(50),
    establishment_name VARCHAR(150),
    pf_employee_rate DECIMAL(5,2) DEFAULT 12.00,
    pf_employer_rate DECIMAL(5,2) DEFAULT 12.00,
    eps_rate DECIMAL(5,2) DEFAULT 8.33,
    edli_rate DECIMAL(5,2) DEFAULT 0.50,
    admin_rate DECIMAL(5,2) DEFAULT 0.50,
    pf_wage_ceiling DECIMAL(10,2) DEFAULT 15000,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pf_setup (establishment_id, establishment_name) VALUES ('TNMAS1234567', 'PeopleCore Technologies Pvt Ltd');

CREATE TABLE esi_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    establishment_code VARCHAR(50),
    employee_rate DECIMAL(5,2) DEFAULT 0.75,
    employer_rate DECIMAL(5,2) DEFAULT 3.25,
    wage_ceiling DECIMAL(10,2) DEFAULT 21000,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO esi_setup (establishment_code) VALUES ('ESI1234567890');

CREATE TABLE pt_slabs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    state VARCHAR(50) NOT NULL,
    income_from DECIMAL(10,2) DEFAULT 0,
    income_to DECIMAL(10,2) DEFAULT 0,
    monthly_pt DECIMAL(8,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pt_slabs (state, income_from, income_to, monthly_pt) VALUES
('Tamil Nadu', 0, 3500, 0),
('Tamil Nadu', 3501, 5000, 45),
('Tamil Nadu', 5001, 7500, 75),
('Tamil Nadu', 7501, 10000, 112),
('Tamil Nadu', 10001, 12500, 125),
('Tamil Nadu', 12501, 99999999, 208);

CREATE TABLE gratuity_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    min_service_years INT DEFAULT 5,
    formula_type ENUM('15_26','formula') DEFAULT '15_26',
    max_gratuity_amount DECIMAL(14,2) DEFAULT 2000000,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO gratuity_setup (id, min_service_years, formula_type, max_gratuity_amount, is_active) VALUES (1, 5, '15_26', 2000000, 1);

CREATE TABLE bonus_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    type ENUM('festival','performance','annual') DEFAULT 'annual',
    calculation_type ENUM('fixed','percentage') DEFAULT 'percentage',
    percentage_of VARCHAR(50) DEFAULT 'BASIC',
    percentage DECIMAL(5,2) DEFAULT 8.33,
    max_amount DECIMAL(12,2) DEFAULT 0,
    financial_year VARCHAR(10),
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lta_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cycles_in_4_years INT DEFAULT 2,
    max_amount_per_cycle DECIMAL(12,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO lta_setup (id, cycles_in_4_years, max_amount_per_cycle, is_active) VALUES (1, 2, 0, 1);

CREATE TABLE expense_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20),
    requires_bill TINYINT(1) DEFAULT 1,
    max_amount DECIMAL(10,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO expense_types (name, code, requires_bill, max_amount) VALUES
('Travel', 'TRAVEL', 1, 50000),
('Food', 'FOOD', 0, 1000),
('Accommodation', 'ACCOM', 1, 5000),
('Communication', 'COMM', 1, 2000),
('Office Supplies', 'OFFICE', 1, 5000),
('Other', 'OTHER', 1, 10000);

-- ============================================================
-- 3. ORGANIZATIONS & COMPANIES (multi-tenant / vendor licensing)
-- ============================================================

CREATE TABLE organizations (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(150) NOT NULL,
    licensed_users INT NOT NULL DEFAULT 10,
    cost_per_user  DECIMAL(10,2) NOT NULL DEFAULT 0,
    status         ENUM('active','suspended') DEFAULT 'active',
    contact_name   VARCHAR(100) NULL,
    contact_phone  VARCHAR(20)  NULL,
    contact_email  VARCHAR(100) NULL,
    notes          TEXT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    code VARCHAR(20) NOT NULL DEFAULT '',
    license_seats INT NOT NULL DEFAULT 10,
    organization_id INT NOT NULL,
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    country VARCHAR(50) DEFAULT 'India',
    phone VARCHAR(20),
    email VARCHAR(100),
    website VARCHAR(100),
    pan VARCHAR(20),
    tan VARCHAR(20),
    gst VARCHAR(20),
    pf_number VARCHAR(30),
    esic_number VARCHAR(30),
    logo_path VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_code (code),
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO organizations (id, name, licensed_users, cost_per_user, status) VALUES (1, 'Default Organization', 10, 0, 'active');
INSERT INTO companies (id, name, code, organization_id, is_active) VALUES (1, 'Default Company', 'DEFCO', 1, 1);

CREATE TABLE company_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    phone VARCHAR(20),
    email VARCHAR(100),
    website VARCHAR(100),
    pan VARCHAR(20),
    tan VARCHAR(20),
    pf_number VARCHAR(30),
    esic_number VARCHAR(30),
    logo_path VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO company_settings (id, company_name) VALUES (1, 'Your Company Name');

CREATE TABLE permission_policy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    max_hours_per_month DECIMAL(4,1) DEFAULT 4.0,
    min_duration_minutes INT DEFAULT 30,
    max_duration_minutes INT DEFAULT 120,
    late_grace_minutes INT DEFAULT 15,
    late_to_leave_days INT DEFAULT 2,
    late_to_lop_days INT DEFAULT 3,
    permission_leave_type_id INT,
    lop_leave_type_id INT,
    is_active TINYINT(1) DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permission_policy (id, company_id, max_hours_per_month) VALUES (1, 1, 4.0);

-- ============================================================
-- 4. EMPLOYEES
-- ============================================================

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    company_id INT DEFAULT 1,
    -- Personal
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    gender ENUM('male','female','other'),
    blood_group VARCHAR(5),
    marital_status ENUM('single','married','divorced','widowed'),
    marriage_anniversary_date DATE NULL,
    nationality VARCHAR(50) DEFAULT 'Indian',
    aadhaar_number VARCHAR(12),
    pan_number VARCHAR(10),
    passport_number VARCHAR(20),
    driving_licence VARCHAR(20),
    photo VARCHAR(255),
    -- Professional
    date_of_joining DATE NOT NULL,
    department_id INT,
    designation_id INT,
    grade_id INT,
    category_id INT,
    location_id INT,
    branch_id INT,
    shift_id INT,
    muster_type_id INT,
    off_day_template_id INT,
    reporting_manager_id INT NULL,
    work_type ENUM('full_time','part_time','contract') DEFAULT 'full_time',
    employment_type ENUM('permanent','probation','contract','intern') DEFAULT 'permanent',
    probation_months INT DEFAULT 6,
    confirmation_date DATE NULL,
    -- Contact
    personal_email VARCHAR(100),
    official_email VARCHAR(100),
    mobile VARCHAR(15),
    alternate_phone VARCHAR(15),
    residential_address TEXT,
    residential_city VARCHAR(100),
    residential_state VARCHAR(100),
    residential_pincode VARCHAR(10),
    permanent_address TEXT,
    permanent_city VARCHAR(50),
    permanent_state VARCHAR(50),
    permanent_pincode VARCHAR(10),
    -- Emergency
    emergency_name VARCHAR(100),
    emergency_relation VARCHAR(50),
    emergency_phone VARCHAR(15),
    -- Compliance flags
    pf_applicable TINYINT(1) DEFAULT 1,
    esi_applicable TINYINT(1) DEFAULT 1,
    pt_applicable TINYINT(1) DEFAULT 1,
    tds_applicable TINYINT(1) DEFAULT 1,
    bonus_eligible TINYINT(1) DEFAULT 1,
    gratuity_eligible TINYINT(1) DEFAULT 1,
    lta_eligible TINYINT(1) DEFAULT 1,
    -- PF/ESI details
    uan_number VARCHAR(20),
    pf_number VARCHAR(30),
    esi_number VARCHAR(30),
    esic_number VARCHAR(20),
    tax_regime ENUM('new','old') DEFAULT 'new',
    state_of_work VARCHAR(50),
    -- Status
    status ENUM('active','inactive','on_leave','suspended','resigned','terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (designation_id) REFERENCES designations(id),
    FOREIGN KEY (grade_id) REFERENCES grades(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    bank_id INT,
    account_holder_name VARCHAR(100),
    account_number VARCHAR(30),
    ifsc_code VARCHAR(20),
    branch_name VARCHAR(100),
    account_type ENUM('savings','current') DEFAULT 'savings',
    upi_id VARCHAR(50),
    payment_mode ENUM('bank_transfer','cash','cheque','upi') DEFAULT 'bank_transfer',
    is_primary TINYINT(1) DEFAULT 1,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (bank_id) REFERENCES banks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    document_type_id INT,
    document_name VARCHAR(100),
    file_path VARCHAR(255),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (document_type_id) REFERENCES document_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_skills (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    skill_id INT NOT NULL,
    proficiency ENUM('beginner','intermediate','advanced','expert') DEFAULT 'intermediate',
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (skill_id) REFERENCES skills_master(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    transfer_date DATE NOT NULL,
    from_department_id INT,
    to_department_id INT,
    from_designation_id INT,
    to_designation_id INT,
    from_location_id INT,
    to_location_id INT,
    reason TEXT,
    status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
    approved_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 5. SALARY STRUCTURE
-- ============================================================

CREATE TABLE employee_salary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL UNIQUE,
    ctc_annual DECIMAL(12,2) DEFAULT 0,
    gross_monthly DECIMAL(12,2) DEFAULT 0,
    effective_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_salary_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    salary_head_id INT NOT NULL,
    amount DECIMAL(12,2) DEFAULT 0,
    percentage DECIMAL(5,2) DEFAULT 0,
    effective_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (salary_head_id) REFERENCES salary_heads(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 6. LEAVE
-- ============================================================

CREATE TABLE employee_leave_balance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    year INT NOT NULL,
    allocated INT DEFAULT 0,
    used INT DEFAULT 0,
    carry_forward INT DEFAULT 0,
    UNIQUE KEY (employee_id, leave_type_id, year),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leave_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    days DECIMAL(4,1) DEFAULT 1,
    is_half_day TINYINT(1) DEFAULT 0,
    reason TEXT,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leave_encashment_log (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    employee_id    INT NOT NULL,
    leave_type_id  INT NOT NULL,
    days           DECIMAL(4,1) NOT NULL,
    amount         DECIMAL(12,2) NOT NULL,
    processed_by   INT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 7. ATTENDANCE / MUSTER / ON DUTY / PERMISSIONS
-- ============================================================

CREATE TABLE attendance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    log_date DATE NOT NULL,
    in_time DATETIME NULL,
    out_time DATETIME NULL,
    source ENUM('biometric','mobile_app','manual','web') DEFAULT 'manual',
    device_id VARCHAR(50) NULL,
    latitude DECIMAL(10,8) NULL,
    longitude DECIMAL(11,8) NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_emp_date (employee_id, log_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE muster (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    muster_date DATE NOT NULL,
    status ENUM('P','A','H','L','WO','PH','CO','LOP','OD') DEFAULT 'A',
    in_time TIME NULL,
    out_time TIME NULL,
    work_hours DECIMAL(4,2) DEFAULT 0,
    ot_hours DECIMAL(4,2) DEFAULT 0,
    leave_type_id INT NULL,
    is_late TINYINT(1) DEFAULT 0,
    late_minutes INT DEFAULT 0,
    is_early_exit TINYINT(1) DEFAULT 0,
    is_locked TINYINT(1) DEFAULT 0,
    modified_by INT NULL,
    remarks VARCHAR(255),
    UNIQUE KEY (employee_id, muster_date),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE muster_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    present_days DECIMAL(5,1) DEFAULT 0,
    absent_days DECIMAL(5,1) DEFAULT 0,
    half_days DECIMAL(5,1) DEFAULT 0,
    leave_days DECIMAL(5,1) DEFAULT 0,
    week_off_days INT DEFAULT 0,
    holidays INT DEFAULT 0,
    lop_days DECIMAL(5,1) DEFAULT 0,
    od_days DECIMAL(5,1) DEFAULT 0,
    ot_hours DECIMAL(6,2) DEFAULT 0,
    late_count INT DEFAULT 0,
    is_locked TINYINT(1) DEFAULT 0,
    UNIQUE KEY (employee_id, month, year),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employee_weekoff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    off_day_template_id INT,
    effective_from DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (off_day_template_id) REFERENCES off_day_templates(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comp_off (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    worked_date DATE NOT NULL,
    availed_date DATE NULL,
    status ENUM('pending','approved','availed','expired') DEFAULT 'pending',
    approved_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permission_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    permission_date DATE NOT NULL,
    from_time TIME NOT NULL,
    to_time TIME NOT NULL,
    duration_minutes INT DEFAULT 0,
    reason VARCHAR(255),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at DATETIME NULL,
    remarks VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE on_duty_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    is_full_day TINYINT(1) NOT NULL DEFAULT 1,
    from_time TIME NULL,
    to_time TIME NULL,
    days DECIMAL(4,1) DEFAULT 1,
    purpose VARCHAR(150) NOT NULL,
    location VARCHAR(150),
    reason TEXT,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_on_duty_employee_status ON on_duty_requests(employee_id, status);
CREATE INDEX idx_on_duty_dates ON on_duty_requests(from_date, to_date);

CREATE TABLE late_regularizations (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NOT NULL,
    date          DATE NOT NULL,
    late_minutes  INT DEFAULT 0,
    reason        TEXT NOT NULL,
    status        ENUM('pending','approved','rejected') DEFAULT 'pending',
    approved_by   INT NULL,
    approved_at   DATETIME NULL,
    remarks       TEXT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY uq_emp_date (employee_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_late_reg_status ON late_regularizations(status);
CREATE INDEX idx_late_reg_date   ON late_regularizations(date);

CREATE TABLE muster_modification_requests (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NOT NULL,
    date          DATE NOT NULL,
    time          TIME NULL,
    issue_type    ENUM('forgot_signin','forgot_signout','network_issue','system_problem','others') NOT NULL,
    reason        TEXT NULL,
    status        ENUM('pending','approved','rejected') DEFAULT 'pending',
    approved_by   INT NULL,
    approved_at   DATETIME NULL,
    remarks       TEXT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_muster_mod_status ON muster_modification_requests(status);
CREATE INDEX idx_muster_mod_date   ON muster_modification_requests(date);

CREATE TABLE biometric_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    uploaded_by INT,
    upload_date DATE NOT NULL,
    total_records INT DEFAULT 0,
    processed INT DEFAULT 0,
    errors INT DEFAULT 0,
    status ENUM('pending','processing','completed','failed') DEFAULT 'pending',
    error_log TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 8. PAYROLL
-- ============================================================

CREATE TABLE payroll_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    month INT NOT NULL,
    year INT NOT NULL,
    run_date DATE,
    status ENUM('draft','processed','posted','disbursed') DEFAULT 'draft',
    total_employees INT DEFAULT 0,
    total_gross DECIMAL(14,2) DEFAULT 0,
    total_deductions DECIMAL(14,2) DEFAULT 0,
    total_net DECIMAL(14,2) DEFAULT 0,
    processed_by INT NULL,
    posted_by INT NULL,
    posted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_month_year (company_id, month, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payroll_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT NOT NULL,
    employee_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    present_days DECIMAL(5,1) DEFAULT 0,
    lop_days DECIMAL(5,1) DEFAULT 0,
    basic DECIMAL(12,2) DEFAULT 0,
    hra DECIMAL(12,2) DEFAULT 0,
    da DECIMAL(12,2) DEFAULT 0,
    conveyance DECIMAL(12,2) DEFAULT 0,
    medical DECIMAL(12,2) DEFAULT 0,
    special_allowance DECIMAL(12,2) DEFAULT 0,
    lta DECIMAL(12,2) DEFAULT 0,
    incentive DECIMAL(12,2) DEFAULT 0,
    bonus DECIMAL(12,2) DEFAULT 0,
    arrears DECIMAL(12,2) DEFAULT 0,
    other_earnings DECIMAL(12,2) DEFAULT 0,
    gross_salary DECIMAL(12,2) DEFAULT 0,
    pf_employee DECIMAL(12,2) DEFAULT 0,
    esi_employee DECIMAL(12,2) DEFAULT 0,
    professional_tax DECIMAL(12,2) DEFAULT 0,
    income_tax DECIMAL(12,2) DEFAULT 0,
    advance_recovery DECIMAL(12,2) DEFAULT 0,
    loan_recovery DECIMAL(12,2) DEFAULT 0,
    other_deductions DECIMAL(12,2) DEFAULT 0,
    total_deductions DECIMAL(12,2) DEFAULT 0,
    pf_employer DECIMAL(12,2) DEFAULT 0,
    esi_employer DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) DEFAULT 0,
    payment_status ENUM('pending','paid','failed') DEFAULT 'pending',
    FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE arrear_calculations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    from_month INT,
    from_year INT,
    to_month INT,
    to_year INT,
    arrear_amount DECIMAL(12,2) DEFAULT 0,
    reason TEXT,
    status ENUM('pending','approved','processed') DEFAULT 'pending',
    payroll_run_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 9. ADVANCES / LOANS / INVESTMENT / TDS
-- ============================================================

CREATE TABLE advance_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    advance_head_id INT NOT NULL,
    application_date DATE NOT NULL,
    amount_requested DECIMAL(12,2) NOT NULL,
    amount_approved DECIMAL(12,2) DEFAULT 0,
    installments INT DEFAULT 1,
    interest_rate DECIMAL(5,2) DEFAULT 0,
    total_repayable DECIMAL(12,2) DEFAULT 0,
    monthly_installment DECIMAL(12,2) DEFAULT 0,
    purpose TEXT,
    status ENUM('pending','approved','rejected','active','closed') DEFAULT 'pending',
    approved_by INT NULL,
    approved_date DATE NULL,
    start_month INT NULL,
    start_year INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (advance_head_id) REFERENCES advance_heads(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE advance_repayments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    advance_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    installment_no INT NOT NULL,
    principal_amount DECIMAL(12,2) DEFAULT 0,
    interest_amount DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    balance_after DECIMAL(12,2) DEFAULT 0,
    status ENUM('pending','deducted') DEFAULT 'pending',
    payroll_run_id INT NULL,
    FOREIGN KEY (advance_id) REFERENCES advance_applications(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE investment_declarations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    financial_year VARCHAR(10) NOT NULL,
    tds_head_id INT NOT NULL,
    declared_amount DECIMAL(12,2) DEFAULT 0,
    proof_submitted TINYINT(1) DEFAULT 0,
    verified_amount DECIMAL(12,2) DEFAULT 0,
    status ENUM('submitted','verified','rejected') DEFAULT 'submitted',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (tds_head_id) REFERENCES tds_heads(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tds_calculations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    financial_year VARCHAR(10) NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    gross_income DECIMAL(14,2) DEFAULT 0,
    total_deductions DECIMAL(14,2) DEFAULT 0,
    net_taxable_income DECIMAL(14,2) DEFAULT 0,
    tax_liability DECIMAL(14,2) DEFAULT 0,
    tds_this_month DECIMAL(12,2) DEFAULT 0,
    tds_deducted_ytd DECIMAL(14,2) DEFAULT 0,
    UNIQUE KEY (employee_id, financial_year, month),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE adb_rules (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    company_id      INT NOT NULL,
    tds_head_id     INT NOT NULL,
    name            VARCHAR(150) NOT NULL,
    applicable_to   ENUM('all','category','grade') DEFAULT 'all',
    category_id     INT NULL,
    grade_id        INT NULL,
    basis_type      ENUM('flat','percentage') DEFAULT 'flat',
    flat_amount     DECIMAL(12,2) DEFAULT 0,
    percentage      DECIMAL(5,2) DEFAULT 0,
    salary_head_id  INT NULL,
    financial_year  VARCHAR(10) NOT NULL,
    is_active       TINYINT(1) DEFAULT 1,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tds_head_id)    REFERENCES tds_heads(id),
    FOREIGN KEY (category_id)    REFERENCES categories(id),
    FOREIGN KEY (grade_id)       REFERENCES grades(id),
    FOREIGN KEY (salary_head_id) REFERENCES salary_heads(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_adb_rules_company ON adb_rules(company_id, financial_year);

-- ============================================================
-- 10. SEPARATION (Full & Final Settlement)
-- ============================================================

CREATE TABLE separations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL UNIQUE,
    resignation_date DATE,
    last_working_date DATE,
    leaving_reason_id INT,
    notice_period_days INT DEFAULT 0,
    notice_served_days INT DEFAULT 0,
    notice_pay_recovery DECIMAL(12,2) DEFAULT 0,
    salary_payable DECIMAL(12,2) DEFAULT 0,
    leave_encashment DECIMAL(12,2) DEFAULT 0,
    gratuity_amount DECIMAL(12,2) DEFAULT 0,
    bonus_payable DECIMAL(12,2) DEFAULT 0,
    total_payable DECIMAL(12,2) DEFAULT 0,
    status ENUM('initiated','processing','completed') DEFAULT 'initiated',
    cleared_date DATE NULL,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leaving_reason_id) REFERENCES leaving_reasons(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE separation_clearance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    separation_id INT NOT NULL,
    item VARCHAR(100) NOT NULL,
    is_cleared TINYINT(1) DEFAULT 0,
    cleared_by INT NULL,
    cleared_date DATE NULL,
    remarks VARCHAR(255),
    FOREIGN KEY (separation_id) REFERENCES separations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 11. RECRUITMENT
-- ============================================================

CREATE TABLE job_openings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    title VARCHAR(100) NOT NULL,
    department_id INT,
    designation_id INT,
    location_id INT,
    openings INT DEFAULT 1,
    experience_min INT DEFAULT 0,
    experience_max INT DEFAULT 10,
    description TEXT,
    requirements TEXT,
    status ENUM('open','closed','on_hold') DEFAULT 'open',
    posted_date DATE,
    closing_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE job_applicants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    resume_path VARCHAR(255),
    experience_years INT DEFAULT 0,
    current_ctc DECIMAL(12,2) DEFAULT 0,
    expected_ctc DECIMAL(12,2) DEFAULT 0,
    notice_period_days INT DEFAULT 30,
    status ENUM('applied','screened','interview','offer','joined','rejected') DEFAULT 'applied',
    applied_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES job_openings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE interviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    applicant_id INT NOT NULL,
    round INT DEFAULT 1,
    interview_date DATETIME,
    interviewer_id INT NULL,
    mode ENUM('in_person','video','phone') DEFAULT 'in_person',
    feedback TEXT,
    rating TINYINT DEFAULT 0,
    status ENUM('scheduled','completed','cancelled','no_show') DEFAULT 'scheduled',
    FOREIGN KEY (applicant_id) REFERENCES job_applicants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 12. TRAINING
-- ============================================================

CREATE TABLE trainers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    specialization TEXT,
    type ENUM('internal','external') DEFAULT 'internal',
    employee_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE training_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    title VARCHAR(150) NOT NULL,
    description TEXT,
    trainer_id INT,
    from_date DATE,
    to_date DATE,
    duration_hours INT DEFAULT 8,
    mode ENUM('classroom','online','hybrid') DEFAULT 'classroom',
    location VARCHAR(150),
    max_participants INT DEFAULT 20,
    status ENUM('planned','active','completed','cancelled') DEFAULT 'planned',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (trainer_id) REFERENCES trainers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE training_enrollment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    training_id INT NOT NULL,
    employee_id INT NOT NULL,
    enrolled_date DATE,
    attendance_status ENUM('enrolled','attended','absent','completed') DEFAULT 'enrolled',
    score DECIMAL(5,2) DEFAULT 0,
    certificate_issued TINYINT(1) DEFAULT 0,
    UNIQUE KEY (training_id, employee_id),
    FOREIGN KEY (training_id) REFERENCES training_programs(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 13. APPRAISAL
-- ============================================================

CREATE TABLE appraisal_cycles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    name VARCHAR(100) NOT NULL,
    financial_year VARCHAR(10),
    from_date DATE,
    to_date DATE,
    self_appraisal_deadline DATE,
    manager_review_deadline DATE,
    status ENUM('planned','active','completed') DEFAULT 'planned',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE appraisals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cycle_id INT NOT NULL,
    employee_id INT NOT NULL,
    manager_id INT NULL,
    self_rating DECIMAL(3,1) DEFAULT 0,
    manager_rating DECIMAL(3,1) DEFAULT 0,
    final_rating DECIMAL(3,1) DEFAULT 0,
    rating_label ENUM('outstanding','exceeds','meets','needs_improvement','poor') NULL,
    increment_percentage DECIMAL(5,2) DEFAULT 0,
    self_comments TEXT,
    manager_comments TEXT,
    status ENUM('pending','self_done','manager_reviewed','completed') DEFAULT 'pending',
    FOREIGN KEY (cycle_id) REFERENCES appraisal_cycles(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 14. EXPENSE
-- ============================================================

CREATE TABLE expense_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    expense_type_id INT,
    expense_date DATE,
    amount DECIMAL(12,2) DEFAULT 0,
    description TEXT,
    attachment VARCHAR(255),
    project_code VARCHAR(50),
    status ENUM('pending','approved','rejected','paid') DEFAULT 'pending',
    approved_by INT NULL,
    approved_date DATE NULL,
    payment_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (expense_type_id) REFERENCES expense_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 15. NOTICE BOARD / TICKETS
-- ============================================================

CREATE TABLE notices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    category ENUM('general','hr','payroll','holiday','urgent') DEFAULT 'general',
    target_departments JSON NULL,
    attachment VARCHAR(255),
    published_by INT NULL,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATE NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notice_board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    title VARCHAR(200) NOT NULL,
    category VARCHAR(50) DEFAULT 'General',
    content TEXT NOT NULL,
    priority ENUM('low','medium','high') DEFAULT 'medium',
    visible_to ENUM('all','hr','admin','management') DEFAULT 'all',
    expiry_date DATE,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL DEFAULT 1,
    ticket_number VARCHAR(20) UNIQUE NOT NULL,
    employee_id INT,
    category VARCHAR(50),
    subject VARCHAR(200) NOT NULL,
    description TEXT,
    priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
    status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    closed_at DATETIME,
    updated_at DATETIME,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    reply_by INT,
    message TEXT NOT NULL,
    is_staff TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 16. LETTER TEMPLATES
-- ============================================================

CREATE TABLE letter_templates (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    company_id  INT NOT NULL,
    type_key    VARCHAR(50) NOT NULL,
    title       VARCHAR(150) NOT NULL,
    body_html   MEDIUMTEXT NOT NULL,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_company_type (company_id, type_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 17. TIMESHEET
-- ============================================================

CREATE TABLE timesheets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    week_start DATE NOT NULL,
    week_end DATE NOT NULL,
    total_hours DECIMAL(5,2) DEFAULT 0,
    status ENUM('draft','submitted','approved','rejected') DEFAULT 'draft',
    submitted_at TIMESTAMP NULL,
    approved_by INT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE timesheet_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timesheet_id INT NOT NULL,
    entry_date DATE NOT NULL,
    project VARCHAR(100),
    task VARCHAR(200),
    hours DECIMAL(4,2) DEFAULT 0,
    description TEXT,
    FOREIGN KEY (timesheet_id) REFERENCES timesheets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 18. MOBILE APP — DEVICES, TOKENS, UPDATES, FEATURE FLAGS
-- ============================================================

CREATE TABLE mobile_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    device_id VARCHAR(100) NOT NULL,
    device_name VARCHAR(100),
    platform ENUM('android','ios') DEFAULT 'android',
    fcm_token VARCHAR(255),
    is_registered TINYINT(1) DEFAULT 1,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE app_config (
    config_key   VARCHAR(50) PRIMARY KEY,
    config_value VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bump these from the vendor console (Push Product Update card) every
-- time you release a new APK — the mobile app checks this on launch
-- and shows the "Update Now" popup if the installed build is older.
-- IMPORTANT: change update_url below to your REAL live domain once you
-- know it — http://192.168.1.113/... only worked on your local network.
INSERT INTO app_config (config_key, config_value) VALUES
    ('latest_version_code', '3'),
    ('latest_version_name', '3.0.0'),
    ('update_message', 'A new version of the app is available.'),
    ('update_url', 'https://REPLACE-WITH-YOUR-DOMAIN.com/hr_system/downloads/app-latest.apk');

CREATE TABLE product_updates (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    version     VARCHAR(30)  NOT NULL,
    title       VARCHAR(150) NOT NULL,
    message     VARCHAR(1000) NOT NULL,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE feature_flags (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    feature_key         VARCHAR(60) UNIQUE NOT NULL,
    name                VARCHAR(150) NOT NULL,
    description         VARCHAR(500) NULL,
    is_default_enabled  TINYINT(1) DEFAULT 0,
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE organization_features (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    organization_id  INT NOT NULL,
    feature_key      VARCHAR(60) NOT NULL,
    is_enabled       TINYINT(1) DEFAULT 1,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_org_feature (organization_id, feature_key),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_org_features_org ON organization_features(organization_id);

-- ============================================================
-- 19. USERS / AUTH / AUDIT
-- ============================================================

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NULL,
    company_id INT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('vendor','superadmin','admin','hr','manager','employee','management','hod','supervisor') DEFAULT 'employee',
    is_active TINYINT(1) DEFAULT 1,
    must_change_password TINYINT(1) DEFAULT 1,
    last_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE menu_rights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('superadmin','admin','hr','manager','employee','management','hod','supervisor') NOT NULL,
    module VARCHAR(50) NOT NULL,
    can_view TINYINT(1) DEFAULT 0,
    can_add TINYINT(1) DEFAULT 0,
    can_edit TINYINT(1) DEFAULT 0,
    can_delete TINYINT(1) DEFAULT 0,
    can_approve TINYINT(1) DEFAULT 0,
    UNIQUE KEY (role, module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    module VARCHAR(50),
    record_id INT,
    old_data JSON,
    new_data JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NULL,
    user_id       INT NULL,
    title         VARCHAR(150) NOT NULL,
    message       VARCHAR(500) NOT NULL,
    type          VARCHAR(30) DEFAULT 'general',
    ref_id        INT NULL,
    is_read       TINYINT(1) DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_notifications_emp_read  ON notifications(employee_id, is_read);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read);
CREATE INDEX idx_notifications_created   ON notifications(created_at);

-- ============================================================
-- 20. SEED DATA — LOGIN ACCOUNTS
-- ============================================================
-- ⚠️  security note: these exact password hashes have been shared in
-- chat, so treat both passwords as already public. Log in once, then
-- IMMEDIATELY change both — must_change_password=1 will force this
-- on first login anyway, so just follow the prompt.
--
--   admin  / Gmhpl_2025   (role: superadmin)
--   vendor / Gmhpl_2025   (role: vendor — your own vendor console login)
-- ============================================================

-- Dummy employee record for the admin login (some screens expect every
-- user to be linked to an employee row).
INSERT INTO employees (employee_id, first_name, last_name, date_of_joining, status, company_id)
VALUES ('EMP001', 'Super', 'Admin', CURDATE(), 'active', 1);

SET @admin_emp_id = LAST_INSERT_ID();

INSERT INTO users (username, password_hash, role, employee_id, company_id, must_change_password) VALUES
('admin',  '$2y$12$B5/.Nx/UEvBx7VeMxVGwZOgSD1Wgi.n3atReUk9411AO7VkR5Ucz.', 'superadmin', @admin_emp_id, 1, 1),
('vendor', '$2b$12$z0yNLfycAn0fSu6FjFlFV.TRNaAhnPARcDWrUiFsw3sW3JWz3/Rra', 'vendor',      NULL,          1, 1);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- DONE. Next: update config/config.php with your real cPanel DB
-- name/user/password/host and your real domain for BASE_URL.
-- ============================================================
