-- 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'),
(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');

-- 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'),
(18, 'Kurt Bürger Stadium', 'Wismar, Mecklenburg-Western Pomerania'),
(21, 'Tannenberg Sports Ground', 'Grevesmühlen, Mecklenburg-Western Pomerania'),
(23, 'Falkenwiese Sports Ground', 'Lübeck, Schleswig-Holstein'),
(30, 'Klütz Disc Park', 'Klütz, Mecklenburg-Western Pomerania'),
(33, 'Water Tower Stadium', 'Rehna, Mecklenburg-Western Pomerania'),
(37, 'Boltenhagen Beach Arena', 'Boltenhagen, Mecklenburg-Western Pomerania'),
(41, 'Friesen Sports Ground', 'Warin, Mecklenburg-Western Pomerania'),
(46, 'Palmberg Arena', 'Schwerin, Mecklenburg-Western Pomerania'),
(52, 'Ratzeburg Island Stadium', 'Ratzeburg, Schleswig-Holstein');

-- 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),
(28, 'Wismar Wind Catchers', 2004, 4, 18),
(29, 'Grevesmühlen Griffins', 2011, 5, 21),
(30, 'Lübeck High Flyers', 1997, 6, 23),
(31, 'Klütz Edge Runners', 2015, 7, 30),
(32, 'Rehna Red Discs', 2008, 8, 33),
(33, 'Boltenhagen Beach Flyers', 2013, 9, 37),
(34, 'Warin Throwing Wolves', 2006, 10, 41),
(35, 'Schwerin Disc Hunters', 1995, 11, 46),
(36, 'Ratzeburg Raptors', 2002, 12, 52);

-- 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),
(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);

-- 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),
(4, 'Baltic Sea Indoor Open 2024', '2024-11-16', 23),
(5, 'Wismar Winter Throw 2025', '2025-02-08', 18),
(6, 'Baltic Disc Challenge 2025', '2025-04-26', 37),
(7, 'Schönberg Whitsun Cup 2025', '2025-06-07', 5),
(8, 'Schwerin Lakes 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, 'Coastal Cup 2026', '2026-05-30', 37),
(12, 'MV Ultimate Open 2026', '2026-07-18', 41),
(13, 'Autumn Flight 2026', '2026-10-03', 21);

-- 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),
(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);