DROP TABLE IF EXISTS Ticket            CASCADE;
DROP TABLE IF EXISTS Seat              CASCADE;
DROP TABLE IF EXISTS Player_contract   CASCADE;
DROP TABLE IF EXISTS Player_valuation  CASCADE;
DROP TABLE IF EXISTS Transfer          CASCADE;
DROP TABLE IF EXISTS Transfer_type     CASCADE;
DROP TABLE IF EXISTS Sponsor_deal      CASCADE;
DROP TABLE IF EXISTS Sponsor           CASCADE;
DROP TABLE IF EXISTS Admin             CASCADE;
DROP TABLE IF EXISTS Coach_assignment  CASCADE;
DROP TABLE IF EXISTS Coach             CASCADE;
DROP TABLE IF EXISTS Event_attribute   CASCADE;
DROP TABLE IF EXISTS Event             CASCADE;
DROP TABLE IF EXISTS Event_type        CASCADE;
DROP TABLE IF EXISTS Lineup            CASCADE;
DROP TABLE IF EXISTS Player            CASCADE;
DROP TABLE IF EXISTS Match_odds        CASCADE;
DROP TABLE IF EXISTS Referee_match     CASCADE;
DROP TABLE IF EXISTS Match             CASCADE;
DROP TABLE IF EXISTS Stadium           CASCADE;
DROP TABLE IF EXISTS Referee           CASCADE;
DROP TABLE IF EXISTS Team_alias        CASCADE;
DROP TABLE IF EXISTS Team              CASCADE;
DROP TABLE IF EXISTS Season            CASCADE;
DROP TABLE IF EXISTS Division          CASCADE;

CREATE TABLE Division (
    division_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    country varchar(100),
    PRIMARY KEY (division_id)
);

CREATE TABLE Season (
    season_id SERIAL NOT NULL,
    division_id INT NOT NULL,
    season_name varchar(20) NOT NULL,
    PRIMARY KEY (season_id),
    CONSTRAINT uq_season_division_name UNIQUE (division_id, season_name)
);

CREATE TABLE Team (
    team_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    country varchar(100),
    PRIMARY KEY (team_id)
);

CREATE TABLE Team_alias (
    alias_id SERIAL NOT NULL,
    team_id INT NOT NULL,
    alias_name varchar(100) NOT NULL,
    PRIMARY KEY (alias_id),
    CONSTRAINT uq_team_alias UNIQUE (team_id, alias_name)
);

CREATE TABLE Referee (
    referee_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    country varchar(100),
    date_of_birth date,
    PRIMARY KEY (referee_id),
    CONSTRAINT chk_referee_dob_past CHECK (
        date_of_birth IS NULL
        OR date_of_birth <= CURRENT_DATE
    )
);

CREATE TABLE Stadium (
    stadium_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    country varchar(100),
    capacity INT NOT NULL,
    year_built INT,
    PRIMARY KEY (stadium_id),
    CONSTRAINT chk_stadium_capacity CHECK (capacity > 0),
    CONSTRAINT chk_stadium_year_built CHECK (
        year_built IS NULL
        OR year_built BETWEEN 1800
        AND EXTRACT(
            YEAR
            FROM
                CURRENT_DATE
        ) :: INT
    )
);

CREATE TABLE Match (
    match_id SERIAL NOT NULL,
    season_id INT NOT NULL,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    stadium_id INT NOT NULL,
    match_date date NOT NULL,
    PRIMARY KEY (match_id),
    CONSTRAINT chk_match_different_teams CHECK (home_team_id <> away_team_id),
    CONSTRAINT chk_match_date_valid CHECK (
        match_date BETWEEN DATE '1850-01-01'
        AND (CURRENT_DATE + INTERVAL '2 years') :: date
    )
);

CREATE TABLE Referee_match (
    referee_match_id SERIAL NOT NULL,
    referee_id INT NOT NULL,
    match_id INT NOT NULL,
    role varchar(50) NOT NULL,
    PRIMARY KEY (referee_match_id),
    CONSTRAINT uq_referee_match_role UNIQUE (referee_id, match_id, role),
    CONSTRAINT chk_referee_match_role_not_blank CHECK (BTRIM(role) <> '')
);

