-- WhatsApp CRM Database Schema
-- Optimized for MySQL 5.7+ and MySQL 8.0+
-- Charset: utf8mb4 (Required for WhatsApp Emojis and Multilingual text)

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users / Agents Table
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'manager', 'agent') DEFAULT 'agent',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `phone` VARCHAR(30) NULL,
    `avatar` VARCHAR(255) NULL,
    `last_login` DATETIME NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Pipeline Stages Table
DROP TABLE IF EXISTS `pipeline_stages`;
CREATE TABLE `pipeline_stages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `color` VARCHAR(20) DEFAULT '#3b82f6',
    `order_index` INT DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Tags Table
DROP TABLE IF EXISTS `tags`;
CREATE TABLE `tags` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `color` VARCHAR(20) DEFAULT '#64748b',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Contacts / Leads Table
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `whatsapp_id` VARCHAR(50) NOT NULL UNIQUE,
    `phone_number` VARCHAR(30) NOT NULL,
    `name` VARCHAR(150) NOT NULL DEFAULT 'WhatsApp User',
    `email` VARCHAR(150) NULL,
    `company` VARCHAR(150) NULL,
    `stage_id` INT NULL,
    `assigned_to` INT NULL,
    `avatar` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `custom_fields` JSON NULL,
    `source` VARCHAR(50) DEFAULT 'whatsapp',
    `last_activity_at` DATETIME NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_phone` (`phone_number`),
    INDEX `idx_stage` (`stage_id`),
    INDEX `idx_assigned` (`assigned_to`),
    CONSTRAINT `fk_contacts_stage` FOREIGN KEY (`stage_id`) REFERENCES `pipeline_stages`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_contacts_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Contact Tags Pivot Table
