DROP TABLE IF EXISTS team_players;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS club;
DROP TABLE IF EXISTS league;
DROP TABLE IF EXISTS user_team;
DROP TABLE IF EXISTS user;

CREATE TABLE `league`
(
  `league_id` int
(11) NOT NULL,
  `league_name` varchar
(45) DEFAULT NULL,
  `country_name` varchar
(45) DEFAULT NULL,
  `no_of_clubs` int
(11) DEFAULT NULL,
  PRIMARY KEY
(`league_id`)
) ENGINE=InnoDB;


CREATE TABLE `user`
(
  `id` int
(11) NOT NULL,
  `full_name` varchar
(45) NOT NULL,
  `email` varchar
(45) NOT NULL,
  `password` varchar
(45) NOT NULL,
  PRIMARY KEY
(`id`)
) ENGINE=InnoDB;



CREATE TABLE `club`
(
  `club_id` int
(11) NOT NULL,
  `club_name` varchar
(45) DEFAULT NULL,
  `club_league_id` int
(11) NOT NULL,
  PRIMARY KEY
(`club_id`),
  KEY `club_league_id_idx`
(`club_league_id`),
  CONSTRAINT `club_league_id` FOREIGN KEY
(`club_league_id`) REFERENCES `league`
(`league_id`)
) ENGINE=InnoDB ;


CREATE TABLE `player`
(
  `player_id` int
(11) NOT NULL,
  `player_club_id` int
(11) DEFAULT NULL,
  `player_name` varchar
(45) DEFAULT NULL,
  `player_rating` int
(11) DEFAULT NULL,
  `player_position` varchar
(45) DEFAULT NULL,
  PRIMARY KEY
(`player_id`),
  KEY `player_club_id_idx`
(`player_club_id`),
  CONSTRAINT `player_club_id` FOREIGN KEY
(`player_club_id`) REFERENCES `club`
(`club_id`)
) ENGINE=InnoDB ;


CREATE TABLE `user_team`
(
  `user_team_id` int
(11) NOT NULL,
  `user_id` int
(11) DEFAULT NULL,
  PRIMARY KEY
(`user_team_id`),
  KEY `user_id_idx`
(`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY
(`user_id`) REFERENCES `user`
(`id`)
) ENGINE=InnoDB;


CREATE TABLE `team_players`
(
  `team_player_id` int
(11) NOT NULL,
  `user_team_id` int
(11) DEFAULT NULL,
  `player_id` int
(11) DEFAULT NULL,
  PRIMARY KEY
(`team_player_id`),
  KEY `team_player_id_idx`
(`user_team_id`),
  KEY `player_id_idx`
(`player_id`),
  CONSTRAINT `player_id` FOREIGN KEY
(`player_id`) REFERENCES `player`
(`player_id`),
  CONSTRAINT `team_player_id` FOREIGN KEY
(`user_team_id`) REFERENCES `user_team`
(`user_team_id`)
) ENGINE=InnoDB;

INSERT INTO `user`
    (`id`, `full_name`, `email`, `password`) VALUES
(1,'Sushant Baskota','sbaskota@go.olemiss.edu','thikxahai'),
(2,'Ayush','aupadhyay@go.olemiss.edu','thikxaina'),
(3, 'Thomas Shelby', 'tommy@shelbyltd.com', 'peakyblinders');

INSERT INTO `league` (`league_id`,`league_name`, `country_name`, `no_of_clubs`) VALUES
(1,'Barclays','England',20),
(2,'Laliga','Spain',20),
(3,'Serie A','Italy',20),
(4,'Bundesliga','Germany',18),
(5,'Ligue 1','France',18);

INSERT INTO `club` (`club_id`,`club_name`, `club_league_id`) VALUES
(1,'Manchester City',1),
(2,'Liverpool',1),
(3,'Arsenal',1),
(4,'Barcelona',2),
(5,'Real Madrid',2),
(6,'PSG',5),
(7,'Dortmund',4),
(8,'Monaco',5),
(9,'Juventus',3),
(10,'Atletico Madrid',2),
(11,'Bayern Munich',4);

INSERT INTO `player` (`player_id`,`player_club_id`, `player_name`, `player_rating`, `player_position`) VALUES
(1,4,'Lionel Messi',5,'RW'),
(2,4,'Suarez',4,'ST'),
(3,4,'Frenkie Dejong',5,'CM'),
(4,1,'Sergio Aguero',4,'ST'),
(5,6,'Neymar Jr.',5,'LW'),
(6,9,'Cristiano Ronaldo',5,'CF'),
(7,6,'Angel Di Maria',4,'RW'),
(8,1,'Mahrez',4,'RW'),
(9,9,'Paulo Dybala', 5, 'RM');

INSERT INTO `user_team` (`user_team_id`,`user_id`) VALUES
(1,1),
(2,2),
(3,3);

INSERT INTO `team_players` (`team_player_id`,`user_team_id`, `player_id`) VALUES
(1,1,1),
(2,1,2),
(3,1,3),
(4,1,4),
(5,2,4),
(6,2,2),
(7,2,1),
(8,2,4),
(9,3,9),
(10,3,8),
(11,3,7),
(12,3,6),
(13,3,5);

SELECT full_name , group_concat(player_name) AS NumberOfPlayers
FROM user natural join user_team natural join team_players natural join player
where   user.id = user_team.user_id and user_team.user_team_id= team_players.user_team_id
Group by user.id;

SELECT league_name, COUNT(distinct player_id)
from player natural join club natural join league
where player_club_id = club_id and club_league_id = league_id
group by league_name;