-- Salary Register Table
-- Stores monthly salary calculations for employees

CREATE TABLE IF NOT EXISTS salary_register (
    salary_register_id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    employee_id INT NOT NULL,
    salary_month VARCHAR(50) NOT NULL COMMENT 'Format: January 2026',
    
    -- Employee Details
    employee_type VARCHAR(50) DEFAULT 'FTC',
    category VARCHAR(50) DEFAULT 'Skilled',
    
    -- Employer Structure
    basic DECIMAL(10,2) DEFAULT 0,
    vda DECIMAL(10,2) DEFAULT 0,
    special_allowance DECIMAL(10,2) DEFAULT 0,
    other_allowance DECIMAL(10,2) DEFAULT 0,
    hra DECIMAL(10,2) DEFAULT 0,
    total_earning DECIMAL(10,2) DEFAULT 0,
    
    -- Attendance
    present INT DEFAULT 0,
    absent INT DEFAULT 0,
    weekly_off INT DEFAULT 0,
    pay_days INT DEFAULT 0,
    
    -- Earned Salary
    earned_basic DECIMAL(10,2) DEFAULT 0,
    earned_hra DECIMAL(10,2) DEFAULT 0,
    earned_other_allowance DECIMAL(10,2) DEFAULT 0,
    spl_allowance DECIMAL(10,2) DEFAULT 0,
    total_earning_earned DECIMAL(10,2) DEFAULT 0,
    
    -- Deductions
    pf DECIMAL(10,2) DEFAULT 0,
    esi DECIMAL(10,2) DEFAULT 0,
    pt DECIMAL(10,2) DEFAULT 0,
    total_deduction DECIMAL(10,2) DEFAULT 0,
    net_salary DECIMAL(10,2) DEFAULT 0,
    
    -- Rebill
    provident_fund DECIMAL(10,2) DEFAULT 0 COMMENT '12% on Basic',
    esic DECIMAL(10,2) DEFAULT 0 COMMENT '3.25% on Gross',
    leave_amount DECIMAL(10,2) DEFAULT 0,
    holiday DECIMAL(10,2) DEFAULT 0,
    bonus DECIMAL(10,2) DEFAULT 0,
    uniform DECIMAL(10,2) DEFAULT 0,
    gratuity DECIMAL(10,2) DEFAULT 0,
    food_allowance DECIMAL(10,2) DEFAULT 0,
    total_rebill DECIMAL(10,2) DEFAULT 0,
    earned_ctc DECIMAL(10,2) DEFAULT 0,
    per_day_wages DECIMAL(10,2) DEFAULT 0,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    UNIQUE KEY unique_employee_month (site_id, employee_id, salary_month),
    KEY idx_site_month (site_id, salary_month),
    KEY idx_employee (employee_id),
    
    -- Foreign Keys
    FOREIGN KEY (site_id) REFERENCES sites(site_id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
