-- =============================================
-- StablePro Wallet - Database Schema
-- Waitlist Email Validation Tables
-- =============================================

-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS `stablepro_wallet`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `stablepro_wallet`;

-- =============================================
-- TABLE: waitlist_pending (Pre-validation)
-- Stores email submissions awaiting validation
-- =============================================
CREATE TABLE IF NOT EXISTS `waitlist_pending` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `validation_token` VARCHAR(64) NOT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NOT NULL,
    `attempts` TINYINT UNSIGNED DEFAULT 0,
    `last_resend_at` TIMESTAMP NULL,

    -- Indexes
    UNIQUE INDEX `idx_email` (`email`),
    UNIQUE INDEX `idx_validation_token` (`validation_token`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLE: waitlist_confirmed (Post-validation)
-- Stores validated/confirmed email subscriptions
-- =============================================
CREATE TABLE IF NOT EXISTS `waitlist_confirmed` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `subscribed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `confirmed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `referral_source` VARCHAR(100) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `unsubscribed_at` TIMESTAMP NULL,

    -- Indexes
    UNIQUE INDEX `idx_email` (`email`),
    INDEX `idx_confirmed_at` (`confirmed_at`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLE: email_log (Optional: track sent emails)
-- =============================================
CREATE TABLE IF NOT EXISTS `email_log` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `email_type` ENUM('validation', 'welcome', 'newsletter', 'other') NOT NULL,
    `status` ENUM('sent', 'failed', 'bounced') DEFAULT 'sent',
    `error_message` TEXT DEFAULT NULL,
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Indexes
    INDEX `idx_email` (`email`),
    INDEX `idx_email_type` (`email_type`),
    INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CLEANUP: Stored Procedure to remove expired tokens
-- Run this via cron job periodically
-- =============================================
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS `cleanup_expired_tokens`()
BEGIN
    DELETE FROM `waitlist_pending`
    WHERE `expires_at` < NOW();
END //

DELIMITER ;

-- =============================================
-- EVENT: Auto-cleanup expired tokens daily
-- (Requires event_scheduler = ON in MySQL config)
-- =============================================
CREATE EVENT IF NOT EXISTS `daily_token_cleanup`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
    CALL cleanup_expired_tokens();
