CREATE TABLE `RaidPlayers` (
  `raidplayer_id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(17),
  `username_lowwercase` VARCHAR(17),
  `uniquie_id` VARCHAR(32),
  `personal_warp_id` INT,
  `coins` INT,
  `join_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`raidplayer_id`)
);

CREATE TABLE `RaidTeams` (
  `team_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32),
  `tag` VARCHAR(10),
  `rally_warp_id` INT,
  `hq_warp_id` INT,
  `max_members` INT,
  `donator_rank` VARCHAR(17),
  PRIMARY KEY (`team_id`),
  KEY `FK` (`rally_warp_id`, `hq_warp_id`)
);

CREATE TABLE `TeamMembers` (
  `raidplayer_id` INT ,
  `team_id` INT,
  `team_role` VARCHAR(17),
  `is_active` TINYINT,
  `join_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`raidplayer_id`, `team_id`)
);

CREATE TABLE `Warps` (
  `warp_id` INT NOT NULL AUTO_INCREMENT,
  `world_name` VARCHAR(50),
  `x` INT,
  `y` INT,
  `z` INT,
  `creation_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`warp_id`)
);

INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 1, 2, 3);
INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 4, 5, 6);
INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 7, 8, 9);
INSERT INTO RaidPlayers (username, username_lowwercase, uniquie_id, personal_warp_id, coins) VALUES ('Sprock', 'sprock', '5d793eed51b747659b384fdaa00034d7', 1, 100);
INSERT INTO RaidPlayers (username, username_lowwercase, uniquie_id, personal_warp_id, coins) VALUES ('Issy', 'issy', '46a492d220754195b9c3a51363cca82e', 2, 200);
INSERT INTO RaidTeams (name, tag, rally_warp_id, hq_warp_id, max_members, donator_rank) VALUES ('Rebellion', 'Rebel', 1, 3, 10, 'ADMIN');

INSERT INTO `TeamMembers` (raidplayer_id, team_id, team_role, is_active) VALUES (1, 1, 'LEADER', 1);
INSERT INTO `TeamMembers` (raidplayer_id, team_id, team_role, is_active) VALUES (2, 1, 'MOD', 1);


UPDATE `RaidPlayers` SET `coins` = 1 WHERE raidplayer_id = 2;
UPDATE `RaidTeams` SET `rally_warp_id` = 2 WHERE team_id = 1;

-- add a new warp and set it as team's warp
INSERT INTO Warps (world_name, x, y, z) VALUES ('world', 10, 11, 12);
UPDATE RaidTeams SET RaidTeams.rally_warp_id = (SELECT LAST_INSERT_ID())




==== QUERIES

SELECT * FROM RaidPlayers
INNER JOIN Warps ON `RaidPlayers`.personal_warp_id = `Warps`.warp_id;

SELECT * FROM TeamMembers
INNER JOIN RaidTeams ON `RaidTeams`.team_id = `TeamMembers`.team_id
INNER JOIN RaidPlayers ON RaidPlayers.raidplayer_id = TeamMembers.raidplayer_id;

SELECT team_id,  name, tag, max_members, donator_rank, rally.world_name as rally_world, rally.x as rally_x, rally.y as rally_y, rally.z as rally_z, hq.world_name as hq_world, hq.x as hq_x, hq.y as hq_y, hq.z as hq_z FROM RaidTeams
INNER JOIN Warps as rally ON rally_warp_id = rally.warp_id
INNER JOIN Warps as hq ON hq_warp_id = hq.warp_id;

SELECT * FROM RaidPlayers where `raidplayer_id` = 2;