-- ============================================================
-- SALES BOT & ADMIN PANEL DATABASE STRUCTURE
-- Target Environment: MySQL / phpMyAdmin (Hosting)
-- Author: Antigravity AI
-- Date: 2026-05-23
-- ============================================================

START TRANSACTION;


-- ------------------------------------------------------------
-- 1. Table structure for table `users`
-- Stores all Telegram customer profiles, roles, and balances.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tg_id` BIGINT UNIQUE NOT NULL COMMENT 'Telegram Unique User ID',
  `balance` INT DEFAULT 0 COMMENT 'Available wallet balance (VND)',
  `step` VARCHAR(255) DEFAULT '' COMMENT 'Track ongoing Telegram chat state',
  `lang` VARCHAR(10) DEFAULT 'vi' COMMENT 'Preferred language code',
  `cart` TEXT DEFAULT NULL COMMENT 'Active shopping cart in JSON format',
  `last_qr_msg_id` INT DEFAULT 0 COMMENT 'Message ID of last generated QR code',
  `qr_time` DATETIME DEFAULT NULL COMMENT 'Timestamp when QR code was generated',
  `role` VARCHAR(20) DEFAULT 'member' COMMENT 'Member role: member, ctv (VIP)',
  `fullname` VARCHAR(255) DEFAULT NULL COMMENT 'Customer First & Last Name from Telegram',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. Table structure for table `transactions`
-- Logs deposits, direct purchases, cart transactions, and refunds.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tg_id` BIGINT NOT NULL COMMENT 'Telegram User ID',
  `type` VARCHAR(50) NOT NULL COMMENT 'topup, buy, buy_direct, refund',
  `amount` INT NOT NULL COMMENT 'Transaction amount (VND)',
  `description` TEXT DEFAULT NULL COMMENT 'Brief details of the transaction',
  `bank_trans_id` VARCHAR(100) DEFAULT NULL COMMENT 'Unique payment gateway transaction ID',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `idx_bank_trans_id` (`bank_trans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. Table structure for table `categories`
-- Dynamic classification groups for either local or synced products.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `external_id` VARCHAR(50) DEFAULT NULL COMMENT 'Corresponding Clone99 Category ID if synced',
  `name` VARCHAR(255) NOT NULL COMMENT 'Category display name',
  `status` TINYINT DEFAULT 1 COMMENT 'Active visibility: 1=Active, 0=Hidden',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. Table structure for table `products`
-- Local overrides of catalog inventories, custom local stock items, and pricing.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL COMMENT 'Belongs to local category ID',
  `external_id` VARCHAR(50) DEFAULT NULL COMMENT 'Corresponding Clone99 Product ID if synced',
  `name` VARCHAR(255) NOT NULL COMMENT 'Product title',
  `description` TEXT DEFAULT NULL COMMENT 'Product specifications description',
  `price` INT NOT NULL COMMENT 'Selling price (VND)',
  `original_price` INT DEFAULT 0 COMMENT 'Cost/Original price from supplier (VND)',
  `stock` INT DEFAULT 0 COMMENT 'Current available inventory count',
  `status` TINYINT DEFAULT 1 COMMENT 'Selling status: 1=Selling, 0=Suspended',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. Table structure for table `warranty_claims`
-- User-submitted ticket claims for automated support or admin manual reviews.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `warranty_claims` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tg_id` BIGINT NOT NULL COMMENT 'Telegram ID of claim submitter',
  `username` VARCHAR(255) DEFAULT NULL COMMENT 'Fullname or Username of claim submitter',
  `product_name` VARCHAR(255) NOT NULL COMMENT 'Name of the faulty product',
  `details` TEXT NOT NULL COMMENT 'Customer issues and descriptions',
  `status` VARCHAR(20) DEFAULT 'pending' COMMENT 'Claim status: pending, resolved, rejected',
  `reply` TEXT DEFAULT NULL COMMENT 'Admin review feedback response',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;

