-- ==========================================================
-- W3XIOS SERVERS BOT - MySQL database (v7 + Admin Panel)
-- NEW INSTALL: import this file in phpMyAdmin.
-- EXISTING DATABASE: you do NOT need to import anything —
-- the missing tables/columns are created automatically the first
-- time the bot or the admin panel runs.
-- ==========================================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT NOT NULL,
  `first_name` VARCHAR(255) NOT NULL DEFAULT 'User',
  `username` VARCHAR(100) NOT NULL DEFAULT '',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_given_likes` INT NOT NULL DEFAULT 0,
  `total_deposit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `invited_by` BIGINT NOT NULL DEFAULT 0,
  `ref_credited` TINYINT(1) NOT NULL DEFAULT 0,
  `referral_earned` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `banned` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_invited_by` (`invited_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invites` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `referrer_id` BIGINT NOT NULL,
  `invited_id` BIGINT NOT NULL,
  `name` VARCHAR(255) NOT NULL DEFAULT 'User',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uniq_invited` (`invited_id`), KEY `idx_referrer` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `name` VARCHAR(255) NOT NULL DEFAULT 'User',
  `item` VARCHAR(255) NOT NULL DEFAULT '',
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
  `delivered` TEXT NULL,
  `package_id` INT UNSIGNED NULL,
  `type` VARCHAR(20) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `member_cache` (
  `user_id` BIGINT NOT NULL,
  `checked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `k` VARCHAR(100) NOT NULL,
  `v` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(150) NOT NULL DEFAULT '',
  `chat_id` VARCHAR(100) NOT NULL,
  `url` VARCHAR(255) NOT NULL DEFAULT '',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(20) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `qty` INT NOT NULL DEFAULT 1,
  `pool` VARCHAR(50) NOT NULL DEFAULT 'default',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(20) NOT NULL,
  `pool` VARCHAR(50) NOT NULL DEFAULT 'default',
  `content` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_pool` (`type`, `pool`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Payment system (Add Money) ----------
CREATE TABLE IF NOT EXISTS `pay_orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ref` VARCHAR(40) NOT NULL,
  `user_id` BIGINT NOT NULL DEFAULT 0,
  `kind` VARCHAR(10) NOT NULL DEFAULT 'bot',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` VARCHAR(12) NOT NULL DEFAULT 'pending',
  `method` VARCHAR(12) NOT NULL DEFAULT '',
  `trx_id` VARCHAR(40) NOT NULL DEFAULT '',
  `attempts` INT NOT NULL DEFAULT 0,
  `chat_id` BIGINT NULL,
  `msg_id` BIGINT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  `paid_at` DATETIME NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uniq_ref` (`ref`), KEY `idx_user` (`user_id`), KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pay_trx` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `method` VARCHAR(12) NOT NULL,
  `trx_id` VARCHAR(40) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `message` TEXT NULL,
  `source` VARCHAR(12) NOT NULL DEFAULT 'admin',
  `used` TINYINT(1) NOT NULL DEFAULT 0,
  `used_ref` VARCHAR(40) NOT NULL DEFAULT '',
  `used_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uniq_trx` (`method`, `trx_id`), KEY `idx_used` (`used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pay_keys` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NOT NULL DEFAULT '',
  `api_key` VARCHAR(64) NOT NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `hits` INT NOT NULL DEFAULT 0,
  `last_used` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uniq_key` (`api_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---------- FFGlory API integration ----------
CREATE TABLE IF NOT EXISTS `ffglory_settings` (
  `k` varchar(100) NOT NULL,
  `v` longtext NOT NULL,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ffglory_orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `group_id` varchar(120) NOT NULL DEFAULT '',
  `clan_id` varchar(80) NOT NULL,
  `region` varchar(50) NOT NULL,
  `region_name` varchar(150) NOT NULL DEFAULT '',
  `tier` varchar(20) NOT NULL DEFAULT 'basic',
  `bot_count` int NOT NULL DEFAULT 0,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` varchar(30) NOT NULL DEFAULT 'processing',
  `error_message` text NULL,
  `api_response` longtext NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_user` (`user_id`), KEY `idx_group` (`group_id`),
  KEY `idx_status` (`status`), KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ffglory_coupons` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(80) NOT NULL,
  `user_id` bigint NULL,
  `basic_credits` int NOT NULL DEFAULT 0,
  `premium_credits` int NOT NULL DEFAULT 0,
  `status` varchar(30) NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL,
  `assigned_at` datetime NULL,
  `redeemed_at` datetime NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `uniq_coupon` (`coupon_code`),
  KEY `idx_coupon_user` (`user_id`), KEY `idx_coupon_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ffglory_api_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `endpoint` varchar(180) NOT NULL,
  `method` varchar(10) NOT NULL,
  `http_status` int NOT NULL DEFAULT 0,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `response_ms` int NOT NULL DEFAULT 0,
  `actor_id` bigint NOT NULL DEFAULT 0,
  `error_message` varchar(500) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_endpoint` (`endpoint`), KEY `idx_status` (`http_status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `ffglory_settings` (`k`,`v`) VALUES
('enabled','0'),
('base_url','https://ffglory.pro'),
('price_basic','0'),
('price_premium','0'),
('last_success',''),
('last_error',''),
('account_username',''),
('account_role',''),
('account_basic_credits','0'),
('account_premium_credits','0'),
('account_max_groups','0');


CREATE TABLE IF NOT EXISTS `ffglory_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `group_id` varchar(120) NOT NULL,
  `clan_id` varchar(80) NOT NULL DEFAULT '',
  `region` varchar(50) NOT NULL DEFAULT '',
  `status` varchar(30) NOT NULL DEFAULT '',
  `glory_farmed` bigint NOT NULL DEFAULT 0,
  `bot_count` int NOT NULL DEFAULT 0,
  `launched_at` datetime NULL,
  `snapshot_json` longtext NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_history_group` (`group_id`), KEY `idx_history_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ffglory_notifications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `message` text NOT NULL,
  `remote_created_at` varchar(80) NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`), KEY `idx_notif_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;