CREATE TABLE Match_odds (
    odds_id SERIAL NOT NULL,
    match_id INT NOT NULL,
    bookmaker varchar(100) NOT NULL,
    home_win numeric(6, 2) NOT NULL,
    draw numeric(6, 2) NOT NULL,
    away_win numeric(6, 2) NOT NULL,
    recorded_time timestamp NOT NULL,
    PRIMARY KEY (odds_id),
    CONSTRAINT uq_matchodds_unique UNIQUE (match_id, bookmaker, recorded_time),
    CONSTRAINT chk_match_odds_positive CHECK (
        home_win > 0
        AND draw > 0
        AND away_win > 0
    )
);

CREATE TABLE Player (
    player_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    date_of_birth date,
    PRIMARY KEY (player_id),
    CONSTRAINT chk_player_dob_past CHECK (
        date_of_birth IS NULL
        OR date_of_birth <= CURRENT_DATE
    )
);

CREATE TABLE Lineup (
    lineup_id SERIAL NOT NULL,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    shirt_number INT NOT NULL,
    position varchar(50) NOT NULL,
    is_starter bool DEFAULT 'TRUE' NOT NULL,
    PRIMARY KEY (lineup_id),
    CONSTRAINT uq_lineup_player UNIQUE (match_id, player_id),
    CONSTRAINT uq_lineup_shirt UNIQUE (match_id, team_id, shirt_number),
    CONSTRAINT chk_lineup_shirt_number CHECK (shirt_number >= 1),
    CONSTRAINT chk_lineup_position_not_blank CHECK (BTRIM(position) <> '')
);

CREATE TABLE Event_type (
    event_type_id SERIAL NOT NULL,
    type varchar(50) NOT NULL,
    PRIMARY KEY (event_type_id),
    CONSTRAINT uq_event_type UNIQUE (type),
    CONSTRAINT chk_event_type_value CHECK (
        type IN (
            '50/50',
            'Bad Behaviour',
            'Ball Receipt*',
            'Ball Recovery',
            'Block',
            'Camera On',
            'Camera off',
            'Carry',
            'Clearance',
            'Dispossessed',
            'Dribble',
            'Dribbled Past',
            'Duel',
            'Error',
            'Foul Committed',
            'Foul Won',
            'Goal Keeper',
            'Half End',
            'Half Start',
            'Injury Stoppage',
            'Interception',
            'Miscontrol',
            'Offside',
            'Own Goal Against',
            'Own Goal For',
            'Pass',
            'Player Off',
            'Player On',
            'Pressure',
            'Referee Ball-Drop',
            'Shield',
            'Shot',
            'Starting XI',
            'Substitution',
            'Tactical Shift'
        )
    )
);

CREATE TABLE Event (
    event_id SERIAL NOT NULL,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    event_type_id INT NOT NULL,
    minute INT NOT NULL,
    second INT NOT NULL,
    PRIMARY KEY (event_id),
    CONSTRAINT chk_event_minute CHECK (
        minute BETWEEN 0
        AND 130
    ),
    CONSTRAINT chk_event_second CHECK (
        second BETWEEN 0
        AND 59
    )
);

CREATE TABLE Event_attribute (
    attr_id SERIAL NOT NULL,
    event_id INT NOT NULL,
    attribute_name varchar(50) NOT NULL,
    attribute_value text NOT NULL,
    PRIMARY KEY (attr_id),
    CONSTRAINT uq_event_attribute_name UNIQUE (event_id, attribute_name)
);

CREATE TABLE Coach (
    coach_id SERIAL NOT NULL,
    name varchar(100) NOT NULL,
    nationality varchar(100),
    PRIMARY KEY (coach_id)
);

CREATE TABLE Coach_assignment (
    assignment_id SERIAL NOT NULL,
    coach_id INT NOT NULL,
    season_id INT NOT NULL,
    team_id INT NOT NULL,
    assignment_value numeric(15, 2) NOT NULL,
    start_date date NOT NULL,
    end_date date,
    PRIMARY KEY (assignment_id),
    CONSTRAINT chk_coachassign_value CHECK (assignment_value >= 0),
    CONSTRAINT chk_coachassign_dates CHECK (
        end_date IS NULL
        OR end_date > start_date
    )
);

CREATE TABLE Sponsor (
    sponsor_id SERIAL NOT NULL,
    company_name varchar(100) NOT NULL,
    PRIMARY KEY (sponsor_id)
);

CREATE TABLE Admin (
    admin_id SERIAL NOT NULL,
    username varchar(100) NOT NULL,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    PRIMARY KEY (admin_id),
    CONSTRAINT uq_admin_username UNIQUE (username),
    CONSTRAINT uq_admin_email UNIQUE (email),
    CONSTRAINT chk_admin_username_not_blank CHECK (BTRIM(username) <> ''),
    CONSTRAINT chk_admin_email_not_blank CHECK (BTRIM(email) <> ''),
    CONSTRAINT chk_admin_password_not_blank CHECK (BTRIM(password) <> '')
);

