drop database ToolsDB;

create database ToolsDB;

use ToolsDB;

create table Player(
   player_id INT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(100) NOT NULL,
   lastname VARCHAR(40) NOT NULL,
   nickname VARCHAR(40) NOT NULL,
   wins integer,
   losses integer,
   current_win_streak integer,
   created DATETIME,
   last_seen DATETIME,
   PRIMARY KEY ( player_id ),
   UNIQUE KEY player_unique_id (firstname , lastname, nickname)

   );

create table Battles(
    battle_id INT NOT NULL AUTO_INCREMENT,
    starttime DATETIME,
    endtime DATETIME,
    PRIMARY KEY ( battle_id )
    );



create table Attacker_Battles(
    attacker_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( attacker_id ),
    UNIQUE KEY attacking_player_unique_id (battle_id , attacker_id, player_id),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (attacker_id) REFERENCES player (player_id)
    ON DELETE CASCADE
    );
create table Defender_Battles(
    defender_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( defender_id ),
    UNIQUE KEY defending_player_unique_id (battle_id , defender_id, player_id),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (defender_id) REFERENCES player (player_id)
    ON DELETE CASCADE
    );
create table Winner_Battles(
    winner_id INT NOT NULL AUTO_INCREMENT,
    battle_id INT NOT NULL,
    player_id INT NOT NULL,
    PRIMARY KEY ( winner_id ),
    UNIQUE KEY winning_player_unique_id (battle_id , winner_id, player_id),
    FOREIGN KEY (battle_id) REFERENCES Battles (battle_id),
    FOREIGN KEY (winner_id) REFERENCES player (player_id)
    ON DELETE CASCADE
    );




insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'vaibhav','chauhan', 'anshu',   0,    0,        0 ,              NOW(),       NOW());


insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'rishabh','chauhan', 'rishu',   0,    0,        0 ,              NOW(),       NOW());

insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());
insert into Battles (starttime, endtime ) values (
                     NOW(),       NOW());

select * from player;

delete from player where player_id = 3;

select * from Battles;

insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'Naman','Aggarwal', 'baniya',   0,    0,        0 ,              NOW(),       NOW());


insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'abhishek','gaur', 'goudd',   0,    0,        0 ,              NOW(),       NOW());

insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'venkatnandan','pandey', 'champak',   0,    0,        0 ,              NOW(),       NOW());

insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'pawan','yadav', 'duggal',   0,    0,        0 ,              NOW(),       NOW());

insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'Arun','yadav', 'commando',   0,    0,        0 ,              NOW(),       NOW());

insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'Direndra','Singh', 'gujjar',   0,    0,        0 ,              NOW(),       NOW());
insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'Jayant','Verma', 'thapa',   0,    0,        0 ,              NOW(),       NOW());
insert into player (firstname, lastname,nickname,  wins, losses,current_win_streak, created, last_seen ) values (
                    'Ankit','Sharma', 'chapad',   0,    0,        0 ,              NOW(),       NOW());

insert into Attacker_Battles (battle_id, player_id) values (1,2);
insert into Defender_Battles (battle_id, player_id) values (1,1);
insert into Winner_Battles (battle_id, player_id) values (1,1);

insert into Attacker_Battles (battle_id, player_id) values (2,4);
insert into Defender_Battles (battle_id, player_id) values (2,6);
insert into Winner_Battles (battle_id, player_id) values (2,4);

insert into Attacker_Battles (battle_id, player_id) values (3,4);
insert into Defender_Battles (battle_id, player_id) values (3,6);
insert into Winner_Battles (battle_id, player_id) values (3,4);

insert into Attacker_Battles (battle_id, player_id) values (4,10);
insert into Defender_Battles (battle_id, player_id) values (4,9);
insert into Winner_Battles (battle_id, player_id) values (4,10);

insert into Attacker_Battles (battle_id, player_id) values (5,1);
insert into Defender_Battles (battle_id, player_id) values (5,9);
insert into Winner_Battles (battle_id, player_id) values (5,9);

insert into Attacker_Battles (battle_id, player_id) values (6,2);
insert into Defender_Battles (battle_id, player_id) values (6,4);
insert into Winner_Battles (battle_id, player_id) values (6,2);

insert into Attacker_Battles (battle_id, player_id) values (8,1);
insert into Defender_Battles (battle_id, player_id) values (8,10);
insert into Winner_Battles (battle_id, player_id) values (8,1);

select CONCAT(player.firstname, player.lastname) as Attackers,  Battles.battle_id As BATTLE from ((player inner join Attacker_Battles on Attacker_Battles.player_id = player.player_id ) inner join Battles on Battles.battle_id = Attacker_Battles.battle_id);

select CONCAT(player.firstname, player.lastname) as Defenders from ((player inner join Defender_Battles on Defender_Battles.player_id = player.player_id ) inner join Battles on Battles.battle_id = Defender_Battles.battle_id);

select CONCAT(player.firstname, player.lastname) as Winners from ((player inner join Winner_battles on Winner_battles.player_id = player.player_id ) inner join Battles on Battles.battle_id = Winner_battles.battle_id);