CREATE TABLE IF NOT EXISTS timesheet_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    owner_user_id INT NOT NULL,
    job_id INT NOT NULL,
    contract_id INT NULL,
    employee_name VARCHAR(190) NOT NULL,
    employee_email VARCHAR(190) NULL,
    work_date DATE NOT NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    hours DECIMAL(8,2) NOT NULL DEFAULT 0,
    hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'approved',
    source VARCHAR(80) NOT NULL DEFAULT 'payroll_import',
    external_reference VARCHAR(120) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_timesheet_entries_tenant_job_date ON timesheet_entries (tenant_id, job_id, work_date);
CREATE INDEX idx_timesheet_entries_tenant_contract_date ON timesheet_entries (tenant_id, contract_id, work_date);
CREATE INDEX idx_timesheet_entries_tenant_status_date ON timesheet_entries (tenant_id, status, work_date);
CREATE INDEX idx_timesheet_entries_tenant_employee_date ON timesheet_entries (tenant_id, employee_email, work_date);