-- Database Schema for Telegram Bot v2.0
-- PHP 7.4+
-- MySQL 5.7+ / MariaDB 10.2+

-- Set charset
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- =====================================================
-- Core Tables
-- =====================================================

-- Groups table
CREATE TABLE IF NOT EXISTS `groups` (
    `id` BIGINT NOT NULL PRIMARY KEY,
    `creator` BIGINT NULL,
    `vip` JSON NULL,
    `promote` JSON NULL,
    `ban` JSON NULL,
    `flood` JSON NULL,
    `locked` JSON NULL,
    `warn` JSON NULL,
    `silent` JSON NULL,
    `step` JSON NULL,
    `filter` JSON NULL,
    `installer` BIGINT NULL,
    `del` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_creator` (`creator`),
    INDEX `idx_installer` (`installer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Members table
CREATE TABLE IF NOT EXISTS `members` (
    `id` BIGINT NOT NULL PRIMARY KEY,
    `username` VARCHAR(255) NULL,
    `first_name` VARCHAR(255) NULL,
    `language` VARCHAR(5) DEFAULT 'fa',
    `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_username` (`username`),
    INDEX `idx_language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Send all (broadcast) table
CREATE TABLE IF NOT EXISTS `sendall` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `send` JSON NULL,
    `now` INT NOT NULL DEFAULT 0,
    `total` INT NOT NULL DEFAULT 0,
    `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logs table
CREATE TABLE IF NOT EXISTS `logs` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `type` VARCHAR(50) NOT NULL,
    `message` TEXT NOT NULL,
    `data` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_type` (`type`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Subscription Tables
-- =====================================================

-- Subscription plans table
CREATE TABLE IF NOT EXISTS `subscription_plans` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `duration_days` INT NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `features` JSON NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User subscriptions table
CREATE TABLE IF NOT EXISTS `user_subscriptions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `plan_id` INT NOT NULL,
    `start_date` TIMESTAMP NULL,
    `end_date` TIMESTAMP NULL,
    `status` ENUM('pending', 'active', 'expired', 'cancelled') DEFAULT 'pending',
    `notified_expiry` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_end_date` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment receipts table
CREATE TABLE IF NOT EXISTS `payment_receipts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `plan_id` INT NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `receipt_image` VARCHAR(255) NULL,
    `transaction_id` VARCHAR(100) NULL,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `admin_note` TEXT NULL,
    `submitted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `reviewed_at` TIMESTAMP NULL,
    `reviewed_by` BIGINT NULL,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment settings table
CREATE TABLE IF NOT EXISTS `payment_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `card_number` VARCHAR(20) NOT NULL,
    `card_holder` VARCHAR(100) NOT NULL,
    `bank_name` VARCHAR(100) NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Plugin Tables
-- =====================================================

-- Plugins table
CREATE TABLE IF NOT EXISTS `plugins` (
    `name` VARCHAR(100) PRIMARY KEY,
    `enabled` BOOLEAN DEFAULT TRUE,
    `config` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Sample Data (Optional)
-- =====================================================

-- Insert sample subscription plans (uncomment to use)
/*
INSERT INTO `subscription_plans` (`name`, `name_en`, `duration_days`, `price`, `features`) VALUES
('یک ماهه', 'Monthly', 30, 50000.00, '["دسترسی کامل به AI", "پشتیبانی 24/7", "بدون محدودیت"]'),
('سه ماهه', 'Quarterly', 90, 120000.00, '["دسترسی کامل به AI", "پشتیبانی اختصاصی", "بدون محدودیت", "تخفیف 20%"]'),
('یک ساله', 'Yearly', 365, 400000.00, '["دسترسی کامل به AI", "پشتیبانی VIP", "بدون محدودیت", "تخفیف 33%", "ویژگی‌های اختصاصی"]');
*/

-- Insert sample payment settings (uncomment and edit)
/*
INSERT INTO `payment_settings` (`card_number`, `card_holder`, `bank_name`, `is_active`) VALUES
('6037991234567890', 'علی احمدی', 'ملی', 1);
*/
