-- Qudurat AI - M5 Question Import Pipeline
-- Run AFTER M1, M2, M3, M4
USE qudurat_ai;

CREATE TABLE question_import_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by BIGINT UNSIGNED NULL,
  import_type ENUM('excel','pdf','image') NOT NULL,
  status ENUM('queued','processing','needs_review','completed','failed','cancelled') NOT NULL DEFAULT 'queued',
  total_items INT UNSIGNED NOT NULL DEFAULT 0,
  processed_items INT UNSIGNED NOT NULL DEFAULT 0,
  valid_items INT UNSIGNED NOT NULL DEFAULT 0,
  invalid_items INT UNSIGNED NOT NULL DEFAULT 0,
  approved_items INT UNSIGNED NOT NULL DEFAULT 0,
  failed_items INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  started_at TIMESTAMP NULL,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_import_type (import_type),
  INDEX idx_import_status (status),
  INDEX idx_import_creator_date (created_by, created_at),
  CONSTRAINT fk_import_job_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE question_import_files (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_import_job_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  disk VARCHAR(50) NOT NULL DEFAULT 'local',
  path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(150) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL,
  sha256 VARCHAR(64) NOT NULL,
  openai_file_id VARCHAR(120) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_import_file_job_mime (question_import_job_id, mime_type),
  INDEX idx_import_file_sha (sha256),
  CONSTRAINT fk_import_file_job FOREIGN KEY (question_import_job_id) REFERENCES question_import_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE question_import_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_import_job_id BIGINT UNSIGNED NOT NULL,
  question_import_file_id BIGINT UNSIGNED NULL,
  row_number INT UNSIGNED NULL,
  status ENUM('extracted','invalid','needs_review','approved','rejected','published','failed') NOT NULL DEFAULT 'extracted',
  raw_payload_json JSON NULL,
  normalized_payload_json JSON NULL,
  validation_errors_json JSON NULL,
  ai_confidence DECIMAL(5,4) NULL,
  ai_model VARCHAR(100) NULL,
  created_question_id BIGINT UNSIGNED NULL,
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at TIMESTAMP NULL,
  review_notes TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_import_item_status (question_import_job_id, status),
  CONSTRAINT fk_import_item_job FOREIGN KEY (question_import_job_id) REFERENCES question_import_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_import_item_file FOREIGN KEY (question_import_file_id) REFERENCES question_import_files(id) ON DELETE SET NULL,
  CONSTRAINT fk_import_item_question FOREIGN KEY (created_question_id) REFERENCES questions(id) ON DELETE SET NULL,
  CONSTRAINT fk_import_item_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;