-- Cơ sở dữ liệu Trợ lý Phân Tích Cầu Baccarat & Quản Lý Vốn
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') DEFAULT 'user',
  `xu_balance` DECIMAL(15, 2) DEFAULT 0.00,
  `bank_balance` DECIMAL(15, 2) DEFAULT 0.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `xu_transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `admin_id` INT NULL,
  `amount` DECIMAL(15, 2) NOT NULL,
  `type` ENUM('grant', 'deduct', 'usage') NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bridge_records` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `table_name` VARCHAR(20) NOT NULL,
  `result` ENUM('P', 'B', 'T') NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_table_time` (`table_name`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tạo sẵn tài khoản Admin mặc định (mật khẩu: 123412345)
INSERT INTO `users` (`username`, `password_hash`, `role`, `xu_balance`, `bank_balance`)
VALUES ('admin1324', '$2y$10$tHfgcO5E0U89P0v6X2P4p.r9hV3zU7V8vKk4u3W0j0B6yQh5o5HlS', 'admin', 999999, 0)
ON DUPLICATE KEY UPDATE `role`='admin';