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

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- ------------------------------------------------------------
-- 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;

-- ------------------------------------------------------------
-- 3. Table structure for table `settings`
-- Dynamic administrator configurations managed via Admin browser Panel.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) UNIQUE NOT NULL COMMENT 'Variable name code',
  `value` TEXT DEFAULT NULL COMMENT 'Variable active value',
  `description` TEXT DEFAULT NULL COMMENT 'Variable description for Admin Panel UI',
  `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;

-- ------------------------------------------------------------
-- Seed default configurations into `settings`
-- ------------------------------------------------------------
INSERT INTO `settings` (`key`, `value`, `description`) VALUES
('bot_token', '8628782818:AAE5T-dt92JPTXHL367wRy_7uJebaaVam70', 'Telegram Bot Token'),
('master_api_key', '9feda2c5b1d208c789991a3037f1d5f5', 'Clone99 API Key'),
('support_link', 'https://t.me/+xJRewyQXaiphZmI1', 'Support contact link'),
('admin_id', '807341907', 'Telegram Administrator ID'),
('bank_id', 'MB', 'Bank ID (MB, VCB...) for QR payments'),
('stk', '4440104031999', 'Bank account number for QR payments'),
('chu_tk', 'NGUYEN VAN LONG', 'Account holder name for QR payments'),
('web2m_token', '944AA153-C52B-74D0-B2BF-2BF6DF1D9F6D', 'Web2M key'),
('msg_welcome', '👋 *Chào mừng bạn đến với bot mua hàng tự động shop clone99.com*\n\n💎 *Clone99.com* - Tổng kho tài khoản Ads, Digital, AI giá xưởng.\n🚀 *Sub99.net* - Top 1 dịch vụ MXH.\n\n📌 *Lệnh:* /shop, /deposit, /cart, /history', 'Start welcome message'),
('is_maintenance', '0', 'Maintenance Mode (1=On, 0=Off)'),
('cron_key', 'cron_key_secure_99', 'Mã bí mật để chạy link Cron (Web/URL)')
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`);

-- ------------------------------------------------------------
-- 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;

-- ------------------------------------------------------------
-- 6. Table structure for table `local_accounts`
-- Manual pre-uploaded user credentials codes and accounts sold by the bot.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `local_accounts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL COMMENT 'Belongs to local product ID',
  `credentials` TEXT NOT NULL COMMENT 'Account format/Access codes (e.g. user|pass|cookie)',
  `status` TINYINT DEFAULT 0 COMMENT 'Sold status: 0=Unsold, 1=Sold',
  `sold_to` BIGINT DEFAULT NULL COMMENT 'Telegram ID of user who bought the account',
  `transaction_id` VARCHAR(100) DEFAULT NULL COMMENT 'Sales Invoice transaction code',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `sold_at` DATETIME DEFAULT NULL
) 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;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;