database.sql

-- ================================================================
-- MySQL Database Schema for Respondly
-- Pure SQL for clean fresh installation
-- All patches consolidated — no ALTER TABLE or stored procedures needed
-- ================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Drop all tables for clean installation
DROP TABLE IF EXISTS `system_chat_messages`;
DROP TABLE IF EXISTS `system_chats`;
DROP TABLE IF EXISTS `system_knowledge`;
DROP TABLE IF EXISTS `agent_leads`;
DROP TABLE IF EXISTS `agent_api_keys`;
DROP TABLE IF EXISTS `agent_analytics`;
DROP TABLE IF EXISTS `agent_integrations`;
DROP TABLE IF EXISTS `agent_memory`;
DROP TABLE IF EXISTS `agent_messages`;
DROP TABLE IF EXISTS `agent_conversations`;
DROP TABLE IF EXISTS `agent_documents`;
DROP TABLE IF EXISTS `agent_knowledge`;
DROP TABLE IF EXISTS `business_agents`;
DROP TABLE IF EXISTS `business_product_images`;
DROP TABLE IF EXISTS `business_product_variants`;
DROP TABLE IF EXISTS `business_products`;
DROP TABLE IF EXISTS `business_categories`;
DROP TABLE IF EXISTS `business_leads`;
DROP TABLE IF EXISTS `business_invoices`;
DROP TABLE IF EXISTS `business_notifications`;
DROP TABLE IF EXISTS `business_integrations`;
DROP TABLE IF EXISTS `integration_logs`;
DROP TABLE IF EXISTS `business_order_items`;
DROP TABLE IF EXISTS `business_orders`;
DROP TABLE IF EXISTS `business_appointments`;
DROP TABLE IF EXISTS `business_chat_messages`;
DROP TABLE IF EXISTS `blog_views`;
DROP TABLE IF EXISTS `blog_post_tags`;
DROP TABLE IF EXISTS `blog_comments`;
DROP TABLE IF EXISTS `blog_posts`;
DROP TABLE IF EXISTS `blog_tags`;
DROP TABLE IF EXISTS `blog_categories`;
DROP TABLE IF EXISTS `business_extra_messages`;
DROP TABLE IF EXISTS `extra_messages_addons`;
DROP TABLE IF EXISTS `business_chats`;
DROP TABLE IF EXISTS `business_knowledge`;
DROP TABLE IF EXISTS `business_settings`;
DROP TABLE IF EXISTS `businesses`;
DROP TABLE IF EXISTS `payment_transactions`;
DROP TABLE IF EXISTS `chat_ai_locks`;
DROP TABLE IF EXISTS `webhook_message_log`;
DROP TABLE IF EXISTS `cron_execution_logs`;
DROP TABLE IF EXISTS `cron_jobs`;
DROP TABLE IF EXISTS `queue_jobs`;
DROP TABLE IF EXISTS `ticket_replies`;
DROP TABLE IF EXISTS `tickets`;
DROP TABLE IF EXISTS `webhook_deliveries`;
DROP TABLE IF EXISTS `global_webhooks`;
DROP TABLE IF EXISTS `files`;
DROP TABLE IF EXISTS `backups`;
DROP TABLE IF EXISTS `api_keys`;
DROP TABLE IF EXISTS `integrations`;
DROP TABLE IF EXISTS `ai_cache`;
DROP TABLE IF EXISTS `ai_usage_logs`;
DROP TABLE IF EXISTS `ai_models`;
DROP TABLE IF EXISTS `ai_api_keys`;
DROP TABLE IF EXISTS `payment_providers`;
DROP TABLE IF EXISTS `login_history`;
DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `ip_bans`;
DROP TABLE IF EXISTS `rate_limits`;
DROP TABLE IF EXISTS `subscription_plans`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `system_settings`;

SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================
-- 1. System Settings
-- ================================================================
CREATE TABLE `system_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) UNIQUE NOT NULL,
  `value` TEXT 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;

-- ================================================================
-- 2. Rate Limiting
-- ================================================================
CREATE TABLE `rate_limits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ip_address` VARCHAR(45) NOT NULL,
  `endpoint` VARCHAR(255) NOT NULL,
  `hits` INT DEFAULT 1,
  `reset_time` INT UNSIGNED NOT NULL,
  UNIQUE KEY `ip_endpoint` (`ip_address`, `endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 3. IP Ban
-- ================================================================
CREATE TABLE `ip_bans` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ip_address` VARCHAR(45) UNIQUE NOT NULL,
  `reason` TEXT NULL,
  `banned_by` INT NULL,
  `expires_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 4. Subscription Plans
