-- Crystal 2 · application schema (reference tables in tvviewers remain untouched)
CREATE TABLE IF NOT EXISTS estimate_runs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  run_id CHAR(12) NOT NULL UNIQUE,
  title VARCHAR(200) NOT NULL DEFAULT '',
  original_filename VARCHAR(255) NOT NULL,
  upload_path VARCHAR(500) NOT NULL,
  status ENUM('PENDING','RUNNING','NEEDS_REVIEW','COMPLETED','FAILED') NOT NULL DEFAULT 'PENDING',
  progress_pct TINYINT UNSIGNED NOT NULL DEFAULT 0,
  stage_label VARCHAR(120) NOT NULL DEFAULT 'Waiting to start',
  detail_line VARCHAR(255) NOT NULL DEFAULT '',
  rows_total INT UNSIGNED NULL, rows_estimated INT UNSIGNED NULL, rows_flagged INT UNSIGNED NULL,
  summary_path VARCHAR(500) NULL, detailed_path VARCHAR(500) NULL,
  error_message VARCHAR(500) NULL,
  created_by VARCHAR(80) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at DATETIME NULL, finished_at DATETIME NULL,
  claimed_by VARCHAR(80) NULL,
  INDEX idx_status (status), INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS run_overrides (
  id INT AUTO_INCREMENT PRIMARY KEY,
  run_id CHAR(12) NOT NULL,
  row_ref INT UNSIGNED NOT NULL,
  field ENUM('ama','reach','telecast_type') NOT NULL,
  new_value VARCHAR(60) NOT NULL,
  reason VARCHAR(300) NOT NULL,
  created_by VARCHAR(80) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_run (run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
