
-- Tabel untuk menyimpan data user
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabel untuk menyimpan verification requests
CREATE TABLE IF NOT EXISTS verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    verification_id VARCHAR(255) UNIQUE NOT NULL,
    status ENUM('pending', 'processing', 'success', 'error', 'cancelled') DEFAULT 'pending',
    message TEXT,
    check_token VARCHAR(255),
    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
);

-- Tabel untuk menyimpan batch verification requests
CREATE TABLE IF NOT EXISTS batch_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    batch_id VARCHAR(255) UNIQUE NOT NULL,
    total_verifications INT DEFAULT 0,
    completed_verifications INT DEFAULT 0,
    status ENUM('pending', 'processing', 'completed', 'error') DEFAULT 'pending',
    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
);

-- Tabel untuk menyimpan global settings (BARU)
CREATE TABLE IF NOT EXISTS global_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO global_settings (setting_key, setting_value, description) VALUES
('hcaptcha_token', NULL, 'Global HCaptcha token for all users'),
('turnstile_token', NULL, 'Global Turnstile token for all users'),
('api_base_url', 'https://your-api-domain.com', 'Base URL for verification API'),
('bot_status', 'active', 'Bot status: active, maintenance'),
('max_verifications_per_user', '10', 'Maximum verifications per user per day')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- Hapus tabel user_tokens lama (tidak diperlukan lagi)
-- DROP TABLE IF EXISTS user_tokens;

-- Index untuk performa
CREATE INDEX idx_telegram_id ON users(telegram_id);
CREATE INDEX idx_verification_id ON verifications(verification_id);
CREATE INDEX idx_batch_id ON batch_verifications(batch_id);
CREATE INDEX idx_user_verifications ON verifications(user_id);
CREATE INDEX idx_setting_key ON global_settings(setting_key);