CREATE TABLE IF NOT EXISTS echo_casting_shares (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token_hash CHAR(64) NOT NULL UNIQUE,
  reviewer_name VARCHAR(100) NOT NULL,
  generation_no SMALLINT UNSIGNED NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX echo_share_active(generation_no,expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS echo_casting_reviewer_votes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  share_id BIGINT UNSIGNED NOT NULL,
  candidate_id BIGINT UNSIGNED NOT NULL,
  decision ENUM('liked','rewrite','rejected') NOT NULL,
  voted_at DATETIME NOT NULL,
  UNIQUE KEY echo_reviewer_candidate(share_id,candidate_id),
  FOREIGN KEY(share_id) REFERENCES echo_casting_shares(id) ON DELETE CASCADE,
  FOREIGN KEY(candidate_id) REFERENCES echo_casting_candidates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
