-- Phase 2 of the Projects/Tasks governance upgrade: a no-code automation rules engine.
-- A rule fires when a workitem's status changes (more trigger types can be added later
-- without a schema change, since trigger/condition/action shape lives in JSON columns).
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/automation_rules.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_automation_rules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    project_id BIGINT NULL COMMENT 'NULL = applies to every project for this tenant',
    name VARCHAR(255) NOT NULL,
    trigger_type VARCHAR(64) NOT NULL COMMENT 'STATUS_CHANGED (the only trigger implemented so far)',
    trigger_config LONGTEXT NULL COMMENT 'JSON, e.g. {"toStatus":"Done"} — trigger-type-specific match criteria',
    conditions_json LONGTEXT NULL COMMENT 'JSON array of {field, operator, value} — ANDed',
    actions_json LONGTEXT NOT NULL COMMENT 'JSON array of {type, params} — executed in order, e.g. [{"type":"SET_FIELD","params":{"field":"priority","value":"Low"}}]',
    is_enabled TINYINT(1) NOT NULL DEFAULT 1,
    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_automation_rules_tenant (tenant_id, is_deleted, is_enabled),
    INDEX idx_automation_rules_project (tenant_id, project_id, is_deleted, is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auditable log of every rule firing (which workitem, which actions ran, success/failure) —
-- same "transparent action log" principle called for on the AI-integration side.
CREATE TABLE IF NOT EXISTS fact_automation_runs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    rule_id BIGINT NOT NULL,
    workitem_id BIGINT NOT NULL,
    actions_summary VARCHAR(1000) NULL,
    success TINYINT(1) NOT NULL DEFAULT 1,
    error_message VARCHAR(1000) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_automation_runs_rule (tenant_id, rule_id),
    INDEX idx_automation_runs_workitem (tenant_id, workitem_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;