-- =============================================================================
-- Twosome – database/migration_auth_update.sql
-- Migration: Remove all SMS/OTP tables and enforce Phone + Password + Recovery Code auth.
--
-- Can be run via phpMyAdmin or MySQL CLI on existing installations:
--   mysql -u cpanelusername_api -p cpanelusername_twosome < database/migration_auth_update.sql
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. DROP LEGACY OTP & SMS TABLES (if any existed in earlier builds)
DROP TABLE IF EXISTS otp_codes;
DROP TABLE IF EXISTS sms_logs;
DROP TABLE IF EXISTS phone_verifications;

-- 2. ENSURE USERS TABLE HAS PHONE + PASSWORD + RECOVERY CODE FIELDS
CREATE TABLE IF NOT EXISTS users (
    id                   BIGINT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    phone_e164           VARCHAR(20)       NOT NULL,
    email                VARCHAR(254)      DEFAULT NULL,
    password_hash        VARCHAR(255)      NOT NULL,
    recovery_code_hash   VARCHAR(255)      NOT NULL,
    display_name         VARCHAR(64)       NOT NULL,
    birth_year_confirmed YEAR              NOT NULL,
    public_key           TEXT              DEFAULT NULL,
    created_at           DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at           DATETIME          DEFAULT NULL,
    UNIQUE KEY uq_users_phone (phone_e164),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. ENSURE COLUMNS AND CONSTRAINTS EXIST ON EXISTING USERS TABLE
-- (Safely handle cases where users table was created under an older schema)
DELIMITER $$
CREATE PROCEDURE migrate_users_auth()
BEGIN
    -- Add phone_e164 if missing
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'phone_e164'
    ) THEN
        ALTER TABLE users ADD COLUMN phone_e164 VARCHAR(20) NOT NULL AFTER id;
    END IF;

    -- Add email if missing
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'email'
    ) THEN
        ALTER TABLE users ADD COLUMN email VARCHAR(254) DEFAULT NULL AFTER phone_e164;
    END IF;

    -- Add password_hash if missing
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'password_hash'
    ) THEN
        ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NOT NULL AFTER email;
    END IF;

    -- Add recovery_code_hash if missing
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'recovery_code_hash'
    ) THEN
        ALTER TABLE users ADD COLUMN recovery_code_hash VARCHAR(255) NOT NULL AFTER password_hash;
    END IF;

    -- Drop legacy OTP columns if any exist from earlier prototypes
    IF EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'otp_secret'
    ) THEN
        ALTER TABLE users DROP COLUMN otp_secret;
    END IF;

    IF EXISTS (
        SELECT 1 FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_phone_verified'
    ) THEN
        ALTER TABLE users DROP COLUMN is_phone_verified;
    END IF;
END$$
DELIMITER ;

CALL migrate_users_auth();
DROP PROCEDURE migrate_users_auth;

-- 4. ENSURE UNIQUE INDEXES EXIST
DELIMITER $$
CREATE PROCEDURE ensure_user_indexes()
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'uq_users_phone'
    ) THEN
        ALTER TABLE users ADD UNIQUE KEY uq_users_phone (phone_e164);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'uq_users_email'
    ) THEN
        ALTER TABLE users ADD UNIQUE KEY uq_users_email (email);
    END IF;
END$$
DELIMITER ;

CALL ensure_user_indexes();
DROP PROCEDURE ensure_user_indexes;

SET FOREIGN_KEY_CHECKS = 1;