-- Pack 22 – Time Tracking + Payroll + Employee Project Expenses

ALTER TABLE projects ADD COLUMN work_region_type ENUM('local','outstation') NOT NULL DEFAULT 'local';

ALTER TABLE employees ADD COLUMN hourly_rate_local DECIMAL(12,2) NULL;
ALTER TABLE employees ADD COLUMN hourly_rate_outstation DECIMAL(12,2) NULL;
ALTER TABLE employees ADD COLUMN daily_allowance_outstation DECIMAL(12,2) NULL;
ALTER TABLE employees ADD COLUMN food_allowance DECIMAL(12,2) NULL;
ALTER TABLE employees ADD COLUMN overnight_allowance DECIMAL(12,2) NULL;

CREATE TABLE IF NOT EXISTS work_sessions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NULL,
    work_date DATE NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NULL,
    pause_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    net_minutes INT UNSIGNED NOT NULL DEFAULT 0,
    work_mode ENUM('local','outstation') NOT NULL DEFAULT 'local',
    hourly_rate_used DECIMAL(12,2) NULL,
    cost_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('open','paused','closed') NOT NULL DEFAULT 'open',
    notes TEXT 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_work_sessions_employee (employee_id),
    KEY idx_work_sessions_project (project_id),
    KEY idx_work_sessions_work_date (work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_project_expenses (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    employee_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NOT NULL,
    expense_date DATE NOT NULL,
    expense_type VARCHAR(120) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_employee_project_expenses_employee (employee_id),
    KEY idx_employee_project_expenses_project (project_id),
    KEY idx_employee_project_expenses_date (expense_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
