task3.sql

CREATE SCHEMA IF NOT EXISTS COCLIKERS;
set search_path = COCLIKERS, public;

DROP TABLE IF EXISTS PLAYERS CASCADE;
CREATE TABLE PLAYERS (
    player_id SERIAL PRIMARY KEY,
    nick VARCHAR(255) NOT NULL UNIQUE ,
    mail VARCHAR(255) NOT NULL UNIQUE,
    pass VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS INTERNSHIPS_TYPE CASCADE;
CREATE TABLE INTERNSHIPS_TYPE (
    int_type SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS INTERNSHIPS CASCADE;
CREATE TABLE INTERNSHIPS (
    int_id SERIAL PRIMARY KEY,
    int_type INTEGER NOT NULL REFERENCES INTERNSHIPS_TYPE(int_type),
    player_id1 INTEGER NOT NULL REFERENCES PLAYERS(player_id),
    player_id2 INTEGER NOT NULL REFERENCES PLAYERS(player_id)
);

DROP TABLE IF EXISTS RESURSES CASCADE;
CREATE TABLE RESURSES (
    res_type SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS WARRIORS CASCADE;
CREATE TABLE WARRIORS (
    warrior_type SERIAL PRIMARY KEY,
    res_type INTEGER NOT NULL REFERENCES RESURSES(res_type),
    name VARCHAR(255) NOT NULL UNIQUE,
    price INTEGER NOT NULL
);

DROP TABLE IF EXISTS BUILDS CASCADE;
CREATE TABLE BUILDS (
    build_type SERIAL PRIMARY KEY,
    res_type INTEGER NOT NULL REFERENCES RESURSES(res_type),
    name VARCHAR(255) NOT NULL UNIQUE,
    price INTEGER NOT NULL
);

DROP TABLE IF EXISTS PLAYERS_X_BUILDS CASCADE;
CREATE TABLE PLAYERS_X_BUILDS (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES PLAYERS(player_id),
    build_type INTEGER NOT NULL REFERENCES BUILDS(build_type),
    quantity INTEGER NOT NULL
);

DROP TABLE IF EXISTS PLAYERS_X_RESURSES CASCADE;
CREATE TABLE PLAYERS_X_RESURSES (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES PLAYERS(player_id),
    res_type INTEGER NOT NULL REFERENCES RESURSES(res_type),
    quantity INTEGER NOT NULL
);

DROP TABLE IF EXISTS PLAYERS_X_WARRIORS CASCADE;
CREATE TABLE PLAYERS_X_WARRIORS (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES PLAYERS(player_id),
    warrior_type INTEGER NOT NULL REFERENCES WARRIORS(warrior_type),
    quantity INTEGER NOT NULL
);

DROP TABLE IF EXISTS INTERNSHIPS_RES CASCADE;
CREATE TABLE INTERNSHIPS_RES (
    id SERIAL PRIMARY KEY,
    int_id INTEGER NOT NULL REFERENCES INTERNSHIPS(int_id),
    res_type INTEGER NOT NULL REFERENCES RESURSES(res_type),
    quantity INTEGER NOT NULL
);

DROP TABLE IF EXISTS BUILDS_HISTORY CASCADE;
CREATE TABLE BUILDS_HISTORY (
    build_type INTEGER PRIMARY KEY REFERENCES BUILDS(build_type),
    res_type INTEGER REFERENCES RESURSES(res_type),
    name_old VARCHAR(255) NOT NULL,
    price_old INTEGER NOT NULL
);

DROP TABLE IF EXISTS WARRIORS_HISTORY CASCADE;
CREATE TABLE WARRIORS_HISTORY (
    warrior_type INTEGER PRIMARY KEY REFERENCES WARRIORS(warrior_type),
    res_type INTEGER REFERENCES RESURSES(res_type),
    name_old VARCHAR(255) NOT NULL,
    price_old INTEGER NOT NULL
);