creation.sql

use cs3380;

-- Drop tables in reverse dependency order to avoid foreign key constraints errors
IF OBJECT_ID('GOALS', 'U') IS NOT NULL DROP TABLE GOALS;
IF OBJECT_ID('BOOKINGS', 'U') IS NOT NULL DROP TABLE BOOKINGS;
IF OBJECT_ID('SUBSTITUTIONS', 'U') IS NOT NULL DROP TABLE SUBSTITUTIONS;
IF OBJECT_ID('PENALTY_KICKS', 'U') IS NOT NULL DROP TABLE PENALTY_KICKS;
IF OBJECT_ID('PLAYER_APPEARANCES', 'U') IS NOT NULL DROP TABLE PLAYER_APPEARANCES;
IF OBJECT_ID('MANAGER_APPEARANCES', 'U') IS NOT NULL DROP TABLE MANAGER_APPEARANCES;
IF OBJECT_ID('MATCHES', 'U') IS NOT NULL DROP TABLE MATCHES;
IF OBJECT_ID('AWARD_WINNERS', 'U') IS NOT NULL DROP TABLE AWARD_WINNERS;
IF OBJECT_ID('TOURNAMENT_STANDINGS', 'U') IS NOT NULL DROP TABLE TOURNAMENT_STANDINGS;
IF OBJECT_ID('SQUADS', 'U') IS NOT NULL DROP TABLE SQUADS;
IF OBJECT_ID('QUALIFIED_TEAMS', 'U') IS NOT NULL DROP TABLE QUALIFIED_TEAMS;
IF OBJECT_ID('TOURNAMENTS', 'U') IS NOT NULL DROP TABLE TOURNAMENTS;
IF OBJECT_ID('STADIUMS', 'U') IS NOT NULL DROP TABLE STADIUMS;
IF OBJECT_ID('REFEREES', 'U') IS NOT NULL DROP TABLE REFEREES;
IF OBJECT_ID('MANAGERS', 'U') IS NOT NULL DROP TABLE MANAGERS;
IF OBJECT_ID('CITIES', 'U') IS NOT NULL DROP TABLE CITIES;
IF OBJECT_ID('PLAYERS', 'U') IS NOT NULL DROP TABLE PLAYERS;
IF OBJECT_ID('TEAMS', 'U') IS NOT NULL DROP TABLE TEAMS;
IF OBJECT_ID('AWARDS', 'U') IS NOT NULL DROP TABLE AWARDS;
IF OBJECT_ID('PLAYER_POSITIONS', 'U') IS NOT NULL DROP TABLE PLAYER_POSITIONS;
IF OBJECT_ID('CONFEDERATIONS', 'U') IS NOT NULL DROP TABLE CONFEDERATIONS;
IF OBJECT_ID('STAGE_HIERARCHY', 'U') IS NOT NULL DROP TABLE STAGE_HIERARCHY;
-- 22 tables
GO

-- SET NOCOUNT ON is often used to prevent the count of the number of rows affected by a T-SQL statement from being returned as part of the result set.
SET NOCOUNT ON;
GO

-- Data Type Mapping:
-- TEXT/IDs -> VARCHAR(50) for PK/FKs, VARCHAR(MAX) for large fields
-- INTEGER -> INT
-- BOOLEAN -> BIT

-- -----------------------------------------------------------
-- 1. Core Lookup Entities (Base Tables)
-- -----------------------------------------------------------

CREATE TABLE CONFEDERATIONS (
    confederation_id VARCHAR(50) PRIMARY KEY NOT NULL,
    confederation_name VARCHAR(MAX),
    confederation_code VARCHAR(10) UNIQUE,
    confederation_wikipedia VARCHAR(MAX)
);

CREATE TABLE PLAYER_POSITIONS (
    position_code VARCHAR(50) PRIMARY KEY NOT NULL,
    position_name VARCHAR(255) UNIQUE
);

CREATE TABLE AWARDS (
    award_id VARCHAR(50) PRIMARY KEY NOT NULL,
    award_name VARCHAR(255) UNIQUE,
    award_description VARCHAR(MAX),
    year_introduced INT
);

CREATE TABLE STAGE_HIERARCHY (
    stage_name VARCHAR(255) PRIMARY KEY,
    stage_level INT NOT NULL
);
GO

-- -----------------------------------------------------------
-- 2. Main Entities (Depend on Lookups)
-- -----------------------------------------------------------