DROP TABLE IF EXISTS `contact_tags`;
CREATE TABLE `contact_tags` (
    `contact_id` INT NOT NULL,
    `tag_id` INT NOT NULL,
    PRIMARY KEY (`contact_id`, `tag_id`),
    CONSTRAINT `fk_ct_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ct_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Conversations Table (One active thread per contact)
DROP TABLE IF EXISTS `conversations`;
CREATE TABLE `conversations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contact_id` INT NOT NULL UNIQUE,
    `assigned_user_id` INT NULL,
    `status` ENUM('open', 'resolved', 'pending', 'snoozed') DEFAULT 'open',
    `unread_count` INT DEFAULT 0,
    `last_message` TEXT NULL,
    `last_message_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    INDEX `idx_assigned_user` (`assigned_user_id`),
    CONSTRAINT `fk_conv_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_conv_assigned` FOREIGN KEY (`assigned_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Messages Table
DROP TABLE IF EXISTS `messages`;
CREATE TABLE `messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `conversation_id` INT NOT NULL,
    `contact_id` INT NOT NULL,
    `user_id` INT NULL,
    `direction` ENUM('inbound', 'outbound') NOT NULL,
    `message_type` ENUM('text', 'image', 'document', 'audio', 'video', 'template', 'interactive', 'location') DEFAULT 'text',
    `content` TEXT NULL,
    `media_url` VARCHAR(500) NULL,
    `media_filename` VARCHAR(255) NULL,
    `meta_message_id` VARCHAR(150) NULL UNIQUE,
    `status` ENUM('pending', 'sent', 'delivered', 'read', 'failed') DEFAULT 'sent',
    `error_message` TEXT NULL,
    `raw_payload` JSON NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_conv` (`conversation_id`),
    INDEX `idx_contact` (`contact_id`),
    INDEX `idx_meta_id` (`meta_message_id`),
    INDEX `idx_created` (`created_at`),
    CONSTRAINT `fk_msg_conv` FOREIGN KEY (`conversation_id`) REFERENCES `conversations`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_msg_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_msg_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Deals / Sales Pipeline Table
DROP TABLE IF EXISTS `deals`;
CREATE TABLE `deals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `contact_id` INT NOT NULL,
    `stage_id` INT NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `value` DECIMAL(12, 2) DEFAULT 0.00,
    `currency` VARCHAR(10) DEFAULT 'USD',
    `closing_date` DATE NULL,
    `assigned_to` INT NULL,
    `notes` TEXT NULL,
    `status` ENUM('open', 'won', 'lost') DEFAULT 'open',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_deal_contact` (`contact_id`),
    INDEX `idx_deal_stage` (`stage_id`),
    INDEX `idx_deal_assigned` (`assigned_to`),
    CONSTRAINT `fk_deal_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_deal_stage` FOREIGN KEY (`stage_id`) REFERENCES `pipeline_stages`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_deal_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Quick Replies (Canned Responses) Table
DROP TABLE IF EXISTS `quick_replies`;
CREATE TABLE `quick_replies` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `shortcut` VARCHAR(50) NOT NULL UNIQUE,
    `title` VARCHAR(150) NOT NULL,
    `message` TEXT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. WhatsApp Message Templates Table
DROP TABLE IF EXISTS `whatsapp_templates`;
CREATE TABLE `whatsapp_templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `meta_template_id` VARCHAR(100) NULL,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `category` VARCHAR(50) DEFAULT 'MARKETING',
    `language` VARCHAR(20) DEFAULT 'en_US',
    `header_type` ENUM('NONE', 'TEXT', 'IMAGE', 'DOCUMENT', 'VIDEO') DEFAULT 'NONE',
    `body_text` TEXT NOT NULL,
    `footer_text` VARCHAR(150) NULL,
    `buttons` JSON NULL,
    `status` ENUM('APPROVED', 'PENDING', 'REJECTED') DEFAULT 'APPROVED',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Automation & Bot Rules Table
DROP TABLE IF EXISTS `automation_rules`;
CREATE TABLE `automation_rules` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `trigger_event` ENUM('message_contains', 'message_exact', 'welcome_new_contact', 'working_hours_out') NOT NULL,
    `trigger_keyword` VARCHAR(255) NULL,
    `reply_type` ENUM('text', 'template') DEFAULT 'text',
    `reply_content` TEXT NOT NULL,
    `action_type` ENUM('none', 'assign_agent', 'add_tag', 'move_stage') DEFAULT 'none',
    `action_value` VARCHAR(100) NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Broadcast Campaigns Table
DROP TABLE IF EXISTS `broadcasts`;
CREATE TABLE `broadcasts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `template_id` INT NULL,
    `target_tag_id` INT NULL,
    `target_stage_id` INT NULL,
    `total_contacts` INT DEFAULT 0,
    `sent_count` INT DEFAULT 0,
    `delivered_count` INT DEFAULT 0,
    `read_count` INT DEFAULT 0,
    `failed_count` INT DEFAULT 0,
    `status` ENUM('draft', 'processing', 'completed', 'failed') DEFAULT 'draft',
    `scheduled_at` DATETIME NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_bcast_tmpl` FOREIGN KEY (`template_id`) REFERENCES `whatsapp_templates`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Broadcast Logs Table
DROP TABLE IF EXISTS `broadcast_logs`;
CREATE TABLE `broadcast_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `broadcast_id` INT NOT NULL,
    `contact_id` INT NOT NULL,
    `phone_number` VARCHAR(30) NOT NULL,
    `status` ENUM('pending', 'sent', 'delivered', 'read', 'failed') DEFAULT 'pending',
    `meta_message_id` VARCHAR(150) NULL,
    `error_message` TEXT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_bcast_log` (`broadcast_id`),
    CONSTRAINT `fk_blog_bcast` FOREIGN KEY (`broadcast_id`) REFERENCES `broadcasts`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_blog_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Settings Table (Key-Value configuration)
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT NULL,
    `group` VARCHAR(50) DEFAULT 'general',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;