-- ============================================
-- Create Counter-Strike Authentication Tables
-- ============================================
-- This script creates the physical tables needed for PE HOST login/registration
-- Safe to run multiple times - will not delete existing data
-- Matches exactly the conditions used by the PHP scripts
-- ============================================



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

--
-- Table: counter_strike_users
-- Used by: auto.php, submit.php
-- Main users table for login/registration
--

CREATE TABLE IF NOT EXISTS `counter_strike_users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `passhash` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `steamid` varchar(64) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `auto_login` tinyint(1) NOT NULL DEFAULT 0,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `money` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_steamid` (`steamid`),
  KEY `idx_ip` (`ip`),
  KEY `idx_auto_login` (`auto_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table: counter_strike_tokens
-- Used by: util.php, status.php, submit.php
-- Authentication tokens for MOTD
--

CREATE TABLE IF NOT EXISTS `counter_strike_tokens` (
  `token` char(40) NOT NULL,
  `steamid` varchar(32) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `action` enum('login','register','open') NOT NULL DEFAULT 'open',
  `status` enum('PENDING','OK','FAIL','pending','ok','fail') NOT NULL DEFAULT 'PENDING',
  `username` varchar(32) DEFAULT NULL,
  `remember` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`token`),
  KEY `idx_token_status` (`status`),
  KEY `idx_token_steamid` (`steamid`),
  KEY `idx_token_ip` (`ip`),
  KEY `idx_token_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table: counter_strike_users_util
-- Used by: util.php (utility functions)
-- Alternative users table for utility functions
--

CREATE TABLE IF NOT EXISTS `counter_strike_users_util` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `steamid` varchar(32) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `username` varchar(32) NOT NULL,
  `passhash` varchar(255) NOT NULL,
  `remember` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_sid` (`steamid`),
  KEY `idx_ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Add missing columns to counter_strike_users (matching submit.php logic)
-- Safe: Only adds if missing, converts password to passhash if needed
-- Using stored procedures for safe execution
--

DELIMITER $$

DROP PROCEDURE IF EXISTS add_counter_strike_columns$$

CREATE PROCEDURE add_counter_strike_columns()
BEGIN
  DECLARE passhash_exists INT DEFAULT 0;
  DECLARE password_exists INT DEFAULT 0;
  DECLARE email_exists INT DEFAULT 0;
  DECLARE email_not_null INT DEFAULT 0;
  DECLARE steamid_exists INT DEFAULT 0;
  DECLARE ip_exists INT DEFAULT 0;
  DECLARE auto_login_exists INT DEFAULT 0;
  DECLARE last_login_exists INT DEFAULT 0;
  DECLARE created_at_exists INT DEFAULT 0;
  DECLARE money_exists INT DEFAULT 0;
  
  -- Check if passhash column exists
  SELECT COUNT(*) INTO passhash_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'passhash';
  

    

    ELSE
      ALTER TABLE `counter_strike_users` ADD COLUMN `passhash` VARCHAR(255) NOT NULL AFTER `username`;
    END IF;
  END IF;
  
  -- Check if email column exists
  SELECT COUNT(*) INTO email_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'email';
  
  IF email_exists > 0 THEN
    -- Check if email is NOT NULL and make it NULL
    SELECT COUNT(*) INTO email_not_null
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'counter_strike_users'
      AND column_name = 'email'
      AND IS_NULLABLE = 'NO'
      AND COLUMN_DEFAULT IS NULL;
    
    IF email_not_null > 0 THEN
      ALTER TABLE `counter_strike_users` MODIFY `email` VARCHAR(255) NULL DEFAULT NULL;
    END IF;
  ELSE
    ALTER TABLE `counter_strike_users` ADD COLUMN `email` VARCHAR(255) DEFAULT NULL AFTER `passhash`;
  END IF;
  
  -- Check and add steamid
  SELECT COUNT(*) INTO steamid_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'steamid';
  
  IF steamid_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `steamid` VARCHAR(64) DEFAULT NULL;
  END IF;
  
  -- Check and add ip
  SELECT COUNT(*) INTO ip_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'ip';
  
  IF ip_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `ip` VARCHAR(45) DEFAULT NULL;
  END IF;
  
  -- Check and add auto_login
  SELECT COUNT(*) INTO auto_login_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'auto_login';
  
  IF auto_login_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `auto_login` TINYINT(1) NOT NULL DEFAULT 0;
  END IF;
  
  -- Check and add last_login
  SELECT COUNT(*) INTO last_login_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'last_login';
  
  IF last_login_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `last_login` TIMESTAMP NULL DEFAULT NULL;
  END IF;
  
  -- Check and add created_at
  SELECT COUNT(*) INTO created_at_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'created_at';
  
  IF created_at_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
  END IF;
  
  -- Check and add money column
  SELECT COUNT(*) INTO money_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND column_name = 'money';
  
  IF money_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD COLUMN `money` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `created_at`;
  END IF;
  
END$$

DELIMITER ;

CALL add_counter_strike_columns();

DROP PROCEDURE IF EXISTS add_counter_strike_columns;

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

--
-- Add missing columns to counter_strike_tokens
--

DELIMITER $$

DROP PROCEDURE IF EXISTS add_counter_strike_tokens_columns$$

CREATE PROCEDURE add_counter_strike_tokens_columns()
BEGIN
  DECLARE updated_at_exists INT DEFAULT 0;
  
  SELECT COUNT(*) INTO updated_at_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_tokens'
    AND column_name = 'updated_at';
  
  IF updated_at_exists = 0 THEN
    ALTER TABLE `counter_strike_tokens` ADD COLUMN `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;
  END IF;
  
END$$

DELIMITER ;

CALL add_counter_strike_tokens_columns();

DROP PROCEDURE IF EXISTS add_counter_strike_tokens_columns;

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

--
-- Add missing columns to counter_strike_users_util
--

DELIMITER $$

DROP PROCEDURE IF EXISTS add_counter_strike_users_util_columns$$

CREATE PROCEDURE add_counter_strike_users_util_columns()
BEGIN
  DECLARE passhash_exists INT DEFAULT 0;
  DECLARE pass_hash_exists INT DEFAULT 0;
  
  SELECT COUNT(*) INTO passhash_exists
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users_util'
    AND column_name = 'passhash';
  
  IF passhash_exists = 0 THEN
    SELECT COUNT(*) INTO pass_hash_exists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = DATABASE()
      AND table_name = 'counter_strike_users_util'
      AND column_name = 'pass_hash';
    
    IF pass_hash_exists > 0 THEN
      ALTER TABLE `counter_strike_users_util` CHANGE `pass_hash` `passhash` VARCHAR(255) NOT NULL;
    ELSE
      ALTER TABLE `counter_strike_users_util` ADD COLUMN `passhash` VARCHAR(255) NOT NULL AFTER `username`;
    END IF;
  END IF;
  
END$$

DELIMITER ;

CALL add_counter_strike_users_util_columns();

DROP PROCEDURE IF EXISTS add_counter_strike_users_util_columns;

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

--
-- Add missing indexes (safe - only adds if missing)
--

DELIMITER $$

DROP PROCEDURE IF EXISTS add_counter_strike_indexes$$

CREATE PROCEDURE add_counter_strike_indexes()
BEGIN
  DECLARE idx_exists INT DEFAULT 0;
  
  -- Indexes for counter_strike_users
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND index_name = 'idx_steamid';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD KEY `idx_steamid` (`steamid`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND index_name = 'idx_ip';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD KEY `idx_ip` (`ip`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users'
    AND index_name = 'idx_auto_login';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_users` ADD KEY `idx_auto_login` (`auto_login`);
  END IF;
  
  -- Indexes for counter_strike_tokens
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_tokens'
    AND index_name = 'idx_token_status';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_tokens` ADD KEY `idx_token_status` (`status`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_tokens'
    AND index_name = 'idx_token_steamid';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_tokens` ADD KEY `idx_token_steamid` (`steamid`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_tokens'
    AND index_name = 'idx_token_ip';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_tokens` ADD KEY `idx_token_ip` (`ip`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_tokens'
    AND index_name = 'idx_token_expires';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_tokens` ADD KEY `idx_token_expires` (`expires_at`);
  END IF;
  
  -- Indexes for counter_strike_users_util
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users_util'
    AND index_name = 'idx_sid';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_users_util` ADD KEY `idx_sid` (`steamid`);
  END IF;
  
  SELECT COUNT(*) INTO idx_exists
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE table_schema = DATABASE()
    AND table_name = 'counter_strike_users_util'
    AND index_name = 'idx_ip';
  
  IF idx_exists = 0 THEN
    ALTER TABLE `counter_strike_users_util` ADD KEY `idx_ip` (`ip`);
  END IF;
  
END$$

DELIMITER ;

CALL add_counter_strike_indexes();

DROP PROCEDURE IF EXISTS add_counter_strike_indexes;

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

--
-- Table: counter_strike_skins
-- Available skins in the shop
--

CREATE TABLE IF NOT EXISTS `counter_strike_skins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table: counter_strike_user_skins
-- User purchased skins
--

CREATE TABLE IF NOT EXISTS `counter_strike_user_skins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `skin_name` varchar(100) NOT NULL,
  `purchased_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_skin_name` (`skin_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table: counter_strike_admins
-- Admin accounts for shop management
--

CREATE TABLE IF NOT EXISTS `counter_strike_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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



-- ============================================
-- Tables created successfully!
-- ============================================
-- This script can be run multiple times safely
-- It will:
-- - Create tables only if they don't exist
-- - Add missing columns without affecting existing data
-- - Convert password/pass_hash to passhash if needed
-- - Make email NULL if it was NOT NULL (matching submit.php logic)
-- - Add missing indexes without errors
-- - Never delete or modify existing data
-- All tables use unique names with 'counter_strike_' prefix to avoid conflicts
-- ============================================

