-- Projects: type-specific work-item hierarchy (parent_id self-join) + Action Item
-- cross-project linking. Idempotent (information_schema-guarded), same pattern as
-- projects_foundation_refactor.sql.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/projects_hierarchy_and_ai.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 = 'parent_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `parent_id` BIGINT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND INDEX_NAME = 'idx_fact_project_tasks_parent');
SET @sql = IF(@idx_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD INDEX `idx_fact_project_tasks_parent` (`tenant_id`, `is_deleted`, `parent_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Normalize any legacy type values to the new vocabulary's closest root type, so old
-- rows don't become orphaned/invalid under the new hierarchy validation.
-- `type` carries no index, so this trips client-side "safe update mode" (Error 1175)
-- even with a WHERE clause present — toggle it off for just this one statement.
SET SQL_SAFE_UPDATES = 0;
UPDATE fact_project_tasks SET type = 'Task' WHERE type IN ('Backlog', 'Epic') AND is_deleted = 0;
SET SQL_SAFE_UPDATES = 1;

CREATE TABLE IF NOT EXISTS fact_action_item_projects (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    action_item_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    UNIQUE KEY uq_action_item_project (action_item_id, project_id),
    INDEX idx_action_item_projects_item (tenant_id, action_item_id),
    INDEX idx_action_item_projects_project (tenant_id, project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;