CREATE TABLE trainer (
    id INTEGER PRIMARY KEY,
    vorname TEXT NOT NULL,
    nachname TEXT NOT NULL
);

CREATE TABLE spieler (
    id INTEGER PRIMARY KEY,
    vorname TEXT NOT NULL,
    nachname TEXT NOT NULL,
    gebdatum DATE NOT NULL,
    trikotnr INTEGER NOT NULL,
    spielt_fuer INTEGER NOT NULL,
    FOREIGN KEY (spielt_fuer) REFERENCES team(id)
);

CREATE TABLE team (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    gruendungsjahr INTEGER NOT NULL,
    trainer_id INTEGER NOT NULL,
    heimat_stadion_id INTEGER NOT NULL,
    FOREIGN KEY (trainer_id) REFERENCES trainer(id),
    FOREIGN KEY (heimat_stadion_id) REFERENCES stadion(id)
);

CREATE TABLE turnier (
    id INTEGER PRIMARY KEY,
    titel TEXT,
    datum DATE NOT NULL,
    stadion_id INTEGER NOT NULL,
    FOREIGN KEY (stadion_id) REFERENCES stadion(id)
);

CREATE TABLE stadion (
    id INTEGER PRIMARY KEY,
    name TEXT,
    adresse TEXT NOT NULL
);

CREATE TABLE nimmt_teil_an (
    team_id INTEGER NOT NULL,
    turnier_id TEXT NOT NULL,
    platzierung INTEGER,
    PRIMARY KEY (team_id, turnier_id),
    FOREIGN KEY (team_id) REFERENCES team(id),
    FOREIGN KEY (turnier_id) REFERENCES turnier(id)
);


-- Trainer
INSERT INTO trainer (id, vorname, nachname) VALUES 
(1, 'Trixi', 'Train'),
(2, 'Wim', 'Winner'),
(3, 'Willi', 'Werfer');

-- stadion
INSERT INTO stadion (id, name, adresse) VALUES 
(5, 'Frisbee-Arena am Oberteich', 'Schönberg, MV'),
(12, 'Discadium', 'Gadebusch, MV'),
(14, 'Euroimmun-Arena', 'Dassow, MV');

-- Team
INSERT INTO team (id, name, gruendungsjahr, trainer_id, heimat_stadion_id) VALUES 
(24, 'Frisbee-Club Schönberg', 1999, 1, 5),
(25, '1. FC Gadebusch', 1980, 2, 12),
(27, 'Disc Destroyers Dassow', 1985, 3, 14);

-- Spieler
INSERT INTO spieler (id, vorname, nachname, gebdatum, spielt_fuer, trikotnr) 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);

-- Turnier
INSERT INTO turnier (id, titel, datum, stadion_id) VALUES 
(2, 'Ultimate Schönberg-Cup 2024', '2024-05-25', 5),
(3, 'NWM Open 2024', '2024-07-10', 14);

-- Teilnahme
INSERT INTO nimmt_teil_an (team_id, turnier_id, platzierung) VALUES 
(24, 2, 1),
(25, 2, 3),
(27, 2, 1),
(24, 3, 2),
(25, 3, 1);