CREATE TABLE IF NOT EXISTS echo_casting_user_proposals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  candidate_id BIGINT UNSIGNED NULL,
  share_id BIGINT UNSIGNED NULL,
  author_name VARCHAR(100) NOT NULL,
  proposal_title VARCHAR(255) NOT NULL DEFAULT 'Idée spontanée',
  context_slug ENUM('recycle','before','between','after','studio') NOT NULL DEFAULT 'recycle',
  proposal_text TEXT NOT NULL,
  status ENUM('new','selected','archived') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(candidate_id) REFERENCES echo_casting_candidates(id) ON DELETE SET NULL,
  FOREIGN KEY(share_id) REFERENCES echo_casting_shares(id) ON DELETE SET NULL,
  INDEX echo_user_proposals_status(status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
