-- Create Database
CREATE DATABASE IF NOT EXISTS checkerbotdb3;

USE checkerbotdb3;

-- Create Users Table
CREATE TABLE IF NOT EXISTS users (
    chatid BIGINT PRIMARY KEY,
    username VARCHAR(255),
    authlimit INT DEFAULT 20,
    puanlimit INT DEFAULT 150,
    vbvlimit INT DEFAULT 50,
    iyzicolimit INT DEFAULT 5,
    auth2limit INT DEFAULT 20,
    mokalimit INT DEFAULT 5,
    disneylimit INT DEFAULT 5,
    stripelimit INT DEFAULT 10,
    stripegenellimit INT DEFAULT 20,
    authnetlimit INT DEFAULT 5,
    shopifylimit INT DEFAULT 10,
    banned INT DEFAULT 0,
    membership_type VARCHAR(50) DEFAULT 'normal',
    referral_code VARCHAR(20) UNIQUE,
    referred_by VARCHAR(20),
    ref1 BIGINT DEFAULT NULL,
    ref2 BIGINT DEFAULT NULL,
    ref3 BIGINT DEFAULT NULL,
    ref4 BIGINT DEFAULT NULL,
    ref5 BIGINT DEFAULT NULL,
    total_referrals INT DEFAULT 0,
    FOREIGN KEY (ref1) REFERENCES users(chatid) ON DELETE SET NULL,
    FOREIGN KEY (ref2) REFERENCES users(chatid) ON DELETE SET NULL,
    FOREIGN KEY (ref3) REFERENCES users(chatid) ON DELETE SET NULL,
    FOREIGN KEY (ref4) REFERENCES users(chatid) ON DELETE SET NULL,
    FOREIGN KEY (ref5) REFERENCES users(chatid) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Gate Management Table
CREATE TABLE IF NOT EXISTS gateyonetim (
    id INT AUTO_INCREMENT PRIMARY KEY,
    gate_name VARCHAR(100) UNIQUE NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    description VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default gates
INSERT INTO gateyonetim (gate_name, is_active, description) VALUES
('bot_status', 1, 'Bot Ana Durumu'),
('auth1', 1, 'Auth1 Gate'),
('auth2', 1, 'Auth2 Gate'),
('vbv', 1, 'VBV Gate'),
('puan1', 1, 'Puan1 Gate'),
('puan2', 1, 'Puan2 Gate'),
('puan4', 1, 'Puan4 Ipekyol Gate'),
('iyzico1', 1, 'İyzico1 Gate'),
('iyzico2', 1, 'İyzico2 Gate'),
('iyzico3', 1, 'İyzico3 Gate'),
('stripe1', 1, 'Stripe1 Gate'),
('stripe2', 1, 'Stripe2 Gate'),
('stripe3', 1, 'Stripe3 Gate'),
('stripeauth', 1, 'Stripe Auth Gate'),
('disney', 1, 'Disney Gate'),
('authnet', 1, 'Authorize.NET Gate'),
('shopify1', 1, 'Shopify1 Gate'),
('auth1mass', 1, 'Auth1 Mass Gate'),
('auth2mass', 1, 'Auth2 Mass Gate'),
('vbvmass', 1, 'VBV Mass Gate'),
('puanmass', 1, 'Puan Mass Gate'),
('puan2mass', 1, 'Puan2 Mass Gate'),
('puan4mass', 1, 'Puan4 Ipekyol Mass Gate')
ON DUPLICATE KEY UPDATE gate_name=gate_name;

-- Create Redeem Codes Table
CREATE TABLE IF NOT EXISTS redeem_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    limit_type VARCHAR(50) NOT NULL,
    amount INT NOT NULL,
    max_uses INT NOT NULL,
    used_count INT DEFAULT 0,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Code Usage History Table
CREATE TABLE IF NOT EXISTS code_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    user_id BIGINT NOT NULL,
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_code (user_id, code),
    INDEX idx_user (user_id),
    INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

