CREATE TABLE organizer (
    id_organizer SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    logo VARCHAR(1024)
);

CREATE TABLE judge (
    id_judge SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    patronymic VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL,
    region VARCHAR(100),
    federation VARCHAR(100)
);

CREATE TABLE sportsman (
    id_sportsman SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    patronymic VARCHAR(100),
    gender VARCHAR(10) NOT NULL,
    birthday DATE,
    region VARCHAR(100),
    club VARCHAR(100),
    federation VARCHAR(100),
    category VARCHAR(100)
);

CREATE TABLE division (
    id_division SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    bow_type VARCHAR(100) NOT NULL,
    distance SMALLINT NOT NULL,
    msmk SMALLINT NOT NULL,
    ms SMALLINT NOT NULL,
    kms SMALLINT NOT NULL,
    category_1 SMALLINT NOT NULL,
    category_2 SMALLINT NOT NULL,
    category_3 SMALLINT NOT NULL
);

CREATE TABLE competition (
    id_competition SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    address VARCHAR(100),
    logo VARCHAR(1024),
    start_date DATE,
    end_date DATE,
    id_organizer INTEGER,
    FOREIGN KEY (id_organizer) REFERENCES organizer(id_organizer)
);

CREATE TABLE division_in_competition (
    id_division_in_competition SERIAL PRIMARY KEY,
    id_division INTEGER NOT NULL,
    id_competition INTEGER NOT NULL,
    FOREIGN KEY (id_division) REFERENCES division(id_division),
    FOREIGN KEY (id_competition) REFERENCES competition(id_competition)
);

CREATE TABLE protocol (
    id_protocol SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    file VARCHAR(1024) NOT NULL,
    id_competition INTEGER NOT NULL,
    FOREIGN KEY (id_competition) REFERENCES competition(id_competition)
);

CREATE TABLE participant_request (
    id_participant_request SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    patronymic VARCHAR(100),
    gender VARCHAR(10) NOT NULL,
    birthday DATE,
    region VARCHAR(100),
    club VARCHAR(100),
    federation VARCHAR(100),
    category VARCHAR(100),
    is_registered BOOLEAN NOT NULL,
    id_competition INTEGER NOT NULL,
    id_sportsman INTEGER NOT NULL,
    id_division INTEGER NOT NULL,
    FOREIGN KEY (id_competition) REFERENCES competition(id_competition),
    FOREIGN KEY (id_sportsman) REFERENCES sportsman(id_sportsman),
    FOREIGN KEY (id_division) REFERENCES division(id_division)
);

CREATE TABLE competition_stage (
    id_competition_stage SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    count_of_series SMALLINT NOT NULL
);

CREATE TABLE result_in_stage (
    id_result_in_stage SERIAL PRIMARY KEY,
    score SMALLINT NOT NULL,
    shield_index SMALLINT NOT NULL,
    target_index CHAR(1) NOT NULL,
    id_participant_request INTEGER NOT NULL,
    id_division INTEGER NOT NULL,
    id_competition_stage INTEGER NOT NULL,
    FOREIGN KEY (id_participant_request) REFERENCES participant_request(id_participant_request),
    FOREIGN KEY (id_division) REFERENCES division(id_division),
    FOREIGN KEY (id_competition_stage) REFERENCES competition_stage(id_competition_stage)
);

CREATE TABLE judge_request (
    id_judge_request SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    patronymic VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL,
    region VARCHAR(100),
    federation VARCHAR(100),
    is_registered BOOLEAN NOT NULL,
    id_competition INTEGER NOT NULL,
    id_judge INTEGER NOT NULL,
    FOREIGN KEY (id_competition) REFERENCES competition(id_competition),
    FOREIGN KEY (id_judge) REFERENCES judge(id_judge)
);

CREATE TABLE shot_series (
    id_shot_series SERIAL PRIMARY KEY,
    score INTEGER NOT NULL,
    photo VARCHAR(1024) NOT NULL,
    id_participant_request INTEGER NOT NULL,
    id_result_in_stage INTEGER NOT NULL,
    id_judge_request INTEGER NOT NULL,
    FOREIGN KEY (id_participant_request) REFERENCES participant_request(id_participant_request),
    FOREIGN KEY (id_result_in_stage) REFERENCES result_in_stage(id_result_in_stage),
    FOREIGN KEY (id_judge_request) REFERENCES judge_request(id_judge_request)
);