USE hrms_payroll;

CREATE TABLE IF NOT EXISTS weekend_off_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    off_days JSON NOT NULL, -- Array of days (e.g. ["Saturday", "Sunday"])
    rotation_type ENUM('Fixed', 'Alternate', 'Rotational') DEFAULT 'Fixed',
    alt_weeks JSON NULL, -- e.g. [2, 4] for 2nd and 4th Saturday
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS shift_rotation_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    rotation_frequency_days INT UNSIGNED DEFAULT 7,
    shift_sequence JSON NOT NULL, -- Ordered array of shift IDs (e.g. [1, 2, 3])
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS employee_shift_rotations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    rotation_rule_id BIGINT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    current_index INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (rotation_rule_id) REFERENCES shift_rotation_rules(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS shift_swap_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_employee_id BIGINT UNSIGNED NOT NULL,
    target_employee_id BIGINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    requester_shift_id BIGINT UNSIGNED NOT NULL,
    target_shift_id BIGINT UNSIGNED NOT NULL,
    status ENUM('PendingPeer', 'PendingManager', 'Approved', 'Rejected') DEFAULT 'PendingPeer',
    approved_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (requester_employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (target_employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);