-- ENH-032: Full Timesheet System - Database Migration (Phase 1 + 2 + 3)
-- Creates all tables with no test data
-- Safe to re-run (uses IF NOT EXISTS / conditional index creation)
-- Date: 2026-02-22
-- Production: MySQL 5.5.62

-- Drop the audit table if it was partially created
DROP TABLE IF EXISTS ts_entry_audit;

-- Core time entries table
CREATE TABLE IF NOT EXISTS ts_time_entries (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    user_id         INT(11) NOT NULL,
    job_id          INT(11) DEFAULT NULL,
    customer_id     INT(11) DEFAULT NULL,
    clock_in        DATETIME NOT NULL,
    clock_out       DATETIME DEFAULT NULL,
    duration_minutes INT(11) DEFAULT NULL,
    break_minutes   INT(11) NOT NULL DEFAULT 0,
    notes           TEXT DEFAULT NULL,
    entry_type      ENUM('clock','manual') NOT NULL DEFAULT 'clock',
    status          ENUM('active','deleted') NOT NULL DEFAULT 'active',
    created_by      INT(11) NOT NULL,
    updated_by      INT(11) DEFAULT NULL,
    created_at      DATETIME NOT NULL,
    updated_at      DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_clock_in (clock_in),
    KEY idx_user_date (user_id, clock_in),
    KEY idx_status (status),
    KEY idx_job_id (job_id),
    KEY idx_customer_id (customer_id),
    KEY idx_status_user_clockin (status, user_id, clock_in),
    KEY idx_status_clockin (status, clock_in),
    KEY idx_status_customer_clockin (status, customer_id, clock_in),
    KEY idx_user_status_clockout (user_id, status, clock_out)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Settings table
CREATE TABLE IF NOT EXISTS ts_settings (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    setting_key     VARCHAR(100) NOT NULL,
    setting_value   VARCHAR(255) NOT NULL,
    description     VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Seed default settings (ignore if already exist)
INSERT IGNORE INTO ts_settings (setting_key, setting_value, description) VALUES
    ('overtime_daily_threshold', '480', 'Minutes/day before overtime (480=8h)'),
    ('overtime_weekly_threshold', '2400', 'Minutes/week before overtime (2400=40h)');

-- Groups table
CREATE TABLE IF NOT EXISTS ts_groups (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    group_name      VARCHAR(100) NOT NULL,
    description     VARCHAR(255) DEFAULT NULL,
    created_by      INT(11) NOT NULL,
    created_at      DATETIME NOT NULL,
    updated_at      DATETIME DEFAULT NULL,
    status          ENUM('active','deleted') NOT NULL DEFAULT 'active',
    PRIMARY KEY (id),
    UNIQUE KEY uniq_group_name (group_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Group members table
CREATE TABLE IF NOT EXISTS ts_group_members (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    group_id        INT(11) NOT NULL,
    user_id         INT(11) NOT NULL,
    added_by        INT(11) NOT NULL,
    added_at        DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_group_user (group_id, user_id),
    KEY idx_group_id (group_id),
    KEY idx_user_id (user_id),
    KEY idx_user_group (user_id, group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Audit trail table (Phase 3)
CREATE TABLE IF NOT EXISTS ts_entry_audit (
    id              INT(11) NOT NULL AUTO_INCREMENT,
    entry_id        INT(11) NOT NULL,
    action          ENUM('create','edit','delete') NOT NULL,
    changed_by      INT(11) NOT NULL,
    changed_at      DATETIME NOT NULL,
    before_json     TEXT NULL,
    after_json      TEXT NULL,
    reason          VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_entry_id (entry_id),
    KEY idx_changed_by (changed_by),
    KEY idx_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
