-- ============================================================
-- Pipeline v3.0 — Migration 4 of 4
-- Phase 9 self-improvement tables: feedback + prompt improvements
--
-- Run against the curric MySQL database
-- ============================================================

-- ============================================================
-- Pipeline feedback
-- Phase 9 analyzes quality issues and logs structured feedback.
-- Human reviewers can also add feedback during approval flow.
-- ============================================================
CREATE TABLE IF NOT EXISTS curric.cur_ai_pipeline_feedback (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    build_id         INT          NOT NULL COMMENT 'FK → cur_ai_build.id',
    unit_id          INT          NOT NULL COMMENT 'FK → cur_detail.unit_id',
    reviewer_user_id INT          NULL     COMMENT 'NULL = AI-generated, non-NULL = human reviewer',
    phase_number     INT          NULL     COMMENT 'NULL = overall feedback, 1-9 = phase-specific',
    feedback_type    VARCHAR(50)  NOT NULL
                                  COMMENT 'approve | reject | revise | comment | quality_issue | pattern_detected',
    feedback_text    TEXT         NULL,
    rating           INT          NULL     COMMENT '1-5 quality rating',
    category         VARCHAR(100) NULL     COMMENT 'content_quality | interactivity | standards_alignment | assessment_quality',
    created_at       DATETIME     NOT NULL DEFAULT NOW(),

    INDEX idx_build_phase (build_id, phase_number),
    INDEX idx_unit        (unit_id),
    INDEX idx_type        (feedback_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pipeline v3.0 — feedback from Phase 9 and human reviewers';

-- ============================================================
-- Prompt improvement suggestions
-- Phase 9 detects patterns across runs and suggests prompt
-- refinements. Human approval required before applying.
-- ============================================================
CREATE TABLE IF NOT EXISTS curric.cur_ai_prompt_improvement (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    phase_number        INT          NOT NULL COMMENT 'Which phase prompt to improve (1-8)',
    prompt_version      VARCHAR(20)  NOT NULL COMMENT 'e.g. 3.0.1, 3.0.2',
    change_description  TEXT         NOT NULL COMMENT 'What to change and why',
    suggested_prompt    TEXT         NULL     COMMENT 'Full suggested prompt text',
    performance_before  DECIMAL(5,2) NULL     COMMENT 'Avg quality score before change',
    performance_after   DECIMAL(5,2) NULL     COMMENT 'Avg quality score after change',
    status              VARCHAR(20)  NOT NULL DEFAULT 'pending'
                                     COMMENT 'pending | approved | rejected | applied',
    approved_by         INT          NULL     COMMENT 'user_id who approved',
    approved_at         DATETIME     NULL,
    created_at          DATETIME     NOT NULL DEFAULT NOW(),
    created_by          INT          NULL     COMMENT 'NULL = AI-generated',

    INDEX idx_phase   (phase_number),
    INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pipeline v3.0 — AI-suggested prompt improvements (Phase 9)';
