-- Phase 5 of the Projects/Tasks governance upgrade: native time tracking (actual
-- logged minutes per workitem, vs. the existing estimate-only fact_project_tasks
-- .estimate_hours). Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/time_entries.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS fact_time_entries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    workitem_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    minutes INT NOT NULL,
    entry_date DATE NOT NULL,
    note VARCHAR(500) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_time_entries_workitem (tenant_id, workitem_id, is_deleted),
    INDEX idx_time_entries_user (tenant_id, user_id, is_deleted),
    INDEX idx_time_entries_business (tenant_id, business_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;