-- ==========================================================
-- 1. SETUP & CLEANUP
-- ==========================================================
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- Drop tables if they exist to ensure a clean slate
DROP TABLE IF EXISTS buchungen;
DROP TABLE IF EXISTS fluege;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS piloten;
DROP TABLE IF EXISTS flugzeuge;
DROP TABLE IF EXISTS mitarbeiter;

-- ==========================================================
-- 2. TABLE CREATION
-- ==========================================================

-- USERS TABLE
CREATE TABLE users (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Vorname varchar(100) NOT NULL,
  Nachname varchar(100) NOT NULL,
  Email varchar(100) NOT NULL,
  PasswortHash varchar(255) NOT NULL,
  Rolle varchar(50) NOT NULL DEFAULT 'User', -- 'User' or 'Admin'
  Stadt varchar(100) DEFAULT NULL,
  Anrede varchar(20) DEFAULT NULL,
  Geburtsdatum date DEFAULT NULL,
  CreatedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (Id),
  UNIQUE KEY Email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- AIRPLANES TABLE
CREATE TABLE flugzeuge (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Modell varchar(100) NOT NULL,
  Plaetze int(11) NOT NULL,
  Lagerflaeche float NOT NULL,
  Gewicht float NOT NULL,
  Kerosinverbrauch float NOT NULL,
  Stundengeschwindigkeit float NOT NULL,
  Stundenstand float NOT NULL,
  Herstellungsdatum date NOT NULL,
  IstDefekt tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PILOTS TABLE
CREATE TABLE piloten (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Vorname varchar(100) NOT NULL,
  Nachname varchar(100) NOT NULL,
  Flugerfahrung float NOT NULL,
  Groesse float NOT NULL,
  Bewertung float NOT NULL,
  Pilotalter date NOT NULL,
  Gender varchar(10) DEFAULT NULL,
  IstVerfuegbar tinyint(1) NOT NULL DEFAULT 1,
  Sprachen text DEFAULT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FLIGHTS TABLE
CREATE TABLE fluege (
  Id int(11) NOT NULL AUTO_INCREMENT,
  Abflugort varchar(100) NOT NULL,
  Zielort varchar(100) NOT NULL,
  Abflugdatum datetime NOT NULL,
  Ankunftsdatum datetime NOT NULL,
  Flugnummer varchar(20) NOT NULL,
  Preis decimal(10,2) NOT NULL,
  Created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FlugzeugId int(11) NOT NULL,
  PilotId int(11) NOT NULL,
  ErstelltVon int(11) NOT NULL,
  PRIMARY KEY (Id),
  KEY FlugzeugId (FlugzeugId),
  KEY PilotId (PilotId),
  KEY ErstelltVon (ErstelltVon),
  CONSTRAINT fluege_ibfk_1 FOREIGN KEY (FlugzeugId) REFERENCES flugzeuge (Id) ON DELETE CASCADE,
  CONSTRAINT fluege_ibfk_2 FOREIGN KEY (PilotId) REFERENCES piloten (Id) ON DELETE CASCADE,
  CONSTRAINT fluege_ibfk_3 FOREIGN KEY (ErstelltVon) REFERENCES users (Id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- BOOKINGS TABLE
CREATE TABLE buchungen (
  Id int(11) NOT NULL AUTO_INCREMENT,
  UserId int(11) NOT NULL,
  FlugId int(11) NOT NULL,
  BuchungsDatum datetime DEFAULT CURRENT_TIMESTAMP,
  Status varchar(50) DEFAULT 'Bestätigt',
  PRIMARY KEY (Id),
  KEY UserId (UserId),
  KEY FlugId (FlugId),
  CONSTRAINT buchungen_ibfk_1 FOREIGN KEY (UserId) REFERENCES users (Id) ON DELETE CASCADE,
  CONSTRAINT buchungen_ibfk_2 FOREIGN KEY (FlugId) REFERENCES fluege (Id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==========================================================
-- 3. DATA INJECTION
-- ==========================================================

-- A. ADMIN ACCOUNT (ID 1)
-- Credentials: admin@skyteam.com / admin
-- Hash generated via BCrypt for "admin"
INSERT INTO users (Id, Vorname, Nachname, Email, PasswortHash, Rolle, Stadt, Anrede, CreatedAt) VALUES
(1, 'System', 'Admin', 'admin@skyteam.com', '$2a$11$s/l.wH4/vG.9TjF3.uz.CO4.d.z.y.t.x.w.v.u.s.r.q.p.o', 'Admin', 'Headquarters', 'Herr', NOW());

-- B. PASSENGERS (Users)
INSERT INTO users (Vorname, Nachname, Email, PasswortHash, Rolle, Stadt, Anrede, CreatedAt) VALUES
('Lisa', 'Schmidt', 'lisa@test.com', '$2a$11$dummyhash', 'User', 'Hamburg', 'Frau', NOW()),
('Tony', 'Stark', 'ironman@avengers.com', '$2a$11$dummyhash', 'User', 'Malibu', 'Herr', NOW()),
('Peter', 'Parker', 'spidey@queens.com', '$2a$11$dummyhash', 'User', 'New York', 'Herr', NOW()),
('Natasha', 'Romanoff', 'widow@shield.com', '$2a$11$dummyhash', 'User', 'Budapest', 'Frau', NOW()),
('Bruce', 'Wayne', 'batman@gotham.com', '$2a$11$dummyhash', 'User', 'Gotham', 'Herr', NOW()),
('Clark', 'Kent', 'superman@daily.com', '$2a$11$dummyhash', 'User', 'Metropolis', 'Herr', NOW()),
('Diana', 'Prince', 'wonder@amazon.com', '$2a$11$dummyhash', 'User', 'Themyscira', 'Frau', NOW()),
('Han', 'Solo', 'han@falcon.com', '$2a$11$dummyhash', 'User', 'Space', 'Herr', NOW()),
('Luke', 'Skywalker', 'luke@jedi.com', '$2a$11$dummyhash', 'User', 'Tatooine', 'Herr', NOW()),
('Leia', 'Organa', 'leia@rebel.com', '$2a$11$dummyhash', 'User', 'Alderaan', 'Frau', NOW());

-- C. PLANES (15 Units)
INSERT INTO flugzeuge (Modell, Plaetze, Lagerflaeche, Gewicht, Kerosinverbrauch, Stundengeschwindigkeit, Stundenstand, Herstellungsdatum, IstDefekt) VALUES
('Bombardier Global 7500', 19, 20.5, 23000, 1100, 950, 450.5, '2021-03-15', 0),
('Cessna Citation X', 12, 10.0, 16000, 950, 970, 1200.0, '2018-06-20', 0),
('Gulfstream G650ER', 18, 18.5, 25000, 1200, 960, 800.2, '2020-01-10', 0),
('Embraer Praetor 600', 12, 12.0, 14000, 850, 890, 300.0, '2022-11-05', 0),
('Boeing 737-800', 189, 45.0, 41000, 2400, 840, 15000.5, '2015-08-12', 0),
('Airbus A320neo', 180, 42.0, 42000, 2200, 840, 5000.0, '2019-04-22', 0),
('Dassault Falcon 8X', 16, 15.0, 18000, 1050, 920, 650.0, '2021-09-30', 0),
('Bombardier Challenger 350', 10, 8.5, 11000, 900, 870, 2100.0, '2017-02-14', 0),
('HondaJet Elite', 6, 4.0, 4800, 450, 780, 150.0, '2023-01-01', 0),
('Pilatus PC-24', 10, 6.0, 8000, 600, 815, 900.0, '2019-12-12', 0),
('Boeing 787 Dreamliner', 290, 120.0, 115000, 4800, 903, 8000.0, '2016-07-04', 1),
('Airbus A350-900', 325, 130.0, 135000, 5000, 910, 4500.0, '2018-10-20', 0),
('Embraer Phenom 300', 9, 5.0, 8000, 550, 830, 2200.5, '2017-05-15', 0),
('Learjet 75 Liberty', 8, 4.5, 7500, 600, 860, 1800.0, '2018-08-01', 0),
('Beechcraft King Air 350', 11, 6.0, 6800, 400, 580, 5400.0, '2012-03-30', 0);

-- D. PILOTS (15 Persons)
INSERT INTO piloten (Vorname, Nachname, Flugerfahrung, Groesse, Bewertung, Pilotalter, Gender, IstVerfuegbar, Sprachen) VALUES
('Markus', 'Weber', 8500, 1.82, 4.8, '1980-05-15', 'M', 1, 'Deutsch, Englisch'),
('Julia', 'Müller', 4200, 1.70, 4.9, '1992-11-02', 'F', 1, 'Deutsch, Englisch, Französisch'),
('James', 'Smith', 12000, 1.78, 5.0, '1975-03-22', 'M', 1, 'Englisch, Spanisch'),
('Sophie', 'Dubois', 3500, 1.68, 4.5, '1995-07-14', 'F', 1, 'Französisch, Englisch'),
('Alessandro', 'Rossi', 6000, 1.85, 4.7, '1988-09-09', 'M', 1, 'Italienisch, Englisch, Deutsch'),
('Yuki', 'Tanaka', 5500, 1.72, 4.8, '1990-01-30', 'M', 1, 'Japanisch, Englisch'),
('Sarah', 'Connor', 9800, 1.75, 5.0, '1982-08-12', 'F', 0, 'Englisch, Deutsch'),
('Thomas', 'Schneider', 1500, 1.80, 4.2, '1998-04-05', 'M', 1, 'Deutsch, Englisch'),
('Elena', 'Popova', 7200, 1.69, 4.6, '1985-12-25', 'F', 1, 'Russisch, Englisch, Deutsch'),
('Carlos', 'Mendez', 11000, 1.76, 4.9, '1978-06-18', 'M', 1, 'Spanisch, Portugiesisch, Englisch'),
('Emma', 'Wilson', 2900, 1.65, 4.4, '1996-02-14', 'F', 1, 'Englisch'),
('Lukas', 'Hofer', 4800, 1.88, 4.7, '1991-10-31', 'M', 1, 'Deutsch, Italienisch'),
('Anna', 'Kovalenko', 6500, 1.73, 4.8, '1987-03-08', 'F', 1, 'Ukrainisch, Englisch, Polnisch'),
('David', 'Brown', 13500, 1.81, 5.0, '1970-11-20', 'M', 0, 'Englisch, Französisch'),
('Maria', 'Garcia', 5100, 1.67, 4.6, '1993-05-05', 'F', 1, 'Spanisch, Englisch');

-- E. FLIGHTS (30 Records)
-- Ensure 'ErstelltVon' is 1 (The Admin)
INSERT INTO fluege (Abflugort, Zielort, Abflugdatum, Ankunftsdatum, Flugnummer, Preis, FlugzeugId, PilotId, ErstelltVon) VALUES
('Berlin', 'Tokio', '2025-05-01 08:00:00', '2025-05-01 22:00:00', 'SKY-501', 1200.00, 1, 1, 1),
('München', 'Dubai', '2025-05-02 14:00:00', '2025-05-02 23:00:00', 'SKY-502', 850.50, 2, 2, 1),
('Frankfurt', 'New York', '2025-06-10 10:00:00', '2025-06-10 18:00:00', 'SKY-503', 600.00, 3, 3, 1),
('Hamburg', 'Mallorca', '2025-06-12 06:00:00', '2025-06-12 09:00:00', 'SKY-504', 150.00, 4, 4, 1),
('London', 'Sydney', '2025-07-01 22:00:00', '2025-07-03 06:00:00', 'SKY-505', 2100.00, 5, 5, 1),
('Paris', 'Berlin', '2025-07-05 09:00:00', '2025-07-05 10:30:00', 'SKY-506', 120.00, 6, 6, 1),
('Madrid', 'Rom', '2025-07-10 11:00:00', '2025-07-10 13:30:00', 'SKY-507', 99.90, 7, 7, 1),
('Lissabon', 'Berlin', '2025-08-01 15:00:00', '2025-08-01 19:00:00', 'SKY-508', 230.00, 8, 8, 1),
('Wien', 'Zürich', '2025-08-05 08:30:00', '2025-08-05 09:45:00', 'SKY-509', 180.00, 9, 9, 1),
('Amsterdam', 'Kapstadt', '2025-09-01 20:00:00', '2025-09-02 08:00:00', 'SKY-510', 950.00, 10, 10, 1),
('Berlin', 'Istanbul', '2025-09-15 12:00:00', '2025-09-15 16:00:00', 'SKY-511', 300.00, 11, 11, 1),
('Dubai', 'Singapur', '2025-10-01 02:00:00', '2025-10-01 10:00:00', 'SKY-512', 780.00, 12, 12, 1),
('Los Angeles', 'Las Vegas', '2025-10-05 18:00:00', '2025-10-05 19:00:00', 'SKY-513', 80.00, 13, 13, 1),
('Miami', 'Cancun', '2025-11-01 10:00:00', '2025-11-01 12:00:00', 'SKY-514', 250.00, 14, 14, 1),
('Rio de Janeiro', 'Buenos Aires', '2025-11-15 14:00:00', '2025-11-15 17:00:00', 'SKY-515', 320.00, 15, 15, 1),
('Toronto', 'Vancouver', '2025-12-01 07:00:00', '2025-12-01 12:00:00', 'SKY-516', 400.00, 1, 2, 1),
('Paderborn', 'München', '2025-12-05 09:00:00', '2025-12-05 10:00:00', 'SKY-517', 150.00, 2, 3, 1),
('Dortmund', 'Kattowitz', '2025-12-10 16:00:00', '2025-12-10 18:00:00', 'SKY-518', 60.00, 3, 4, 1),
('Köln', 'London', '2026-01-01 10:00:00', '2026-01-01 11:30:00', 'SKY-519', 110.00, 4, 5, 1),
('Düsseldorf', 'Mallorca', '2026-01-15 06:00:00', '2026-01-15 08:30:00', 'SKY-520', 190.00, 5, 6, 1),
('Stuttgart', 'Berlin', '2026-02-01 08:00:00', '2026-02-01 09:15:00', 'SKY-521', 140.00, 6, 7, 1),
('Hannover', 'Paris', '2026-02-14 14:00:00', '2026-02-14 16:00:00', 'SKY-522', 210.00, 7, 8, 1),
('Nürnberg', 'Antalya', '2026-03-01 11:00:00', '2026-03-01 15:00:00', 'SKY-523', 250.00, 8, 9, 1),
('Leipzig', 'Wien', '2026-03-15 09:00:00', '2026-03-15 10:30:00', 'SKY-524', 130.00, 9, 10, 1),
('Bremen', 'Zürich', '2026-04-01 13:00:00', '2026-04-01 14:30:00', 'SKY-525', 175.00, 10, 11, 1),
('Berlin', 'Bangkok', '2026-04-20 22:00:00', '2026-04-21 14:00:00', 'SKY-526', 900.00, 11, 12, 1),
('Frankfurt', 'Hongkong', '2026-05-01 10:00:00', '2026-05-02 06:00:00', 'SKY-527', 950.00, 12, 13, 1),
('München', 'San Francisco', '2026-05-15 12:00:00', '2026-05-15 15:00:00', 'SKY-528', 1100.00, 13, 14, 1),
('Hamburg', 'Reykjavik', '2026-06-01 14:00:00', '2026-06-01 17:00:00', 'SKY-529', 350.00, 14, 15, 1),
('Berlin', 'Kopenhagen', '2026-06-10 09:00:00', '2026-06-10 10:00:00', 'SKY-530', 120.00, 15, 1, 1);

-- F. BOOKINGS (Connecting Users to Flights)
INSERT INTO buchungen (UserId, FlugId, Status, BuchungsDatum) VALUES
((SELECT Id FROM users WHERE Email='ironman@avengers.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-501'), 'Bestätigt', NOW()),
((SELECT Id FROM users WHERE Email='ironman@avengers.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-528'), 'Bestätigt', NOW()),
((SELECT Id FROM users WHERE Email='spidey@queens.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-513'), 'Bestätigt', NOW()),
((SELECT Id FROM users WHERE Email='batman@gotham.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-527'), 'Bestätigt', NOW()),
((SELECT Id FROM users WHERE Email='batman@gotham.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-502'), 'Storniert', NOW()),
((SELECT Id FROM users WHERE Email='lisa@test.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-504'), 'Bestätigt', NOW()),
((SELECT Id FROM users WHERE Email='han@falcon.com'), (SELECT Id FROM fluege WHERE Flugnummer='SKY-529'), 'Bestätigt', NOW());

-- ==========================================================
-- FINALIZATION
-- ==========================================================
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;