CREATE TABLE TEAMS (
    team_id VARCHAR(50) PRIMARY KEY NOT NULL,
    team_name VARCHAR(255) UNIQUE,
    team_code VARCHAR(10),
    mens_team BIT,
    womens_team BIT,
    federation_name VARCHAR(255),
    region_name VARCHAR(255),
    mens_team_wikipedia_link VARCHAR(MAX),
    womens_team_wikipedia_link VARCHAR(MAX),
    federation_wikipedia_link VARCHAR(MAX),
    confederation_id VARCHAR(50) NOT NULL,
    FOREIGN KEY (confederation_id) REFERENCES CONFEDERATIONS(confederation_id)
);

CREATE TABLE PLAYERS (
    player_id VARCHAR(50) PRIMARY KEY NOT NULL,
    family_name VARCHAR(255),
    given_name VARCHAR(255),
    birth_date VARCHAR(20), -- TEXT equivalent
    female BIT,
    goal_keeper BIT,
    defender BIT,
    midfielder BIT,
    forward BIT,
    player_wikipedia_link VARCHAR(MAX)
    -- BCNF Fix: Removed count_tournaments and list_tournaments
);

-- CITIES must be created after TEAMS (country_id FK points to TEAMS.team_id)
CREATE TABLE CITIES (
    city_id VARCHAR(50) PRIMARY KEY NOT NULL,
    city_name VARCHAR(255) UNIQUE,
    city_wikipedia_link VARCHAR(MAX),
    -- Per instructions: country_id FK references TEAMS(team_id)
    country_id VARCHAR(50) NOT NULL, 
    FOREIGN KEY (country_id) REFERENCES TEAMS(team_id)
);

CREATE TABLE MANAGERS (
    manager_id VARCHAR(50) PRIMARY KEY NOT NULL,
    family_name VARCHAR(255),
    given_name VARCHAR(255),
    female BIT,
    -- Per instructions: country_id FK references TEAMS(team_id)
    country_id VARCHAR(50), 
    manager_wikipedia_link VARCHAR(MAX),
    FOREIGN KEY (country_id) REFERENCES TEAMS(team_id)
);

