-- ============================================================================
-- Chat & WhatsApp Module Migration — Gmind Institute
-- ============================================================================
-- Creates: chat_conversations, chat_messages tables
-- Run this SQL on your database before uploading the PHP files.
-- ============================================================================

-- Chat Conversations table
CREATE TABLE IF NOT EXISTS `chat_conversations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `visitor_name` VARCHAR(100) DEFAULT NULL,
    `visitor_email` VARCHAR(255) DEFAULT NULL,
    `visitor_phone` VARCHAR(20) DEFAULT NULL,
    `session_id` VARCHAR(128) NOT NULL,
    `status` ENUM('active', 'closed', 'pending') NOT NULL DEFAULT 'pending',
    `last_message_at` TIMESTAMP NULL DEFAULT NULL,
    `admin_unread` INT UNSIGNED NOT NULL DEFAULT 0,
    `visitor_unread` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_chat_conv_session` (`session_id`),
    INDEX `idx_chat_conv_status` (`status`),
    INDEX `idx_chat_conv_last_msg` (`last_message_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat Messages table
CREATE TABLE IF NOT EXISTS `chat_messages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id` INT UNSIGNED NOT NULL,
    `sender_type` ENUM('visitor', 'admin') NOT NULL,
    `sender_id` INT UNSIGNED DEFAULT NULL,
    `message` TEXT NOT NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_chat_msg_conv` (`conversation_id`),
    INDEX `idx_chat_msg_read` (`is_read`),
    CONSTRAINT `fk_chat_msg_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `chat_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
