-- ═══════════════════════════════════════════════════════════════
-- Migration : table candidatures + photos
-- À exécuter dans phpMyAdmin (base uluana)
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS candidatures (
  id              VARCHAR(40)   NOT NULL PRIMARY KEY,
  user_id         VARCHAR(40)   NULL,                    -- NULL si prospect non encore créé
  name            VARCHAR(160)  NOT NULL,
  email           VARCHAR(190)  NOT NULL,
  marque          VARCHAR(160)  NOT NULL DEFAULT '',
  craft           VARCHAR(160)  NOT NULL,
  need            VARCHAR(40)   NOT NULL,
  ca              VARCHAR(20)   NOT NULL,
  objective       TEXT          NOT NULL,
  tried           TEXT          NULL,
  photos_json     TEXT          NULL,                    -- JSON : [{url, source:'upload'|'external', caption}]
  status          ENUM('en_attente','validee','refusee') NOT NULL DEFAULT 'en_attente',
  note_responsable TEXT         NULL,
  source_feature  VARCHAR(80)   NULL,
  submitted_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at     DATETIME      NULL,
  reviewed_by     VARCHAR(40)   NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_cand_status (status),
  INDEX idx_cand_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;