create_tables_23511190.sql

-- ============================================================
-- Cricket Database Schema
-- Creating all tables in correct order to satisfy FK constraints
-- ============================================================

-- tables with no dependencies first

CREATE TABLE Tournament (
    tournament_id   INT             AUTO_INCREMENT,
    name            VARCHAR(100)    NOT NULL,
    year            YEAR            NOT NULL,
    format          ENUM('T20', 'ODI', 'Test') NOT NULL,
    organising_body VARCHAR(100)    NULL,
    start_date      DATE            NOT NULL,
    end_date        DATE            NULL,
    PRIMARY KEY (tournament_id)
);

CREATE TABLE Stadium (
    stadium_id  INT             AUTO_INCREMENT,
    name        VARCHAR(100)    NOT NULL,
    city        VARCHAR(100)    NOT NULL,
    country     VARCHAR(100)    NOT NULL,
    capacity    INT             NOT NULL,
    PRIMARY KEY (stadium_id)
);

-- player created before Team due to Team.captain_id FK referencing Player
CREATE TABLE Player (
    player_id       INT             AUTO_INCREMENT,
    name            VARCHAR(100)    NOT NULL,
    dob             DATE            NOT NULL,
    nationality     VARCHAR(100)    NOT NULL,
    batting_style   ENUM('Right-hand bat', 'Left-hand bat') NOT NULL,
    bowling_style   VARCHAR(50)     NULL,
    player_role     ENUM('Batter', 'Bowler', 'All-rounder', 'Wicket-keeper') NOT NULL,
    PRIMARY KEY (player_id)
);

-- team depends on Player (captain_id FK)
CREATE TABLE Team (
    team_id     INT             AUTO_INCREMENT,
    team_name   VARCHAR(100)    NOT NULL,
    country     VARCHAR(100)    NOT NULL,
    coach_name  VARCHAR(100)    NULL,
    captain_id  INT             NULL,
    PRIMARY KEY (team_id),
    FOREIGN KEY (captain_id) REFERENCES Player(player_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE Umpire (
    umpire_id           INT             AUTO_INCREMENT,
    name                VARCHAR(100)    NOT NULL,
    nationality         VARCHAR(100)    NOT NULL,
    certification_level ENUM('International', 'Domestic', 'Associate') NOT NULL,
    PRIMARY KEY (umpire_id)
);

-- now create tables that depend on the above
-- NOTE -> match is a reserved keyword, hence use backticks when ever referencing 
CREATE TABLE `Match` (
    match_id        INT             AUTO_INCREMENT,
    match_date      DATE            NOT NULL,
    match_type      ENUM('T20', 'ODI', 'Test') NOT NULL,
    result_summary  VARCHAR(255)    NULL,
    stadium_id      INT             NOT NULL,
    tournament_id   INT             NOT NULL,
    PRIMARY KEY (match_id),
    FOREIGN KEY (stadium_id)    REFERENCES Stadium(stadium_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (tournament_id) REFERENCES Tournament(tournament_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- weak entity Innings -> depends on Match and Team

CREATE TABLE Innings (
    match_id        INT         NOT NULL,
    innings_number  TINYINT     NOT NULL,
    total_runs      INT         NOT NULL DEFAULT 0,
    total_wickets   TINYINT     NOT NULL DEFAULT 0,
    total_overs     DECIMAL(4,1) NOT NULL DEFAULT 0,
    total_fours     INT         NOT NULL DEFAULT 0,
    total_sixes     INT         NOT NULL DEFAULT 0,
    team_id         INT         NOT NULL,
    PRIMARY KEY (match_id, innings_number),
    CHECK (innings_number BETWEEN 1 AND 4),
    CHECK (total_wickets  BETWEEN 0 AND 10),
    FOREIGN KEY (match_id) REFERENCES `Match`(match_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (team_id)  REFERENCES Team(team_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- create all M:N realtionship tables 

CREATE TABLE Participates_In (
    team_id     INT                             NOT NULL,
    match_id    INT                             NOT NULL,
    team_role   ENUM('Home', 'Away', 'Neutral') NOT NULL,
    PRIMARY KEY (team_id, match_id),
    FOREIGN KEY (team_id)  REFERENCES Team(team_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (match_id) REFERENCES `Match`(match_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE Member_Of (
    player_id       INT     NOT NULL,
    team_id         INT     NOT NULL,
    jersey_number   TINYINT NULL,
    date_joined     DATE    NULL,
    PRIMARY KEY (player_id, team_id),
    FOREIGN KEY (player_id) REFERENCES Player(player_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (team_id)   REFERENCES Team(team_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE Officiated_By (
    match_id    INT                                     NOT NULL,
    umpire_id   INT                                     NOT NULL,
    umpire_role ENUM('On-field', 'Third', 'Fourth')     NOT NULL,
    PRIMARY KEY (match_id, umpire_id),
    FOREIGN KEY (match_id)  REFERENCES `Match`(match_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (umpire_id) REFERENCES Umpire(umpire_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE Wins (
    match_id    INT                                         NOT NULL,
    team_id     INT                                         NOT NULL,
    win_margin  INT                                         NOT NULL,
    win_type    ENUM('Runs', 'Wickets', 'Super Over', 'DLS') NOT NULL,
    PRIMARY KEY (match_id),
    FOREIGN KEY (match_id) REFERENCES `Match`(match_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (team_id)  REFERENCES Team(team_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);