CREATE DATABASE IF NOT EXISTS banco_siqueira;
USE banco_siqueira;

CREATE TABLE IF NOT EXISTS macsiqueira (
    idmacsiqueira VARCHAR(17) NOT NULL PRIMARY KEY,
    nome      VARCHAR(45),
    contador  INTEGER DEFAULT 0,
    ativo     BOOL    DEFAULT TRUE,
    limite    INTEGER
);

CREATE TABLE IF NOT EXISTS coletorsiqueira (
    idcoletorsiqueira INTEGER     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nome          VARCHAR(45),
    contador      INTEGER     DEFAULT 0,
    ativo         BOOL        DEFAULT TRUE,
    limite        INTEGER
);

CREATE TABLE IF NOT EXISTS sensorsiqueira (
    idsensorsiqueira INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nome         VARCHAR(45),
    ativo        BOOL         DEFAULT TRUE,
    limite       INTEGER,
    contador     INTEGER      DEFAULT 0,
    unidade      VARCHAR(10),
    sigla        VARCHAR(3)
);

CREATE TABLE IF NOT EXISTS leiturasiqueira (
    idleiturasiqueira             INTEGER     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sensorsiqueira_idsensorsiqueira   INTEGER     NOT NULL,
    macsiqueira_idmacsiqueira         VARCHAR(17) NOT NULL,
    coletorsiqueira_idcoletorsiqueira INTEGER     NOT NULL,
    dataleitura               DATE,
    horaleitura               TIME,
    valor                     INTEGER,

    CONSTRAINT leitura_FKIndex1
        FOREIGN KEY (macsiqueira_idmacsiqueira)
        REFERENCES macsiqueira (idmacsiqueira),

    CONSTRAINT leitura_FKIndex2
        FOREIGN KEY (sensorsiqueira_idsensorsiqueira)
        REFERENCES sensorsiqueira (idsensorsiqueira),

    CONSTRAINT leitura_FKIndex3
        FOREIGN KEY (coletorsiqueira_idcoletorsiqueira)
        REFERENCES coletorsiqueira (idcoletorsiqueira)
);

DELIMITER $$

CREATE TRIGGER trg_before_insert_leiturasiqueira
BEFORE INSERT ON leiturasiqueira
FOR EACH ROW
BEGIN
    DECLARE v_ativo_mac     BOOL;
    DECLARE v_ativo_sensor  BOOL;
    DECLARE v_ativo_coletor BOOL;

    SELECT ativo INTO v_ativo_mac
        FROM macsiqueira     WHERE idmacsiqueira     = NEW.macsiqueira_idmacsiqueira;
    SELECT ativo INTO v_ativo_sensor
        FROM sensorsiqueira  WHERE idsensorsiqueira  = NEW.sensorsiqueira_idsensorsiqueira;
    SELECT ativo INTO v_ativo_coletor
        FROM coletorsiqueira WHERE idcoletorsiqueira = NEW.coletorsiqueira_idcoletorsiqueira;

    IF v_ativo_mac = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'macsiqueira inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_sensor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'sensorsiqueira inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_coletor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'coletorsiqueira inativo. Insercao bloqueada.';
    END IF;
END$$

CREATE TRIGGER trg_after_insert_leiturasiqueira
AFTER INSERT ON leiturasiqueira
FOR EACH ROW
BEGIN
    UPDATE macsiqueira
        SET contador = contador + 1
        WHERE idmacsiqueira = NEW.macsiqueira_idmacsiqueira;

    UPDATE sensorsiqueira
        SET contador = contador + 1
        WHERE idsensorsiqueira = NEW.sensorsiqueira_idsensorsiqueira;

    UPDATE coletorsiqueira
        SET contador = contador + 1
        WHERE idcoletorsiqueira = NEW.coletorsiqueira_idcoletorsiqueira;
END$$