-- ================================================================
CREATE TABLE `subscription_plans` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `billing_interval` ENUM('monthly', 'yearly') NOT NULL DEFAULT 'monthly',
  `limit_messages` INT NOT NULL DEFAULT 0,
  `limit_tokens` INT NOT NULL DEFAULT 0,
  `limit_storage_mb` INT NOT NULL DEFAULT 0,
  `limit_users` INT NOT NULL DEFAULT 0,
  `limit_agents` INT DEFAULT 5,
  `trial_days` INT DEFAULT 0,
  `allowed_channels` JSON DEFAULT ('[]'),
  `features` JSON NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  `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;

-- ================================================================
-- 5. Businesses (SaaS Tenants)
-- ================================================================
CREATE TABLE `businesses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) UNIQUE NOT NULL,
  `status` ENUM('Active', 'Suspended', 'Banned') NOT NULL DEFAULT 'Active',
  `subscription_plan_id` INT NULL,
  `subscription_status` VARCHAR(50) DEFAULT 'Trial',
  `subscription_expires_at` TIMESTAMP NULL,
  `logo_url` VARCHAR(500) DEFAULT NULL,
  `cover_url` VARCHAR(500) DEFAULT NULL,
  `settings_json` JSON DEFAULT NULL,
  `subscription_storage_mb` INT DEFAULT 1000,
  `trial_ends_at` TIMESTAMP NULL DEFAULT NULL,
  `daily_prompt_gen_count` INT NOT NULL DEFAULT 0,
  `daily_prompt_gen_date` DATE DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_business_plan` FOREIGN KEY (`subscription_plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 6. Users
