-- eskuel:system=sqlite
-- eskuel:systemMinVersion=3.37.0

CREATE TABLE coach (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

CREATE TABLE player (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    jersey_number INTEGER NOT NULL,
    plays_for INTEGER NOT NULL,
    FOREIGN KEY (plays_for) REFERENCES team(id)
);

CREATE TABLE team (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    year_founded INTEGER NOT NULL,
    coach_id INTEGER NOT NULL,
    home_stadium_id INTEGER NOT NULL,
    FOREIGN KEY (coach_id) REFERENCES coach(id),
    FOREIGN KEY (home_stadium_id) REFERENCES stadium(id)
);

CREATE TABLE tournament (
    id INTEGER PRIMARY KEY,
    title TEXT,
    date DATE NOT NULL,
    stadium_id INTEGER NOT NULL,
    FOREIGN KEY (stadium_id) REFERENCES stadium(id)
);

CREATE TABLE stadium (
    id INTEGER PRIMARY KEY,
    name TEXT,
    address TEXT NOT NULL
);

CREATE TABLE participates_in (
    team_id INTEGER NOT NULL,
    tournament_id TEXT NOT NULL,
    placement INTEGER,
    PRIMARY KEY (team_id, tournament_id),
    FOREIGN KEY (team_id) REFERENCES team(id),
    FOREIGN KEY (tournament_id) REFERENCES tournament(id)
);


-- Coaches
INSERT INTO coach (id, first_name, last_name) VALUES 
(1, 'Trixi', 'Train'),
(2, 'Wim', 'Winner'),
(3, 'Willi', 'Werfer');

-- Stadiums
INSERT INTO stadium (id, name, address) VALUES 
(5, 'Frisbee Arena at Oberteich', 'Schönberg, Mecklenburg-Western Pomerania'),
(12, 'Discadium', 'Gadebusch, Mecklenburg-Western Pomerania'),
(14, 'Euroimmun Arena', 'Dassow, Mecklenburg-Western Pomerania');

-- Teams
INSERT INTO team (id, name, year_founded, coach_id, home_stadium_id) VALUES 
(24, 'Schönberg Frisbee Club', 1999, 1, 5),
(25, '1st Gadebusch Frisbee Club', 1980, 2, 12),
(27, 'Dassow Disc Destroyers', 1985, 3, 14);

-- Players
INSERT INTO player (id, first_name, last_name, date_of_birth, plays_for, jersey_number) VALUES 
(1, 'Florian', 'Flyman', '2000-05-15', 27, 10),
(2, 'Frieda', 'Flip', '1998-11-23', 24, 7),
(3, 'Lars', 'Lasso', '2002-02-10', 24, 5);

-- Tournaments
INSERT INTO tournament (id, title, date, stadium_id) VALUES 
(2, 'Ultimate Schönberg Cup 2024', '2024-05-25', 5),
(3, 'NWM Open 2024', '2024-07-10', 14);

-- Participation
INSERT INTO participates_in (team_id, tournament_id, placement) VALUES 
(24, 2, 1),
(25, 2, 3),
(27, 2, 1),
(24, 3, 2),
(25, 3, 1);