-- ========================================
-- WhatsApp CRM Database Schema v5.9
-- ========================================
-- Database: whatscrm
-- ========================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `whatscrm` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `whatscrm`;

-- ========================================
-- 1. USER TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `mobile_with_country_code` VARCHAR(50) DEFAULT NULL,
  `plan` JSON DEFAULT NULL,
  `plan_expiry` DATETIME DEFAULT NULL,
  `fcm_projectId` VARCHAR(255) DEFAULT NULL,
  `fcm_clientEmail` VARCHAR(255) DEFAULT NULL,
  `fcm_privateKey` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 2. ADMIN TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `admin` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) UNIQUE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 3. PHONEBOOK TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `phonebook` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 4. CONTACT TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `contact` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `phonebook_id` INT NOT NULL,
  `phonebook_name` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `mobile` VARCHAR(50) NOT NULL,
  `var1` VARCHAR(255) DEFAULT '',
  `var2` VARCHAR(255) DEFAULT '',
  `var3` VARCHAR(255) DEFAULT '',
  `var4` VARCHAR(255) DEFAULT '',
  `var5` VARCHAR(255) DEFAULT '',
  `var6` VARCHAR(255) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_mobile` (`mobile`),
  INDEX `idx_phonebook_id` (`phonebook_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 5. CHATS TABLE (Legacy)
