-- ═════════════════════════════════════════════════════════════════
-- 🗄️ دیتابیس ربات فروشگاهی تلگرام
-- Telegram Shop Bot Database Schema
-- ═════════════════════════════════════════════════════════════════

-- حذف دیتابیس قبلی (اختیاری - برای توسعه)
-- DROP DATABASE IF EXISTS telegram_shop;

-- ایجاد دیتابیس
CREATE DATABASE IF NOT EXISTS telegram_shop 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE telegram_shop;

-- ─────────────────────────────────────────────────────────────────
-- 👥 جدول کاربران
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(100),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    language_code VARCHAR(10) DEFAULT 'fa',
    
    -- کیف پول
    wallet_balance DECIMAL(15, 2) DEFAULT 0.00,
    
    -- رفرال
    referrer_id BIGINT UNSIGNED NULL,
    referral_rewarded BOOLEAN DEFAULT FALSE,
    referral_code VARCHAR(20) UNIQUE,
    referral_count INT DEFAULT 0,
    referral_earnings DECIMAL(15, 2) DEFAULT 0.00,
    
    -- وضعیت و نقش
    role ENUM('user', 'admin', 'super_admin') DEFAULT 'user',
    status ENUM('active', 'banned', 'suspended') DEFAULT 'active',
    is_premium BOOLEAN DEFAULT FALSE,
    
    -- آمار
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(15, 2) DEFAULT 0.00,
    
    -- زمان‌ها
    last_activity TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_username (username),
    INDEX idx_referrer_id (referrer_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📦 جدول دسته‌بندی محصولات
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE categories (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_slug (slug),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🛍 جدول محصولات
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE products (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    
    -- قیمت
    price DECIMAL(15, 2) NOT NULL,
    discount_price DECIMAL(15, 2) NULL,
    
    -- موجودی
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 5,
    max_purchase INT DEFAULT 10,
    
    -- تحویل
    delivery_type ENUM('auto', 'manual') DEFAULT 'auto',
    delivery_time VARCHAR(50) DEFAULT 'فوری',
    
    -- متا
    image_url VARCHAR(500),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- آمار
    view_count INT DEFAULT 0,
    purchase_count INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_category_id (category_id),
    INDEX idx_slug (slug),
    INDEX idx_is_active (is_active),
    INDEX idx_is_featured (is_featured)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📋 جدول موجودی محصولات (برای تحویل خودکار)
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE product_stock (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    product_id INT UNSIGNED NOT NULL,
    stock_data TEXT NOT NULL, -- محتوای محصول (اکانت، کد، لینک و...)
    status ENUM('available', 'sold', 'reserved') DEFAULT 'available',
    reserved_at TIMESTAMP NULL,
    sold_at TIMESTAMP NULL,
    order_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product_status (product_id, status),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🛒 جدول سبد خرید
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE cart (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cart_item (user_id, product_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📜 جدول سفارشات
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    
    -- مبالغ
    subtotal DECIMAL(15, 2) NOT NULL,
    discount_amount DECIMAL(15, 2) DEFAULT 0.00,
    total_amount DECIMAL(15, 2) NOT NULL,
    
    -- کد تخفیف
    discount_code_id INT UNSIGNED NULL,
    
    -- وضعیت
    status ENUM('pending', 'paid', 'processing', 'completed', 'cancelled', 'refunded') DEFAULT 'pending',
    payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid',
    payment_method ENUM('wallet', 'zarinpal', 'idpay', 'nextpay', 'crypto') NULL,
    
    -- تحویل
    delivery_status ENUM('pending', 'delivered', 'failed') DEFAULT 'pending',
    delivery_data TEXT NULL,
    
    -- یادداشت
    note TEXT,
    admin_note TEXT,
    
    -- زمان‌ها
    paid_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📦 جدول آیتم‌های سفارش
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE order_items (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(15, 2) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    stock_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 💳 جدول تراکنش‌ها
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE transactions (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    
    -- نوع تراکنش
    type ENUM('deposit', 'withdraw', 'purchase', 'refund', 'referral_bonus', 'admin_adjustment') NOT NULL,
    
    -- مبلغ
    amount DECIMAL(15, 2) NOT NULL,
    balance_before DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2) NOT NULL,
    
    -- روش پرداخت
    payment_method VARCHAR(50),
    gateway_transaction_id VARCHAR(200),
    
    -- وضعیت
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    
    -- توضیحات
    description TEXT,
    metadata JSON,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_id (order_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🎁 جدول کدهای تخفیف
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE discount_codes (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    
    -- نوع تخفیف
    type ENUM('percentage', 'fixed') NOT NULL,
    value DECIMAL(15, 2) NOT NULL,
    
    -- محدودیت‌ها
    min_purchase DECIMAL(15, 2) DEFAULT 0.00,
    max_discount DECIMAL(15, 2) NULL,
    usage_limit INT NULL,
    usage_per_user INT DEFAULT 1,
    usage_count INT DEFAULT 0,
    
    -- زمان‌ها
    valid_from TIMESTAMP NULL,
    valid_until TIMESTAMP NULL,
    
    -- وضعیت
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_code (code),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🎫 جدول استفاده از کد تخفیف
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE discount_usage (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    discount_code_id INT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    discount_amount DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (discount_code_id) REFERENCES discount_codes(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_discount_code_id (discount_code_id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 💳 جدول پرداخت کارت به کارت
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE card_payments (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    amount DECIMAL(15, 2) NOT NULL,
    tracking_code VARCHAR(50) UNIQUE NOT NULL,
    
    -- رسید پرداخت
    receipt_file_id VARCHAR(200) NULL,
    receipt_uploaded_at TIMESTAMP NULL,
    
    -- وضعیت
    status ENUM('pending', 'waiting_approval', 'approved', 'rejected') DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT 'card_to_card',
    
    -- تایید/رد
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    reject_reason TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_tracking_code (tracking_code),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🎫 جدول تیکت‌های پشتیبانی
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE tickets (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    ticket_number VARCHAR(50) UNIQUE NOT NULL,
    subject VARCHAR(200) NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('open', 'pending', 'answered', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at TIMESTAMP NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 💬 جدول پیام‌های تیکت
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE ticket_messages (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ticket_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    message TEXT NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_ticket_id (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📊 جدول آمار روزانه
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE daily_stats (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    date DATE UNIQUE NOT NULL,
    new_users INT DEFAULT 0,
    total_orders INT DEFAULT 0,
    completed_orders INT DEFAULT 0,
    total_revenue DECIMAL(15, 2) DEFAULT 0.00,
    wallet_charges DECIMAL(15, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🔔 جدول اعلان‌ها
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE notifications (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- ⚙️ جدول تنظیمات
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE settings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(50) DEFAULT 'string',
    description VARCHAR(500),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 📝 جدول لاگ‌ها
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE logs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    level ENUM('debug', 'info', 'warning', 'error') DEFAULT 'info',
    message TEXT NOT NULL,
    context JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_level (level),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- 🎯 درج داده‌های اولیه
-- ─────────────────────────────────────────────────────────────────

-- دسته‌بندی‌های پیش‌فرض
INSERT INTO categories (name, slug, icon, sort_order) VALUES
('پریمیوم تلگرام', 'telegram-premium', '⭐️', 1),
('استارز تلگرام', 'telegram-stars', '✨', 2),
('گیفت کد', 'gift-codes', '🎁', 3),
('اکانت', 'accounts', '👤', 4),
('بوست', 'boost', '🚀', 5);

-- تنظیمات پیش‌فرض
INSERT INTO settings (setting_key, setting_value, setting_type, description) VALUES
('shop_name', 'فروشگاه رمزینو', 'string', 'نام فروشگاه'),
('maintenance_mode', '0', 'boolean', 'حالت تعمیر و نگهداری'),
('min_wallet_charge', '10000', 'integer', 'حداقل مبلغ شارژ کیف پول'),
('max_wallet_charge', '10000000', 'integer', 'حداکثر مبلغ شارژ کیف پول'),
('referral_bonus', '5000', 'integer', 'پاداش دعوت دوستان'),
('currency', 'تومان', 'string', 'واحد پول');

-- کد تخفیف نمونه
INSERT INTO discount_codes (code, type, value, min_purchase, usage_limit, valid_until) VALUES
('WELCOME10', 'percentage', 10, 50000, 100, DATE_ADD(NOW(), INTERVAL 30 DAY)),
('FIRST20', 'percentage', 20, 100000, 50, DATE_ADD(NOW(), INTERVAL 15 DAY));
