-- ============================================================
-- PEOPLECORE HR SYSTEM - Complete Database Schema
-- MySQL 8.0+ | UTF-8 | InnoDB
-- ============================================================

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

-- ============================================================
-- CONFIGURATION / MASTER 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
);

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),
    is_active TINYINT(1) DEFAULT 1
);

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

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

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

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

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

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

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

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

-- ============================================================
-- SHIFT & ATTENDANCE CONFIG
-- ============================================================

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

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

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

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

-- ============================================================
-- SALARY HEADS
-- ============================================================

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

-- Default salary heads
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);

-- ============================================================
-- LEAVE TYPES
-- ============================================================

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

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

-- ============================================================
-- ADVANCE HEADS
-- ============================================================

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

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

-- ============================================================
-- TDS / TAX CONFIG
-- ============================================================

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

INSERT INTO tax_slabs (financial_year, regime, income_from, income_to, tax_rate) VALUES
('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) NOT NULL,
    name VARCHAR(150) 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
);

INSERT INTO tds_heads (section, name, max_limit, is_old_regime, is_new_regime) VALUES
('80C', 'PF, PPF, LIC, ELSS, NSC, SCSS', 150000, 1, 0),
('80D', 'Medical Insurance Premium', 25000, 1, 0),
('80E', 'Education Loan Interest', 0, 1, 0),
('80G', 'Donations to Charity', 0, 1, 0),
('80TTA', 'Savings Account Interest', 10000, 1, 0),
('24B', 'Home Loan Interest', 200000, 1, 0),
('HRA', 'House Rent Allowance Exemption', 0, 1, 0),
('LTA', 'Leave Travel Allowance', 0, 1, 0),
('STD', 'Standard Deduction', 50000, 1, 1);

-- ============================================================
-- PF / ESI SETUP
-- ============================================================

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

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

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

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

-- ============================================================
-- EMPLOYEES
-- ============================================================

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,
    -- 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'),
    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(50),
    residential_state VARCHAR(50),
    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),
    tax_regime ENUM('new','old') DEFAULT 'new',
    -- 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)
);

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

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

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

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

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

-- ============================================================
-- LEAVE ASSIGNMENT & MANAGEMENT
-- ============================================================

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

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

-- Bell #2 (personal notifications) -- apply/approve/reject alerts.
-- employee_id targets a specific employee; user_id targets a login
-- account directly (used for superadmin broadcasts, since some
-- superadmin logins have no linked employee record).
-- See migration_notifications.sql for the same table if applying to an
-- existing (already-installed) database.
CREATE TABLE IF NOT EXISTS 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);

-- ============================================================
-- ATTENDANCE & MUSTER
-- ============================================================

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

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

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

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

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

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,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

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

-- ============================================================
-- PAYROLL
-- ============================================================

CREATE TABLE payroll_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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 (month, year)
);

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,
    -- Earnings
    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,
    -- Deductions
    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,
    -- Employer contributions
    pf_employer DECIMAL(12,2) DEFAULT 0,
    esi_employer DECIMAL(12,2) DEFAULT 0,
    -- Net
    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)
);

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

-- ============================================================
-- ADVANCE / LOANS
-- ============================================================

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

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

-- ============================================================
-- TDS / TAX
-- ============================================================

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

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

-- ============================================================
-- GRATUITY / BONUS / LTA
-- ============================================================

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

INSERT INTO gratuity_setup 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
);

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

INSERT INTO lta_setup VALUES (1, 2, 0, 1);

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

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

-- ============================================================
-- RECRUITMENT
-- ============================================================

CREATE TABLE job_openings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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)
);

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

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

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

CREATE TABLE training_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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)
);

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

-- ============================================================
-- APPRAISAL
-- ============================================================

CREATE TABLE appraisal_cycles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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
);

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

-- ============================================================
-- EXPENSE
-- ============================================================

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

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

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

-- ============================================================
-- NOTICE BOARD
-- ============================================================

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

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

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

-- ============================================================
-- EMPLOYEE TRANSFER
-- ============================================================

CREATE TABLE employee_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    transfer_date DATE,
    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','completed','rejected') DEFAULT 'pending',
    approved_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- ============================================================
-- MOBILE APP - DEVICES & TOKENS
-- ============================================================

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

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

-- ============================================================
-- USERS / AUTH
-- ============================================================

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin','admin','hr','manager','employee') 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)
);

-- Default superadmin  (password: Gmhpl_2025)
INSERT INTO users (username, password_hash, role, must_change_password) VALUES ('admin', '$2y$12$XehSjhz1f8BxheXRrGJP5uFTOIa3mc8PtuwCIHxcVzs/BCPf1lYJC', 'superadmin', 1);

CREATE TABLE menu_rights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role ENUM('superadmin','admin','hr','manager','employee') 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)
);

-- Audit log
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
);

-- ============================================================
-- MIGRATION: Add must_change_password (run on existing DBs)
-- ============================================================
-- ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) DEFAULT 1 AFTER is_active;
-- UPDATE users SET must_change_password = 0 WHERE last_login IS NOT NULL;
-- UPDATE users SET password_hash = '$2y$12$XehSjhz1f8BxheXRrGJP5uFTOIa3mc8PtuwCIHxcVzs/BCPf1lYJC' WHERE username = 'admin';
-- Uncomment and run the above lines on an existing database.



