-- ============================================================
-- May Cash Franchise — Migration 2026-04-27
-- Adds: email + attribution + manager-managed columns
-- Run this ONCE on the existing live database (phpMyAdmin → SQL tab)
-- BEFORE uploading the new PHP files.
-- ============================================================

ALTER TABLE submissions
  ADD COLUMN email        VARCHAR(160) DEFAULT NULL AFTER phone,
  ADD COLUMN utm_source   VARCHAR(80)  DEFAULT NULL AFTER city_second,
  ADD COLUMN utm_medium   VARCHAR(80)  DEFAULT NULL AFTER utm_source,
  ADD COLUMN utm_campaign VARCHAR(120) DEFAULT NULL AFTER utm_medium,
  ADD COLUMN utm_content  VARCHAR(120) DEFAULT NULL AFTER utm_campaign,
  ADD COLUMN utm_term     VARCHAR(120) DEFAULT NULL AFTER utm_content,
  ADD COLUMN fbclid       VARCHAR(255) DEFAULT NULL AFTER utm_term,
  ADD COLUMN ttclid       VARCHAR(255) DEFAULT NULL AFTER fbclid,
  ADD COLUMN referrer     VARCHAR(500) DEFAULT NULL AFTER ttclid,
  ADD COLUMN contacted    ENUM('non','oui') NOT NULL DEFAULT 'non' AFTER referrer,
  ADD COLUMN rdv_at       DATE         DEFAULT NULL AFTER contacted,
  ADD COLUMN comment      TEXT         DEFAULT NULL AFTER rdv_at,
  ADD COLUMN updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
  ADD INDEX  idx_contacted  (contacted),
  ADD INDEX  idx_utm_source (utm_source);
