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

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'),
(4, 'Maja', 'Kreis'),
(5, 'Holger', 'Bender'),
(6, 'Nele', 'Sturm'),
(7, 'Robert', 'Schnell'),
(8, 'Yasmin', 'Adler'),
(9, 'Jan', 'Petersen'),
(10, 'Silke', 'Brandt'),
(11, 'Ole', 'Martens'),
(12, 'Greta', 'König');

-- 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'),
(18, 'Kurt-Bürger-Stadion', 'Wismar, MV'),
(21, 'Sportplatz am Tannenberg', 'Grevesmühlen, MV'),
(23, 'Falkenwiese', 'Lübeck, SH'),
(30, 'Klützer Discpark', 'Klütz, MV'),
(33, 'Stadion am Wasserturm', 'Rehna, MV'),
(37, 'Strandarena Boltenhagen', 'Boltenhagen, MV'),
(41, 'Friesensportplatz', 'Warin, MV'),
(46, 'Palmberg-Arena', 'Schwerin, MV'),
(52, 'Ratzeburger Inselstadion', 'Ratzeburg, SH');

-- 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),
(28, 'Windfänger Wismar', 2004, 4, 18),
(29, 'Grevesmühlen Griffins', 2011, 5, 21),
(30, 'Lübecker Lufthoheit', 1997, 6, 23),
(31, 'Klützer Kantenläufer', 2015, 7, 30),
(32, 'Rehna Red Discs', 2008, 8, 33),
(33, 'Boltenhagener Beach Flyers', 2013, 9, 37),
(34, 'Wariner Wurfwölfe', 2006, 10, 41),
(35, 'Schweriner Scheibenjäger', 1995, 11, 46),
(36, 'Ratzeburg Raptors', 2002, 12, 52);