CREATE TABLE Sponsor_deal (
    deal_id SERIAL NOT NULL,
    sponsor_id INT NOT NULL,
    team_id INT NOT NULL,
    season_id INT NOT NULL,
    deal_value numeric(15, 2) NOT NULL,
    start_date date NOT NULL,
    end_date date,
    PRIMARY KEY (deal_id),
    CONSTRAINT chk_sponsordeal_value CHECK (deal_value >= 0),
    CONSTRAINT chk_sponsordeal_dates CHECK (
        end_date IS NULL
        OR end_date > start_date
    )
);

CREATE TABLE Transfer_type (
    transfer_type_id SERIAL NOT NULL,
    type varchar(50) NOT NULL,
    PRIMARY KEY (transfer_type_id),
    CONSTRAINT uq_transfer_type UNIQUE (type)
);

CREATE TABLE Transfer (
    transfer_id SERIAL NOT NULL,
    player_id INT NOT NULL,
    from_team_id INT NOT NULL,
    to_team_id INT NOT NULL,
    transfer_date date NOT NULL,
    fee numeric(15, 2) NOT NULL,
    transfer_type_id INT NOT NULL,
    PRIMARY KEY (transfer_id),
    CONSTRAINT chk_transfer_fee CHECK (fee >= 0),
    CONSTRAINT chk_transfer_diff_teams CHECK (from_team_id <> to_team_id)
);

CREATE TABLE Player_valuation (
    valuation_id SERIAL NOT NULL,
    player_id INT NOT NULL,
    season_id INT NOT NULL,
    market_value numeric(15, 2) NOT NULL,
    valuation_date date NOT NULL,
    PRIMARY KEY (valuation_id),
    CONSTRAINT chk_player_valuation_nonnegative CHECK (market_value >= 0)
);

CREATE TABLE Player_contract (
    contract_id SERIAL NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    season_id INT NOT NULL,
    contract_value numeric(15, 2) NOT NULL,
    start_date date NOT NULL,
    end_date date,
    PRIMARY KEY (contract_id),
    CONSTRAINT chk_playercon_value CHECK (contract_value >= 0),
    CONSTRAINT chk_playercon_dates CHECK (
        end_date IS NULL
        OR end_date > start_date
    )
);

CREATE TABLE Seat (
    seat_id SERIAL NOT NULL,
    stadium_id INT NOT NULL,
    row INT NOT NULL,
    number INT NOT NULL,
    PRIMARY KEY (seat_id),
    CONSTRAINT uq_seat_location UNIQUE (stadium_id, row, number),
    CONSTRAINT chk_seat_row_positive CHECK (row > 0),
    CONSTRAINT chk_seat_number_positive CHECK (number > 0)
);

CREATE TABLE Ticket (
    ticket_id SERIAL NOT NULL,
    seat_id INT NOT NULL,
    match_id INT NOT NULL,
    is_scanned bool DEFAULT 'FALSE' NOT NULL,
    price numeric(10, 2) NOT NULL,
    PRIMARY KEY (ticket_id),
    CONSTRAINT uq_ticket_seat_match UNIQUE (seat_id, match_id),
    CONSTRAINT chk_ticket_price CHECK (price >= 0)
);

ALTER TABLE
    Season
ADD
    CONSTRAINT FKSeason510230 FOREIGN KEY (division_id) REFERENCES Division (division_id);

ALTER TABLE
    Team_alias
ADD
    CONSTRAINT FKTeam_alias310783 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Match
ADD
    CONSTRAINT FKMatch844341 FOREIGN KEY (season_id) REFERENCES Season (season_id);

ALTER TABLE
    Match
ADD
    CONSTRAINT FKMatch520640 FOREIGN KEY (home_team_id) REFERENCES Team (team_id);

ALTER TABLE
    Match
ADD
    CONSTRAINT FKMatch958033 FOREIGN KEY (away_team_id) REFERENCES Team (team_id);

ALTER TABLE
    Match
ADD
    CONSTRAINT FKMatch891939 FOREIGN KEY (stadium_id) REFERENCES Stadium (stadium_id);

ALTER TABLE
    Referee_match
ADD
    CONSTRAINT FKReferee_ma282357 FOREIGN KEY (referee_id) REFERENCES Referee (referee_id);