-- ================================================================
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NULL,
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `role` ENUM('Super Admin', 'Admin', 'Moderator', 'Support Agent', 'Developer', 'Business Owner', 'Business User') NOT NULL DEFAULT 'Business User',
  `status` ENUM('Active', 'Suspended') NOT NULL DEFAULT 'Active',
  `failed_login_attempts` INT DEFAULT 0,
  `locked_until` TIMESTAMP NULL DEFAULT NULL,
  `two_factor_secret` VARCHAR(100) NULL,
  `two_factor_enabled` TINYINT(1) DEFAULT 0,
  `settings_json` JSON DEFAULT NULL,
  `avatar_url` VARCHAR(500) DEFAULT NULL,
  `google_id` VARCHAR(100) DEFAULT NULL,
  `email_verified` TINYINT(1) DEFAULT 0,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `verification_code` VARCHAR(10) DEFAULT NULL,
  `verification_expires_at` TIMESTAMP NULL DEFAULT NULL,
  `password_reset_token` VARCHAR(255) DEFAULT NULL,
  `password_reset_expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_user_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 7. Login History
-- ================================================================
CREATE TABLE `login_history` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(255) NULL,
  `device_details` VARCHAR(255) NULL,
  `status` ENUM('Success', 'Failed') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_login_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_login_user` (`user_id`),
  INDEX `idx_login_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 8. Audit Logs
-- ================================================================
CREATE TABLE `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `action` VARCHAR(255) NOT NULL,
  `details` JSON NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  INDEX `idx_user_id` (`user_id`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 9. Payment Providers
-- ================================================================
CREATE TABLE `payment_providers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `provider_code` VARCHAR(50) DEFAULT NULL,
  `api_credentials` TEXT NOT NULL,
  `webhook_url` VARCHAR(255) NOT NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Inactive',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_provider_code` (`provider_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prebuilt global gateways (Stripe, PayPal, Adyen, Paymob, Fawaterk)
INSERT INTO `payment_providers` (`name`, `provider_code`, `api_credentials`, `webhook_url`, `status`) VALUES
('Stripe', 'stripe', 'N/A', 'http://localhost/api/webhook/stripe', 'Inactive'),
('PayPal', 'paypal', 'N/A', 'http://localhost/api/webhook/paypal', 'Inactive'),
('Adyen', 'adyen', 'N/A', 'http://localhost/api/webhook/adyen', 'Inactive'),
('Paymob', 'paymob', 'N/A', 'http://localhost/api/webhook/paymob', 'Inactive'),
('Fawaterk', 'fawaterk', 'N/A', 'http://localhost/api/webhook/fawaterk', 'Inactive');

-- ================================================================
-- 10. AI API Keys
-- ================================================================
CREATE TABLE `ai_api_keys` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `provider` VARCHAR(50) NOT NULL DEFAULT 'OpenRouter',
  `name` VARCHAR(100) NOT NULL,
  `api_key` TEXT NOT NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  `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;

-- ================================================================
-- 11. AI Models
-- ================================================================
CREATE TABLE `ai_models` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `model_identifier` VARCHAR(150) UNIQUE NOT NULL,
  `priority` INT NOT NULL DEFAULT 1,
  `cost_per_token_prompt` DECIMAL(12,8) NOT NULL DEFAULT 0.00000000,
  `cost_per_token_completion` DECIMAL(12,8) NOT NULL DEFAULT 0.00000000,
  `fallback_model_id` INT NULL,
  `provider` VARCHAR(50) DEFAULT 'openai',
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_model_fallback` FOREIGN KEY (`fallback_model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 12. AI Usage Logs
-- ================================================================
CREATE TABLE `ai_usage_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `user_id` INT NULL,
  `model_id` INT NULL,
  `agent_id` INT DEFAULT NULL,
  `prompt_tokens` INT NOT NULL DEFAULT 0,
  `completion_tokens` INT NOT NULL DEFAULT 0,
  `cost` DECIMAL(16,8) NOT NULL DEFAULT 0.00000000,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_ai_log_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ai_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ai_log_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL,
  INDEX `idx_usage_business` (`business_id`),
  INDEX `idx_usage_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 13. AI Cache
-- ================================================================
CREATE TABLE `ai_cache` (
  `hash` VARCHAR(64) PRIMARY KEY,
  `response` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 14. Integrations (Platform)
-- ================================================================
CREATE TABLE `integrations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `config` TEXT NOT NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Inactive',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_integrations_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 15. Global Webhooks
-- ================================================================
CREATE TABLE `global_webhooks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `url` VARCHAR(255) NOT NULL,
  `events` JSON NOT NULL,
  `secret` VARCHAR(100) NOT NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  `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;

-- ================================================================
-- 16. Webhook Deliveries
-- ================================================================
CREATE TABLE `webhook_deliveries` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `webhook_id` INT NOT NULL,
  `event` VARCHAR(100) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `response_status` INT NULL,
  `response_body` TEXT NULL,
  `attempts` INT DEFAULT 1,
  `status` ENUM('Success', 'Failed', 'Pending') NOT NULL DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_delivery_webhook` FOREIGN KEY (`webhook_id`) REFERENCES `global_webhooks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 17. Support Tickets
-- ================================================================
CREATE TABLE `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NULL,
  `user_id` INT NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `type` ENUM('Ticket', 'Bug Report', 'Feature Request') NOT NULL DEFAULT 'Ticket',
  `priority` ENUM('Low', 'Medium', 'High', 'Urgent') NOT NULL DEFAULT 'Low',
  `status` ENUM('Open', 'In Progress', 'Resolved', 'Closed') NOT NULL DEFAULT 'Open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_ticket_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ticket_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 18. Ticket Replies
-- ================================================================
CREATE TABLE `ticket_replies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `message` LONGTEXT NOT NULL,
  `is_internal` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_reply_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_reply_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 19. Backups
-- ================================================================
CREATE TABLE `backups` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(255) NOT NULL,
  `size` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('Success', 'Failed') NOT NULL DEFAULT 'Success',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 20. Files Manager
-- ================================================================
CREATE TABLE `files` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `path` VARCHAR(255) NOT NULL,
  `size` INT UNSIGNED NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `folder` VARCHAR(255) DEFAULT '/',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_files_folder` (`folder`),
  INDEX `idx_files_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 21. API Keys
-- ================================================================
CREATE TABLE `api_keys` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `key_hash` VARCHAR(64) UNIQUE NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `permissions` JSON NOT NULL,
  `status` ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_apikey_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 22. Business Settings
-- ================================================================
CREATE TABLE `business_settings` (
  `business_id` INT PRIMARY KEY,
  `business_type` VARCHAR(100) NOT NULL DEFAULT 'Custom',
  `strict_ai_mode` TINYINT(1) DEFAULT 1,
  `emergency_mode` TINYINT(1) DEFAULT 0,
  `order_timeout_minutes` INT DEFAULT 30,
  `primary_model_id` INT NULL,
  `secondary_model_id` INT NULL,
  `emergency_model_id` INT NULL,
  `comment_model_id` INT DEFAULT NULL,
  `vision_model_id` INT DEFAULT NULL,
  `comments_auto_reply` TINYINT(1) DEFAULT 0,
  `comments_private_dm` TINYINT(1) DEFAULT 0,
  `comments_keywords` VARCHAR(500) DEFAULT NULL,
  `comments_ai_prompt` TEXT DEFAULT NULL,
  `comments_platforms` VARCHAR(255) DEFAULT NULL,
  `order_auto_confirm_hours` INT DEFAULT 24,
  `order_confirm_message_template` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bs_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bs_primary` FOREIGN KEY (`primary_model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_bs_secondary` FOREIGN KEY (`secondary_model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_bs_emergency` FOREIGN KEY (`emergency_model_id`) REFERENCES `ai_models` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 23. Business Knowledge Base
-- ================================================================
CREATE TABLE `business_knowledge` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `agent_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bk_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `idx_knowledge_business` (`business_id`),
  INDEX `idx_knowledge_content` (`business_id`, `category`),
  INDEX `idx_knowledge_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 24. Business Orders
-- ================================================================
CREATE TABLE `business_orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `customer_name` VARCHAR(150) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL,
  `customer_address` TEXT NOT NULL,
  `details` TEXT NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `amount_paid` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `remaining_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('Pending', 'Confirmed', 'Shipped', 'Delivered', 'Cancelled', 'Refunded') NOT NULL DEFAULT 'Pending',
  `shipment_tracking_number` VARCHAR(100) NULL,
  `shipment_status` VARCHAR(100) NOT NULL DEFAULT 'Manifest Created',
  `confirmed_at` TIMESTAMP NULL,
  `source` VARCHAR(50) DEFAULT 'dashboard',
  `confirmation_status` ENUM('pending','awaiting_reply','reminder_sent','confirmed','cancelled','auto_confirmed','auto_cancelled') DEFAULT 'pending',
  `confirmation_deadline` TIMESTAMP NULL DEFAULT NULL,
  `confirmation_delay` INT DEFAULT 0,
  `confirmation_sent_at` TIMESTAMP NULL DEFAULT NULL,
  `confirmation_received_at` TIMESTAMP NULL DEFAULT NULL,
  `confirmation_response` VARCHAR(50) NULL DEFAULT NULL,
  `reminder_count` INT DEFAULT 0,
  `last_reminder_at` TIMESTAMP NULL DEFAULT NULL,
  `auto_cancel_at` TIMESTAMP NULL DEFAULT NULL,
  `cancel_reason` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bo_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `idx_order_business` (`business_id`),
  INDEX `idx_order_created` (`business_id`, `created_at`),
  INDEX `idx_order_customer_phone` (`customer_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 25. Business Order Items
-- ================================================================
CREATE TABLE `business_order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `product_name` VARCHAR(255) DEFAULT NULL,
  `quantity` INT DEFAULT 1,
  `unit_price` DECIMAL(10,2) DEFAULT 0.00,
  `cost_price` DECIMAL(10,2) DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_oi_order` (`order_id`),
  CONSTRAINT `fk_oi_order` FOREIGN KEY (`order_id`) REFERENCES `business_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 26. Business Agents (MUST come before business_chats due to FK)
-- ================================================================
CREATE TABLE `business_agents` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `avatar_url` VARCHAR(500) DEFAULT NULL,
  `role_description` text,
  `welcome_message` text DEFAULT NULL,
  `model_id` INT DEFAULT NULL,
  `temperature` DECIMAL(3,2) DEFAULT 0.70,
  `max_tokens` INT DEFAULT 2048,
  `language` VARCHAR(10) DEFAULT 'ar',
  `widget_color` VARCHAR(7) DEFAULT '#6366f1',
  `widget_position` ENUM('bottom-right','bottom-left') DEFAULT 'bottom-right',
  `daily_reply_limit` INT NOT NULL DEFAULT 3,
  `daily_reply_count` INT NOT NULL DEFAULT 0,
  `last_reply_date` DATE DEFAULT NULL,
  `status` ENUM('Active','Inactive') DEFAULT 'Active',
  `is_archived` TINYINT(1) DEFAULT 0,
  `archived_at` TIMESTAMP NULL DEFAULT NULL,
  `total_chats` INT DEFAULT 0,
  `total_messages` INT DEFAULT 0,
  `total_leads` INT DEFAULT 0,
  `last_active_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ba_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_ba_business` (`business_id`),
  INDEX `idx_ba_status` (`business_id`, `status`),
  INDEX `idx_ba_archived` (`business_id`, `is_archived`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 27. Business Chats
-- ================================================================
CREATE TABLE `business_chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `channel` VARCHAR(50) NOT NULL DEFAULT 'website',
  `customer_name` VARCHAR(150) NOT NULL DEFAULT 'Guest Customer',
  `customer_identifier` VARCHAR(150) NOT NULL,
  `client_hash` VARCHAR(100) DEFAULT NULL,
  `ai_status` ENUM('Active', 'Paused', 'Disabled') NOT NULL DEFAULT 'Active',
  `escalated_to_human` TINYINT(1) DEFAULT 0,
  `escalation_pending` TINYINT(1) DEFAULT 0,
  `short_term_memory` TEXT NULL,
  `long_term_memory` TEXT NULL,
  `chat_tags` VARCHAR(500) NULL,
  `customer_avatar` VARCHAR(500) DEFAULT NULL,
  `agent_id` INT DEFAULT NULL,
  `customer_typing_at` TIMESTAMP NULL DEFAULT NULL,
  `admin_typing_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `bus_cust_channel` (`business_id`, `customer_identifier`, `channel`),
  CONSTRAINT `fk_bc_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bc_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE SET NULL,
  INDEX `idx_chat_business_status` (`business_id`, `ai_status`),
  INDEX `idx_chat_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 28. Business Chat Messages
-- ================================================================
CREATE TABLE `business_chat_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `chat_id` INT NOT NULL,
  `sender` ENUM('Customer', 'AI', 'Human') NOT NULL,
  `message` TEXT NOT NULL,
  `external_message_id` TEXT DEFAULT NULL,
  `is_edited` TINYINT(1) NOT NULL DEFAULT 0,
  `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `agent_id` INT DEFAULT NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bcm_chat` FOREIGN KEY (`chat_id`) REFERENCES `business_chats` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bcm_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE SET NULL,
  INDEX `idx_chat_id` (`chat_id`),
  INDEX `idx_chat_sender` (`chat_id`, `sender`),
  INDEX `idx_chat_created` (`chat_id`, `created_at`),
  INDEX `idx_msg_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 28b. Business Chat Reactions
-- ================================================================
CREATE TABLE `business_chat_reactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `message_id` INT NOT NULL,
  `emoji` VARCHAR(10) NOT NULL,
  `user_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bcr_message` FOREIGN KEY (`message_id`) REFERENCES `business_chat_messages` (`id`) ON DELETE CASCADE,
  INDEX `idx_bcr_message` (`message_id`),
  UNIQUE KEY `unique_reaction` (`message_id`, `emoji`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 29. Business Appointments
-- ================================================================
CREATE TABLE `business_appointments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `customer_name` VARCHAR(150) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL,
  `customer_email` VARCHAR(150) NULL,
  `service` VARCHAR(200) NOT NULL DEFAULT 'استشارة',
  `appointment_date` DATE NOT NULL,
  `appointment_time` TIME NOT NULL,
  `duration_minutes` INT NOT NULL DEFAULT 30,
  `notes` TEXT NULL,
  `status` ENUM('Pending', 'Confirmed', 'Completed', 'Cancelled', 'NoShow') NOT NULL DEFAULT 'Pending',
  `confirmed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_bapp_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `idx_appointment_business` (`business_id`),
  INDEX `idx_appointment_date` (`business_id`, `appointment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 29. Agent Knowledge Base
-- ================================================================
CREATE TABLE `agent_knowledge` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT 'general',
  `title` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `image_url` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ak_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ak_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_ak_agent` (`agent_id`),
  INDEX `fk_ak_business` (`business_id`),
  INDEX `idx_ak_agent_category` (`agent_id`, `category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 30. Agent Documents
-- ================================================================
CREATE TABLE `agent_documents` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED DEFAULT 0,
  `file_type` VARCHAR(50) DEFAULT NULL,
  `status` ENUM('Processing','Ready','Failed') DEFAULT 'Processing',
  `chunk_count` INT DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ad_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ad_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_ad_agent` (`agent_id`),
  INDEX `fk_ad_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 31. Agent Conversations
-- ================================================================
CREATE TABLE `agent_conversations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `customer_name` VARCHAR(150) DEFAULT 'Guest',
  `customer_identifier` VARCHAR(150) NOT NULL,
  `channel` VARCHAR(50) DEFAULT 'widget',
  `status` ENUM('Active','Closed') DEFAULT 'Active',
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ac_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ac_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_ac_agent` (`agent_id`),
  INDEX `fk_ac_business` (`business_id`),
  INDEX `idx_ac_agent_status` (`agent_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 32. Agent Messages
-- ================================================================
CREATE TABLE `agent_messages` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `conversation_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `sender` ENUM('Customer','AI','Human') NOT NULL,
  `message` TEXT NOT NULL,
  `tokens_used` INT DEFAULT 0,
  `model_used` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_am_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `agent_conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_am_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  INDEX `fk_am_conversation` (`conversation_id`),
  INDEX `fk_am_agent` (`agent_id`),
  INDEX `idx_am_conv_created` (`conversation_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 33. Agent Memory
-- ================================================================
CREATE TABLE `agent_memory` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `conversation_id` INT DEFAULT NULL,
  `memory_type` ENUM('short_term','long_term','entity') DEFAULT 'long_term',
  `content` TEXT NOT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_amem_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_amem_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_amem_agent` (`agent_id`),
  INDEX `fk_amem_business` (`business_id`),
  INDEX `idx_amem_agent_type` (`agent_id`, `memory_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 34. Agent Integrations
-- ================================================================
CREATE TABLE `agent_integrations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `platform` VARCHAR(50) NOT NULL,
  `config_json` TEXT DEFAULT NULL,
  `status` ENUM('Active','Inactive') DEFAULT 'Inactive',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_agent_platform` (`agent_id`, `platform`),
  CONSTRAINT `fk_ai_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ai_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_ai_agent` (`agent_id`),
  INDEX `fk_ai_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 35. Agent Analytics
-- ================================================================
CREATE TABLE `agent_analytics` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `total_conversations` INT DEFAULT 0,
  `total_messages` INT DEFAULT 0,
  `total_tokens` INT DEFAULT 0,
  `total_cost` DECIMAL(16,8) DEFAULT 0.00000000,
  `avg_response_time_ms` INT DEFAULT 0,
  `satisfaction_score` DECIMAL(3,2) DEFAULT NULL,
  `leads_generated` INT DEFAULT 0,
  `escalations` INT DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_aa_agent_date` (`agent_id`, `date`),
  CONSTRAINT `fk_aa_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_aa_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_aa_business` (`business_id`),
  INDEX `idx_aa_date_range` (`agent_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 36. Agent API Keys
-- ================================================================
CREATE TABLE `agent_api_keys` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `key_hash` VARCHAR(64) NOT NULL,
  `key_prefix` VARCHAR(12) NOT NULL,
  `permissions` JSON DEFAULT NULL,
  `rate_limit` INT DEFAULT 100,
  `status` ENUM('Active','Inactive') DEFAULT 'Active',
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_aak_keyhash` (`key_hash`),
  CONSTRAINT `fk_aak_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_aak_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_aak_agent` (`agent_id`),
  INDEX `fk_aak_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 37. Agent Leads
-- ================================================================
CREATE TABLE `agent_leads` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `agent_id` INT NOT NULL,
  `conversation_id` INT DEFAULT NULL,
  `name` VARCHAR(150) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `status` ENUM('New','Contacted','Qualified','Converted','Lost') DEFAULT 'New',
  `source` VARCHAR(100) DEFAULT 'chat',
  `notes` TEXT DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_alead_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_alead_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_alead_agent` (`agent_id`),
  INDEX `fk_alead_business` (`business_id`),
  INDEX `idx_alead_agent_status` (`agent_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 38. Business Leads
-- ================================================================
CREATE TABLE `business_leads` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `name` VARCHAR(150) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `status` VARCHAR(50) DEFAULT 'New',
  `source` VARCHAR(100) DEFAULT NULL,
  `notes` TEXT,
  `agent_id` INT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_bl_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bl_agent` FOREIGN KEY (`agent_id`) REFERENCES `business_agents` (`id`) ON DELETE SET NULL,
  INDEX `fk_bl_business` (`business_id`),
  INDEX `idx_business_leads_created` (`created_at`),
  INDEX `idx_leads_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 39. Business Categories
-- ================================================================
CREATE TABLE `business_categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_bcat_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_bcat_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 40. Business Products
-- ================================================================
CREATE TABLE `business_products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `category_id` INT DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `sku` VARCHAR(100) DEFAULT NULL,
  `price` DECIMAL(10,2) DEFAULT '0.00',
  `currency` VARCHAR(10) DEFAULT 'EGP',
  `stock_quantity` INT DEFAULT '0',
  `brand` VARCHAR(100) DEFAULT NULL,
  `tags` TEXT,
  `description` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_bp_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bp_category` FOREIGN KEY (`category_id`) REFERENCES `business_categories` (`id`) ON DELETE SET NULL,
  INDEX `fk_bp_business` (`business_id`),
  INDEX `fk_bp_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 41. Business Product Variants
-- ================================================================
CREATE TABLE `business_product_variants` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NOT NULL,
  `name` VARCHAR(100) DEFAULT NULL,
  `sku` VARCHAR(100) DEFAULT NULL,
  `price_override` DECIMAL(10,2) DEFAULT NULL,
  `stock_quantity` INT DEFAULT '0',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fp_bpv_product` FOREIGN KEY (`product_id`) REFERENCES `business_products` (`id`) ON DELETE CASCADE,
  INDEX `fp_bpv_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 42. Business Product Images
-- ================================================================
CREATE TABLE `business_product_images` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NOT NULL,
  `image_path` VARCHAR(500) DEFAULT NULL,
  `thumbnail_path` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT DEFAULT '0',
  `is_main` TINYINT(1) DEFAULT '0',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_bpi_product` FOREIGN KEY (`product_id`) REFERENCES `business_products` (`id`) ON DELETE CASCADE,
  INDEX `fk_bpi_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 43. Business Invoices
-- ================================================================
CREATE TABLE `business_invoices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `plan_name` VARCHAR(100) DEFAULT NULL,
  `amount` DECIMAL(10,2) DEFAULT NULL,
  `currency` VARCHAR(10) DEFAULT 'EGP',
  `status` ENUM('Pending','Paid','Failed','Cancelled') DEFAULT 'Pending',
  `billing_cycle` VARCHAR(20) DEFAULT NULL,
  `due_date` DATE DEFAULT NULL,
  `paid_at` TIMESTAMP NULL DEFAULT NULL,
  `reference_number` VARCHAR(100) DEFAULT NULL,
  `raw_payload` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_binv_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_binv_business` (`business_id`),
  INDEX `idx_business_invoices_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 44. Business Notifications
-- ================================================================
CREATE TABLE `business_notifications` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `type` VARCHAR(50) DEFAULT 'info',
  `message` TEXT,
  `title` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT '0',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_bn_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_bn_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 45. Business Integrations
-- ================================================================
CREATE TABLE `business_integrations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT NOT NULL,
  `platform` VARCHAR(50) NOT NULL,
  `config_json` TEXT,
  `status` ENUM('Active','Inactive') DEFAULT 'Inactive',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bus_platform` (`business_id`,`platform`),
  CONSTRAINT `fk_bi_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  INDEX `fk_bi_business` (`business_id`),
  INDEX `idx_bi_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 46. Integration Logs
-- ================================================================
CREATE TABLE `integration_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `integration_id` INT NOT NULL,
  `direction` ENUM('Inbound', 'Outbound') NOT NULL,
  `status_code` INT NULL,
  `payload` LONGTEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_integration_log_parent` FOREIGN KEY (`integration_id`) REFERENCES `business_integrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 47. Payment Transactions
-- ================================================================
CREATE TABLE `payment_transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `business_id` INT DEFAULT NULL,
  `order_id` INT DEFAULT NULL,
  `invoice_id` INT DEFAULT NULL,
  `invoice_reference` VARCHAR(255) DEFAULT NULL,
  `gateway` VARCHAR(50) NOT NULL,
  `transaction_id` VARCHAR(255) DEFAULT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `currency` VARCHAR(3) DEFAULT 'USD',
  `status` VARCHAR(30) NOT NULL DEFAULT 'Pending',
  `payment_method` VARCHAR(100) DEFAULT NULL,
  `customer_email` VARCHAR(255) DEFAULT NULL,
  `customer_name` VARCHAR(255) DEFAULT NULL,
  `raw_payload` LONGTEXT DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_invoice_reference` (`invoice_reference`),
  INDEX `idx_payment_txn_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 48. Queue Jobs
-- ================================================================
CREATE TABLE `queue_jobs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NULL,
  `job_type` VARCHAR(100) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `status` ENUM('Pending', 'Processing', 'Completed', 'Failed') NOT NULL DEFAULT 'Pending',
  `attempts` INT NOT NULL DEFAULT 0,
  `error_message` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_queue_status_created` (`status`, `created_at`),
  INDEX `idx_queue_business` (`business_id`),
  CONSTRAINT `fk_qj_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 49b. Sessions
-- ================================================================
CREATE TABLE `sessions` (
  `id` VARCHAR(128) NOT NULL PRIMARY KEY,
  `data` MEDIUMTEXT NOT NULL,
  `last_activity` INT UNSIGNED NOT NULL,
  INDEX `idx_sessions_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 50. System Knowledge Base
-- ================================================================
CREATE TABLE `system_knowledge` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 51. System Chats (Homepage Live Chat)
-- ================================================================
CREATE TABLE `system_chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `session_id` VARCHAR(128) NOT NULL,
  `visitor_name` VARCHAR(150) DEFAULT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `status` ENUM('Active', 'Human_Intervention', 'Closed') NOT NULL DEFAULT 'Active',
  `customer_typing_at` TIMESTAMP NULL DEFAULT NULL,
  `admin_typing_at` TIMESTAMP NULL 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;

-- ================================================================
-- 52. System Chat Messages
-- ================================================================
CREATE TABLE `system_chat_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `chat_id` INT NOT NULL,
  `sender` ENUM('User', 'AI', 'Human') NOT NULL,
  `message` TEXT NOT NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_scm_chat` FOREIGN KEY (`chat_id`) REFERENCES `system_chats` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 53. Extra Messages Add-ons (الرسائل الإضافية)
-- ================================================================
CREATE TABLE `extra_messages_addons` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `message_count` INT NOT NULL DEFAULT 0,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `is_one_time` TINYINT(1) NOT NULL DEFAULT 0,
    `status` ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    `display_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_addons_status_order` (`status`, `display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 54. Business Extra Messages (owned add-ons)
-- ================================================================
CREATE TABLE `business_extra_messages` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `business_id` INT NOT NULL,
    `addon_id` INT NOT NULL,
    `original_messages` INT NOT NULL DEFAULT 0,
    `remaining_messages` INT NOT NULL DEFAULT 0,
    `purchased_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `consumed_at` TIMESTAMP NULL,
    `invoice_id` INT NULL,
    `status` ENUM('Active','Consumed','Expired','Pending') NOT NULL DEFAULT 'Active',
    INDEX `idx_bem_business_status` (`business_id`, `status`),
    INDEX `idx_bem_addon` (`addon_id`),
    CONSTRAINT `fk_bem_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bem_addon` FOREIGN KEY (`addon_id`) REFERENCES `extra_messages_addons` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 55. Cron Jobs
-- ================================================================
CREATE TABLE `cron_jobs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `description` TEXT DEFAULT NULL,
    `script_path` VARCHAR(500) NOT NULL,
    `schedule_cron` VARCHAR(100) DEFAULT NULL,
    `is_enabled` TINYINT(1) DEFAULT 1,
    `last_run_at` TIMESTAMP NULL DEFAULT NULL,
    `last_status` ENUM('pending','running','success','failed') DEFAULT 'pending',
    `last_duration_seconds` DECIMAL(10,2) DEFAULT NULL,
    `last_output` TEXT DEFAULT NULL,
    `total_runs` INT DEFAULT 0,
    `total_failures` INT DEFAULT 0,
    `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;

-- ================================================================
-- 56. Cron Execution Logs
-- ================================================================
CREATE TABLE `cron_execution_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `cron_job_id` INT NOT NULL,
    `status` ENUM('running','success','failed') DEFAULT 'running',
    `triggered_by` VARCHAR(50) DEFAULT 'system',
    `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `finished_at` TIMESTAMP NULL DEFAULT NULL,
    `duration_seconds` DECIMAL(10,2) DEFAULT NULL,
    `output` TEXT DEFAULT NULL,
    `error_message` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cel_job` FOREIGN KEY (`cron_job_id`) REFERENCES `cron_jobs` (`id`) ON DELETE CASCADE,
    INDEX `idx_cel_job_id` (`cron_job_id`),
    INDEX `idx_cel_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 57. Webhook Message Deduplication Log
-- ================================================================
CREATE TABLE `webhook_message_log` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `platform` VARCHAR(20) NOT NULL,
    `message_id` VARCHAR(255) NOT NULL,
    `business_id` INT NOT NULL,
    `status` ENUM('Processing','Completed','Failed') NOT NULL DEFAULT 'Processing',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_platform_msg` (`platform`, `message_id`),
    INDEX `idx_wml_created` (`created_at`),
    INDEX `idx_wml_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 58. Chat AI Concurrency Locks
-- ================================================================
CREATE TABLE `chat_ai_locks` (
    `chat_id` INT NOT NULL,
    `locked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 59. Vodafone Cash Payment Requests
-- ================================================================
CREATE TABLE `vodafone_cash_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT NOT NULL,
  `invoice_id` INT NULL,
  `plan_id` INT NULL,
  `sender_phone` VARCHAR(20) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `receipt_image` VARCHAR(500) NOT NULL,
  `status` ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
  `admin_notes` TEXT NULL,
  `reviewed_by` INT NULL,
  `reviewed_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_vcr_business` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vcr_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `business_invoices` (`id`) ON DELETE SET NULL,
  INDEX `idx_vcr_status` (`status`),
  INDEX `idx_vcr_business` (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- Seed Data: System Settings
-- ================================================================
INSERT INTO `system_settings` (`key`, `value`) VALUES
('email_verification_enabled', '0'),
('forgot_password_enabled', '1'),
('smtp_test_recipient', '');

-- ================================================================
-- Blog Tables
-- ================================================================

CREATE TABLE `blog_categories` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `description` TEXT NULL,
    `image` VARCHAR(500) NULL,
    `seo_title` VARCHAR(255) NULL,
    `seo_description` TEXT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `status` ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_blog_cat_slug` (`slug`),
    INDEX `idx_blog_cat_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_tags` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_blog_tag_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_posts` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(500) NOT NULL,
    `slug` VARCHAR(500) NOT NULL UNIQUE,
    `excerpt` TEXT NULL,
    `content` LONGTEXT NULL,
    `featured_image` VARCHAR(500) NULL,
    `category_id` INT NULL,
    `author_id` INT NULL,
    `status` ENUM('Draft','Published','Archived') NOT NULL DEFAULT 'Draft',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `meta_title` VARCHAR(255) NULL,
    `meta_description` TEXT NULL,
    `reading_time` INT NOT NULL DEFAULT 0,
    `published_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_blog_post_slug` (`slug`),
    INDEX `idx_blog_post_status` (`status`),
    INDEX `idx_blog_post_featured` (`is_featured`),
    INDEX `idx_blog_post_category` (`category_id`),
    INDEX `idx_blog_post_author` (`author_id`),
    INDEX `idx_blog_post_published` (`published_at`),
    CONSTRAINT `fk_blog_post_category` FOREIGN KEY (`category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_blog_post_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_post_tags` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `tag_id` INT NOT NULL,
    UNIQUE KEY `uk_post_tag` (`post_id`, `tag_id`),
    CONSTRAINT `fk_bpt_post` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bpt_tag` FOREIGN KEY (`tag_id`) REFERENCES `blog_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_comments` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `parent_id` INT NULL,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NULL,
    `website` VARCHAR(500) NULL,
    `content` TEXT NOT NULL,
    `status` ENUM('Pending','Approved','Spam') NOT NULL DEFAULT 'Pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_blog_comment_post` (`post_id`),
    INDEX `idx_blog_comment_status` (`status`),
    CONSTRAINT `fk_blog_comment_post` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_blog_comment_parent` FOREIGN KEY (`parent_id`) REFERENCES `blog_comments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_views` (
    `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `viewed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_blog_view_post` (`post_id`),
    INDEX `idx_blog_view_date` (`viewed_at`),
    CONSTRAINT `fk_blog_view_post` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- Seed Data: Cron Jobs
-- ================================================================
INSERT INTO `cron_jobs` (`name`, `slug`, `description`, `script_path`, `schedule_cron`, `is_enabled`) VALUES
('Log Cleanup', 'log-cleanup', 'Rotates old logs, removes stale rate limits and AI locks', 'cron_cleanup_logs.php', '0 3 * * *', 1),
('Order Confirmation', 'order-confirmation', 'Auto-confirm pending orders', 'cron_order_confirmation.php', '*/15 * * * *', 1),
('Queue Worker', 'queue-worker', 'Processes pending background queue tasks', 'cron_queue_worker.php', '* * * * *', 1);

-- ================================================================
-- Performance Indexes (added for production readiness)
-- ================================================================
CREATE INDEX `idx_users_business_id` ON `users` (`business_id`);
CREATE INDEX `idx_users_role` ON `users` (`role`);
CREATE INDEX `idx_users_email_status` ON `users` (`email`, `status`);
CREATE INDEX `idx_tickets_business_id` ON `tickets` (`business_id`);
CREATE INDEX `idx_tickets_user_id` ON `tickets` (`user_id`);
CREATE INDEX `idx_tickets_status` ON `tickets` (`status`);
CREATE INDEX `idx_ticket_replies_ticket_id` ON `ticket_replies` (`ticket_id`);
CREATE INDEX `idx_webhook_deliveries_webhook_id` ON `webhook_deliveries` (`webhook_id`);
CREATE INDEX `idx_integration_logs_integration_id` ON `integration_logs` (`integration_id`);
CREATE INDEX `idx_business_chat_messages_external_id` ON `business_chat_messages` (`external_message_id`);
CREATE INDEX `idx_chat_messages_chat_id_id` ON `business_chat_messages` (`chat_id`, `id`);
CREATE INDEX `idx_chat_messages_unread` ON `business_chat_messages` (`sender`, `read_at`, `chat_id`);
CREATE INDEX `idx_chat_messages_edited_deleted` ON `business_chat_messages` (`chat_id`, `is_edited`, `is_deleted`);
CREATE INDEX `idx_chats_business_created` ON `business_chats` (`business_id`, `created_at`);
CREATE INDEX `idx_chats_escalated` ON `business_chats` (`business_id`, `escalated_to_human`);
CREATE INDEX `idx_business_leads_status` ON `business_leads` (`business_id`, `status`);
CREATE INDEX `idx_business_notifications_read` ON `business_notifications` (`business_id`, `is_read`);
CREATE INDEX `idx_system_chats_session_id` ON `system_chats` (`session_id`);
CREATE INDEX `idx_system_chat_messages_chat_id` ON `system_chat_messages` (`chat_id`);
CREATE INDEX `idx_invoices_business_status` ON `business_invoices` (`business_id`, `status`);