-- ============================================================================
--  BLACK TUNNEL VPN  —  Database schema (MySQL / MariaDB, utf8mb4)
--  Import this file once via phpMyAdmin or the cPanel MySQL importer.
--  Then open  https://your-domain/admin/setup.php  to create the first admin.
-- ============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

-- Uncomment the next two lines only if your host allows creating databases
-- from SQL. On most cPanel hosts you must create the DB + user in cPanel first.
-- CREATE DATABASE IF NOT EXISTS `black_tunnel` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `black_tunnel`;

-- ---------------------------------------------------------------------------
--  Roles & administrators
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `roles` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(50)  NOT NULL,
  `permissions` TEXT         NOT NULL,
  `is_system`   TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(150) NOT NULL,
  `username`      VARCHAR(60)  NOT NULL,
  `email`         VARCHAR(190) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `role_id`       INT UNSIGNED NOT NULL,
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `last_login_at` DATETIME     NULL,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admins_username` (`username`),
  UNIQUE KEY `uq_admins_email` (`email`),
  KEY `idx_admins_role` (`role_id`),
  CONSTRAINT `fk_admins_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Customers
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`            VARCHAR(150) NOT NULL,
  `email`           VARCHAR(190) NOT NULL,
  `phone`           VARCHAR(30)  NULL,
  `password_hash`   VARCHAR(255) NOT NULL,
  `is_reseller`     TINYINT(1)   NOT NULL DEFAULT 0,
  `reseller_status` ENUM('none','pending','approved','rejected','suspended') NOT NULL DEFAULT 'none',
  `is_active`       TINYINT(1)   NOT NULL DEFAULT 1,
  `last_login_at`   DATETIME     NULL,
  `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Products / plans
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(190) NOT NULL,
  `slug`          VARCHAR(190) NOT NULL,
  `description`   TEXT         NULL,
  `volume_tb`     DECIMAL(6,2) NULL,
  `duration_days` INT UNSIGNED NULL,
  `price_toman`   BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `features`      TEXT         NULL,
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `sort_order`    INT          NOT NULL DEFAULT 0,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_slug` (`slug`),
  KEY `idx_products_active` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Orders & payments
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ref`            VARCHAR(32)  NOT NULL,
  `user_id`        INT UNSIGNED NOT NULL,
  `product_id`     INT UNSIGNED NULL,
  `order_type`     ENUM('single','bulk','renewal') NOT NULL DEFAULT 'single',
  `quantity`       INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price`     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `total_price`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `status`         ENUM('pending_payment','payment_review','paid','processing','completed','rejected','cancelled') NOT NULL DEFAULT 'pending_payment',
  `payment_status` ENUM('unpaid','awaiting_review','approved','rejected') NOT NULL DEFAULT 'unpaid',
  `customer_note`  TEXT NULL,
  `admin_note`     TEXT NULL,
  `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 `uq_orders_ref` (`ref`),
  KEY `idx_orders_user` (`user_id`),
  KEY `idx_orders_status` (`status`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_orders_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id`         INT UNSIGNED NOT NULL,
  `amount`           BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `payer_name`       VARCHAR(150) NULL,
  `tracking_code`    VARCHAR(100) NULL,
  `receipt_path`     VARCHAR(255) NULL,
  `receipt_original` VARCHAR(190) NULL,
  `status`           ENUM('awaiting_review','approved','rejected') NOT NULL DEFAULT 'awaiting_review',
  `reviewed_by`      INT UNSIGNED NULL,
  `reviewed_at`      DATETIME NULL,
  `rejection_reason` TEXT NULL,
  `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payments_order` (`order_id`),
  KEY `idx_payments_status` (`status`),
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payments_admin` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  VPN services, history & renewals
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `services` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED NOT NULL,
  `order_id`    INT UNSIGNED NULL,
  `product_id`  INT UNSIGNED NULL,
  `label`       VARCHAR(190) NOT NULL,
  `config`      TEXT NULL,
  `expires_at`  DATETIME NULL,
  `status`      ENUM('pending','active','expired','suspended') NOT NULL DEFAULT 'pending',
  `admin_note`  TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_services_user` (`user_id`),
  KEY `idx_services_order` (`order_id`),
  CONSTRAINT `fk_services_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_services_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_services_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `service_history` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `action`     VARCHAR(100) NOT NULL,
  `meta`       TEXT NULL,
  `admin_id`   INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_history_service` (`service_id`),
  CONSTRAINT `fk_history_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `renewals` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` INT UNSIGNED NOT NULL,
  `user_id`    INT UNSIGNED NOT NULL,
  `order_id`   INT UNSIGNED NULL,
  `status`     ENUM('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending',
  `note`       TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_renewals_service` (`service_id`),
  CONSTRAINT `fk_renewals_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_renewals_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Reseller system
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `reseller_applications` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`          INT UNSIGNED NOT NULL,
  `business_name`    VARCHAR(190) NULL,
  `phone`            VARCHAR(30)  NULL,
  `message`          TEXT NULL,
  `status`           ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by`      INT UNSIGNED NULL,
  `reviewed_at`      DATETIME NULL,
  `rejection_reason` TEXT NULL,
  `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rapp_user` (`user_id`),
  KEY `idx_rapp_status` (`status`),
  CONSTRAINT `fk_rapp_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reseller_plans` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`             VARCHAR(190) NOT NULL,
  `min_tb`           DECIMAL(6,2) NOT NULL DEFAULT 0,
  `max_tb`           DECIMAL(6,2) NOT NULL DEFAULT 0,
  `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `permissions`      TEXT NULL,
  `is_active`        TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order`       INT NOT NULL DEFAULT 0,
  `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
--  Support tickets
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tickets` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED NOT NULL,
  `subject`    VARCHAR(190) NOT NULL,
  `category`   VARCHAR(60)  NOT NULL DEFAULT 'general',
  `priority`   ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
  `status`     ENUM('open','answered','pending','closed') NOT NULL DEFAULT 'open',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tickets_user` (`user_id`),
  KEY `idx_tickets_status` (`status`),
  CONSTRAINT `fk_tickets_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ticket_replies` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id`   INT UNSIGNED NOT NULL,
  `author_type` ENUM('user','admin') NOT NULL,
  `author_id`   INT UNSIGNED NOT NULL,
  `body`        TEXT NOT NULL,
  `is_internal` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_replies_ticket` (`ticket_id`),
  CONSTRAINT `fk_replies_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(190) NOT NULL,
  `ip`         VARCHAR(45)  NOT NULL,
  `scope`      VARCHAR(20)  NOT NULL DEFAULT 'user',
  `success`    TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attempt_ident` (`identifier`, `created_at`),
  KEY `idx_attempt_ip` (`ip`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id`   INT UNSIGNED NULL,
  `action`     VARCHAR(100) NOT NULL,
  `entity`     VARCHAR(60)  NULL,
  `entity_id`  INT UNSIGNED NULL,
  `meta`       TEXT NULL,
  `ip`         VARCHAR(45)  NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_created` (`created_at`),
  KEY `idx_audit_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
--  SEED DATA
-- ============================================================================

-- --- Roles (permissions stored as JSON) ------------------------------------
INSERT INTO `roles` (`name`, `slug`, `permissions`, `is_system`) VALUES
('مدیر ارشد', 'super_admin', '["*"]', 1),
('مدیر سفارش‌ها', 'order_manager', '["dashboard.view","customers.view","orders.view","orders.manage","services.view","services.manage","reports.view"]', 1),
('بررسی‌کننده پرداخت', 'payment_reviewer', '["dashboard.view","orders.view","payments.view","payments.review","reports.view"]', 1),
('پشتیبان', 'support_agent', '["dashboard.view","customers.view","tickets.view","tickets.manage"]', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `permissions` = VALUES(`permissions`);

-- --- Default site settings -------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'BLACK TUNNEL VPN'),
('site_tagline', 'تونل امن، اینترنت آزاد'),
('contact_email', 'support@example.com'),
('contact_phone', ''),
('telegram', ''),
('whatsapp', ''),
('bank_name', ''),
('card_number', '0000-0000-0000-0000'),
('card_holder', 'نام صاحب کارت'),
('payment_instructions', 'پس از واریز مبلغ به شماره کارت بالا، تصویر رسید پرداخت را در بخش سفارش‌ها بارگذاری کنید. تأیید پرداخت به‌صورت دستی و توسط کارشناسان انجام می‌شود.'),
('footer_about', 'BLACK TUNNEL VPN ارائه‌دهنده سرویس‌های پرسرعت و امن اینترنت است.'),
('installed_at', '')
ON DUPLICATE KEY UPDATE `setting_key` = VALUES(`setting_key`);

-- --- Demo products (edit freely from the admin panel) ----------------------
INSERT INTO `products` (`name`, `slug`, `description`, `volume_tb`, `duration_days`, `price_toman`, `features`, `is_active`, `sort_order`) VALUES
('پلن برنزی ۱ ترابایت', 'bronze-1tb', 'مناسب استفاده شخصی و روزمره با سرعت پایدار.', 1.00, 30, 120000, '["۱ ترابایت حجم","۳۰ روز اعتبار","اتصال همزمان ۲ دستگاه","پشتیبانی ۲۴/۷"]', 1, 10),
('پلن نقره‌ای ۲ ترابایت', 'silver-2tb', 'انتخاب متعادل برای مصرف متوسط و استریم.', 2.00, 30, 210000, '["۲ ترابایت حجم","۳۰ روز اعتبار","اتصال همزمان ۳ دستگاه","پشتیبانی ۲۴/۷"]', 1, 20),
('پلن طلایی ۵ ترابایت', 'gold-5tb', 'پرطرفدارترین پلن برای کاربران حرفه‌ای.', 5.00, 30, 450000, '["۵ ترابایت حجم","۳۰ روز اعتبار","اتصال همزمان ۵ دستگاه","پشتیبانی اولویت‌دار"]', 1, 30),
('پلن الماسی ۸ ترابایت', 'diamond-8tb', 'برای مصرف سنگین و دانلود حجیم.', 8.00, 30, 680000, '["۸ ترابایت حجم","۳۰ روز اعتبار","اتصال همزمان ۸ دستگاه","پشتیبانی اولویت‌دار"]', 1, 40),
('پلن ویژه ۱۰ ترابایت', 'special-10tb', 'حداکثر حجم برای تیم‌ها و کاربران پرمصرف.', 10.00, 30, 820000, '["۱۰ ترابایت حجم","۳۰ روز اعتبار","اتصال نامحدود","پشتیبانی اختصاصی"]', 1, 50)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- --- Reseller plans --------------------------------------------------------
INSERT INTO `reseller_plans` (`name`, `min_tb`, `max_tb`, `discount_percent`, `permissions`, `is_active`, `sort_order`) VALUES
('نمایندگی برنزی', 1.00, 5.00, 10.00, '["buy_bulk","view_services"]', 1, 10),
('نمایندگی نقره‌ای', 5.00, 20.00, 18.00, '["buy_bulk","view_services","renew"]', 1, 20),
('نمایندگی طلایی', 20.00, 100.00, 25.00, '["buy_bulk","view_services","renew","priority_support"]', 1, 30);

-- ============================================================================
--  END OF SCHEMA
-- ============================================================================