-- ============================================================
-- migration_on_duty.sql
-- ============================================================
-- Adds the On Duty (OD) apply/approve workflow — lets an employee
-- request On Duty status for a date range (e.g. client visit, field
-- work) instead of it only being settable manually by an admin editing
-- the muster. Used by both the web app (modules/attendance/onduty.php)
-- and the mobile app (Apply → On Duty tab).
--
-- Run this once against your existing database:
--   mysql -u <user> -p <database_name> < migration_on_duty.sql
--
-- Already ran an earlier version of this file (table already exists)?
-- Run migration_on_duty_add_time.sql instead — it only adds the new
-- is_full_day / from_time / to_time columns to your existing table.
-- ============================================================

CREATE TABLE IF NOT EXISTS on_duty_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    is_full_day TINYINT(1) NOT NULL DEFAULT 1,
    from_time TIME NULL,
    to_time TIME NULL,
    days DECIMAL(4,1) DEFAULT 1,
    purpose VARCHAR(150) NOT NULL,
    location VARCHAR(150),
    reason TEXT,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_on_duty_employee_status ON on_duty_requests(employee_id, status);
CREATE INDEX idx_on_duty_dates ON on_duty_requests(from_date, to_date);
