USE hrms_payroll;

-- 1. Statutory Settings Table
CREATE TABLE IF NOT EXISTS statutory_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    pf_employee_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00,
    pf_employer_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00,
    pf_wage_limit DECIMAL(12,2) NOT NULL DEFAULT 15000.00,
    esi_employee_rate DECIMAL(5,2) NOT NULL DEFAULT 0.75,
    esi_employer_rate DECIMAL(5,2) NOT NULL DEFAULT 3.25,
    esi_wage_limit DECIMAL(12,2) NOT NULL DEFAULT 21000.00,
    professional_tax DECIMAL(10,2) NOT NULL DEFAULT 200.00,
    professional_tax_threshold DECIMAL(12,2) NOT NULL DEFAULT 15000.00,
    tds_enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_statutory_company (company_id)
);

-- 2. Company Settings Table
CREATE TABLE IF NOT EXISTS company_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_key (company_id, setting_key)
);

-- Seed defaults for Company 1
INSERT INTO statutory_settings (company_id, pf_employee_rate, pf_employer_rate, pf_wage_limit, esi_employee_rate, esi_employer_rate, esi_wage_limit, professional_tax, professional_tax_threshold, tds_enabled)
VALUES (1, 12.00, 12.00, 15000.00, 0.75, 3.25, 21000.00, 200.00, 15000.00, 1)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

INSERT INTO company_settings (company_id, setting_key, setting_value) VALUES
(1, 'standard_work_hours', '8'),
(1, 'late_grace_minutes', '15'),
(1, 'standard_break_minutes', '60'),
(1, 'working_days_per_month', '26'),
(1, 'currency', 'INR'),
(1, 'date_format', 'd-m-Y'),
(1, 'timezone', 'Asia/Kolkata')
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

INSERT INTO ot_policies (company_id, name, regular_multiplier, off_day_multiplier, holiday_multiplier, is_default)
VALUES (1, 'Default Company Policy', 1.50, 2.00, 2.50, 1)
ON DUPLICATE KEY UPDATE name = name;