-- Bot Deployment Marketplace — Database Schema
-- MySQL 5.7+ / MariaDB 10.3+

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────
-- USERS (customers)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    username VARCHAR(60) NOT NULL UNIQUE,
    username_slug VARCHAR(60) NOT NULL UNIQUE, -- safe filesystem/db slug, immutable once a deployment exists
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- ADMINS (separate from customers)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner','staff') NOT NULL DEFAULT 'staff',
    failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- CATEGORIES
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- PRODUCTS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    category_id INT UNSIGNED NULL,
    thumbnail_path VARCHAR(255) NULL,
    short_description VARCHAR(300) NULL,
    full_description TEXT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(8) NOT NULL DEFAULT 'INR',
    subscription_type ENUM('lifetime','7_days','30_days','90_days','custom') NOT NULL DEFAULT 'lifetime',
    subscription_days INT UNSIGNED NULL, -- used when subscription_type = custom
    allow_multiple_instances TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('draft','published','disabled') NOT NULL DEFAULT 'draft',
    package_path VARCHAR(255) NULL,        -- master ZIP, stored outside webroot
    package_checksum VARCHAR(64) NULL,     -- sha256 of master ZIP, to detect tampering
    installation_folder VARCHAR(120) NOT NULL DEFAULT 'install', -- relative path inside package, may be nested
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- ORDERS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_ref VARCHAR(40) NOT NULL UNIQUE, -- public-facing reference, e.g. ORD-8F2C1A
    user_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'INR',
    status ENUM('PENDING','PROCESSING','PAID','FAILED','CANCELLED','REFUNDED') NOT NULL DEFAULT 'PENDING',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- PAYMENTS (raw gateway records, immutable audit trail)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    gateway VARCHAR(40) NOT NULL DEFAULT 'neopay',
    gateway_txn_id VARCHAR(120) NOT NULL,   -- unique id from gateway, used for idempotency
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'INR',
    status ENUM('initiated','verified','failed') NOT NULL DEFAULT 'initiated',
    raw_payload_hash VARCHAR(64) NULL, -- hash of the raw callback body (never store secrets in plaintext logs)
    verified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_gateway_txn (gateway, gateway_txn_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- DEPLOYMENTS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS deployments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deployment_ref VARCHAR(40) NOT NULL UNIQUE, -- e.g. DPL-9K2M
    user_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    username_slug VARCHAR(60) NOT NULL,
    deployment_directory VARCHAR(255) NULL,
    database_name VARCHAR(80) NULL,
    database_username VARCHAR(80) NULL,
    database_password_encrypted TEXT NULL, -- libsodium/openssl encrypted, never plaintext
    database_host VARCHAR(120) NULL,
    installation_path VARCHAR(160) NULL,   -- relative folder validated inside package
    installation_url VARCHAR(255) NULL,
    status ENUM(
        'PENDING_PAYMENT','PAYMENT_VERIFIED','PROVISIONING','DATABASE_CREATING',
        'DATABASE_READY','FILES_DEPLOYING','FILES_READY','INSTALLATION_READY',
        'COMPLETED','FAILED'
    ) NOT NULL DEFAULT 'PENDING_PAYMENT',
    failure_reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_order_deployment (order_id), -- idempotency: one order -> one deployment (unless product allows multiple, handled in app layer with instance suffix)
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- DEPLOYMENT LOGS (step-by-step technical trail)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS deployment_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deployment_id INT UNSIGNED NOT NULL,
    step VARCHAR(80) NOT NULL,
    level ENUM('info','ok','warning','error') NOT NULL DEFAULT 'info',
    message VARCHAR(500) NOT NULL, -- never contains secrets — see DeploymentService::logStep()
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- BOT INSTANCES (post-completion reference, 1:1 with a completed deployment)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bot_instances (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    deployment_id INT UNSIGNED NOT NULL UNIQUE,
    user_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    installation_url VARCHAR(255) NOT NULL,
    expires_at DATETIME NULL, -- for subscription-based products
    status ENUM('active','expired','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- SETTINGS (key/value; cPanel + general config)
-- Sensitive values (api_token) are encrypted at rest by SettingsService.
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT NULL,
    is_secret TINYINT(1) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ─────────────────────────────────────────────
-- RATE LIMIT tracking for auth endpoints
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS auth_attempts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    identifier VARCHAR(190) NULL, -- username/email attempted
    context VARCHAR(20) NOT NULL, -- 'customer_login' | 'admin_login' | 'register'
    success TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_context_time (ip_address, context, created_at)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- Seed a default category so the product form has something to select
INSERT INTO categories (name, slug) VALUES ('Telegram Bots', 'telegram-bots')
    ON DUPLICATE KEY UPDATE name = name;

-- Seed default settings keys (values filled in via Admin → Settings)
INSERT INTO settings (`key`, `value`, is_secret) VALUES
    ('cpanel_host', '', 0),
    ('cpanel_username', '', 0),
    ('cpanel_api_token', '', 1),
    ('cpanel_bots_base_dir', '/home/USER/public_html/bots', 0),
    ('cpanel_db_prefix', '', 0),
    ('site_name', 'BotMarket', 0),
    ('site_url', 'https://mgamer.online', 0),
    ('neopay_merchant_id', '', 0),
    ('neopay_secret_key', '', 1),
    ('encryption_key', '', 1)
ON DUPLICATE KEY UPDATE `key` = `key`;