-- 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),
(4, 'Hanna', 'Haken', '1999-03-18', 24, 1),
(5, 'Joris', 'Jensen', '2001-07-09', 24, 2),
(6, 'Emilia', 'Engel', '2003-01-27', 24, 3),
(7, 'Mehmet', 'Mertens', '1997-09-12', 24, 4),
(8, 'Carlotta', 'Clausen', '2004-06-30', 24, 6),
(9, 'Benno', 'Böttcher', '1996-12-04', 24, 8),
(10, 'Amira', 'Ahrens', '2000-08-21', 24, 9),
(11, 'Tom', 'Tiedemann', '2002-10-16', 24, 10),
(12, 'Paula', 'Pohl', '1995-02-08', 25, 1),
(13, 'Finn', 'Fischer', '1998-04-19', 25, 2),
(14, 'Zoe', 'Zimmermann', '2001-11-02', 25, 3),
(15, 'Oskar', 'Otto', '1994-07-28', 25, 4),
(16, 'Milena', 'Meyer', '2003-05-14', 25, 5),
(17, 'Cem', 'Can', '1999-01-22', 25, 6),
(18, 'Jule', 'Janssen', '2000-09-05', 25, 7),
(19, 'Mats', 'Mohr', '1997-06-17', 25, 8),
(20, 'Alina', 'Albrecht', '2002-12-11', 25, 9),
(21, 'Erik', 'Ehlers', '1996-03-26', 25, 10),
(22, 'Greta', 'Gerber', '2001-04-03', 27, 1),
(23, 'Noah', 'Neumann', '1998-08-29', 27, 2),
(24, 'Leni', 'Lehmann', '2004-02-13', 27, 3),
(25, 'Samir', 'Scholz', '1999-10-07', 27, 4),
(26, 'Ida', 'Ivers', '1997-05-20', 27, 5),
(27, 'Anton', 'Arndt', '2002-07-01', 27, 6),
(28, 'Elif', 'Ebert', '2000-12-24', 27, 7),
(29, 'Karl', 'Kramer', '1995-09-15', 27, 8),
(30, 'Sina', 'Seidel', '2003-03-31', 27, 9),
(31, 'Nora', 'Nissen', '1998-01-06', 28, 1),
(32, 'Leo', 'Lindner', '2001-06-18', 28, 2),
(33, 'Marla', 'Martens', '2004-10-09', 28, 3),
(34, 'Kilian', 'Kock', '1997-02-27', 28, 4),
(35, 'Aylin', 'Andersen', '2000-05-12', 28, 5),
(36, 'Fiete', 'Franke', '1996-11-04', 28, 6),
(37, 'Ronja', 'Reimers', '2002-09-23', 28, 7),
(38, 'Theo', 'Thomsen', '1999-07-16', 28, 8),
(39, 'Daria', 'Döring', '2003-04-08', 28, 9),
(40, 'Hannes', 'Hansen', '1995-12-19', 28, 10),
(41, 'Mina', 'Möller', '2001-02-14', 29, 1),
(42, 'Julius', 'Jäger', '1998-10-25', 29, 2),
(43, 'Nele', 'Nagel', '2003-06-03', 29, 3),
(44, 'Jakob', 'Jessen', '1997-08-11', 29, 4),
(45, 'Esra', 'Evers', '2000-01-30', 29, 5),
(46, 'Niklas', 'Noack', '1996-05-22', 29, 6),
(47, 'Luise', 'Lorenzen', '2002-11-07', 29, 7),
(48, 'David', 'Decker', '1999-03-15', 29, 8),
(49, 'Fenja', 'Falk', '2004-07-27', 29, 9),
(50, 'Moritz', 'Madsen', '1995-09-02', 29, 10),
(51, 'Clara', 'Carstens', '1998-04-12', 30, 1),
(52, 'Henning', 'Hinrichs', '2001-12-01', 30, 2),
(53, 'Maja', 'Marquardt', '2003-02-20', 30, 3),
(54, 'Younes', 'Yilmaz', '1997-06-08', 30, 4),
(55, 'Annika', 'Asmus', '2000-10-17', 30, 5),
(56, 'Jan', 'Jürgens', '1996-01-29', 30, 6),
(57, 'Selma', 'Sommer', '2002-05-06', 30, 7),
(58, 'Piet', 'Petersen', '1999-11-21', 30, 8),
(59, 'Rieke', 'Rabe', '2004-08-13', 30, 9),
(60, 'Tarek', 'Thiel', '1995-03-04', 30, 10),
(61, 'Lina', 'Lübke', '2001-07-28', 31, 1),
(62, 'Mattis', 'Mahnke', '1998-09-09', 31, 2),
(63, 'Svea', 'Schreiber', '2003-01-18', 31, 3),
(64, 'Arne', 'Ackermann', '1997-05-30', 31, 4),
(65, 'Leyla', 'Lenz', '2000-12-06', 31, 5),
(66, 'Ole', 'Otte', '1996-04-23', 31, 6),
(67, 'Frida', 'Friedrich', '2002-08-15', 31, 7),
(68, 'Malte', 'Meier', '1999-10-31', 31, 8),
(69, 'Inga', 'Imhoff', '2004-06-11', 31, 9),
(70, 'Bjarne', 'Brandt', '1995-02-24', 31, 10),
(71, 'Mira', 'Morgenstern', '1998-03-07', 32, 1),
(72, 'Nils', 'Nielsen', '2001-11-19', 32, 2),
(73, 'Helene', 'Heuer', '2003-05-26', 32, 3),
(74, 'Kaan', 'Kaya', '1997-09-14', 32, 4),
(75, 'Johanna', 'Jacobsen', '2000-02-02', 32, 5),
(76, 'Hauke', 'Hartmann', '1996-07-25', 32, 6),
(77, 'Dilara', 'Demir', '2002-12-16', 32, 7),
(78, 'Lennart', 'Lüders', '1999-04-27', 32, 8),
(79, 'Tilda', 'Timm', '2004-10-05', 32, 9),
(80, 'Simon', 'Schwarz', '1995-06-20', 32, 10),
(81, 'Ella', 'Eggers', '2001-01-11', 33, 1),
(82, 'Jasper', 'Johannsen', '1998-05-24', 33, 2),
(83, 'Smilla', 'Stein', '2003-09-02', 33, 3),
(84, 'Can', 'Ceylan', '1997-12-13', 33, 4),
(85, 'Thea', 'Thies', '2000-06-29', 33, 5),
(86, 'Mika', 'Möller', '1996-08-18', 33, 6),
(87, 'Lale', 'Lange', '2002-03-10', 33, 7),
(88, 'Jesper', 'Jacobs', '1999-11-26', 33, 8),
(89, 'Merle', 'Mertens', '2004-04-17', 33, 9),
(90, 'Liam', 'Lück', '1995-10-08', 33, 10),
(91, 'Nina', 'Niemann', '1998-02-21', 34, 1),
(92, 'Rasmus', 'Richter', '2001-06-04', 34, 2),
(93, 'Lotta', 'Lorenz', '2003-10-23', 34, 3),
(94, 'Arvid', 'Albers', '1997-01-15', 34, 4),
(95, 'Zeynep', 'Zander', '2000-05-31', 34, 5),
(96, 'Tjark', 'Tesch', '1996-09-12', 34, 6),
(97, 'Sarah', 'Sander', '2002-07-07', 34, 7),
(98, 'Linus', 'Lang', '1999-12-28', 34, 8),
(99, 'Wiebke', 'Wegner', '2004-03-19', 34, 9),
(100, 'Emil', 'Ernst', '1995-08-01', 34, 10),
(101, 'Mara', 'Maas', '2001-04-25', 35, 1),
(102, 'Frederik', 'Fuchs', '1998-11-13', 35, 2),
(103, 'Jette', 'Jordan', '2003-07-30', 35, 3),
(104, 'Karim', 'Kruse', '1997-03-09', 35, 4),
(105, 'Alma', 'Andresen', '2000-09-20', 35, 5),
(106, 'Jannik', 'Jahn', '1996-12-02', 35, 6),
(107, 'Liv', 'Lehmann', '2002-06-14', 35, 7),
(108, 'Sören', 'Schumann', '1999-01-26', 35, 8),
(109, 'Hedi', 'Hoffmann', '2004-05-08', 35, 9),
(110, 'Till', 'Tönnies', '1995-10-22', 35, 10),
(111, 'Enna', 'Ehmke', '1998-07-17', 36, 1),
(112, 'Jonte', 'Jochimsen', '2001-02-28', 36, 2),
(113, 'Maila', 'Menzel', '2003-12-05', 36, 3),
(114, 'Hamza', 'Hamed', '1997-04-16', 36, 4),
(115, 'Solveig', 'Schulze', '2000-08-03', 36, 5),
(116, 'Thore', 'Theissen', '1996-06-26', 36, 6),
(117, 'Eda', 'Erdem', '2002-10-12', 36, 7),
(118, 'Mats', 'Matthiesen', '1999-05-21', 36, 8),
(119, 'Romy', 'Rohde', '2004-01-07', 36, 9),
(120, 'Felix', 'Förster', '1995-11-18', 36, 10);