CREATE TABLE REFEREES (
    referee_id VARCHAR(50) PRIMARY KEY NOT NULL,
    family_name VARCHAR(255),
    given_name VARCHAR(255),
    female BIT,
    -- Per instructions: country_id FK references TEAMS(team_id)
    country_id VARCHAR(50),
    confederation_id VARCHAR(50),
    referee_wikipedia_link VARCHAR(MAX),
    FOREIGN KEY (country_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (confederation_id) REFERENCES CONFEDERATIONS(confederation_id)
);

CREATE TABLE STADIUMS (
    stadium_id VARCHAR(50) PRIMARY KEY NOT NULL,
    stadium_name VARCHAR(255),
    city_id VARCHAR(50),
    stadium_capacity INT,
    stadium_wikipedia_link VARCHAR(MAX),
    FOREIGN KEY (city_id) REFERENCES CITIES(city_id)
);
GO

-- -----------------------------------------------------------
-- 3. Tournament Structure
-- -----------------------------------------------------------

CREATE TABLE TOURNAMENTS (
    tournament_id VARCHAR(50) PRIMARY KEY NOT NULL,
    tournament_name VARCHAR(255),
    start_date VARCHAR(20), -- TEXT equivalent
    end_date VARCHAR(20),   -- TEXT equivalent
    year INT UNIQUE,        -- BCNF Fix: Year is unique
    host_won BIT,
    count_teams INT,
    group_stage BIT,
    second_group_stage BIT,
    final_round BIT,
    round_of_16 BIT,
    quarter_finals BIT,
    semi_finals BIT,
    third_place_match BIT,
    final BIT,
    host_team_id VARCHAR(50),
    winner_team_id VARCHAR(50),
    FOREIGN KEY (host_team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (winner_team_id) REFERENCES TEAMS(team_id)
    -- BCNF Fix: Removed winner (text)
);

CREATE TABLE QUALIFIED_TEAMS (
    tournament_id VARCHAR(50) NOT NULL,
    team_id VARCHAR(50) NOT NULL,
    performance VARCHAR(255) NOT NULL,
    PRIMARY KEY (tournament_id, team_id),
    FOREIGN KEY (tournament_id) REFERENCES TOURNAMENTS(tournament_id),
    FOREIGN KEY (team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (performance) REFERENCES STAGE_HIERARCHY(stage_name)
);

CREATE TABLE SQUADS (
    tournament_id VARCHAR(50) NOT NULL,
    team_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    shirt_number INT,
    position_code VARCHAR(50),
    PRIMARY KEY (tournament_id, team_id, player_id),
    FOREIGN KEY (tournament_id) REFERENCES TOURNAMENTS(tournament_id),
    FOREIGN KEY (team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id),
    FOREIGN KEY (position_code) REFERENCES PLAYER_POSITIONS(position_code)
);

CREATE TABLE TOURNAMENT_STANDINGS (
    tournament_id VARCHAR(50) NOT NULL,
    team_id VARCHAR(50) NOT NULL,
    position INT,
    PRIMARY KEY (tournament_id, team_id),
    FOREIGN KEY (tournament_id) REFERENCES TOURNAMENTS(tournament_id),
    FOREIGN KEY (team_id) REFERENCES TEAMS(team_id)
);

CREATE TABLE AWARD_WINNERS (
    tournament_id VARCHAR(50) NOT NULL,
    award_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    shared BIT,
    PRIMARY KEY (tournament_id, award_id, player_id),
    FOREIGN KEY (tournament_id) REFERENCES TOURNAMENTS(tournament_id),
    FOREIGN KEY (award_id) REFERENCES AWARDS(award_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id)
);
GO

-- -----------------------------------------------------------
-- 4. Match & Event Transactions
-- -----------------------------------------------------------

CREATE TABLE MATCHES (
    match_id VARCHAR(50) PRIMARY KEY NOT NULL,
    tournament_id VARCHAR(50) NOT NULL,
    match_name VARCHAR(255),
    stage_name VARCHAR(255) NOT NULL,
    group_name VARCHAR(255),
    replayed BIT,
    replay BIT,
    match_date VARCHAR(20), -- TEXT equivalent
    match_time VARCHAR(20), -- TEXT equivalent
    stadium_id VARCHAR(50),
    home_team_id VARCHAR(50),
    away_team_id VARCHAR(50),
    referee_id VARCHAR(50),
    home_team_score INT,
    away_team_score INT,
    extra_time BIT,
    penalty_shootout BIT,
    score_penalties INT,
    home_team_score_penalties INT,
    away_team_score_penalties INT,
    FOREIGN KEY (tournament_id) REFERENCES TOURNAMENTS(tournament_id),
    FOREIGN KEY (stadium_id) REFERENCES STADIUMS(stadium_id),
    FOREIGN KEY (home_team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (away_team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (referee_id) REFERENCES REFEREES(referee_id),
    FOREIGN KEY (stage_name) REFERENCES STAGE_HIERARCHY(stage_name)
    -- BCNF Fix: Removed group_stage and knockout_stage flags
);

CREATE TABLE MANAGER_APPEARANCES (
    match_id VARCHAR(50) NOT NULL,
    team_id VARCHAR(50) NOT NULL,
    manager_id VARCHAR(50) NOT NULL,
    PRIMARY KEY (match_id, team_id, manager_id),
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (team_id) REFERENCES TEAMS(team_id),
    FOREIGN KEY (manager_id) REFERENCES MANAGERS(manager_id)
);

CREATE TABLE PLAYER_APPEARANCES (
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    starter BIT,
    substitute BIT,
    position_code VARCHAR(50), 
    PRIMARY KEY (match_id, player_id),
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id),
    FOREIGN KEY (position_code) REFERENCES PLAYER_POSITIONS(position_code)
);

CREATE TABLE GOALS (
    goal_id VARCHAR(50) PRIMARY KEY NOT NULL,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    minute_regulation INT,
    minute_stoppage INT,
    match_period VARCHAR(255),
    own_goal BIT,
    penalty BIT,
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id)
);

CREATE TABLE BOOKINGS (
    booking_id VARCHAR(50) PRIMARY KEY NOT NULL,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    minute_regulation INT,
    minute_stoppage INT,
    match_period VARCHAR(255),
    yellow_card BIT,
    red_card BIT,
    second_yellow_card BIT,
    sending_off BIT,
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id)
);

CREATE TABLE SUBSTITUTIONS (
    substitution_id VARCHAR(50) PRIMARY KEY NOT NULL,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    going_off BIT,
    coming_on BIT,
    minute_regulation INT,
    minute_stoppage INT,
    match_period VARCHAR(255),
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id)
);

CREATE TABLE PENALTY_KICKS (
    penalty_kick_id VARCHAR(50) PRIMARY KEY NOT NULL,
    match_id VARCHAR(50) NOT NULL,
    player_id VARCHAR(50) NOT NULL,
    converted BIT,
    FOREIGN KEY (match_id) REFERENCES MATCHES(match_id),
    FOREIGN KEY (player_id) REFERENCES PLAYERS(player_id)
);
GO