-- ============================================
-- SALARY REGISTER TABLE SETUP
-- Run this in phpMyAdmin if auto-creation fails
-- ============================================

-- Step 1: Select 'hrm2' database
USE hrm2;

-- Step 2: Create salary_register table
CREATE TABLE IF NOT EXISTS salary_register (
    register_id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    month VARCHAR(50) NOT NULL COMMENT 'e.g., January 2026',
    
    -- Details Section
    employee_name VARCHAR(255) NOT NULL,
    designation VARCHAR(100),
    employee_type VARCHAR(50) DEFAULT 'FTC',
    category VARCHAR(50) DEFAULT 'Skilled',
    status VARCHAR(20) DEFAULT 'Active',
    
    -- Employer Structure Section
    basic_employer DECIMAL(10,2) DEFAULT 0,
    vda DECIMAL(10,2) DEFAULT 0,
    special_allowance DECIMAL(10,2) DEFAULT 0,
    other_allowance_employer DECIMAL(10,2) DEFAULT 0,
    hra_employer DECIMAL(10,2) DEFAULT 0,
    
    -- Attendance Section
    days_present INT DEFAULT 0,
    days_absent INT DEFAULT 0,
    weekly_off INT DEFAULT 0,
    pay_days INT DEFAULT 0,
    
    -- Earned Salary Section
    basic_earned DECIMAL(10,2) DEFAULT 0,
    hra_earned DECIMAL(10,2) DEFAULT 0,
    other_allowance_earned DECIMAL(10,2) DEFAULT 0,
    spl_allowance DECIMAL(10,2) DEFAULT 0,
    
    -- Deductions Section
    pf DECIMAL(10,2) DEFAULT 0,
    esi DECIMAL(10,2) DEFAULT 0,
    pt DECIMAL(10,2) DEFAULT 0,
    
    -- Rebill Section
    pf_rebill DECIMAL(10,2) DEFAULT 0,
    esic_rebill DECIMAL(10,2) DEFAULT 0,
    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,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    KEY idx_site_month (site_id, month),
    KEY idx_employee (employee_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Verify table created
SHOW TABLES LIKE 'salary_register';

-- Step 4: Check table structure
DESCRIBE salary_register;

-- Success message
SELECT 'Table created successfully! You can now use the Salary Register.' AS Status;
