-- ============================================================
-- 003_customer_auth_fixes.sql
-- Fixes for customer authentication (from pre-launch review):
--
--   Bug 2: customers.routes.js reads/writes google_id and
--          auth_provider columns that didn't exist → add them.
--   Bug 3: customers.phone was NOT NULL UNIQUE, but email-only and
--          Google sign-ups insert customers without a phone →
--          relax phone to allow NULL (kept UNIQUE; MySQL allows
--          multiple NULLs in a UNIQUE column).
--
-- Idempotent — safe to run more than once.
-- ============================================================

-- ─── google_id ───────────────────────────────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'google_id');
SET @sql := IF(@c = 0,
  'ALTER TABLE customers ADD COLUMN google_id VARCHAR(255) NULL UNIQUE AFTER password_hash',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─── auth_provider ───────────────────────────────────────
SET @c := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'auth_provider');
SET @sql := IF(@c = 0,
  "ALTER TABLE customers ADD COLUMN auth_provider ENUM('phone','email','google') DEFAULT 'phone' AFTER google_id",
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─── Relax phone: NOT NULL → NULL ────────────────────────
-- Only modify if it's currently NOT NULL, to stay idempotent.
SET @nn := (SELECT IS_NULLABLE FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'phone');
SET @sql := IF(@nn = 'NO',
  'ALTER TABLE customers MODIFY COLUMN phone VARCHAR(20) NULL',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─── password_hash: allow NULL for Google sign-ups ───────
-- Google customers have no password; code inserts them without one.
SET @nn := (SELECT IS_NULLABLE FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'password_hash');
SET @sql := IF(@nn = 'NO',
  'ALTER TABLE customers MODIFY COLUMN password_hash VARCHAR(255) NULL',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;