<?php
require_once '../../config/config.php';
header('Content-Type: application/json');

function ok($m,$e=[]) { echo json_encode(['status'=>'success','message'=>$m]+$e); exit; }
function err($m) { http_response_code(500); echo json_encode(['status'=>'error','message'=>$m]); exit; }

try {
    // Create table if not exists
    $sql = "CREATE TABLE IF NOT EXISTS `pattern_questions` (
                `id` INT NOT NULL AUTO_INCREMENT,
                `section_id` INT NOT NULL,
                `question_no` INT NOT NULL,
                `question_text` TEXT NOT NULL,
                `co_codes` JSON NULL,
                `po_codes` JSON NULL,
                PRIMARY KEY (`id`),
                KEY `idx_pq_section_id` (`section_id`),
                CONSTRAINT `fk_pq_section` FOREIGN KEY (`section_id`) REFERENCES `pattern_sections`(`section_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    try {
        $pdo->exec($sql);
    } catch (Throwable $e) {
        // Fallback for MySQL without JSON
        $sql2 = str_replace([' JSON ', ' JSON NULL'], [' LONGTEXT ', ' LONGTEXT NULL'], $sql);
        $pdo->exec($sql2);
    }

    // Ensure columns co_codes and po_codes exist
    $cols = $pdo->query("SHOW COLUMNS FROM pattern_questions")->fetchAll(PDO::FETCH_COLUMN, 0);
    if (!in_array('co_codes', $cols, true)) {
        try { $pdo->exec("ALTER TABLE pattern_questions ADD COLUMN co_codes JSON NULL AFTER question_text"); }
        catch (Throwable $e) { $pdo->exec("ALTER TABLE pattern_questions ADD COLUMN co_codes LONGTEXT NULL AFTER question_text"); }
    }
    if (!in_array('po_codes', $cols, true)) {
        try { $pdo->exec("ALTER TABLE pattern_questions ADD COLUMN po_codes JSON NULL AFTER co_codes"); }
        catch (Throwable $e) { $pdo->exec("ALTER TABLE pattern_questions ADD COLUMN po_codes LONGTEXT NULL AFTER co_codes"); }
    }

    // If legacy co_code column exists, migrate values to co_codes JSON
    if (in_array('co_code', $cols, true)) {
        // best effort: wrap as single-element array
        $pdo->exec("UPDATE pattern_questions SET co_codes = JSON_ARRAY(co_code) WHERE co_code IS NOT NULL AND co_codes IS NULL");
        // For non-JSON engines, set as string like ["CO1"]
        $pdo->exec('UPDATE pattern_questions SET co_codes = CONCAT(\'["\', co_code, \']\') WHERE co_code IS NOT NULL AND (co_codes IS NULL OR co_codes=\'\')');
    }

    ok('pattern_questions table verified/migrated');
} catch (Throwable $e) {
    err($e->getMessage());
}
?>