-- Phase 1 of the Projects/Tasks governance upgrade: configurable per-status rules
-- (required fields before a status transition is allowed, and "terminal" statuses
-- that auto-stamp fact_project_tasks.completed_at). Idempotent (information_schema-
-- guarded), same pattern as projects_hierarchy_and_ai.sql. Purely additive — no
-- existing rows are touched beyond the new nullable column, and an empty rules
-- table (the default) preserves today's fully-open status behavior.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/workflow_governance.sql

USE db_abos_v0.1;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'completed_at');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `completed_at` TIMESTAMP(6) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS dim_workflow_status_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    project_type VARCHAR(64) NOT NULL,
    status VARCHAR(64) NOT NULL,
    required_fields VARCHAR(255) NULL COMMENT 'CSV of workitem field names that must be non-null before this status can be set, e.g. "assignedUserId,estimateHours"',
    is_terminal TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'When true, entering this status stamps completed_at; leaving it clears completed_at',
    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,
    UNIQUE KEY uq_workflow_status_rule (tenant_id, project_type, status),
    INDEX idx_workflow_status_rules_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;