-- ============================================================
--  BANCO DE DADOS - LAYOUT mateus
--  Instituto Federal de Santa Catarina - EMI
--  Tópicos Especiais - Prova III - 50%
-- ============================================================

CREATE DATABASE IF NOT EXISTS banco_mateus;
USE banco_mateus;

-- ============================================================
-- TABELAS
-- ============================================================

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

CREATE TABLE IF NOT EXISTS coletormateus (
    idcoletormateus 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 sensormateus (
    idsensormateus 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 leituramateus (
    idleituramateus             INTEGER     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sensormateus_idsensormateus   INTEGER     NOT NULL,
    macmateus_idmacmateus         VARCHAR(17) NOT NULL,
    coletormateus_idcoletormateus INTEGER     NOT NULL,
    dataleitura               DATE,
    horaleitura               TIME,
    valor                     INTEGER,

    CONSTRAINT leitura_FKIndex1
        FOREIGN KEY (macmateus_idmacmateus)
        REFERENCES macmateus (idmacmateus),

    CONSTRAINT leitura_FKIndex2
        FOREIGN KEY (sensormateus_idsensormateus)
        REFERENCES sensormateus (idsensormateus),

    CONSTRAINT leitura_FKIndex3
        FOREIGN KEY (coletormateus_idcoletormateus)
        REFERENCES coletormateus (idcoletormateus)
);

-- ============================================================
-- TRIGGERS
-- ============================================================

DELIMITER $$

-- Trigger BEFORE INSERT: bloqueia se qualquer dispositivo estiver inativo
CREATE TRIGGER trg_before_insert_leituramateus
BEFORE INSERT ON leituramateus
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 macmateus     WHERE idmacmateus     = NEW.macmateus_idmacmateus;
    SELECT ativo INTO v_ativo_sensor
        FROM sensormateus  WHERE idsensormateus  = NEW.sensormateus_idsensormateus;
    SELECT ativo INTO v_ativo_coletor
        FROM coletormateus WHERE idcoletormateus = NEW.coletormateus_idcoletormateus;

    IF v_ativo_mac = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'macmateus inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_sensor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'sensormateus inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_coletor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'coletormateus inativo. Insercao bloqueada.';
    END IF;
END$$

-- Trigger AFTER INSERT: incrementa contadores
CREATE TRIGGER trg_after_insert_leituramateus
AFTER INSERT ON leituramateus
FOR EACH ROW
BEGIN
    UPDATE macmateus
        SET contador = contador + 1
        WHERE idmacmateus = NEW.macmateus_idmacmateus;

    UPDATE sensormateus
        SET contador = contador + 1
        WHERE idsensormateus = NEW.sensormateus_idsensormateus;

    UPDATE coletormateus
        SET contador = contador + 1
        WHERE idcoletormateus = NEW.coletormateus_idcoletormateus;
END$$

-- Trigger AFTER DELETE: decrementa contadores
CREATE TRIGGER trg_after_delete_leituramateus
AFTER DELETE ON leituramateus
FOR EACH ROW
BEGIN
    UPDATE macmateus
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idmacmateus = OLD.macmateus_idmacmateus;

    UPDATE sensormateus
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idsensormateus = OLD.sensormateus_idsensormateus;

    UPDATE coletormateus
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idcoletormateus = OLD.coletormateus_idcoletormateus;
END$$

DELIMITER ;

-- ============================================================
-- PROCEDURES - macmateus
-- ============================================================

DELIMITER $$

CREATE PROCEDURE sp_inserir_macmateus(
    IN p_idmacmateus VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    INSERT INTO macmateus (idmacmateus, nome, contador, ativo, limite)
    VALUES (p_idmacmateus, p_nome, 0, p_ativo, p_limite);
END$$

CREATE PROCEDURE sp_editar_macmateus(
    IN p_idmacmateus VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    UPDATE macmateus
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idmacmateus = p_idmacmateus;
END$$

CREATE PROCEDURE sp_deletar_macmateus(
    IN p_idmacmateus VARCHAR(17)
)
BEGIN
    DELETE FROM macmateus WHERE idmacmateus = p_idmacmateus;
END$$

CREATE PROCEDURE sp_listar_macmateus()
BEGIN
    SELECT idmacmateus, nome, contador, ativo, limite
    FROM macmateus
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - coletormateus
-- ============================================================

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

CREATE PROCEDURE sp_editar_coletormateus(
    IN p_idcoletormateus INTEGER,
    IN p_nome          VARCHAR(45),
    IN p_ativo         BOOL,
    IN p_limite        INTEGER
)
BEGIN
    UPDATE coletormateus
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idcoletormateus = p_idcoletormateus;
END$$

CREATE PROCEDURE sp_deletar_coletormateus(
    IN p_idcoletormateus INTEGER
)
BEGIN
    DELETE FROM coletormateus WHERE idcoletormateus = p_idcoletormateus;
END$$

CREATE PROCEDURE sp_listar_coletormateus()
BEGIN
    SELECT idcoletormateus, nome, contador, ativo, limite
    FROM coletormateus
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - sensormateus
-- ============================================================

CREATE PROCEDURE sp_inserir_sensormateus(
    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 sensormateus (nome, ativo, limite, contador, unidade, sigla)
    VALUES (p_nome, p_ativo, p_limite, 0, p_unidade, p_sigla);
END$$

CREATE PROCEDURE sp_editar_sensormateus(
    IN p_idsensormateus 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 sensormateus
    SET nome    = p_nome,
        ativo   = p_ativo,
        limite  = p_limite,
        unidade = p_unidade,
        sigla   = p_sigla
    WHERE idsensormateus = p_idsensormateus;
END$$

CREATE PROCEDURE sp_deletar_sensormateus(
    IN p_idsensormateus INTEGER
)
BEGIN
    DELETE FROM sensormateus WHERE idsensormateus = p_idsensormateus;
END$$

CREATE PROCEDURE sp_listar_sensormateus()
BEGIN
    SELECT idsensormateus, nome, ativo, limite, contador, unidade, sigla
    FROM sensormateus
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - leituramateus
-- ============================================================

CREATE PROCEDURE sp_inserir_leituramateus(
    IN p_sensormateus_id  INTEGER,
    IN p_macmateus_id     VARCHAR(17),
    IN p_coletormateus_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    INSERT INTO leituramateus
        (sensormateus_idsensormateus, macmateus_idmacmateus, coletormateus_idcoletormateus,
         dataleitura, horaleitura, valor)
    VALUES
        (p_sensormateus_id, p_macmateus_id, p_coletormateus_id,
         p_dataleitura, p_horaleitura, p_valor);
END$$

CREATE PROCEDURE sp_editar_leituramateus(
    IN p_idleituramateus  INTEGER,
    IN p_sensormateus_id  INTEGER,
    IN p_macmateus_id     VARCHAR(17),
    IN p_coletormateus_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    UPDATE leituramateus
    SET sensormateus_idsensormateus   = p_sensormateus_id,
        macmateus_idmacmateus         = p_macmateus_id,
        coletormateus_idcoletormateus = p_coletormateus_id,
        dataleitura               = p_dataleitura,
        horaleitura               = p_horaleitura,
        valor                     = p_valor
    WHERE idleituramateus = p_idleituramateus;
END$$

CREATE PROCEDURE sp_deletar_leituramateus(
    IN p_idleituramateus INTEGER
)
BEGIN
    DELETE FROM leituramateus WHERE idleituramateus = p_idleituramateus;
END$$

CREATE PROCEDURE sp_listar_leituramateus()
BEGIN
    SELECT
        l.idleituramateus,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        s.idsensormateus,
        s.nome    AS sensor,
        s.sigla   AS sigla_sensor,
        s.unidade,
        m.idmacmateus,
        m.nome    AS nome_mac,
        c.idcoletormateus,
        c.nome    AS coletor
    FROM leituramateus l
    INNER JOIN sensormateus  s ON s.idsensormateus   = l.sensormateus_idsensormateus
    INNER JOIN macmateus     m ON m.idmacmateus       = l.macmateus_idmacmateus
    INNER JOIN coletormateus c ON c.idcoletormateus   = l.coletormateus_idcoletormateus
    ORDER BY l.dataleitura DESC, l.horaleitura DESC;
END$$

CREATE PROCEDURE sp_buscar_leituramateus(
    IN p_idleituramateus INTEGER
)
BEGIN
    SELECT
        l.idleituramateus,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        l.sensormateus_idsensormateus,
        l.macmateus_idmacmateus,
        l.coletormateus_idcoletormateus,
        s.nome  AS sensor,
        m.nome  AS nome_mac,
        c.nome  AS coletor
    FROM leituramateus l
    INNER JOIN sensormateus  s ON s.idsensormateus   = l.sensormateus_idsensormateus
    INNER JOIN macmateus     m ON m.idmacmateus       = l.macmateus_idmacmateus
    INNER JOIN coletormateus c ON c.idcoletormateus   = l.coletormateus_idcoletormateus
    WHERE l.idleituramateus = p_idleituramateus;
END$$

-- ============================================================
-- PROCEDURES - UTILITÁRIOS
-- ============================================================

CREATE PROCEDURE sp_resetar_contadores()
BEGIN
    UPDATE macmateus     SET contador = 0;
    UPDATE sensormateus  SET contador = 0;
    UPDATE coletormateus SET contador = 0;
END$$

DELIMITER ;

-- ============================================================
-- DADOS DE EXEMPLO (mínimo para testar tudo)
-- ============================================================

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

CALL sp_inserir_coletormateus('Coletor mateus 01', TRUE, 50);
CALL sp_inserir_coletormateus('Coletor mateus 02', TRUE, 80);
CALL sp_inserir_coletormateus('Coletor mateus 03', TRUE, 60);

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

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

-- ============================================================
-- FIM DO SCRIPT
-- ============================================================