-- CineBook Datenbank Schema + Testdaten
-- Erstellt: 2026-03-05

CREATE DATABASE IF NOT EXISTS CineBook;
USE CineBook;

-- Tabelle: Benutzer / Rollen
CREATE TABLE IF NOT EXISTS Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    Role ENUM('Admin', 'User') NOT NULL DEFAULT 'User',
    Email VARCHAR(100),
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle: Filme
CREATE TABLE IF NOT EXISTS Movies (
    MovieID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Genre VARCHAR(50),
    DurationMinutes INT NOT NULL,
    Description TEXT,
    Rating DECIMAL(3,1) DEFAULT 0.0,
    PosterUrl VARCHAR(500),
    IsActive TINYINT(1) DEFAULT 1,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle: Vorführungen (Screenings)
CREATE TABLE IF NOT EXISTS Screenings (
    ScreeningID INT AUTO_INCREMENT PRIMARY KEY,
    MovieID INT NOT NULL,
    ScreeningDate DATE NOT NULL,
    StartTime TIME NOT NULL,
    Hall VARCHAR(20) NOT NULL DEFAULT 'Saal 1',
    TotalSeats INT NOT NULL DEFAULT 80,
    AvailableSeats INT NOT NULL DEFAULT 80,
    PricePerSeat DECIMAL(6,2) NOT NULL DEFAULT 12.00,
    FOREIGN KEY (MovieID) REFERENCES Movies(MovieID) ON DELETE CASCADE
);

-- Tabelle: Sitzplätze
CREATE TABLE IF NOT EXISTS Seats (
    SeatID INT AUTO_INCREMENT PRIMARY KEY,
    ScreeningID INT NOT NULL,
    Row CHAR(1) NOT NULL,
    SeatNumber INT NOT NULL,
    IsBooked TINYINT(1) DEFAULT 0,
    Category ENUM('Standard', 'VIP', 'Loge') DEFAULT 'Standard',
    FOREIGN KEY (ScreeningID) REFERENCES Screenings(ScreeningID) ON DELETE CASCADE,
    UNIQUE KEY unique_seat (ScreeningID, Row, SeatNumber)
);

-- Tabelle: Buchungen
CREATE TABLE IF NOT EXISTS Bookings (
    BookingID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT NOT NULL,
    ScreeningID INT NOT NULL,
    SeatID INT NOT NULL,
    BookingDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    TotalPrice DECIMAL(8,2) NOT NULL,
    Status ENUM('Confirmed', 'Cancelled', 'Pending') DEFAULT 'Confirmed',
    BookingCode VARCHAR(20) UNIQUE,
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (ScreeningID) REFERENCES Screenings(ScreeningID),
    FOREIGN KEY (SeatID) REFERENCES Seats(SeatID)
);

-- ===================== TESTDATEN =====================

-- Admin + Benutzer (Passwort: "admin123" bzw. "user123" als SHA256)
INSERT INTO Users (Username, PasswordHash, Role, Email) VALUES
('admin', '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9', 'Admin', 'admin@cinebook.de'),
('maria', '6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b', 'User', 'maria@example.de'),
('thomas', '6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b', 'User', 'thomas@example.de'),
('julia', '6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b', 'User', 'julia@example.de');

-- Filme
INSERT INTO Movies (Title, Genre, DurationMinutes, Description, Rating) VALUES
('Interstellar', 'Sci-Fi', 169, 'Eine Reise durch Raum und Zeit jenseits unserer Galaxis.', 8.6),
('The Dark Knight', 'Action', 152, 'Batman kämpft gegen den Joker in Gotham City.', 9.0),
('Inception', 'Thriller', 148, 'Ein Dieb schleust sich in Träume ein um Geheimnisse zu stehlen.', 8.8),
('Parasite', 'Drama', 132, 'Eine arme Familie infiltriert ein reiches Haushalt.', 8.5),
('Dune', 'Sci-Fi', 155, 'Ein junger Adliger reist zur gefährlichsten Welt im Universum.', 8.0),
('The Matrix', 'Action', 136, 'Ein Hacker entdeckt die erschreckende Wahrheit über seine Welt.', 8.7),
('Oppenheimer', 'Drama', 180, 'Die Geschichte des Vaters der Atombombe.', 8.9),
('Barbie', 'Komödie', 114, 'Barbie und Ken erkunden die Realität.', 6.9);

-- Vorführungen
INSERT INTO Screenings (MovieID, ScreeningDate, StartTime, Hall, TotalSeats, AvailableSeats, PricePerSeat) VALUES
(1, '2026-03-06', '18:00:00', 'Saal 1', 80, 75, 12.00),
(1, '2026-03-07', '20:30:00', 'Saal 1', 80, 60, 12.00),
(2, '2026-03-06', '20:00:00', 'Saal 2', 60, 45, 11.50),
(3, '2026-03-08', '17:30:00', 'Saal 1', 80, 80, 12.00),
(4, '2026-03-09', '19:00:00', 'Saal 3', 40, 38, 13.50),
(5, '2026-03-10', '21:00:00', 'Saal 2', 60, 55, 14.00),
(6, '2026-03-06', '15:00:00', 'Saal 1', 80, 70, 10.00),
(7, '2026-03-11', '18:00:00', 'Saal 2', 60, 50, 15.00),
(8, '2026-03-12', '16:00:00', 'Saal 3', 40, 40, 9.50);

-- Sitzplätze für Screening 1 (Saal 1 = 8 Reihen A-H, 10 Sitze)
INSERT INTO Seats (ScreeningID, Row, SeatNumber, IsBooked, Category) VALUES
(1,'A',1,0,'Standard'),(1,'A',2,0,'Standard'),(1,'A',3,0,'Standard'),(1,'A',4,0,'Standard'),(1,'A',5,0,'Standard'),
(1,'B',1,1,'Standard'),(1,'B',2,1,'Standard'),(1,'B',3,0,'Standard'),(1,'B',4,0,'Standard'),(1,'B',5,0,'Standard'),
(1,'C',1,1,'VIP'),(1,'C',2,1,'VIP'),(1,'C',3,1,'VIP'),(1,'C',4,0,'VIP'),(1,'C',5,0,'VIP'),
(1,'D',1,0,'Standard'),(1,'D',2,0,'Standard'),(1,'D',3,0,'Standard'),(1,'D',4,0,'Standard'),(1,'D',5,0,'Standard');

-- Buchungen
INSERT INTO Bookings (UserID, ScreeningID, SeatID, TotalPrice, Status, BookingCode) VALUES
(2, 1, 6, 12.00, 'Confirmed', 'CB-2026-001'),
(2, 1, 7, 12.00, 'Confirmed', 'CB-2026-002'),
(3, 1, 11, 12.00, 'Confirmed', 'CB-2026-003'),
(3, 1, 12, 12.00, 'Confirmed', 'CB-2026-004'),
(4, 1, 13, 12.00, 'Confirmed', 'CB-2026-005');