-- ==========================================================
-- TOPBD — MySQL Database Schema
-- Production Ready for PHP 8+ and MySQL / MariaDB
-- ==========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Admins Table
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(60) NOT NULL UNIQUE,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(20) DEFAULT 'admin',
  `last_login` DATETIME NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. General Settings Table
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY,
  `setting_value` TEXT NOT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Frontend Texts Table
CREATE TABLE IF NOT EXISTS `frontend_texts` (
  `text_key` VARCHAR(100) PRIMARY KEY,
  `text_value` TEXT NOT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Categories Table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `icon` VARCHAR(50) DEFAULT 'Sparkles',
  `display_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_cat_slug` (`slug`),
  INDEX `idx_cat_order` (`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Listings Table
CREATE TABLE IF NOT EXISTS `listings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `image_url` TEXT NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `current_bid` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `clicks` INT UNSIGNED NOT NULL DEFAULT 0,
  `hearts` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_bid_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_listing_bid` (`current_bid` DESC, `last_bid_at` DESC),
  INDEX `idx_listing_cat` (`category_id`),
  INDEX `idx_listing_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Bids History Table
CREATE TABLE IF NOT EXISTS `bids` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `listing_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `previous_bid` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `payment_id` INT UNSIGNED NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_bids_listing` (`listing_id`),
  INDEX `idx_bids_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Payment Transactions Table
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` VARCHAR(100) NOT NULL UNIQUE,
  `listing_id` INT UNSIGNED NULL,
  `listing_name` VARCHAR(191) NOT NULL,
  `listing_url` VARCHAR(500) NOT NULL,
  `listing_image` TEXT NOT NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(14,2) NOT NULL,
  `gateway` VARCHAR(50) NOT NULL,
  `mode` ENUM('test', 'live') DEFAULT 'test',
  `status` ENUM('pending', 'success', 'failed', 'cancelled') DEFAULT 'pending',
  `gateway_response` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `verified_at` DATETIME NULL,
  INDEX `idx_pay_status` (`status`),
  INDEX `idx_pay_txn` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Live Visitors Heartbeat Table
CREATE TABLE IF NOT EXISTS `visitors` (
  `session_id` VARCHAR(100) PRIMARY KEY,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(255) NULL,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Header Navigation Menu Items
CREATE TABLE IF NOT EXISTS `menu_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL,
  `url` VARCHAR(255) NOT NULL,
  `target` VARCHAR(20) DEFAULT '_self',
  `display_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Content Pages Table (Rules, Terms, Privacy, Stats)
CREATE TABLE IF NOT EXISTS `pages` (
  `slug` VARCHAR(50) PRIMARY KEY,
  `title` VARCHAR(191) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Payment Gateway Configuration Table
CREATE TABLE IF NOT EXISTS `gateway_configs` (
  `gateway_id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `is_enabled` TINYINT(1) DEFAULT 1,
  `mode` ENUM('test', 'live') DEFAULT 'test',
  `merchant_id` VARCHAR(191) NULL,
  `store_id` VARCHAR(191) NULL,
  `username` VARCHAR(191) NULL,
  `password` VARCHAR(191) NULL,
  `app_key` VARCHAR(191) NULL,
  `app_secret` VARCHAR(191) NULL,
  `api_url` VARCHAR(255) NULL,
  `callback_url` VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
