-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 10, 2026 at 07:34 AM
-- Server version: 10.11.16-MariaDB
-- PHP Version: 8.4.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `gamingserver_server`
--

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `username` varchar(80) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `allowed_ip` varchar(45) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admins`
--

INSERT INTO `admins` (`id`, `username`, `password_hash`, `allowed_ip`, `is_active`, `created_at`) VALUES
(1, 'admin', '$2y$10$LshpGjY0pPjCO53f1z9aN.wc55OblpIkPgnrukr2Y.yXHVXpA22/6', '103.7.120.63', 1, '2026-01-26 10:31:25');

-- --------------------------------------------------------

--
-- Table structure for table `admin_tokens`
--

CREATE TABLE `admin_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `admin_id` bigint(20) UNSIGNED NOT NULL,
  `token_hash` char(64) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admin_tokens`
--

INSERT INTO `admin_tokens` (`id`, `admin_id`, `token_hash`, `ip`, `user_agent`, `expires_at`, `created_at`) VALUES
(147, 1, 'd7f67741d88ad2bf50998e704f6a5438531b549dd941459d7cb1e8232f58b584', '103.60.205.50', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36', '2026-04-15 14:00:24', '2026-03-03 06:32:26'),
(154, 1, 'f52971e84ca1e84f8fc598bd47c61e29b16c20cb4a49375cfd71dca7094babb7', '103.7.120.63', 'Mozilla/5.0 (Linux; Android 10; itel P681L) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.181 Mobile Safari/537.36', '2026-05-09 22:04:00', '2026-04-10 04:04:00'),
(155, 1, '50f2581c2b32225bc564ac71c919f6be69cdde32c10152878ac6be1357544377', '103.7.120.63', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36 OPR/129.0.0.0', '2026-05-10 05:12:52', '2026-04-10 11:12:52');

-- --------------------------------------------------------

--
-- Table structure for table `rooms`
--

CREATE TABLE `rooms` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `room_code` varchar(64) NOT NULL,
  `instance_id` varchar(64) DEFAULT NULL,
  `host_name` varchar(64) DEFAULT NULL,
  `screenshot_path` varchar(255) DEFAULT NULL,
  `screenshot_expires_at` datetime DEFAULT NULL,
  `is_assigned` tinyint(1) NOT NULL DEFAULT 0,
  `assigned_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `lock_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `lock_until` datetime DEFAULT NULL,
  `reserved_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `reserved_until` datetime DEFAULT NULL,
  `assigned_at` datetime DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `assigned_to` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_assigned_by` varchar(120) DEFAULT NULL,
  `status` enum('waiting','assigned','started','closed') NOT NULL DEFAULT 'waiting'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `rooms`
--

INSERT INTO `rooms` (`id`, `room_code`, `instance_id`, `host_name`, `screenshot_path`, `screenshot_expires_at`, `is_assigned`, `assigned_user_id`, `lock_user_id`, `lock_until`, `reserved_user_id`, `reserved_until`, `assigned_at`, `started_at`, `closed_at`, `expires_at`, `updated_at`, `assigned_to`, `created_at`, `last_assigned_by`, `status`) VALUES
(38, '999999999', 'GZ-8', NULL, NULL, NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 03:31:51', NULL, '2026-04-10 11:30:17', '2026-04-10 03:41:51', '2026-04-10 11:30:17', 'GZ-8', '2026-04-10 03:30:12', 'API', 'closed'),
(71, '989898', 'GZ-7', NULL, NULL, NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 04:00:07', NULL, '2026-04-10 11:30:17', '2026-04-10 04:10:07', '2026-04-10 11:30:17', 'GZ-7', '2026-04-10 03:31:17', 'API', 'closed'),
(89, '09316184', 'GZ-9', 'lktxwqcm', NULL, NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 11:42:51', NULL, '2026-04-10 11:57:46', '2026-04-10 11:52:51', '2026-04-10 11:57:46', 'GZ-9', '2026-04-10 11:42:39', 'API', 'closed'),
(90, '05742314', 'GZ-10', 'lktxwqcm', 'uploads/rooms/room_05742314_1775800655.png', NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 11:58:40', NULL, '2026-04-10 12:49:19', '2026-04-10 12:08:40', '2026-04-10 12:49:19', 'GZ-10', '2026-04-10 11:57:35', 'API', 'closed'),
(116, '07811225', 'GZ-11', 'lktxwqcm', NULL, NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 12:20:13', NULL, '2026-04-10 12:49:26', '2026-04-10 12:30:13', '2026-04-10 12:49:26', 'GZ-11', '2026-04-10 12:18:52', 'API', 'closed'),
(117, '08321681', NULL, 'lktxwqcm', 'uploads/rooms/room_08321681_1775803758.png', NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-04-10 12:50:00', NULL, '2026-04-10 12:50:00', NULL, '2026-04-10 12:49:18', NULL, 'closed'),
(118, '06651810', 'GZ-12', 'lktxwqcm', 'uploads/rooms/room_06651810_1775803907.png', NULL, 1, 1, NULL, NULL, NULL, NULL, '2026-04-10 12:51:49', NULL, NULL, '2026-04-10 12:53:49', '2026-04-10 06:52:39', 'GZ-12', '2026-04-10 12:51:47', 'API', 'assigned');

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `key` varchar(100) NOT NULL,
  `value` text NOT NULL DEFAULT '',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`key`, `value`, `updated_at`) VALUES
('bot_submit_secret', 'botSecret2026LK', '2026-04-10 05:41:44'),
('cron_last_run', '1775803766', '2026-04-10 12:49:26'),
('cron_secret', '8e590da0cadaa5dcca53e2c954d69267', '2026-04-10 01:58:16'),
('super_access_key', 'তোমার-key', '2026-04-10 06:10:11');

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('credit','debit') NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `admin_username` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `username` varchar(80) NOT NULL,
  `email` varchar(255) NOT NULL DEFAULT '',
  `phone` varchar(30) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `balance` decimal(18,2) NOT NULL DEFAULT 0.00,
  `room_price` decimal(18,2) NOT NULL DEFAULT 0.00,
  `api_key` varchar(64) NOT NULL,
  `api_secret` varchar(128) NOT NULL DEFAULT '',
  `api_secret_hash` char(64) NOT NULL,
  `api_secret_last4` char(4) NOT NULL DEFAULT '----',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_room_assign_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `phone`, `password_hash`, `balance`, `room_price`, `api_key`, `api_secret`, `api_secret_hash`, `api_secret_last4`, `is_active`, `created_at`, `last_room_assign_at`) VALUES
(1, 'TopGamingBD', '', '01700111223', '$2y$10$bMN.b/PHA3m2pcAvQG7cT.HYBOuY2TzPUQ85UcNIzAzWrPMyJGTsS', 100000.00, 1.00, 'XpY2GAXeKJwxSqF87BbPzD68Woy5trj8iKS1PPM', '', '182ae519c6143236c8e071579986195fe94e4d267c4390c767c78d58496a6c34', '6c34', 1, '2026-01-26 11:45:35', '2026-02-07 00:01:54');

-- --------------------------------------------------------

--
-- Table structure for table `user_tokens`
--

CREATE TABLE `user_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `token_hash` char(64) NOT NULL,
  `user_agent` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `admin_tokens`
