CREATE TABLE Teams (
    TeamID INT AUTO_INCREMENT PRIMARY KEY,
    TeamName VARCHAR(100) NOT NULL,
    City VARCHAR(100),
    FoundedYear INT,
    Stadium VARCHAR(100)
);

CREATE TABLE Players (
    PlayerID INT AUTO_INCREMENT PRIMARY KEY,
    PlayerName VARCHAR(100) NOT NULL,
    Age INT,
    Position VARCHAR(50),
    TeamID INT,
    FOREIGN KEY (TeamID) REFERENCES Teams(TeamID) ON DELETE SET NULL
);

CREATE TABLE Coaches (
    CoachID INT AUTO_INCREMENT PRIMARY KEY,
    CoachName VARCHAR(100) NOT NULL,
    ExperienceYears INT,
    TeamID INT,
    FOREIGN KEY (TeamID) REFERENCES Teams(TeamID) ON DELETE SET NULL
);

CREATE TABLE Matches (
    MatchID INT AUTO_INCREMENT PRIMARY KEY,
    HomeTeamID INT,
    AwayTeamID INT,
    MatchDate DATE,
    Stadium VARCHAR(100),
    HomeScore INT,
    AwayScore INT,
    FOREIGN KEY (HomeTeamID) REFERENCES Teams(TeamID) ON DELETE CASCADE,
    FOREIGN KEY (AwayTeamID) REFERENCES Teams(TeamID) ON DELETE CASCADE
);

CREATE TABLE Statistics (
    StatID INT AUTO_INCREMENT PRIMARY KEY,
    PlayerID INT,
    MatchID INT,
    Goals INT DEFAULT 0,
    Assists INT DEFAULT 0,
    YellowCards INT DEFAULT 0,
    RedCards INT DEFAULT 0,
    FOREIGN KEY (PlayerID) REFERENCES Players(PlayerID) ON DELETE CASCADE,
    FOREIGN KEY (MatchID) REFERENCES Matches(MatchID) ON DELETE CASCADE
);

INSERT Teams (TeamID,TeamName,City,FoundedYear,Stadium) VALUES
    (1,'Lions FC','New York',1998,'Lions Arena'),
    (2,'Eagles FC','Los Angeles',2005,'Eagles Stadium'),
    (3,'Sharks FC','Chicago',2010,'Sharks Park'),
    (4,'Tigers FC','Houston',2012,'Tigers Den'),
    (5,'Wolves FC','Miami',2018,'Wolves Stadium');

INSERT Players (PlayerID,PlayerName,Age,Position,TeamID) VALUES
    (1,'John Doe',25,'Forward',1),
    (2,'Mike Smith',28,'Midfielder',2),
    (3,'David Johnson',22,'Defender',3),
    (4,'Chris Adams',24,'Goalkeeper',4),
    (5,'James Taylor',27,'Forward',5),
    (6,'Samuel Green',29,'Midfielder',1),
    (7,'Adam Scott',26,'Defender',2),
    (8,'Ryan White',23,'Goalkeeper',3),
    (9,'Lucas Brown',25,'Forward',4),
    (10,'Eric Roberts',24,'Midfielder',5),
    (11,'Daniel Martin',28,'Defender',1),
    (12,'Kevin Lee',22,'Goalkeeper',2),
    (13,'Joshua Harris',26,'Forward',3),
    (14,'Robert Young',27,'Midfielder',4),
    (15,'Patrick Nelson',23,'Defender',5),
    (16,'Stephen Carter',29,'Goalkeeper',1),
    (17,'Jonathan Walker',24,'Forward',2),
    (18,'Alexander King',25,'Midfielder',3),
    (19,'Brandon Turner',26,'Defender',4),
    (20,'Benjamin Clark',27,'Goalkeeper',5);

INSERT Coaches (CoachID,CoachName,ExperienceYears,TeamID) VALUES
    (1,'Robert Brown',15,1),
    (2,'James Wilson',10,2),
    (3,'Edward Miller',8,3),
    (4,'Thomas Anderson',12,4),
    (5,'William Thompson',20,5);

INSERT Matches (MatchID,HomeTeamID,AwayTeamID,MatchDate,Stadium,HomeScore,AwayScore) VALUES
    (1,1,2,'2025-03-15','Lions Arena',2,1),
    (2,3,1,'2025-04-10','Sharks Park',0,3),
    (3,2,4,'2025-05-05','Eagles Stadium',1,1),
    (4,5,3,'2025-06-20','Wolves Stadium',3,2),
    (5,4,1,'2025-07-18','Tigers Den',1,2),
    (6,3,5,'2025-08-12','Sharks Park',0,1),
    (7,2,1,'2025-09-30','Eagles Stadium',2,2),
    (8,4,5,'2025-10-25','Tigers Den',3,3),
    (9,5,2,'2025-11-11','Wolves Stadium',1,0),
    (10,1,3,'2025-12-05','Lions Arena',2,1);

INSERT Statistics (StatID,PlayerID,MatchID,Goals,Assists,YellowCards,RedCards) VALUES
    (1,1,1,2,1,0,0),
    (2,2,2,1,0,1,0),
    (3,3,3,0,0,0,1),
    (4,4,4,1,2,0,0),
    (5,5,5,3,1,1,0),
    (6,6,6,0,0,0,0),
    (7,7,7,1,0,0,0),
    (8,8,8,0,2,1,0),
    (9,9,9,2,1,0,1),
    (10,10,10,0,0,0,0),
    (11,11,1,1,0,0,0),
    (12,12,2,2,1,1,0),
    (13,13,3,0,0,0,0),
    (14,14,4,1,1,0,1),
    (15,15,5,2,0,1,0),
    (16,16,6,0,1,0,0),
    (17,17,7,1,0,0,1),
    (18,18,8,1,1,0,0),
    (19,19,9,0,0,1,0),
    (20,20,10,1,0,0,0);

-- SELECT Players.PlayerID, Players.PlayerName, Players.Age, Players.Position, Teams.TeamID
-- FROM Players
-- JOIN Teams ON Teams.TeamID=Players.TeamID
-- WHERE Players.Age > 25
-- ORDER BY Players.PlayerID;

SELECT Teams.TeamID, Teams.TeamName, Teams.City, Teams.FoundedYear, Teams.Stadium
FROM Teams
WHERE Teams.FoundedYear >2000