-- migrate_otp.sql
-- Run this on your EXISTING live database BEFORE uploading any PHP files.
-- Safe to run multiple times (uses IF NOT EXISTS / IF EXISTS guards).
--
-- Step 1 — Add the otp_code column (skip if already present)
ALTER TABLE messages
    ADD COLUMN IF NOT EXISTS otp_code VARCHAR(8) NULL AFTER body;

-- Step 2 — Add dedup unique index (skip if already present)
-- MySQL < 8.0 does not support IF NOT EXISTS on CREATE INDEX,
-- so we use a stored procedure trick for compatibility.
DROP PROCEDURE IF EXISTS _add_dedup_index;
DELIMITER $$
CREATE PROCEDURE _add_dedup_index()
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME   = 'messages'
          AND INDEX_NAME   = 'uq_message_dedup'
    ) THEN
        CREATE UNIQUE INDEX uq_message_dedup
            ON messages(number_id, from_number, body(255));
    END IF;
END$$
DELIMITER ;
CALL _add_dedup_index();
DROP PROCEDURE IF EXISTS _add_dedup_index;