CREATE TABLE user
(
    id            varchar(32) PRIMARY KEY,
    mail_address  varchar NOT NULL UNIQUE,
    name          varchar(30) NOT NULL,
    forename      varchar(30) NOT NULL,
    password_hash varchar
);

CREATE TABLE team
(
    id        int,
    name      varchar(30),
    club_name varchar(30),
    PRIMARY KEY (id)
);

CREATE TABLE tactic_group
(
    tactic_id int,
    team_id   int,
    PRIMARY KEY (tactic_id, team_id),
    FOREIGN KEY (tactic_id) REFERENCES tactic (id),
    FOREIGN KEY (team_id) REFERENCES team (id)
);

CREATE TABLE member
(
    team_id  int,
    user_id  int,
    is_admin boolean NOT NULL,
    PRIMARY KEY (team_id, user_id),
    FOREIGN KEY (team_id) REFERENCES team (id),
    FOREIGN KEY (user_id) REFERENCES user (id)
);

CREATE TABLE tactic
(
    id        int PRIMARY KEY,
    name      varchar(30) NOT NULL,
    owner_id  int,
    file_path varchar     NOT NULL,
    FOREIGN KEY (owner_id) REFERENCES user (id)
);