-- WhatsApp self-service (registration / password reset) - schema changes
-- Mirrors migration: database/migrations/2026_09_21_120000_create_whatsapp_conversations_table.php
--
-- This is the ONLY schema change introduced by the WhatsApp self-service
-- feature (registration, password reset, and the ticket-creation gate all
-- reuse existing tables - users, auth_tokens, tickets, whatsapp_inbox).
--
-- Preferred deployment path: `php artisan migrate` (keeps the migrations
-- table in sync automatically). Use this script only when applying the
-- change by hand against an environment where artisan isn't available
-- (e.g. handing off to a DBA). Safe to run more than once.

CREATE TABLE IF NOT EXISTS `whatsapp_conversations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `flow` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `step` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` json DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `whatsapp_conversations_phone_unique` (`phone`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Record the migration as applied so a later `php artisan migrate` on this
-- same database doesn't try (and fail) to create the table again.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT
    '2026_09_21_120000_create_whatsapp_conversations_table',
    (SELECT COALESCE(MAX(batch), 0) + 1 FROM `migrations`)
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM `migrations`
    WHERE `migration` = '2026_09_21_120000_create_whatsapp_conversations_table'
);