--
ALTER TABLE `admin_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_token_hash` (`token_hash`),
  ADD KEY `idx_admin_id` (`admin_id`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- Indexes for table `rooms`
--
ALTER TABLE `rooms`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_room_code` (`room_code`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_assigned_at` (`assigned_at`),
  ADD KEY `idx_is_assigned` (`is_assigned`),
  ADD KEY `idx_assigned_user_id` (`assigned_user_id`),
  ADD KEY `idx_reserved_until` (`reserved_until`),
  ADD KEY `idx_waiting` (`status`,`is_assigned`,`id`),
  ADD KEY `idx_assigned_user` (`assigned_user_id`,`assigned_at`),
  ADD KEY `idx_rooms_instance_id` (`instance_id`),
  ADD KEY `idx_rooms_status` (`status`),
  ADD KEY `idx_rooms_expires_at` (`expires_at`),
  ADD KEY `idx_rooms_waiting_pick` (`status`,`is_assigned`,`expires_at`,`id`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`key`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `api_key` (`api_key`),
  ADD UNIQUE KEY `uniq_api_key` (`api_key`),
  ADD UNIQUE KEY `uq_phone` (`phone`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_is_active` (`is_active`);

--
-- Indexes for table `user_tokens`
--
ALTER TABLE `user_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_token_hash` (`token_hash`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `admin_tokens`
--
ALTER TABLE `admin_tokens`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=156;

--
-- AUTO_INCREMENT for table `rooms`
--
ALTER TABLE `rooms`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=119;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `user_tokens`
--
ALTER TABLE `user_tokens`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin_tokens`
--
ALTER TABLE `admin_tokens`
  ADD CONSTRAINT `fk_admin_tokens_admin` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `rooms`
--
ALTER TABLE `rooms`
  ADD CONSTRAINT `fk_rooms_assigned_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `fk_tx_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_tokens`
--
ALTER TABLE `user_tokens`
  ADD CONSTRAINT `fk_user_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
