-- utilizatori
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('SUPER_ADMIN','SUPERVISOR','CHECKER','MAINTENANCE') NOT NULL
);

-- grupuri sanitare
CREATE TABLE toilets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    etaj VARCHAR(10),
    numar VARCHAR(10),
    nume VARCHAR(50),
    tip ENUM('F','B','D') NOT NULL,
    chiuvete INT,
    wc INT,
    pisoare INT,
    status ENUM('NEVERIFICAT','IN_PROGRESS','OK','ISSUES') DEFAULT 'NEVERIFICAT',
    last_check DATETIME NULL
);

-- verificari
CREATE TABLE checks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toilet_id INT,
    user_id INT,
    time DATETIME DEFAULT CURRENT_TIMESTAMP,
    chiuvete TINYINT,
    wc TINYINT,
    pisoare TINYINT,
    hartie TINYINT,
    sapun TINYINT,
    uscator TINYINT,
    podea TINYINT,
    miros TINYINT,
    FOREIGN KEY(toilet_id) REFERENCES toilets(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

-- necesar pentru interventie
CREATE TABLE issues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toilet_id INT,
    check_id INT,
    issue VARCHAR(50),
    time DATETIME DEFAULT CURRENT_TIMESTAMP,
    resolved TINYINT DEFAULT 0,
    FOREIGN KEY(toilet_id) REFERENCES toilets(id),
    FOREIGN KEY(check_id) REFERENCES checks(id)
);

-- asignare grupuri sanitare la useri
CREATE TABLE user_toilets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    toilet_id INT,
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(toilet_id) REFERENCES toilets(id)
);
