CREATE TABLE IF NOT EXISTS payroll_rule_sets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    lunch_minutes INT UNSIGNED NOT NULL DEFAULT 60,
    working_lunch_paid TINYINT(1) NOT NULL DEFAULT 1,
    notes VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_prs_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_rule_set_days (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_set_id BIGINT UNSIGNED NOT NULL,
    day_type ENUM('WEEKDAY','SATURDAY','SUNDAY','PUBLIC_HOLIDAY') NOT NULL,
    normal_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    ot_15_start_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    ot_20_start_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    all_hours_ot_20 TINYINT(1) NOT NULL DEFAULT 0,
    is_day_off TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_prsd_rule_day (rule_set_id, day_type),
    KEY idx_prsd_rule (rule_set_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS staff_payroll_rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    rule_set_id BIGINT UNSIGNED NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_spr_user_dates (user_id, effective_from, effective_to),
    KEY idx_spr_rule_set (rule_set_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_holidays (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    holiday_date DATE NOT NULL,
    title VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_company_holiday_date (holiday_date),
    KEY idx_company_holiday_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO payroll_rule_sets (name, is_active, lunch_minutes, working_lunch_paid, notes)
SELECT 'Standard 9 Hour', 1, 60, 1, 'Default starter rule set'
WHERE NOT EXISTS (SELECT 1 FROM payroll_rule_sets WHERE name = 'Standard 9 Hour');

SET @rule_set_id := (SELECT id FROM payroll_rule_sets WHERE name = 'Standard 9 Hour' LIMIT 1);

INSERT INTO payroll_rule_set_days (rule_set_id, day_type, normal_minutes, ot_15_start_minutes, ot_20_start_minutes, all_hours_ot_20, is_day_off)
VALUES
(@rule_set_id, 'WEEKDAY', 540, 540, 720, 0, 0),
(@rule_set_id, 'SATURDAY', 540, 540, 720, 0, 0),
(@rule_set_id, 'SUNDAY', 0, 0, 0, 1, 0),
(@rule_set_id, 'PUBLIC_HOLIDAY', 0, 0, 0, 1, 0)
ON DUPLICATE KEY UPDATE
normal_minutes = VALUES(normal_minutes),
ot_15_start_minutes = VALUES(ot_15_start_minutes),
ot_20_start_minutes = VALUES(ot_20_start_minutes),
all_hours_ot_20 = VALUES(all_hours_ot_20),
is_day_off = VALUES(is_day_off),
updated_at = CURRENT_TIMESTAMP;