CREATE TABLE IF NOT EXISTS late_regularizations (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NOT NULL,
    date          DATE NOT NULL,
    late_minutes  INT DEFAULT 0,
    reason        TEXT NOT NULL,
    status        ENUM('pending','approved','rejected') DEFAULT 'pending',
    approved_by   INT NULL,
    approved_at   DATETIME NULL,
    remarks       TEXT NULL,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY uq_emp_date (employee_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Index for manager queries
CREATE INDEX idx_late_reg_status ON late_regularizations(status);
CREATE INDEX idx_late_reg_date   ON late_regularizations(date);