UPDATE users SET password_hash = '$2y$12$XehSjhz1f8BxheXRrGJP5uFTOIa3mc8PtuwCIHxcVzs/BCPf1lYJC' WHERE username = 'admin';



-- ============================================================
-- MIGRATION v2 FIXED — MySQL Workbench Compatible
-- Run the whole file at once — no errors even if columns exist
-- ============================================================

-- STEP 1: All CREATE TABLE (safe, skips if already exists)

CREATE TABLE IF NOT EXISTS 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
);

CREATE TABLE IF NOT EXISTS banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    ifsc_prefix VARCHAR(10),
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS 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) NOT NULL,
    ifsc_code VARCHAR(15),
    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 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS 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 DATETIME,
    approved_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS timesheet_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timesheet_id INT NOT NULL,
    entry_date DATE NOT NULL,
    project VARCHAR(100),
    task VARCHAR(100),
    hours DECIMAL(4,2) DEFAULT 0,
    description TEXT
);

CREATE TABLE IF NOT EXISTS arrear_calculations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    from_month INT NOT NULL,
    from_year INT NOT NULL,
    to_month INT NOT NULL,
    to_year INT NOT NULL,
    arrear_amount DECIMAL(10,2) NOT NULL,
    reason TEXT,
    status ENUM('pending','approved','paid') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bonus_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('festival','performance','annual') DEFAULT 'annual',
    calculation_type ENUM('fixed','percentage') DEFAULT 'percentage',
    percentage_of VARCHAR(20) DEFAULT 'BASIC',
    percentage DECIMAL(6,2) DEFAULT 8.33,
    max_amount DECIMAL(10,2) DEFAULT 0,
    financial_year VARCHAR(10),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS esi_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_contribution DECIMAL(5,2) DEFAULT 0.75,
    employer_contribution DECIMAL(5,2) DEFAULT 3.25,
    wage_ceiling DECIMAL(10,2) DEFAULT 21000,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS gratuity_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    min_service_years INT DEFAULT 5,
    max_gratuity_amount DECIMAL(12,2) DEFAULT 2000000,
    formula VARCHAR(20) DEFAULT '15/26',
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS lta_setup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cycles_in_4_years INT DEFAULT 2,
    max_amount_per_cycle DECIMAL(10,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS 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) NOT NULL,
    income_to DECIMAL(12,2) NOT NULL,
    tax_rate DECIMAL(5,2) NOT NULL,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS tds_heads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section VARCHAR(20) 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
);

CREATE TABLE IF NOT EXISTS 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,
    verified_amount DECIMAL(12,2),
    status ENUM('submitted','verified','rejected') DEFAULT 'submitted',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tds_calculations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    financial_year VARCHAR(10),
    gross_income DECIMAL(12,2) DEFAULT 0,
    net_taxable_income DECIMAL(12,2) DEFAULT 0,
    tax_liability DECIMAL(12,2) DEFAULT 0,
    tds_this_month DECIMAL(12,2) DEFAULT 0,
    tds_deducted_ytd DECIMAL(12,2) DEFAULT 0
);

CREATE TABLE IF NOT EXISTS 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 999999,
    pt_amount DECIMAL(8,2) DEFAULT 0,
    frequency ENUM('monthly','half_yearly','annually') DEFAULT 'monthly',
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS job_openings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    department_id INT,
    designation_id INT,
    vacancies INT DEFAULT 1,
    location_id INT,
    employment_type ENUM('full_time','part_time','contract','intern') DEFAULT 'full_time',
    experience_min INT DEFAULT 0,
    experience_max INT DEFAULT 5,
    description TEXT,
    status ENUM('open','on_hold','closed') DEFAULT 'open',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS job_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_opening_id INT NOT NULL,
    candidate_name VARCHAR(100) NOT NULL,
    candidate_email VARCHAR(100),
    candidate_phone VARCHAR(15),
    years_experience INT DEFAULT 0,
    current_company VARCHAR(100),
    current_ctc DECIMAL(10,2),
    expected_ctc DECIMAL(10,2),
    source ENUM('direct','portal','referral','consultant') DEFAULT 'direct',
    status ENUM('applied','screening','shortlisted','interview','offer','hired','rejected') DEFAULT 'applied',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS appraisal_cycles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    review_date DATE,
    status ENUM('active','completed','cancelled') DEFAULT 'active',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS appraisal_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appraisal_cycle_id INT NOT NULL,
    employee_id INT NOT NULL,
    reviewer_id INT,
    self_rating TINYINT,
    manager_rating TINYINT,
    final_rating TINYINT,
    self_comments TEXT,
    manager_comments TEXT,
    status ENUM('draft','submitted','reviewed','completed') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100),
    trainer VARCHAR(100),
    start_date DATE,
    end_date DATE,
    duration_hours INT DEFAULT 8,
    mode ENUM('online','offline','hybrid') DEFAULT 'offline',
    venue VARCHAR(200),
    max_participants INT DEFAULT 20,
    status ENUM('scheduled','ongoing','completed','cancelled') DEFAULT 'scheduled',
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_enrollments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    training_program_id INT NOT NULL,
    employee_id INT NOT NULL,
    status ENUM('enrolled','attended','completed','absent','cancelled') DEFAULT 'enrolled',
    completion_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_enrollment (training_program_id, employee_id)
);

