-- Telegram Shop Bot - Full Database Schema
-- Run this to create all tables


-- Users (Telegram users)
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `telegram_id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`),
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Banned users
CREATE TABLE IF NOT EXISTS `bans` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `telegram_id` bigint(20) NOT NULL,
  `reason` varchar(500) DEFAULT NULL,
  `banned_by` int(11) DEFAULT NULL,
  `banned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payment methods (Bkash, Nagad, Rocket, Upay, Binance, etc.)
CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `requires_uid` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = Binance UID/Username field',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Deposits
CREATE TABLE IF NOT EXISTS `deposits` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `telegram_id` bigint(20) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method_id` int(11) unsigned NOT NULL,
  `transaction_id` varchar(255) NOT NULL,
  `binance_uid` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  KEY `payment_method_id` (`payment_method_id`),
  CONSTRAINT `deposits_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `deposits_payment_fk` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Product categories (Proxy, Gmail, Facebook, CD Keys)
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Products
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `price` decimal(12,2) NOT NULL,
  `stock` int(11) NOT NULL DEFAULT 0,
  `delivery_type` enum('auto','manual') NOT NULL DEFAULT 'auto',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `is_active` (`is_active`),
  CONSTRAINT `products_category_fk` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Product stock items (for auto delivery - one row per deliverable item)
CREATE TABLE IF NOT EXISTS `product_stock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `content` text NOT NULL COMMENT 'Proxy line, email:pass, CD key, etc.',
  `is_sold` tinyint(1) NOT NULL DEFAULT 0,
  `sold_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `is_sold` (`is_sold`),
  CONSTRAINT `product_stock_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transactions (every purchase)
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `telegram_id` bigint(20) NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `delivery_details` text COMMENT 'Delivered content or note',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `product_id` (`product_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `transactions_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `transactions_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global settings (help message, tutorial, deposit on/off, maintenance)
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Balance history (optional - for audit)
CREATE TABLE IF NOT EXISTS `balance_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `type` enum('deposit','purchase','admin_add','admin_subtract') NOT NULL,
  `reference_id` int(11) DEFAULT NULL COMMENT 'deposit_id or transaction_id',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `balance_logs_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Default payment methods
INSERT INTO `payment_methods` (`name`, `requires_uid`, `is_active`, `sort_order`) VALUES
('Bkash', 0, 1, 1),
('Nagad', 0, 1, 2),
('Rocket', 0, 1, 3),
('Upay', 0, 1, 4),
('Binance', 1, 1, 5);

-- Default categories
INSERT INTO `categories` (`name`, `slug`, `sort_order`, `is_active`) VALUES
('Proxy', 'proxy', 1, 1),
('Gmail Accounts', 'gmail', 2, 1),
('Facebook Accounts', 'facebook', 3, 1),
('CD Keys', 'cdkeys', 4, 1);

-- Default settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('deposit_enabled', '1'),
('maintenance_mode', '0'),
('help_message', 'Welcome! Use the menu to check balance, deposit, or buy products. For support contact the admin.'),
('tutorial_message', 'How to deposit:\n1. Choose Deposit from menu\n2. Enter amount and transaction ID\n3. Wait for admin approval\n\nHow to buy:\n1. Choose a category (Proxy, Gmail, etc.)\n2. Select product and confirm\n3. Balance will be deducted and you will receive the product automatically.');
