DROP TABLE IF EXISTS CINE_Registers;
DROP TABLE IF EXISTS CINE_Plage;
DROP TABLE IF EXISTS CINE_Answer;
DROP TABLE IF EXISTS CINE_Person;
DROP TABLE IF EXISTS CINE_Extra;
DROP TABLE IF EXISTS CINE_Film;
DROP TABLE IF EXISTS CINE_Plan;

CREATE TABLE CINE_Plan (
    pk_Plan INT PRIMARY KEY AUTO_INCREMENT,
    fromDate DATE NOT NULL,
    weeks INT NOT NULL,
    isActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE CINE_Film (
    pk_Film INT PRIMARY KEY AUTO_INCREMENT,
    length INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    imagePath VARCHAR(255),
    releaseDate DATE NOT NULL,
    kdmStart DATETIME,
    kdmEnd DATETIME
);

CREATE TABLE CINE_Extra (
    pk_Extra INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    defaultTime INT
);

CREATE TABLE CINE_Person (
    pk_Person INT PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    isAdmin BOOLEAN DEFAULT FALSE,
    isActive BOOLEAN DEFAULT TRUE
);

CREATE TABLE CINE_Answer (
    pk_Answer INT PRIMARY KEY AUTO_INCREMENT,
    label VARCHAR(255) NOT NULL,
    isEnabled BOOLEAN DEFAULT TRUE
);

CREATE TABLE CINE_Plage (
    pk_Plage INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    fromTime TIME NOT NULL,
    toTime TIME NOT NULL,
    extraTime INT,
    fk_Film INT,
    fk_Person_supervises INT,
    fk_Extra INT,
    FOREIGN KEY (fk_Film) REFERENCES CINE_Film(pk_Film),
    FOREIGN KEY (fk_Person_supervises) REFERENCES CINE_Person(pk_Person),
    FOREIGN KEY (fk_Extra) REFERENCES CINE_Extra(pk_Extra)
);

CREATE TABLE CINE_Registers (
    pk_Registers INT PRIMARY KEY AUTO_INCREMENT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    fk_Person INT NOT NULL,
    fk_Plage INT NOT NULL,
    fk_Answer INT,
    FOREIGN KEY (fk_Person) REFERENCES CINE_Person(pk_Person),
    FOREIGN KEY (fk_Plage) REFERENCES CINE_Plage(pk_Plage),
    FOREIGN KEY (fk_Answer) REFERENCES CINE_Answer(pk_Answer)
);
