-- Hybrid WhatsApp Gateway SaaS Platform
-- Database Schema


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;

-- =====================================================
-- 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;

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;

-- =====================================================
-- 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);

