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

CREATE DATABASE IF NOT EXISTS banco_lucas;
USE banco_lucas;

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

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

CREATE TABLE IF NOT EXISTS coletorlucas (
    idcoletorlucas 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 sensorlucas (
    idsensorlucas 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 leituralucas (
    idleituralucas             INTEGER     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sensorlucas_idsensorlucas   INTEGER     NOT NULL,
    maclucas_idmaclucas         VARCHAR(17) NOT NULL,
    coletorlucas_idcoletorlucas INTEGER     NOT NULL,
    dataleitura               DATE,
    horaleitura               TIME,
    valor                     INTEGER,

    CONSTRAINT leitura_FKIndex1
        FOREIGN KEY (maclucas_idmaclucas)
        REFERENCES maclucas (idmaclucas),

    CONSTRAINT leitura_FKIndex2
        FOREIGN KEY (sensorlucas_idsensorlucas)
        REFERENCES sensorlucas (idsensorlucas),

    CONSTRAINT leitura_FKIndex3
        FOREIGN KEY (coletorlucas_idcoletorlucas)
        REFERENCES coletorlucas (idcoletorlucas)
);

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

DELIMITER $$

-- Trigger BEFORE INSERT: bloqueia se qualquer dispositivo estiver inativo
CREATE TRIGGER trg_before_insert_leituralucas
BEFORE INSERT ON leituralucas
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 maclucas     WHERE idmaclucas     = NEW.maclucas_idmaclucas;
    SELECT ativo INTO v_ativo_sensor
        FROM sensorlucas  WHERE idsensorlucas  = NEW.sensorlucas_idsensorlucas;
    SELECT ativo INTO v_ativo_coletor
        FROM coletorlucas WHERE idcoletorlucas = NEW.coletorlucas_idcoletorlucas;

    IF v_ativo_mac = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'maclucas inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_sensor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'sensorlucas inativo. Insercao bloqueada.';
    END IF;
    IF v_ativo_coletor = FALSE THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'coletorlucas inativo. Insercao bloqueada.';
    END IF;
END$$

-- Trigger AFTER INSERT: incrementa contadores
CREATE TRIGGER trg_after_insert_leituralucas
AFTER INSERT ON leituralucas
FOR EACH ROW
BEGIN
    UPDATE maclucas
        SET contador = contador + 1
        WHERE idmaclucas = NEW.maclucas_idmaclucas;

    UPDATE sensorlucas
        SET contador = contador + 1
        WHERE idsensorlucas = NEW.sensorlucas_idsensorlucas;

    UPDATE coletorlucas
        SET contador = contador + 1
        WHERE idcoletorlucas = NEW.coletorlucas_idcoletorlucas;
END$$

-- Trigger AFTER DELETE: decrementa contadores
CREATE TRIGGER trg_after_delete_leituralucas
AFTER DELETE ON leituralucas
FOR EACH ROW
BEGIN
    UPDATE maclucas
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idmaclucas = OLD.maclucas_idmaclucas;

    UPDATE sensorlucas
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idsensorlucas = OLD.sensorlucas_idsensorlucas;

    UPDATE coletorlucas
        SET contador = IF(contador > 0, contador - 1, 0)
        WHERE idcoletorlucas = OLD.coletorlucas_idcoletorlucas;
END$$

DELIMITER ;

-- ============================================================
-- PROCEDURES - maclucas
-- ============================================================

DELIMITER $$

CREATE PROCEDURE sp_inserir_maclucas(
    IN p_idmaclucas VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    INSERT INTO maclucas (idmaclucas, nome, contador, ativo, limite)
    VALUES (p_idmaclucas, p_nome, 0, p_ativo, p_limite);
END$$

CREATE PROCEDURE sp_editar_maclucas(
    IN p_idmaclucas VARCHAR(17),
    IN p_nome      VARCHAR(45),
    IN p_ativo     BOOL,
    IN p_limite    INTEGER
)
BEGIN
    UPDATE maclucas
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idmaclucas = p_idmaclucas;
END$$

CREATE PROCEDURE sp_deletar_maclucas(
    IN p_idmaclucas VARCHAR(17)
)
BEGIN
    DELETE FROM maclucas WHERE idmaclucas = p_idmaclucas;
END$$

CREATE PROCEDURE sp_listar_maclucas()
BEGIN
    SELECT idmaclucas, nome, contador, ativo, limite
    FROM maclucas
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - coletorlucas
-- ============================================================

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

CREATE PROCEDURE sp_editar_coletorlucas(
    IN p_idcoletorlucas INTEGER,
    IN p_nome          VARCHAR(45),
    IN p_ativo         BOOL,
    IN p_limite        INTEGER
)
BEGIN
    UPDATE coletorlucas
    SET nome   = p_nome,
        ativo  = p_ativo,
        limite = p_limite
    WHERE idcoletorlucas = p_idcoletorlucas;
END$$

CREATE PROCEDURE sp_deletar_coletorlucas(
    IN p_idcoletorlucas INTEGER
)
BEGIN
    DELETE FROM coletorlucas WHERE idcoletorlucas = p_idcoletorlucas;
END$$

CREATE PROCEDURE sp_listar_coletorlucas()
BEGIN
    SELECT idcoletorlucas, nome, contador, ativo, limite
    FROM coletorlucas
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - sensorlucas
-- ============================================================

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

CREATE PROCEDURE sp_editar_sensorlucas(
    IN p_idsensorlucas 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 sensorlucas
    SET nome    = p_nome,
        ativo   = p_ativo,
        limite  = p_limite,
        unidade = p_unidade,
        sigla   = p_sigla
    WHERE idsensorlucas = p_idsensorlucas;
END$$

CREATE PROCEDURE sp_deletar_sensorlucas(
    IN p_idsensorlucas INTEGER
)
BEGIN
    DELETE FROM sensorlucas WHERE idsensorlucas = p_idsensorlucas;
END$$

CREATE PROCEDURE sp_listar_sensorlucas()
BEGIN
    SELECT idsensorlucas, nome, ativo, limite, contador, unidade, sigla
    FROM sensorlucas
    ORDER BY nome;
END$$

-- ============================================================
-- PROCEDURES - leituralucas
-- ============================================================

CREATE PROCEDURE sp_inserir_leituralucas(
    IN p_sensorlucas_id  INTEGER,
    IN p_maclucas_id     VARCHAR(17),
    IN p_coletorlucas_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    INSERT INTO leituralucas
        (sensorlucas_idsensorlucas, maclucas_idmaclucas, coletorlucas_idcoletorlucas,
         dataleitura, horaleitura, valor)
    VALUES
        (p_sensorlucas_id, p_maclucas_id, p_coletorlucas_id,
         p_dataleitura, p_horaleitura, p_valor);
END$$

CREATE PROCEDURE sp_editar_leituralucas(
    IN p_idleituralucas  INTEGER,
    IN p_sensorlucas_id  INTEGER,
    IN p_maclucas_id     VARCHAR(17),
    IN p_coletorlucas_id INTEGER,
    IN p_dataleitura    DATE,
    IN p_horaleitura    TIME,
    IN p_valor          INTEGER
)
BEGIN
    UPDATE leituralucas
    SET sensorlucas_idsensorlucas   = p_sensorlucas_id,
        maclucas_idmaclucas         = p_maclucas_id,
        coletorlucas_idcoletorlucas = p_coletorlucas_id,
        dataleitura               = p_dataleitura,
        horaleitura               = p_horaleitura,
        valor                     = p_valor
    WHERE idleituralucas = p_idleituralucas;
END$$

CREATE PROCEDURE sp_deletar_leituralucas(
    IN p_idleituralucas INTEGER
)
BEGIN
    DELETE FROM leituralucas WHERE idleituralucas = p_idleituralucas;
END$$

CREATE PROCEDURE sp_listar_leituralucas()
BEGIN
    SELECT
        l.idleituralucas,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        s.idsensorlucas,
        s.nome    AS sensor,
        s.sigla   AS sigla_sensor,
        s.unidade,
        m.idmaclucas,
        m.nome    AS nome_mac,
        c.idcoletorlucas,
        c.nome    AS coletor
    FROM leituralucas l
    INNER JOIN sensorlucas  s ON s.idsensorlucas   = l.sensorlucas_idsensorlucas
    INNER JOIN maclucas     m ON m.idmaclucas       = l.maclucas_idmaclucas
    INNER JOIN coletorlucas c ON c.idcoletorlucas   = l.coletorlucas_idcoletorlucas
    ORDER BY l.dataleitura DESC, l.horaleitura DESC;
END$$

CREATE PROCEDURE sp_buscar_leituralucas(
    IN p_idleituralucas INTEGER
)
BEGIN
    SELECT
        l.idleituralucas,
        l.dataleitura,
        l.horaleitura,
        l.valor,
        l.sensorlucas_idsensorlucas,
        l.maclucas_idmaclucas,
        l.coletorlucas_idcoletorlucas,
        s.nome  AS sensor,
        m.nome  AS nome_mac,
        c.nome  AS coletor
    FROM leituralucas l
    INNER JOIN sensorlucas  s ON s.idsensorlucas   = l.sensorlucas_idsensorlucas
    INNER JOIN maclucas     m ON m.idmaclucas       = l.maclucas_idmaclucas
    INNER JOIN coletorlucas c ON c.idcoletorlucas   = l.coletorlucas_idcoletorlucas
    WHERE l.idleituralucas = p_idleituralucas;
END$$

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

CREATE PROCEDURE sp_resetar_contadores()
BEGIN
    UPDATE maclucas     SET contador = 0;
    UPDATE sensorlucas  SET contador = 0;
    UPDATE coletorlucas SET contador = 0;
END$$

DELIMITER ;

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

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

CALL sp_inserir_coletorlucas('Coletor lucas 01', TRUE, 50);
CALL sp_inserir_coletorlucas('Coletor lucas 02', TRUE, 80);
CALL sp_inserir_coletorlucas('Coletor lucas 03', TRUE, 60);

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

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

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