-- ╔══════════════════════════════════════════════════════╗
-- ║   NH FREE FIRE SHOP — DATABASE SCHEMA v∞            ║
-- ║   nhfreefire.pro | MySQL 8.x | utf8mb4              ║
-- ╚══════════════════════════════════════════════════════╝

CREATE DATABASE IF NOT EXISTS nhfreefire_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nhfreefire_bot;

-- ── USERS ──────────────────────────────────────────────
CREATE TABLE users (
    id            BIGINT PRIMARY KEY,
    username      VARCHAR(64),
    full_name     VARCHAR(128),
    phone         VARCHAR(20),
    balance       DECIMAL(15,0) DEFAULT 0,
    points        INT DEFAULT 0,
    rank          ENUM('member','silver','gold','vip','diamond') DEFAULT 'member',
    total_spent   DECIMAL(15,0) DEFAULT 0,
    total_orders  INT DEFAULT 0,
    is_banned     TINYINT DEFAULT 0,
    ban_reason    VARCHAR(255),
    state         VARCHAR(64) DEFAULT 'idle',
    state_data    JSON,
    lang          VARCHAR(8) DEFAULT 'vi',
    ref_by        BIGINT,
    ref_count     INT DEFAULT 0,
    last_seen     DATETIME,
    joined_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rank (rank),
    INDEX idx_balance (balance)
);

-- ── CATEGORIES ─────────────────────────────────────────
CREATE TABLE categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(64) UNIQUE,
    name        VARCHAR(128),
    emoji       VARCHAR(16),
    description TEXT,
    sort_order  INT DEFAULT 0,
    is_active   TINYINT DEFAULT 1
);

INSERT INTO categories (slug, name, emoji, sort_order) VALUES
('ai_account',  'Tài khoản AI',    '🤖', 1),
('via_clone',   'Via / Clone',     '👤', 2),
('tut_trick',   'Tut / Trick',     '📚', 3),
('topup',       'Nạp tiền / SOS',  '💳', 4);

-- ── PRODUCTS ───────────────────────────────────────────
CREATE TABLE products (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    category_id   INT,
    name          VARCHAR(255),
    emoji         VARCHAR(16) DEFAULT '📦',
    description   TEXT,
    price         DECIMAL(15,0) NOT NULL,
    sale_price    DECIMAL(15,0),
    stock_count   INT DEFAULT 0,
    sold_count    INT DEFAULT 0,
    delivery_type ENUM('auto','manual') DEFAULT 'auto',
    is_active     TINYINT DEFAULT 1,
    sort_order    INT DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_category (category_id),
    INDEX idx_active (is_active)
);

-- ── STOCK (kho hàng số) ────────────────────────────────
CREATE TABLE stock (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id  INT NOT NULL,
    content     TEXT NOT NULL,
    is_sold     TINYINT DEFAULT 0,
    sold_to     BIGINT,
    order_id    BIGINT,
    added_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    sold_at     DATETIME,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_product_unsold (product_id, is_sold)
);

-- ── ORDERS ─────────────────────────────────────────────
CREATE TABLE orders (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_code      VARCHAR(32) UNIQUE,
    user_id         BIGINT,
    product_id      INT,
    product_name    VARCHAR(255),
    quantity        INT DEFAULT 1,
    unit_price      DECIMAL(15,0),
    total_price     DECIMAL(15,0),
    discount        DECIMAL(15,0) DEFAULT 0,
    paid_amount     DECIMAL(15,0) DEFAULT 0,
    pay_method      ENUM('bank','wallet','point') DEFAULT 'bank',
    status          ENUM('pending','paid','delivered','cancelled','refunded') DEFAULT 'pending',
    delivery_data   TEXT,
    note            VARCHAR(500),
    expires_at      DATETIME,
    paid_at         DATETIME,
    delivered_at    DATETIME,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_code (order_code),
    INDEX idx_expires (expires_at)
);

-- ── TRANSACTIONS ────────────────────────────────────────
CREATE TABLE transactions (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT,
    type            ENUM('deposit','purchase','refund','bonus','point_use') DEFAULT 'deposit',
    amount          DECIMAL(15,0),
    balance_before  DECIMAL(15,0),
    balance_after   DECIMAL(15,0),
    order_id        BIGINT,
    bank_ref        VARCHAR(128),
    description     VARCHAR(500),
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_type (type),
    INDEX idx_bank_ref (bank_ref)
);

-- ── BANK TRANSACTIONS (raw từ API) ─────────────────────
CREATE TABLE bank_transactions (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    bank_ref        VARCHAR(128) UNIQUE,
    amount          DECIMAL(15,0),
    content         VARCHAR(500),
    transaction_at  DATETIME,
    is_processed    TINYINT DEFAULT 0,
    order_code      VARCHAR(32),
    processed_at    DATETIME,
    raw_data        JSON,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_processed (is_processed),
    INDEX idx_ref (bank_ref)
);

-- ── TICKETS (khiếu nại / hỗ trợ) ──────────────────────
CREATE TABLE tickets (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    ticket_code VARCHAR(32) UNIQUE,
    user_id     BIGINT,
    order_id    BIGINT,
    type        ENUM('complaint','support','feedback','refund') DEFAULT 'support',
    subject     VARCHAR(255),
    status      ENUM('open','inprogress','resolved','closed') DEFAULT 'open',
    priority    ENUM('low','normal','high','urgent') DEFAULT 'normal',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status)
);

CREATE TABLE ticket_messages (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    ticket_id   BIGINT,
    sender_id   BIGINT,
    is_admin    TINYINT DEFAULT 0,
    message     TEXT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
);

-- ── BROADCASTS ─────────────────────────────────────────
CREATE TABLE broadcasts (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    admin_id    BIGINT,
    message     TEXT,
    target      ENUM('all','category','rank') DEFAULT 'all',
    target_val  VARCHAR(64),
    sent_count  INT DEFAULT 0,
    fail_count  INT DEFAULT 0,
    status      ENUM('pending','sending','done') DEFAULT 'pending',
    scheduled_at DATETIME,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ── REVIEWS ────────────────────────────────────────────
CREATE TABLE reviews (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT,
    order_id    BIGINT UNIQUE,
    product_id  INT,
    rating      TINYINT,
    comment     TEXT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);
