CREATE TABLE IF NOT EXISTS opportunity_decisions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  analysis_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NOT NULL,
  status ENUM('new','review','selected','rejected','done') NOT NULL DEFAULT 'new',
  effort ENUM('unknown','low','medium','high') NOT NULL DEFAULT 'unknown',
  impact ENUM('unknown','low','medium','high') NOT NULL DEFAULT 'unknown',
  note TEXT NULL,
  decided_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY analysis_project_decision (analysis_id, project_id),
  INDEX decision_status (status, updated_at),
  INDEX decision_project (project_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

