DROP TABLE IF EXISTS Teams;
DROP TABLE IF EXISTS Organizations;
DROP TABLE IF EXISTS Employers;
DROP TABLE IF EXISTS Employee_cooperation;
DROP TABLE IF EXISTS Jobs;
DROP TABLE IF EXISTS Sponsors;
DROP TABLE IF EXISTS Sponsors_organizations;
DROP TABLE IF EXISTS Games;
DROP TABLE IF EXISTS Regions;
DROP TABLE IF EXISTS Countries;
DROP TABLE IF EXISTS Players;
DROP TABLE IF EXISTS Players_in_teams;
DROP TABLE IF EXISTS Tournaments;
DROP TABLE IF EXISTS Teams_into_tournaments;

CREATE TABLE Games();
CREATE TABLE Regions();
CREATE TABLE Countries();
CREATE TABLE Players();
CREATE TABLE Players_in_teams();
CREATE TABLE Tournaments();
CREATE TABLE Teams_into_tournaments();


CREATE TABLE Teams(
    TEAM_ID SERIAL NOT NULL PRIMARY KEY,
    NAME TEXT NOT NULL,
    ORGANIZATION_ID INTEGER NOT NULL,
    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
    GAME_ID INTEGER NOT NULL,
    FOREIGN KEY (GAME_ID) REFERENCES Games,
    TOTAL_WIN INT,
    REGION_ID INTEGER NOT NULL,
    FOREIGN KEY (REGION_ID) REFERENCES Regions
);

CREATE TABLE Organizations(
    ORGANIZATION_ID SERIAL NOT NULL PRIMARY KEY,
    NAME TEXT NOT NULL,
    DESCRIPTION TEXT
);

CREATE TABLE Employers(
    EMPLOYEE_ID SERIAL NOT NULL PRIMARY KEY,
    FS_NAME TEXT NOT NULL,
    SC_NAME TEXT NOT NULL,
    JOD_ID INTEGER NOT NULL,
    FOREIGN KEY (JOD_ID) REFERENCES Jobs,
    ORGANIZATION_ID INTEGER NOT NULL,
    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
    AGE INT CHECK ( AGE BETWEEN 18 AND 99)
);

CREATE TABLE Employee_cooperation(
    COOPERATION_ID SERIAL NOT NULL PRIMARY KEY,
    EMPLOYEE_ID INTEGER NOT NULL,
    FOREIGN KEY (EMPLOYEE_ID) REFERENCES Employers,
    ORGANIZATION_ID INTEGER NOT NULL,
    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations
);

CREATE TABLE Jobs(
    JOB_ID SERIAL NOT NULL PRIMARY KEY,
    DESCRIPTION TEXT,
    SALARY BIGINT
);

CREATE TABLE Sponsors(
    SPONSOR_ID SERIAL NOT NULL PRIMARY KEY,
    NAME TEXT NOT NULL,
    DESCRIPTION TEXT
);

CREATE TABLE Sponsors_organizations(
    START_DATE DATE NOT NULL PRIMARY KEY,
    SPONSOR_ID INTEGER NOT NULL,
    FOREIGN KEY (SPONSOR_ID) REFERENCES Sponsors,
    ORGANIZATION_ID INTEGER NOT NULL,
    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
    END_DATE DATE NOT NULL PRIMARY KEY,
    BUDGET INTEGER NOT NULL,
);