-- 1. Supprimer utilisateur existant si nécessaire
DROP USER IF EXISTS 'onodkedisplay'@'localhost';

-- 2. Créer base de données
DROP DATABASE IF EXISTS displayScreens;
CREATE DATABASE displayScreens;
USE displayScreens;

-- 3. Tables
CREATE TABLE urlsToDisplay (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    last_update DATETIME DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL
);

CREATE TABLE raspberries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hostname VARCHAR(50) UNIQUE NOT NULL,
    shop VARCHAR(50) NOT NULL,
    url_id INT NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    alert_sent BOOLEAN DEFAULT 0,
    open_hour INT DEFAULT 9,
    close_hour INT DEFAULT 18,
    sunday_enabled BOOLEAN DEFAULT 0,
    active BOOLEAN DEFAULT 1,
    screen_type VARCHAR(50) DEFAULT 'generic',
    local_ip VARCHAR(50) DEFAULT NULL
    FOREIGN KEY (url_id) REFERENCES urlsToDisplay(id)
);

-- 4. Procédures stockées
DELIMITER $$

CREATE PROCEDURE create_url(IN p_url VARCHAR(255), IN p_description VARCHAR(255))
BEGIN
    INSERT INTO urlsToDisplay (url, description) VALUES (p_url, p_description);
END $$

CREATE PROCEDURE get_urls()
BEGIN
    SELECT * FROM urlsToDisplay ORDER BY id DESC;
END $$

CREATE PROCEDURE update_url(IN p_id INT, IN p_url VARCHAR(255), IN p_description VARCHAR(255))
BEGIN
    UPDATE urlsToDisplay SET url = p_url, description = p_description WHERE id = p_id;
END $$

CREATE PROCEDURE delete_url(IN p_id INT)
BEGIN
    DELETE FROM urlsToDisplay WHERE id = p_id;
END $$


DELIMITER $$
CREATE PROCEDURE create_raspberry(
  IN p_hostname VARCHAR(50),
  IN p_shop VARCHAR(50),
  IN p_description VARCHAR(255),
  IN p_url_id INT,
  IN p_screen_type VARCHAR(50),
  IN p_active BOOLEAN
)
BEGIN
  INSERT INTO raspberries (hostname, shop, description, url_id, screen_type, active)
  VALUES (p_hostname, p_shop, p_description, p_url_id, p_screen_type, p_active);
END$$
DELIMITER ;

CREATE PROCEDURE get_raspberry_schedule(IN host VARCHAR(50))
BEGIN
    SELECT open_hour, close_hour, sunday_enabled, local_ip
    FROM raspberries
    WHERE hostname = host;
END $$

-- ✅ Procedure: get_raspberry_info
DELIMITER $$
CREATE PROCEDURE get_raspberry_info()
BEGIN
  SELECT r.*, u.url
  FROM raspberries r
  JOIN urlsToDisplay u ON r.url_id = u.id;
END$$
DELIMITER ;

-- ✅ Procedure: get_raspberry_by_hostname: ONLY ONE RASPBERRY
DELIMITER $$
CREATE PROCEDURE get_raspberry_by_hostname(IN input_hostname VARCHAR(50))
BEGIN
  SELECT r.*, u.url
  FROM raspberries r
  JOIN urlsToDisplay u ON r.url_id = u.id
  WHERE r.hostname = input_hostname;
END$$
DELIMITER ;


CREATE PROCEDURE update_raspberry_schedule(
    IN host VARCHAR(50),
    IN openH INT,
    IN closeH INT,
    IN sunday BOOL,
    IN ip VARCHAR(50)
)
BEGIN
    UPDATE raspberries
    SET open_hour = openH,
        close_hour = closeH,
        sunday_enabled = sunday,
        local_ip = ip
    WHERE hostname = host;
END $$

DELIMITER $$
CREATE PROCEDURE update_raspberry_config(
  IN p_hostname VARCHAR(50),
  IN p_open_hour INT,
  IN p_close_hour INT,
  IN p_sunday_enabled BOOLEAN,
  IN p_local_ip VARCHAR(50)
)
BEGIN
  UPDATE raspberries
  SET
    open_hour = p_open_hour,
    close_hour = p_close_hour,
    sunday_enabled = p_sunday_enabled,
    local_ip = p_local_ip
  WHERE hostname = p_hostname;
END$$
DELIMITER ;


CREATE PROCEDURE get_raspberry_list_by_shop(IN shop_name VARCHAR(50))
BEGIN
    SELECT hostname FROM raspberries WHERE shop = shop_name;
END $$

CREATE PROCEDURE get_all_raspberry_hostnames()
BEGIN
    SELECT hostname FROM raspberries;
END $$

DELIMITER ;

-- 5. Création utilisateur MySQL
CREATE USER 'onodkedisplay'@'localhost' IDENTIFIED BY 'Pw&o&d@sd450508';

-- 6. Droits uniquement sur cette base
GRANT ALL PRIVILEGES ON displayScreens.* TO 'onodkedisplay'@'localhost';

-- 7. Appliquer
FLUSH PRIVILEGES;