-- 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),
(4, 'Ostsee Indoor Open 2024', '2024-11-16', 23),
(5, 'Winterwurf Wismar 2025', '2025-02-08', 18),
(6, 'Baltic Disc Challenge 2025', '2025-04-26', 37),
(7, 'Schönberger Pfingst-Cup 2025', '2025-06-07', 5),
(8, 'Schweriner Seen-Cup 2025', '2025-08-23', 46),
(9, 'NWM Open 2025', '2025-09-13', 14),
(10, 'Lübeck Indoor Masters 2026', '2026-01-24', 23),
(11, 'Küsten-Cup 2026', '2026-05-30', 37),
(12, 'MV Ultimate Open 2026', '2026-07-18', 41),
(13, 'Herbst-Flight 2026', '2026-10-03', 21);

-- 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),
(27, 3, 3),
(28, 3, 4),
(29, 3, 5),
(30, 3, 6),
(24, 4, 6),
(25, 4, 8),
(27, 4, 5),
(28, 4, 2),
(29, 4, 4),
(30, 4, 1),
(32, 4, 7),
(35, 4, 3),
(24, 5, 3),
(25, 5, 6),
(27, 5, 8),
(28, 5, 1),
(29, 5, 4),
(31, 5, 7),
(33, 5, 5),
(35, 5, 2),
(24, 6, 5),
(27, 6, 4),
(28, 6, 1),
(29, 6, 7),
(30, 6, 2),
(31, 6, 10),
(32, 6, 6),
(33, 6, 3),
(35, 6, 8),
(36, 6, 9),
(24, 7, 1),
(25, 7, 8),
(27, 7, 4),
(28, 7, 3),
(29, 7, 7),
(30, 7, 2),
(31, 7, 11),
(32, 7, 5),
(33, 7, 9),
(34, 7, 12),
(35, 7, 6),
(36, 7, 10),
(24, 8, 6),
(25, 8, 9),
(27, 8, 8),
(28, 8, 2),
(29, 8, 7),
(30, 8, 4),
(32, 8, 5),
(33, 8, 10),
(35, 8, 1),
(36, 8, 3),
(24, 9, 4),
(25, 9, 11),
(27, 9, 7),
(28, 9, 1),
(29, 9, 5),
(30, 9, 3),
(31, 9, 9),
(32, 9, 8),
(33, 9, 6),
(34, 9, 12),
(35, 9, 2),
(36, 9, 10),
(25, 10, 8),
(28, 10, 5),
(29, 10, 7),
(30, 10, 1),
(31, 10, 4),
(32, 10, 3),
(35, 10, 2),
(36, 10, 6),
(24, 11, 6),
(25, 11, 12),
(27, 11, 9),
(28, 11, 2),
(29, 11, 7),
(30, 11, 3),
(31, 11, 8),
(32, 11, 4),
(33, 11, 1),
(34, 11, 11),
(35, 11, 5),
(36, 11, 10),
(24, 12, 5),
(25, 12, 10),
(27, 12, 8),
(28, 12, 4),
(29, 12, 6),
(30, 12, 3),
(31, 12, 9),
(32, 12, 7),
(33, 12, 2),
(34, 12, 12),
(35, 12, 1),
(36, 12, 11),
(24, 13, NULL),
(27, 13, NULL),
(28, 13, NULL),
(29, 13, NULL),
(30, 13, NULL),
(32, 13, NULL),
(35, 13, NULL),
(36, 13, NULL);