-- migrate_v4.sql
-- ارتقا به نسخه‌ی ۲.۴: مسیر ورود پنل، بونوس شارژ، کدهای تخفیف.
-- قبل از اجرا حتما از دیتابیس بکاپ بگیرید.

ALTER TABLE panels ADD COLUMN dashboard_path VARCHAR(255) NULL AFTER base_url;

CREATE TABLE IF NOT EXISTS charge_bonus_tiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    min_amount BIGINT NOT NULL,
    bonus_percent DECIMAL(5,2) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS charge_bonus_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    tier_id INT NULL,
    bonus_amount BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS discount_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
    value DECIMAL(12,2) NOT NULL,
    max_uses INT NULL,
    used_count INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS discount_code_uses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code_id INT NOT NULL,
    bot_user_id INT NOT NULL,
    representative_id INT NULL,
    discount_amount BIGINT NOT NULL,
    used_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (code_id) REFERENCES discount_codes(id),
    FOREIGN KEY (bot_user_id) REFERENCES bot_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;