-- ========================================
CREATE TABLE IF NOT EXISTS `chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `chat_id` VARCHAR(255) NOT NULL,
  `uid` VARCHAR(255) NOT NULL,
  `sender_name` VARCHAR(255) DEFAULT NULL,
  `sender_mobile` VARCHAR(50) DEFAULT NULL,
  `last_message` TEXT DEFAULT NULL,
  `last_message_came` VARCHAR(50) DEFAULT NULL,
  `is_opened` INT DEFAULT 0,
  `chat_note` TEXT DEFAULT NULL,
  `chat_tags` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_chat_id` (`chat_id`),
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 6. BETA_CHATS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `chat_id` VARCHAR(255) NOT NULL,
  `origin_instance_id` VARCHAR(255) DEFAULT NULL,
  `sender_mobile` VARCHAR(50) DEFAULT NULL,
  `origin` VARCHAR(50) DEFAULT 'whatsapp',
  `last_message` JSON DEFAULT NULL,
  `profile` JSON DEFAULT NULL,
  `unread_count` INT DEFAULT 0,
  `chat_note` TEXT DEFAULT NULL,
  `chat_label` VARCHAR(255) DEFAULT NULL,
  `assigned_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_chat_id` (`chat_id`),
  INDEX `idx_uid` (`uid`),
  INDEX `idx_sender_mobile` (`sender_mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 7. BETA_CONVERSATION TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_conversation` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `chat_id` VARCHAR(255) NOT NULL,
  `msgContext` JSON DEFAULT NULL,
  `type` VARCHAR(50) DEFAULT NULL,
  `metaChatId` VARCHAR(255) DEFAULT NULL,
  `reaction` VARCHAR(50) DEFAULT '',
  `timestamp` VARCHAR(50) DEFAULT NULL,
  `senderName` VARCHAR(255) DEFAULT NULL,
  `senderMobile` VARCHAR(50) DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'sent',
  `err` TEXT DEFAULT NULL,
  `star` BOOLEAN DEFAULT FALSE,
  `route` VARCHAR(50) DEFAULT 'INCOMING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_chat_id` (`chat_id`),
  INDEX `idx_uid` (`uid`),
  INDEX `idx_metaChatId` (`metaChatId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 8. META_API TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `meta_api` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `waba_id` VARCHAR(255) DEFAULT NULL,
  `access_token` TEXT DEFAULT NULL,
  `business_phone_number_id` VARCHAR(255) DEFAULT NULL,
  `app_id` VARCHAR(255) DEFAULT NULL,
  `login_type` VARCHAR(50) DEFAULT 'api',
  `embed_data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 9. INSTANCE TABLE (for QR sessions)
-- ========================================
CREATE TABLE IF NOT EXISTS `instance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `number` VARCHAR(50) DEFAULT NULL,
  `uniqueId` VARCHAR(255) NOT NULL,
  `status` VARCHAR(50) DEFAULT 'disconnected',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_uniqueId` (`uniqueId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 10. CHATBOT TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `chatbot` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `origin` VARCHAR(50) DEFAULT 'whatsapp',
  `active` INT DEFAULT 0,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 11. BETA_CHATBOT TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_chatbot` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `source` VARCHAR(50) DEFAULT 'whatsapp',
  `active` INT DEFAULT 0,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 12. CHAT_TAGS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `chat_tags` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `hex` VARCHAR(50) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 13. FLOW_SESSION TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `flow_session` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `origin` VARCHAR(50) NOT NULL,
  `origin_id` VARCHAR(255) DEFAULT NULL,
  `flow_id` VARCHAR(255) NOT NULL,
  `sender_mobile` VARCHAR(50) NOT NULL,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_flow_id` (`flow_id`),
  INDEX `idx_sender_mobile` (`sender_mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 14. FLOW_DATA TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `flow_data` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `uniqueId` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_uniqueId` (`uniqueId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 15. AGENTS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `agents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) UNIQUE NOT NULL,
  `owner_uid` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `is_active` INT DEFAULT 1,
  `logs` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_owner_uid` (`owner_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 16. WA_CALL_FLOWS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `wa_call_flows` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `flow_id` VARCHAR(255) NOT NULL,
  `source` VARCHAR(50) DEFAULT 'whatsapp',
  `name` VARCHAR(255) NOT NULL,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_flow_id` (`flow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 17. WA_CALL_BOT TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `wa_call_bot` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `flow_id` VARCHAR(255) NOT NULL,
  `active` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 18. WA_CALL_LOGS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `wa_call_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `call_id` VARCHAR(255) DEFAULT NULL,
  `from_number` VARCHAR(50) DEFAULT NULL,
  `to_number` VARCHAR(50) DEFAULT NULL,
  `duration` INT DEFAULT 0,
  `status` VARCHAR(50) DEFAULT NULL,
  `recording_url` TEXT DEFAULT NULL,
  `data` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 19. WA_CALL_BROADCASTS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `wa_call_broadcasts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `campaign_id` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `flow_id` VARCHAR(255) NOT NULL,
  `contacts` JSON DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'draft',
  `permission_granted` INT DEFAULT 0,
  `permission_denied` INT DEFAULT 0,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `logs` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_campaign_id` (`campaign_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 20. BETA_API_LOGS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_api_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `msg_id` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT NULL,
  `err` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_msg_id` (`msg_id`),
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 21. BETA_CAMPAIGN_LOGS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `beta_campaign_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `meta_msg_id` VARCHAR(255) DEFAULT NULL,
  `delivery_status` VARCHAR(50) DEFAULT NULL,
  `error_message` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_meta_msg_id` (`meta_msg_id`),
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 22. WARMERS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `warmers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `is_active` INT DEFAULT 0,
  `schedule` JSON DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 23. PLAN TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `plan` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) DEFAULT 0.00,
  `features` JSON DEFAULT NULL,
  `contact_limit` INT DEFAULT 100,
  `is_trial` INT DEFAULT 0,
  `validity_days` INT DEFAULT 30,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 24. WEB_PUBLIC TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `web_public` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `logo` VARCHAR(255) DEFAULT NULL,
  `app_name` VARCHAR(255) DEFAULT 'WhatsApp CRM',
  `custom_home` TEXT DEFAULT NULL,
  `is_custom_home` INT DEFAULT 0,
  `meta_description` TEXT DEFAULT NULL,
  `currency_code` VARCHAR(10) DEFAULT 'USD',
  `fb_login_app_id` VARCHAR(255) DEFAULT NULL,
  `fb_login_app_sec` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 25. CONTACT_FORM TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `contact_form` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `mobile` VARCHAR(50) DEFAULT NULL,
  `content` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 26. GEN_LINKS TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `gen_links` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `wa_mobile` VARCHAR(50) NOT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `msg` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_wa_mobile` (`wa_mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 27. TELEGRAM_SESSIONS TABLE (Optional)
-- ========================================
CREATE TABLE IF NOT EXISTS `telegram_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `phone_number` VARCHAR(50) NOT NULL,
  `session_id` VARCHAR(255) NOT NULL,
  `api_id` VARCHAR(255) NOT NULL,
  `api_hash` VARCHAR(255) NOT NULL,
  `string_session` TEXT DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 28. ORDERS TABLE (for payment tracking)
-- ========================================
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `order_id` VARCHAR(255) NOT NULL,
  `payment_id` VARCHAR(255) DEFAULT NULL,
  `plan_id` INT DEFAULT NULL,
  `amount` DECIMAL(10,2) DEFAULT 0.00,
  `status` VARCHAR(50) DEFAULT 'pending',
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`),
  INDEX `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 29. ROOMS TABLE (for Socket.IO)
-- ========================================
CREATE TABLE IF NOT EXISTS `rooms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) NOT NULL,
  `socket_id` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 30. THEME TABLE
-- ========================================
CREATE TABLE IF NOT EXISTS `theme` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(255) DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `data` JSON DEFAULT NULL,
  `is_active` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- Insert Default Data
-- ========================================

-- Default admin user (password: admin123)
INSERT INTO `admin` (`uid`, `name`, `email`, `password`) VALUES
('admin123', 'Admin', 'admin@whatscrm.com', '$2b$10$YourHashedPasswordHere');

-- Default web settings
INSERT INTO `web_public` (`app_name`, `meta_description`, `currency_code`) VALUES
('WhatsApp CRM', 'WhatsApp CRM Application', 'USD');

-- Default free plan
INSERT INTO `plan` (`title`, `price`, `contact_limit`, `validity_days`, `is_trial`) VALUES
('Free Trial', 0.00, 100, 30, 1);

COMMIT;