-- ═════════════════════════════════════════════════════════════════
-- 🎟 آپدیت سیستم کد تخفیف
-- Discount System Update
-- ═════════════════════════════════════════════════════════════════

USE telegram_shop;

-- بررسی و اضافه کردن ستون discount_code_id اگر وجود ندارد
ALTER TABLE orders 
ADD COLUMN IF NOT EXISTS discount_code_id INT UNSIGNED NULL AFTER total_amount,
ADD FOREIGN KEY IF NOT EXISTS (discount_code_id) REFERENCES discount_codes(id) ON DELETE SET NULL;

-- بررسی وجود جدول discount_codes
CREATE TABLE IF NOT EXISTS 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;

-- بررسی وجود جدول discount_usage
CREATE TABLE IF NOT EXISTS 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;

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

SELECT '✅ سیستم کد تخفیف با موفقیت به‌روزرسانی شد!' as status;
