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

CREATE TABLE IF NOT EXISTS `clans_tsg_clans` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `owner_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `points` INT NOT NULL DEFAULT 0,
  `total_kills` INT NOT NULL DEFAULT 0,
  `max_slots` INT NOT NULL DEFAULT 10,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` INT NOT NULL DEFAULT 0,
  `updated_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`),
  KEY `idx_active` (`active`),
  KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_members` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clan_id` INT NOT NULL,
  `steamid` VARCHAR(35) NOT NULL,
  `name` VARCHAR(32) NOT NULL DEFAULT '',
  `role` TINYINT NOT NULL DEFAULT 4,
  `joined_at` INT NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_steamid` (`steamid`),
  KEY `idx_clan_id` (`clan_id`),
  KEY `idx_role` (`role`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_player_settings` (
  `steamid` VARCHAR(35) NOT NULL,
  `last_name` VARCHAR(32) NOT NULL DEFAULT '',
  `invites_blocked` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`steamid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_upgrades` (
  `clan_id` INT NOT NULL,
  `hp_level` INT NOT NULL DEFAULT 0,
  `armor_level` INT NOT NULL DEFAULT 0,
  `damage_level` INT NOT NULL DEFAULT 0,
  `regen_level` INT NOT NULL DEFAULT 0,
  `slots_level` INT NOT NULL DEFAULT 0,
  `silent_level` INT NOT NULL DEFAULT 0,
  `vip_level` INT NOT NULL DEFAULT 0,
  `weapon_level` INT NOT NULL DEFAULT 0,
  `updated_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`clan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_points_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clan_id` INT NOT NULL,
  `reason` VARCHAR(64) NOT NULL,
  `amount` INT NOT NULL DEFAULT 0,
  `initiator_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `balance_before` INT NOT NULL DEFAULT 0,
  `balance_after` INT NOT NULL DEFAULT 0,
  `created_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_clan_id` (`clan_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_invites` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clan_id` INT NOT NULL,
  `inviter_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `target_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `status` TINYINT NOT NULL DEFAULT 0,
  `created_at` INT NOT NULL DEFAULT 0,
  `expires_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_clan_id` (`clan_id`),
  KEY `idx_target_steamid` (`target_steamid`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_tournaments` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clan_a_id` INT NOT NULL,
  `clan_b_id` INT NOT NULL,
  `kills_goal` INT NOT NULL DEFAULT 0,
  `score_a` INT NOT NULL DEFAULT 0,
  `score_b` INT NOT NULL DEFAULT 0,
  `reward_points` INT NOT NULL DEFAULT 0,
  `status` TINYINT NOT NULL DEFAULT 0,
  `created_at` INT NOT NULL DEFAULT 0,
  `expires_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_clan_a_id` (`clan_a_id`),
  KEY `idx_clan_b_id` (`clan_b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_tournament_stats` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `tournament_id` INT NOT NULL,
  `killer_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `victim_steamid` VARCHAR(35) NOT NULL DEFAULT '',
  `created_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_tournament_id` (`tournament_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_quests` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `quest_key` VARCHAR(64) NOT NULL,
  `title` VARCHAR(128) NOT NULL DEFAULT '',
  `description` TEXT NULL,
  `type` VARCHAR(32) NOT NULL DEFAULT '',
  `target_value` INT NOT NULL DEFAULT 0,
  `reward_points` INT NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  `starts_at` INT NOT NULL DEFAULT 0,
  `ends_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_quest_progress` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `quest_id` INT NOT NULL,
  `clan_id` INT NOT NULL,
  `progress` INT NOT NULL DEFAULT 0,
  `completed` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_quest_clan` (`quest_id`, `clan_id`),
  KEY `idx_clan_id` (`clan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clans_tsg_clan_stats` (
  `clan_id` INT NOT NULL,
  `wins` INT NOT NULL DEFAULT 0,
  `losses` INT NOT NULL DEFAULT 0,
  `draws` INT NOT NULL DEFAULT 0,
  `members_joined` INT NOT NULL DEFAULT 0,
  `updated_at` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`clan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;