-- ===================================
-- CASUS 9: SPORTSCHOOL DATABASE SETUP
-- ===================================
-- Dit SQL script maakt de volledige database aan voor het sportschool systeem
-- met alle tabellen, kolommen, datatypes en foreign key constraints.
--
-- Database naam: sportschool_systeem
-- ===================================

-- Database aanmaken (indien nodig, verwijder als deze al bestaat)
-- DROP DATABASE IF EXISTS sportschool_systeem;
-- CREATE DATABASE sportschool_systeem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE sportschool_systeem;

-- ===================================
-- TABEL 1: Lid
-- ===================================
-- Bevat alle leden van de sportschool
CREATE TABLE Lid (
    lidnummer INT AUTO_INCREMENT PRIMARY KEY,
    naam VARCHAR(100) NOT NULL,
    abonnementstype VARCHAR(50) NOT NULL,
    vervaldatum DATE NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 2: Instructeur
-- ===================================
-- Bevat alle instructeurs die lessen geven
CREATE TABLE Instructeur (
    instructeurcode VARCHAR(10) PRIMARY KEY,
    instructeurnaam VARCHAR(100) NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 3: Les
-- ===================================
-- Bevat alle groepslessen die aangeboden worden
CREATE TABLE Les (
    lesID INT AUTO_INCREMENT PRIMARY KEY,
    lesnaam VARCHAR(50) NOT NULL,
    dag VARCHAR(10) NOT NULL,
    tijd TIME NOT NULL,
    instructeurcode VARCHAR(10) NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (instructeurcode) REFERENCES Instructeur(instructeurcode) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 4: Apparaat
-- ===================================
-- Bevat alle fitnessapparaten in de sportschool
CREATE TABLE Apparaat (
    apparaatID INT AUTO_INCREMENT PRIMARY KEY,
    apparaatnaam VARCHAR(100) NOT NULL,
    apparaattype VARCHAR(50) NOT NULL,
    aanschafdatum DATE NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 5: Trainingsschema
-- ===================================
-- Bevat persoonlijke trainingsschema's voor leden
CREATE TABLE Trainingsschema (
    schemaID INT AUTO_INCREMENT PRIMARY KEY,
    schemanaam VARCHAR(100) NOT NULL,
    startdatum DATE NOT NULL,
    trainingsdoel TEXT,
    lidnummer INT NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lidnummer) REFERENCES Lid(lidnummer) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 6: Oefening
-- ===================================
-- Bevat alle beschikbare oefeningen (herbruikbaar in meerdere schema's)
CREATE TABLE Oefening (
    oefeningID INT AUTO_INCREMENT PRIMARY KEY,
    oefeningnaam VARCHAR(100) NOT NULL,
    beschrijving TEXT,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 7: Onderhoud
-- ===================================
-- Registreert onderhoudsmomenten van apparaten
CREATE TABLE Onderhoud (
    onderhoudID INT AUTO_INCREMENT PRIMARY KEY,
    onderhoudsdatum DATE NOT NULL,
    beschrijving TEXT,
    apparaatID INT NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (apparaatID) REFERENCES Apparaat(apparaatID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 8: Inschrijving (Koppeltabel)
-- ===================================
-- Koppelt leden aan lessen (veel-op-veel relatie)
-- Registreert ook aanwezigheid
CREATE TABLE Inschrijving (
    lesID INT NOT NULL,
    lidnummer INT NOT NULL,
    aanwezig TINYINT(1) DEFAULT 0,
    inschrijfdatum DATE NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (lesID, lidnummer),
    FOREIGN KEY (lesID) REFERENCES Les(lesID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (lidnummer) REFERENCES Lid(lidnummer) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- TABEL 9: Schema_oefening (Koppeltabel)
-- ===================================
-- Koppelt oefeningen aan trainingsschema's (veel-op-veel relatie)
-- Bevat specifieke informatie per oefening (sets, herhalingen, apparaat)
CREATE TABLE Schema_oefening (
    schemaID INT NOT NULL,
    oefeningID INT NOT NULL,
    volgnummer INT NOT NULL,
    apparaatID INT DEFAULT NULL,
    sets INT NOT NULL,
    herhalingen INT NOT NULL,
    oefening_doel VARCHAR(255),
    deleted_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (schemaID, oefeningID, volgnummer),
    FOREIGN KEY (schemaID) REFERENCES Trainingsschema(schemaID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (oefeningID) REFERENCES Oefening(oefeningID) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (apparaatID) REFERENCES Apparaat(apparaatID) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===================================
-- INDICES VOOR BETERE PERFORMANCE
-- ===================================

-- Index op foreign keys voor snellere joins
CREATE INDEX idx_les_instructeur ON Les(instructeurcode);
CREATE INDEX idx_trainingsschema_lid ON Trainingsschema(lidnummer);
CREATE INDEX idx_onderhoud_apparaat ON Onderhoud(apparaatID);
CREATE INDEX idx_schema_oefening_apparaat ON Schema_oefening(apparaatID);

-- Index op datum velden voor snellere datum queries
CREATE INDEX idx_lid_vervaldatum ON Lid(vervaldatum);
CREATE INDEX idx_trainingsschema_startdatum ON Trainingsschema(startdatum);
CREATE INDEX idx_onderhoud_datum ON Onderhoud(onderhoudsdatum);
CREATE INDEX idx_inschrijving_datum ON Inschrijving(inschrijfdatum);

-- Index op deleted_at voor soft delete queries
CREATE INDEX idx_lid_deleted ON Lid(deleted_at);
CREATE INDEX idx_instructeur_deleted ON Instructeur(deleted_at);
CREATE INDEX idx_les_deleted ON Les(deleted_at);
CREATE INDEX idx_apparaat_deleted ON Apparaat(deleted_at);
CREATE INDEX idx_trainingsschema_deleted ON Trainingsschema(deleted_at);
CREATE INDEX idx_oefening_deleted ON Oefening(deleted_at);
CREATE INDEX idx_onderhoud_deleted ON Onderhoud(deleted_at);
CREATE INDEX idx_inschrijving_deleted ON Inschrijving(deleted_at);
CREATE INDEX idx_schema_oefening_deleted ON Schema_oefening(deleted_at);

-- ===================================
-- EINDE VAN DATABASE SETUP
-- ===================================

-- Overzicht van alle tabellen tonen
SHOW TABLES;
