-- mggafang_Tbot.sql
-- Database and tables for mggafang Telegram bot

CREATE DATABASE IF NOT EXISTS mggafang_Tbot
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;
USE mggafang_Tbot;

-- users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT UNIQUE,
    username VARCHAR(255),
    first_name VARCHAR(255),
    full_name VARCHAR(255) UNIQUE,
    balance BIGINT DEFAULT 0,
    total_deposit BIGINT DEFAULT 0,
    total_withdraw BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- tickets table
CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    type ENUM('فنی','مالی') NOT NULL,
    message TEXT NOT NULL,
    status ENUM('در حال بررسی','پاسخ داده شده') DEFAULT 'در حال بررسی',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- deposits table
CREATE TABLE IF NOT EXISTS deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    amount BIGINT NOT NULL,
    receipt TEXT,
    status ENUM('در حال بررسی','تایید شده','رد شده') DEFAULT 'در حال بررسی',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- withdrawals table
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    amount BIGINT NOT NULL,
    card_number VARCHAR(30) NOT NULL,
    status ENUM('در حال بررسی','واریز شد','رد شد') DEFAULT 'در حال بررسی',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- messages table (for broadcasts and personal messages records)
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT,
    message TEXT,
    type ENUM('عمومی','شخصی'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- sessions table (to keep user steps in multi-step flows)
CREATE TABLE IF NOT EXISTS sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT UNIQUE,
    step VARCHAR(100),
    data TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