ALTER TABLE
    Referee_match
ADD
    CONSTRAINT FKReferee_ma455430 FOREIGN KEY (match_id) REFERENCES Match (match_id);

ALTER TABLE
    Match_odds
ADD
    CONSTRAINT FKMatch_odds538100 FOREIGN KEY (match_id) REFERENCES Match (match_id);

ALTER TABLE
    Lineup
ADD
    CONSTRAINT FKLineup865480 FOREIGN KEY (match_id) REFERENCES Match (match_id);

ALTER TABLE
    Lineup
ADD
    CONSTRAINT FKLineup810481 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Lineup
ADD
    CONSTRAINT FKLineup157055 FOREIGN KEY (player_id) REFERENCES Player (player_id);

ALTER TABLE
    Event
ADD
    CONSTRAINT FKEvent279784 FOREIGN KEY (match_id) REFERENCES Match (match_id);

ALTER TABLE
    Event
ADD
    CONSTRAINT FKEvent334783 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Event
ADD
    CONSTRAINT FKEvent669270 FOREIGN KEY (player_id) REFERENCES Player (player_id);

ALTER TABLE
    Event
ADD
    CONSTRAINT FKEvent509131 FOREIGN KEY (event_type_id) REFERENCES Event_type (event_type_id);

ALTER TABLE
    Event_attribute
ADD
    CONSTRAINT FKEvent_attr568933 FOREIGN KEY (event_id) REFERENCES Event (event_id);

ALTER TABLE
    Coach_assignment
ADD
    CONSTRAINT FKCoach_assi105153 FOREIGN KEY (coach_id) REFERENCES Coach (coach_id);

ALTER TABLE
    Coach_assignment
ADD
    CONSTRAINT FKCoach_assi162820 FOREIGN KEY (season_id) REFERENCES Season (season_id);

ALTER TABLE
    Coach_assignment
ADD
    CONSTRAINT FKCoach_assi895752 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Sponsor_deal
ADD
    CONSTRAINT FKSponsor_de546503 FOREIGN KEY (sponsor_id) REFERENCES Sponsor (sponsor_id);

ALTER TABLE
    Sponsor_deal
ADD
    CONSTRAINT FKSponsor_de865685 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Sponsor_deal
ADD
    CONSTRAINT FKSponsor_de867246 FOREIGN KEY (season_id) REFERENCES Season (season_id);

ALTER TABLE
    Transfer
ADD
    CONSTRAINT FKTransfer480071 FOREIGN KEY (player_id) REFERENCES Player (player_id);

ALTER TABLE
    Transfer
ADD
    CONSTRAINT FKTransfer883152 FOREIGN KEY (from_team_id) REFERENCES Team (team_id);

ALTER TABLE
    Transfer
ADD
    CONSTRAINT FKTransfer66661 FOREIGN KEY (to_team_id) REFERENCES Team (team_id);

ALTER TABLE
    Transfer
ADD
    CONSTRAINT FKTransferType286310 FOREIGN KEY (transfer_type_id) REFERENCES Transfer_type (transfer_type_id);

ALTER TABLE
    Player_valuation
ADD
    CONSTRAINT FKPlayer_val231632 FOREIGN KEY (player_id) REFERENCES Player (player_id);

ALTER TABLE
    Player_valuation
ADD
    CONSTRAINT FKPlayer_val2972 FOREIGN KEY (season_id) REFERENCES Season (season_id);

ALTER TABLE
    Player_contract
ADD
    CONSTRAINT FKPlayer_con491854 FOREIGN KEY (player_id) REFERENCES Player (player_id);

ALTER TABLE
    Player_contract
ADD
    CONSTRAINT FKPlayer_con524317 FOREIGN KEY (team_id) REFERENCES Team (team_id);

ALTER TABLE
    Player_contract
ADD
    CONSTRAINT FKPlayer_con257250 FOREIGN KEY (season_id) REFERENCES Season (season_id);

ALTER TABLE
    Seat
ADD
    CONSTRAINT FKSeat349212 FOREIGN KEY (stadium_id) REFERENCES Stadium (stadium_id);

ALTER TABLE
    Ticket
ADD
    CONSTRAINT FKTicket721318 FOREIGN KEY (seat_id) REFERENCES Seat (seat_id);

ALTER TABLE
    Ticket
ADD
    CONSTRAINT FKTicket154471 FOREIGN KEY (match_id) REFERENCES Match (match_id);