-- ============================================================
-- Pipeline v3.0 — Migration 1 of 4
-- AI Build Pipeline Tracking Table (fresh create)
--
-- Run against the curric MySQL database.
-- This table tracks AI pipeline runs and links projects
-- to their build status throughout the 9-phase pipeline.
-- ============================================================

DROP TABLE IF EXISTS curric.cur_ai_build;

CREATE TABLE curric.cur_ai_build (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    unit_id          INT          NOT NULL COMMENT 'FK → curric.cur_detail.unit_id',
    run_id           VARCHAR(36)  NULL     COMMENT 'Pipeline execution run UUID',
    agent_id         VARCHAR(36)  NULL     COMMENT 'platform_agent UUID (legacy v1/v2)',
    review_id        VARCHAR(36)  NULL     COMMENT 'platform_review UUID (legacy v1/v2)',
    execution_id     VARCHAR(36)  NULL     COMMENT 'execution UUID (legacy v1/v2)',
    reviewer_id      VARCHAR(36)  NULL     COMMENT 'platform_review_reviewer UUID (legacy v1/v2)',

    -- Pipeline status tracking
    status           VARCHAR(50)  NOT NULL DEFAULT 'pending'
                                  COMMENT 'pending | pipeline_running | quality_review | approved | live | failed | never_built',
    current_phase    INT          NOT NULL DEFAULT 0 COMMENT 'Current phase number (0-9)',
    total_phases     INT          NOT NULL DEFAULT 9,
    phase_status     VARCHAR(50)  NOT NULL DEFAULT 'pending'
                                  COMMENT 'pending | running | completed | failed',
    quality_score    DECIMAL(5,2) NULL     COMMENT 'Phase 8 quality score (0-100)',
    error_message    TEXT         NULL     COMMENT 'Error details if phase_status=failed',
    error_phase      INT          NULL     COMMENT 'Which phase failed',

    -- Timing
    started_at       DATETIME     NULL     COMMENT 'When pipeline execution started',
    completed_at     DATETIME     NULL     COMMENT 'When pipeline execution completed',

    -- Pipeline metadata
    pipeline_version VARCHAR(10)  NULL     DEFAULT '3.0',
    source_unit_id   INT          NULL     COMMENT 'Original unit_id that triggered this build',
    callback_url     VARCHAR(500) NULL     COMMENT 'Webhook URL for FastAPI callbacks',
    retry_count      INT          NOT NULL DEFAULT 0,
    last_callback_at DATETIME     NULL     COMMENT 'Last webhook callback received',
    triggered_by     INT          NULL     COMMENT 'user_id who triggered the pipeline',
    metadata         JSON         NULL     COMMENT 'Flexible storage for phase-specific data',

    -- Timestamps
    created_at       DATETIME     NOT NULL DEFAULT NOW(),
    updated_at       DATETIME     NOT NULL DEFAULT NOW() ON UPDATE NOW(),

    INDEX idx_unit_id       (unit_id),
    INDEX idx_run_id        (run_id),
    INDEX idx_review_id     (review_id),
    INDEX idx_status        (status),
    INDEX idx_phase_status  (phase_status),
    INDEX idx_source_unit   (source_unit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pipeline v3.0 — tracks AI curriculum build runs';
