-- Phase 4 of the Projects/Tasks governance upgrade: an OKR / "Work Graph" layer
-- sitting above dim_projects — Objectives with Key Results, linked many-to-many to
-- projects so a Key Result's progress can roll up from the projects that serve it.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/okr_work_graph.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_objectives (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    owner_user_id INT NULL,
    period VARCHAR(64) NULL COMMENT 'Free-text OKR cycle label, e.g. "2026 Q1"',
    status VARCHAR(32) NOT NULL DEFAULT 'Active' COMMENT 'Active, Completed, Archived',
    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_objectives_tenant (tenant_id, is_deleted),
    INDEX idx_objectives_business (tenant_id, business_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS dim_key_results (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    objective_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    unit VARCHAR(32) NULL COMMENT 'e.g. "%", "$", "count" — display only',
    start_value DECIMAL(18,2) NOT NULL DEFAULT 0,
    target_value DECIMAL(18,2) NOT NULL,
    current_value DECIMAL(18,2) NOT NULL DEFAULT 0,
    /** When true, current_value is computed from linked projects' progress rather than
     *  manually entered — see fact_objective_projects and KeyResultService.recomputeRollup. */
    auto_rollup TINYINT(1) NOT NULL DEFAULT 0,
    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_key_results_objective (tenant_id, objective_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fact_objective_projects (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    objective_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    weight DECIMAL(5,2) NOT NULL DEFAULT 1.00 COMMENT 'Relative weight when averaging this project into the objective''s key results',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    UNIQUE KEY uq_objective_project (objective_id, project_id),
    INDEX idx_objective_projects_objective (tenant_id, objective_id),
    INDEX idx_objective_projects_project (tenant_id, project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;