CREATE TABLE IF NOT EXISTS expense_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    claim_date DATE NOT NULL,
    category VARCHAR(100),
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    status ENUM('pending','approved','rejected','reimbursed') DEFAULT 'pending',
    approved_by INT,
    approved_at DATETIME,
    rejection_reason TEXT,
    reimbursed_at DATETIME,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notice_board (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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
);

CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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
);

CREATE TABLE IF NOT EXISTS 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
);

CREATE TABLE IF NOT EXISTS attendance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    log_date DATE NOT NULL,
    in_time DATETIME,
    out_time DATETIME,
    source ENUM('biometric','manual','web','mobile') DEFAULT 'manual',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS muster_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    present_days DECIMAL(4,1) DEFAULT 0,
    absent_days DECIMAL(4,1) DEFAULT 0,
    leave_days DECIMAL(4,1) DEFAULT 0,
    lop_days DECIMAL(4,1) DEFAULT 0,
    od_days DECIMAL(4,1) DEFAULT 0,
    ot_hours DECIMAL(6,2) DEFAULT 0,
    late_count INT DEFAULT 0,
    UNIQUE KEY unique_summary (employee_id, month, year)
);

CREATE TABLE IF NOT EXISTS 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)
);

-- ============================================================
-- STEP 2: SEED DATA
-- ============================================================

-- Fix: use correct column names that match your existing esi_setup table
INSERT IGNORE INTO esi_setup (id, employee_rate, employer_rate, wage_ceiling)
VALUES (1, 0.75, 3.25, 21000);

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

INSERT IGNORE INTO banks (name, ifsc_prefix) VALUES
('State Bank of India','SBIN'), ('HDFC Bank','HDFC'),
('ICICI Bank','ICIC'),          ('Axis Bank','UTIB'),
('Kotak Mahindra Bank','KKBK'), ('Punjab National Bank','PUNB'),
('Bank of Baroda','BARB'),      ('Canara Bank','CNRB'),
('Union Bank of India','UBIN'), ('IDFC First Bank','IDFB'),
('Yes Bank','YESB'),            ('IndusInd Bank','INDB');

INSERT IGNORE 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);

INSERT IGNORE 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);

INSERT IGNORE INTO pt_slabs (state, income_from, income_to, monthly_pt) VALUES
('Tamil Nadu',     0, 21000,   0),
('Tamil Nadu', 21001, 999999, 200);
-- ============================================================
-- STEP 3: ADD COLUMNS TO employees TABLE
-- Uses a stored procedure so it skips columns that already exist
-- No errors, runs in one shot
-- ============================================================

DROP PROCEDURE IF EXISTS add_employee_columns;

DELIMITER $$

CREATE PROCEDURE add_employee_columns()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'lta_eligible') THEN
        ALTER TABLE employees ADD COLUMN lta_eligible TINYINT(1) DEFAULT 0;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'tax_regime') THEN
        ALTER TABLE employees ADD COLUMN tax_regime ENUM('new','old') DEFAULT 'new';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'state_of_work') THEN
        ALTER TABLE employees ADD COLUMN state_of_work VARCHAR(50);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'uan_number') THEN
        ALTER TABLE employees ADD COLUMN uan_number VARCHAR(20);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'esic_number') THEN
        ALTER TABLE employees ADD COLUMN esic_number VARCHAR(20);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'pan_number') THEN
        ALTER TABLE employees ADD COLUMN pan_number VARCHAR(15);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'aadhaar_number') THEN
        ALTER TABLE employees ADD COLUMN aadhaar_number VARCHAR(20);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'residential_address') THEN
        ALTER TABLE employees ADD COLUMN residential_address TEXT;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'residential_city') THEN
        ALTER TABLE employees ADD COLUMN residential_city VARCHAR(100);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'residential_state') THEN
        ALTER TABLE employees ADD COLUMN residential_state VARCHAR(100);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'residential_pincode') THEN
        ALTER TABLE employees ADD COLUMN residential_pincode VARCHAR(10);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'emergency_name') THEN
        ALTER TABLE employees ADD COLUMN emergency_name VARCHAR(100);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'emergency_relation') THEN
        ALTER TABLE employees ADD COLUMN emergency_relation VARCHAR(50);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'emergency_phone') THEN
        ALTER TABLE employees ADD COLUMN emergency_phone VARCHAR(15);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'alternate_phone') THEN
        ALTER TABLE employees ADD COLUMN alternate_phone VARCHAR(15);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees' AND COLUMN_NAME = 'personal_email') THEN
        ALTER TABLE employees ADD COLUMN personal_email VARCHAR(100);
    END IF;
END$$

DELIMITER ;

CALL add_employee_columns();
DROP PROCEDURE IF EXISTS add_employee_columns;