CREATE TRIGGER trg_after_delete_leiturasiqueira
AFTER DELETE ON leiturasiqueira
FOR EACH ROW
BEGIN
    UPDATE macsiqueira
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idmacsiqueira = OLD.macsiqueira_idmacsiqueira;

    UPDATE sensorsiqueira
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idsensorsiqueira = OLD.sensorsiqueira_idsensorsiqueira;

    UPDATE coletorsiqueira
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idcoletorsiqueira = OLD.coletorsiqueira_idcoletorsiqueira;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_inserir_macsiqueira(
    IN p_idmacsiqueira VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    INSERT INTO macsiqueira (idmacsiqueira, nome, contador, ativo, limite)
    VALUES (p_idmacsiqueira, p_nome, 0, p_ativo, p_limite);
END$$

CREATE PROCEDURE sp_editar_macsiqueira(
    IN p_idmacsiqueira VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    UPDATE macsiqueira
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idmacsiqueira = p_idmacsiqueira;
END$$

CREATE PROCEDURE sp_deletar_macsiqueira(
    IN p_idmacsiqueira VARCHAR(17)
)
BEGIN
    DELETE FROM macsiqueira WHERE idmacsiqueira = p_idmacsiqueira;
END$$

CREATE PROCEDURE sp_listar_macsiqueira()
BEGIN
    SELECT idmacsiqueira, nome, contador, ativo, limite
    FROM macsiqueira
    ORDER BY nome;
END$$

CREATE PROCEDURE sp_inserir_coletorsiqueira(
    IN p_nome   VARCHAR(45),
    IN p_ativo  BOOL,
    IN p_limite INTEGER
)
BEGIN
    INSERT INTO coletorsiqueira (nome, contador, ativo, limite)
    VALUES (p_nome, 0, p_ativo, p_limite);
END$$

CREATE PROCEDURE sp_editar_coletorsiqueira(
    IN p_idcoletorsiqueira INTEGER,
    IN p_nome          VARCHAR(45),
    IN p_ativo         BOOL,
    IN p_limite        INTEGER
)
BEGIN
    UPDATE coletorsiqueira
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idcoletorsiqueira = p_idcoletorsiqueira;
END$$

CREATE PROCEDURE sp_deletar_coletorsiqueira(
    IN p_idcoletorsiqueira INTEGER
)
BEGIN
    DELETE FROM coletorsiqueira WHERE idcoletorsiqueira = p_idcoletorsiqueira;
END$$

CREATE PROCEDURE sp_listar_coletorsiqueira()
BEGIN
    SELECT idcoletorsiqueira, nome, contador, ativo, limite
    FROM coletorsiqueira
    ORDER BY nome;
END$$

CREATE PROCEDURE sp_inserir_sensorsiqueira(
    IN p_nome    VARCHAR(45),
    IN p_ativo   BOOL,
    IN p_limite  INTEGER,
    IN p_unidade VARCHAR(10),
    IN p_sigla   VARCHAR(3)
)
BEGIN
    INSERT INTO sensorsiqueira (nome, ativo, limite, contador, unidade, sigla)
    VALUES (p_nome, p_ativo, p_limite, 0, p_unidade, p_sigla);
END$$

CREATE PROCEDURE sp_editar_sensorsiqueira(
    IN p_idsensorsiqueira INTEGER,
    IN p_nome         VARCHAR(45),
    IN p_ativo        BOOL,
    IN p_limite       INTEGER,
    IN p_unidade      VARCHAR(10),
    IN p_sigla        VARCHAR(3)
)
BEGIN
    UPDATE sensorsiqueira
    SET nome    = p_nome,
        ativo   = p_ativo,
        limite  = p_limite,
        unidade = p_unidade,
        sigla   = p_sigla
    WHERE idsensorsiqueira = p_idsensorsiqueira;
END$$

CREATE PROCEDURE sp_deletar_sensorsiqueira(
    IN p_idsensorsiqueira INTEGER
)
BEGIN
    DELETE FROM sensorsiqueira WHERE idsensorsiqueira = p_idsensorsiqueira;
END$$

CREATE PROCEDURE sp_listar_sensorsiqueira()
BEGIN
    SELECT idsensorsiqueira, nome, ativo, limite, contador, unidade, sigla
    FROM sensorsiqueira
    ORDER BY nome;
END$$

CREATE PROCEDURE sp_inserir_leiturasiqueira(
    IN p_sensorsiqueira_id  INTEGER,
    IN p_macsiqueira_id     VARCHAR(17),
    IN p_coletorsiqueira_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    INSERT INTO leiturasiqueira
        (sensorsiqueira_idsensorsiqueira, macsiqueira_idmacsiqueira, coletorsiqueira_idcoletorsiqueira,
         dataleitura, horaleitura, valor)
    VALUES
        (p_sensorsiqueira_id, p_macsiqueira_id, p_coletorsiqueira_id,
         p_dataleitura, p_horaleitura, p_valor);
END$$

CREATE PROCEDURE sp_editar_leiturasiqueira(
    IN p_idleiturasiqueira  INTEGER,
    IN p_sensorsiqueira_id  INTEGER,
    IN p_macsiqueira_id     VARCHAR(17),
    IN p_coletorsiqueira_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    UPDATE leiturasiqueira
    SET sensorsiqueira_idsensorsiqueira   = p_sensorsiqueira_id,
        macsiqueira_idmacsiqueira         = p_macsiqueira_id,
        coletorsiqueira_idcoletorsiqueira = p_coletorsiqueira_id,
        dataleitura               = p_dataleitura,
        horaleitura               = p_horaleitura,
        valor                     = p_valor
    WHERE idleiturasiqueira = p_idleiturasiqueira;
END$$

CREATE PROCEDURE sp_deletar_leiturasiqueira(
    IN p_idleiturasiqueira INTEGER
)
BEGIN
    DELETE FROM leiturasiqueira WHERE idleiturasiqueira = p_idleiturasiqueira;
END$$

CREATE PROCEDURE sp_listar_leiturasiqueira()
BEGIN
    SELECT
        l.idleiturasiqueira,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        s.idsensorsiqueira,
        s.nome    AS sensor,
        s.sigla   AS sigla_sensor,
        s.unidade,
        m.idmacsiqueira,
        m.nome    AS nome_mac,
        c.idcoletorsiqueira,
        c.nome    AS coletor
    FROM leiturasiqueira l
    INNER JOIN sensorsiqueira  s ON s.idsensorsiqueira   = l.sensorsiqueira_idsensorsiqueira
    INNER JOIN macsiqueira     m ON m.idmacsiqueira       = l.macsiqueira_idmacsiqueira
    INNER JOIN coletorsiqueira c ON c.idcoletorsiqueira   = l.coletorsiqueira_idcoletorsiqueira
    ORDER BY l.dataleitura DESC, l.horaleitura DESC;
END$$

CREATE PROCEDURE sp_buscar_leiturasiqueira(
    IN p_idleiturasiqueira INTEGER
)
BEGIN
    SELECT
        l.idleiturasiqueira,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        l.sensorsiqueira_idsensorsiqueira,
        l.macsiqueira_idmacsiqueira,
        l.coletorsiqueira_idcoletorsiqueira,
        s.nome  AS sensor,
        m.nome  AS nome_mac,
        c.nome  AS coletor
    FROM leiturasiqueira l
    INNER JOIN sensorsiqueira  s ON s.idsensorsiqueira   = l.sensorsiqueira_idsensorsiqueira
    INNER JOIN macsiqueira     m ON m.idmacsiqueira       = l.macsiqueira_idmacsiqueira
    INNER JOIN coletorsiqueira c ON c.idcoletorsiqueira   = l.coletorsiqueira_idcoletorsiqueira
    WHERE l.idleiturasiqueira = p_idleiturasiqueira;
END$$

CREATE PROCEDURE sp_resetar_contadores()
BEGIN
    UPDATE macsiqueira     SET contador = 0;
    UPDATE sensorsiqueira  SET contador = 0;
    UPDATE coletorsiqueira SET contador = 0;
END$$

DELIMITER ;

CALL sp_inserir_macsiqueira('AA:BB:CC:DD:EE:FF', 'Mac siqueira 01', TRUE, 100);
CALL sp_inserir_macsiqueira('11:22:33:44:55:66', 'Mac siqueira 02', TRUE, 200);
CALL sp_inserir_macsiqueira('FF:EE:DD:CC:BB:AA', 'Mac siqueira 03', TRUE, 150);

CALL sp_inserir_coletorsiqueira('Coletor siqueira 01', TRUE, 50);
CALL sp_inserir_coletorsiqueira('Coletor siqueira 02', TRUE, 80);
CALL sp_inserir_coletorsiqueira('Coletor siqueira 03', TRUE, 60);

CALL sp_inserir_sensorsiqueira('Temperatura',  TRUE, 100, 'Celsius',  'TMP');
CALL sp_inserir_sensorsiqueira('Umidade',      TRUE, 100, 'Percent',  'UMD');
CALL sp_inserir_sensorsiqueira('Luminosidade', TRUE, 100, 'Lux',      'LUX');
CALL sp_inserir_sensorsiqueira('Pressao',      TRUE, 100, 'hPa',      'PRS');

CALL sp_inserir_leiturasiqueira(1, 'AA:BB:CC:DD:EE:FF', 1, '2026-06-10', '08:00:00', 22);
CALL sp_inserir_leiturasiqueira(2, 'AA:BB:CC:DD:EE:FF', 1, '2026-06-10', '08:05:00', 65);
CALL sp_inserir_leiturasiqueira(3, '11:22:33:44:55:66', 2, '2026-06-11', '09:00:00', 400);
CALL sp_inserir_leiturasiqueira(4, 'FF:EE:DD:CC:BB:AA', 3, '2026-06-11', '10:00:00', 1013);
CALL sp_inserir_leiturasiqueira(1, '11:22:33:44:55:66', 2, '2026-06-12', '11:00:00', 27);