-- Hybrid WhatsApp Gateway SaaS Platform
-- Database Schema


-- =====================================================
-- USERS & AUTHENTICATION
-- =====================================================

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(36) NOT NULL UNIQUE,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password` VARCHAR(255) NULL,
    `name` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(20) NULL,
    `company_name` VARCHAR(255) NULL,
    `avatar` VARCHAR(500) NULL,
    `role` ENUM('client', 'admin', 'superadmin') DEFAULT 'client',
    `status` ENUM('active', 'suspended', 'banned', 'pending') DEFAULT 'active',
    `email_verified_at` TIMESTAMP NULL,
    `google_id` VARCHAR(255) NULL,
    `timezone` VARCHAR(50) DEFAULT 'UTC',
    `language` VARCHAR(10) DEFAULT 'en',
    `two_factor_enabled` TINYINT(1) DEFAULT 0,
    `two_factor_secret` VARCHAR(255) NULL,
    `last_login_at` TIMESTAMP NULL,
    `last_login_ip` VARCHAR(45) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_users_email` (`email`),
    INDEX `idx_users_role` (`role`),
    INDEX `idx_users_status` (`status`),
    INDEX `idx_users_google_id` (`google_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_resets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_password_resets_email` (`email`),
    INDEX `idx_password_resets_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(128) PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` TEXT NOT NULL,
    `last_activity` INT NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_sessions_user_id` (`user_id`),
    INDEX `idx_sessions_last_activity` (`last_activity`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PACKAGES & BILLING
-- =====================================================

CREATE TABLE IF NOT EXISTS `packages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT NULL,
    `price_monthly` DECIMAL(10,2) DEFAULT 0.00,
    `price_yearly` DECIMAL(10,2) DEFAULT 0.00,
    `messages_limit` INT DEFAULT 1000,
    `whatsapp_numbers_limit` INT DEFAULT 1,
    `projects_per_number_limit` INT DEFAULT 1,
    `api_rate_limit` INT DEFAULT 60,
    `meta_enabled` TINYINT(1) DEFAULT 1,
    `baileys_enabled` TINYINT(1) DEFAULT 0,
    `webhook_retries` INT DEFAULT 3,
    `message_retention_days` INT DEFAULT 30,
    `support_level` ENUM('basic', 'priority', 'dedicated') DEFAULT 'basic',
    `features` JSON NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_default` TINYINT(1) DEFAULT 0,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_packages_slug` (`slug`),
    INDEX `idx_packages_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `package_id` INT UNSIGNED NOT NULL,
    `billing_cycle` ENUM('monthly', 'yearly') DEFAULT 'monthly',
    `status` ENUM('active', 'cancelled', 'expired', 'suspended') DEFAULT 'active',
    `starts_at` TIMESTAMP NOT NULL,
    `ends_at` TIMESTAMP NULL,
    `cancelled_at` TIMESTAMP NULL,
    `trial_ends_at` TIMESTAMP NULL,
    `messages_used` INT DEFAULT 0,
    `messages_reset_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_subscriptions_user_id` (`user_id`),
    INDEX `idx_subscriptions_status` (`status`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoices` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `subscription_id` INT UNSIGNED NULL,
    `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
    `amount` DECIMAL(10,2) NOT NULL,
    `tax` DECIMAL(10,2) DEFAULT 0.00,
    `total` DECIMAL(10,2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `status` ENUM('pending', 'paid', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    `payment_method` VARCHAR(50) NULL,
    `payment_reference` VARCHAR(255) NULL,
    `paid_at` TIMESTAMP NULL,
    `due_at` TIMESTAMP NULL,
    `notes` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_invoices_user_id` (`user_id`),
    INDEX `idx_invoices_status` (`status`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `credits` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `balance` DECIMAL(10,4) DEFAULT 0.0000,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_credits_user` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `credit_transactions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` ENUM('credit', 'debit') NOT NULL,
    `amount` DECIMAL(10,4) NOT NULL,
    `balance_after` DECIMAL(10,4) NOT NULL,
    `description` VARCHAR(255) NULL,
    `reference_type` VARCHAR(50) NULL,
    `reference_id` INT UNSIGNED NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_credit_transactions_user_id` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- WHATSAPP NUMBERS & PROJECTS
-- =====================================================

CREATE TABLE IF NOT EXISTS `whatsapp_numbers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `phone_number` VARCHAR(20) NOT NULL,
    `display_name` VARCHAR(255) NULL,
    `channel` ENUM('meta', 'baileys', 'hybrid') DEFAULT 'baileys',
    `status` ENUM('pending', 'connected', 'disconnected', 'banned') DEFAULT 'pending',
    `meta_phone_number_id` VARCHAR(100) NULL,
    `meta_access_token` TEXT NULL,
    `meta_business_account_id` VARCHAR(100) NULL,
    `meta_webhook_verified` TINYINT(1) DEFAULT 0,
    `baileys_session_id` VARCHAR(100) NULL,
    `baileys_qr_token` VARCHAR(100) NULL,
    `baileys_connected_at` TIMESTAMP NULL,
    `last_active_at` TIMESTAMP NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_whatsapp_numbers_user_id` (`user_id`),
    INDEX `idx_whatsapp_numbers_phone` (`phone_number`),
    INDEX `idx_whatsapp_numbers_channel` (`channel`),
    INDEX `idx_whatsapp_numbers_status` (`status`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `projects` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `whatsapp_number_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `webhook_url` VARCHAR(500) NULL,
    `webhook_secret` VARCHAR(255) NULL,
    `webhook_enabled` TINYINT(1) DEFAULT 1,
    `webhook_retry_count` INT DEFAULT 3,
    `webhook_retry_delay` INT DEFAULT 60,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_projects_user_id` (`user_id`),
    INDEX `idx_projects_whatsapp_number_id` (`whatsapp_number_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`whatsapp_number_id`) REFERENCES `whatsapp_numbers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- API KEYS
-- =====================================================

CREATE TABLE IF NOT EXISTS `api_keys` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `api_key` VARCHAR(64) NOT NULL UNIQUE,
    `api_secret` VARCHAR(64) NOT NULL,
    `permissions` JSON NULL,
    `ip_whitelist` JSON NULL,
    `rate_limit` INT DEFAULT 60,
    `last_used_at` TIMESTAMP NULL,
    `last_used_ip` VARCHAR(45) NULL,
    `expires_at` TIMESTAMP NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_api_keys_user_id` (`user_id`),
    INDEX `idx_api_keys_project_id` (`project_id`),
    INDEX `idx_api_keys_api_key` (`api_key`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MESSAGES
-- =====================================================

CREATE TABLE IF NOT EXISTS `messages` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(36) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NOT NULL,
    `whatsapp_number_id` INT UNSIGNED NOT NULL,
    `api_key_id` INT UNSIGNED NULL,
    `direction` ENUM('inbound', 'outbound') NOT NULL,
    `channel` ENUM('meta', 'baileys') NOT NULL,
    `from_number` VARCHAR(20) NOT NULL,
    `to_number` VARCHAR(20) NOT NULL,
    `message_type` ENUM('text', 'image', 'video', 'audio', 'document', 'location', 'contact', 'sticker', 'template', 'interactive') DEFAULT 'text',
    `content` TEXT NULL,
    `media_url` VARCHAR(500) NULL,
    `media_mime_type` VARCHAR(100) NULL,
    `media_filename` VARCHAR(255) NULL,
    `media_size` INT UNSIGNED NULL,
    `template_name` VARCHAR(255) NULL,
    `template_params` JSON NULL,
    `external_id` VARCHAR(255) NULL,
    `status` ENUM('pending', 'sent', 'delivered', 'read', 'failed') DEFAULT 'pending',
    `error_code` VARCHAR(50) NULL,
    `error_message` TEXT NULL,
    `cost` DECIMAL(10,6) DEFAULT 0.000000,
    `metadata` JSON NULL,
    `sent_at` TIMESTAMP NULL,
    `delivered_at` TIMESTAMP NULL,
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_messages_user_id` (`user_id`),
    INDEX `idx_messages_project_id` (`project_id`),
    INDEX `idx_messages_whatsapp_number_id` (`whatsapp_number_id`),
    INDEX `idx_messages_direction` (`direction`),
    INDEX `idx_messages_channel` (`channel`),
    INDEX `idx_messages_status` (`status`),
    INDEX `idx_messages_from_number` (`from_number`),
    INDEX `idx_messages_to_number` (`to_number`),
    INDEX `idx_messages_created_at` (`created_at`),
    INDEX `idx_messages_external_id` (`external_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`whatsapp_number_id`) REFERENCES `whatsapp_numbers`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`api_key_id`) REFERENCES `api_keys`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- WEBHOOKS
-- =====================================================

CREATE TABLE IF NOT EXISTS `webhook_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NOT NULL,
    `message_id` BIGINT UNSIGNED NULL,
    `webhook_url` VARCHAR(500) NOT NULL,
    `event_type` VARCHAR(50) NOT NULL,
    `payload` JSON NOT NULL,
    `response_status` INT NULL,
    `response_body` TEXT NULL,
    `attempt` INT DEFAULT 1,
    `max_attempts` INT DEFAULT 3,
    `status` ENUM('pending', 'success', 'failed', 'retrying') DEFAULT 'pending',
    `next_retry_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `duration_ms` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_webhook_logs_user_id` (`user_id`),
    INDEX `idx_webhook_logs_project_id` (`project_id`),
    INDEX `idx_webhook_logs_status` (`status`),
    INDEX `idx_webhook_logs_next_retry_at` (`next_retry_at`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`message_id`) REFERENCES `messages`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SYSTEM SETTINGS & LOGS
-- =====================================================

CREATE TABLE IF NOT EXISTS `system_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT NULL,
    `type` ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    `description` VARCHAR(255) NULL,
    `is_public` TINYINT(1) 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;

CREATE TABLE IF NOT EXISTS `route_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `route_name` VARCHAR(50) NOT NULL UNIQUE,
    `is_enabled` TINYINT(1) DEFAULT 1,
    `cost_per_message` DECIMAL(10,6) DEFAULT 0.000000,
    `priority` INT DEFAULT 0,
    `settings` JSON 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;

CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50) NULL,
    `entity_id` INT UNSIGNED NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_logs_user_id` (`user_id`),
    INDEX `idx_audit_logs_action` (`action`),
    INDEX `idx_audit_logs_entity` (`entity_type`, `entity_id`),
    INDEX `idx_audit_logs_created_at` (`created_at`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `api_key_id` INT UNSIGNED NULL,
    `method` VARCHAR(10) NOT NULL,
    `endpoint` VARCHAR(255) NOT NULL,
    `request_body` JSON NULL,
    `response_status` INT NOT NULL,
    `response_body` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `duration_ms` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_api_logs_user_id` (`user_id`),
    INDEX `idx_api_logs_api_key_id` (`api_key_id`),
    INDEX `idx_api_logs_endpoint` (`endpoint`),
    INDEX `idx_api_logs_created_at` (`created_at`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`api_key_id`) REFERENCES `api_keys`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- USAGE STATISTICS
-- =====================================================

CREATE TABLE IF NOT EXISTS `daily_usage` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NULL,
    `date` DATE NOT NULL,
    `messages_sent` INT DEFAULT 0,
    `messages_received` INT DEFAULT 0,
    `messages_failed` INT DEFAULT 0,
    `meta_messages` INT DEFAULT 0,
    `baileys_messages` INT DEFAULT 0,
    `api_calls` INT DEFAULT 0,
    `webhooks_sent` INT DEFAULT 0,
    `webhooks_failed` INT DEFAULT 0,
    `cost` DECIMAL(10,4) DEFAULT 0.0000,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_daily_usage` (`user_id`, `project_id`, `date`),
    INDEX `idx_daily_usage_date` (`date`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- NOTIFICATIONS
-- =====================================================

CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `data` JSON NULL,
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_notifications_user_id` (`user_id`),
    INDEX `idx_notifications_read_at` (`read_at`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DEFAULT DATA
-- =====================================================

-- Default packages
INSERT INTO `packages` (`name`, `slug`, `description`, `price_monthly`, `price_yearly`, `messages_limit`, `whatsapp_numbers_limit`, `projects_per_number_limit`, `api_rate_limit`, `meta_enabled`, `baileys_enabled`, `is_default`, `sort_order`) VALUES
('Free', 'free', 'Perfect for testing and small projects', 0.00, 0.00, 100, 1, 1, 30, 0, 1, 1, 1),
('Starter', 'starter', 'Great for small businesses', 29.00, 290.00, 1000, 2, 3, 60, 1, 1, 0, 2),
('Business', 'business', 'For growing businesses', 99.00, 990.00, 5000, 5, 10, 120, 1, 1, 0, 3),
('Enterprise', 'enterprise', 'Unlimited power for large organizations', 299.00, 2990.00, 50000, 20, 50, 300, 1, 1, 0, 4);

-- Default route settings
INSERT INTO `route_settings` (`route_name`, `is_enabled`, `cost_per_message`, `priority`) VALUES
('meta', 1, 0.0050, 1),
('baileys', 1, 0.0010, 2);

-- Default system settings
INSERT INTO `system_settings` (`key`, `value`, `type`, `description`, `is_public`) VALUES
('app_name', 'WhatsApp Gateway', 'string', 'Application name', 1),
('app_url', 'http://localhost', 'string', 'Application URL', 1),
('gateway_url', 'http://localhost:3000', 'string', 'Gateway service URL', 0),
('default_timezone', 'UTC', 'string', 'Default timezone', 1),
('maintenance_mode', '0', 'boolean', 'Maintenance mode status', 1),
('registration_enabled', '1', 'boolean', 'Allow new registrations', 1),
('google_oauth_enabled', '1', 'boolean', 'Enable Google OAuth', 1),
('webhook_timeout', '30', 'integer', 'Webhook timeout in seconds', 0),
('max_webhook_retries', '3', 'integer', 'Maximum webhook retry attempts', 0),
('message_retention_days', '90', 'integer', 'Default message retention in days', 0);

-- Default admin user (password: admin123)
INSERT INTO `users` (`uuid`, `email`, `password`, `name`, `role`, `status`, `email_verified_at`) VALUES
(UUID(), 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Admin', 'superadmin', 'active', NOW());

