-- phpMyAdmin SQL Dump
-- Banco de dados: `bancomaria`

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------
-- Criação do banco de dados
-- --------------------------------------------------------

CREATE DATABASE IF NOT EXISTS `bancomaria`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

USE `bancomaria`;

-- --------------------------------------------------------
-- Tabelas principais
-- --------------------------------------------------------

CREATE TABLE `coletormaria` (
  `idcoletormaria` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome`           varchar(45)       DEFAULT NULL,
  `contador`       int(10) UNSIGNED  DEFAULT 0,
  `ativo`          tinyint(1)        DEFAULT 1,
  `limite`         int(10) UNSIGNED  DEFAULT 100,
  PRIMARY KEY (`idcoletormaria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

CREATE TABLE `macmaria` (
  `idmacmaria` varchar(17)      NOT NULL,
  `nome`       varchar(45)      DEFAULT NULL,
  `contador`   int(10) UNSIGNED DEFAULT 0,
  `ativo`      tinyint(1)       DEFAULT 1,
  `limite`     int(10) UNSIGNED DEFAULT 100,
  PRIMARY KEY (`idmacmaria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

CREATE TABLE `sensormaria` (
  `idsensormaria` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome`          varchar(45)      DEFAULT NULL,
  `ativo`         tinyint(1)       DEFAULT 1,
  `limite`        int(10) UNSIGNED DEFAULT 100,
  `contador`      int(10) UNSIGNED DEFAULT 0,
  `unidade`       varchar(10)      DEFAULT NULL,
  `sigla`         varchar(3)       DEFAULT NULL,
  PRIMARY KEY (`idsensormaria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

CREATE TABLE `leituramaria` (
  `idleituramaria`              int(10) UNSIGNED     NOT NULL AUTO_INCREMENT,
  `sensormaria_idsensormaria`   int(10) UNSIGNED     NOT NULL,
  `macmaria_idmacmaria`         varchar(17)          NOT NULL,
  `coletormaria_idcoletormaria` int(10) UNSIGNED     NOT NULL,
  `dataleitura`                 date                 DEFAULT NULL,
  `horaleitura`                 time                 DEFAULT NULL,
  `valor`                       int(10) UNSIGNED ZEROFILL DEFAULT NULL,
  PRIMARY KEY (`idleituramaria`),
  KEY `leitura_FKIndex1` (`macmaria_idmacmaria`),
  KEY `leitura_FKIndex2` (`sensormaria_idsensormaria`),
  KEY `leitura_FKIndex3` (`coletormaria_idcoletormaria`),
  CONSTRAINT `leituramaria_ibfk_1` FOREIGN KEY (`macmaria_idmacmaria`)
    REFERENCES `macmaria` (`idmacmaria`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `leituramaria_ibfk_2` FOREIGN KEY (`sensormaria_idsensormaria`)
    REFERENCES `sensormaria` (`idsensormaria`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `leituramaria_ibfk_3` FOREIGN KEY (`coletormaria_idcoletormaria`)
    REFERENCES `coletormaria` (`idcoletormaria`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Tabelas de log (CRIADAS AGORA — faltavam no original)
-- Referenciadas pelos triggers de DELETE de cada tabela principal
-- --------------------------------------------------------

CREATE TABLE `log_coletor` (
  `idlog`        int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idcoletor`    int(10) UNSIGNED DEFAULT NULL,
  `nome`         varchar(45)      DEFAULT NULL,
  `data_exclusao` datetime        DEFAULT NULL,
  PRIMARY KEY (`idlog`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `log_macmaria` (
  `idlog`        int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idmacmaria`   varchar(17)      DEFAULT NULL,
  `nome`         varchar(45)      DEFAULT NULL,
  `data_exclusao` datetime        DEFAULT NULL,
  PRIMARY KEY (`idlog`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `log_sensormaria` (
  `idlog`          int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idsensormaria`  int(10) UNSIGNED DEFAULT NULL,
  `nome`           varchar(45)      DEFAULT NULL,
  `data_exclusao`  datetime         DEFAULT NULL,
  PRIMARY KEY (`idlog`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Triggers
-- --------------------------------------------------------

DELIMITER $$

-- coletormaria
CREATE TRIGGER `trg_coletor_bi` BEFORE INSERT ON `coletormaria` FOR EACH ROW BEGIN
    IF NEW.contador IS NULL THEN SET NEW.contador = 0; END IF;
END$$

CREATE TRIGGER `trg_coletor_bu` BEFORE UPDATE ON `coletormaria` FOR EACH ROW BEGIN
    IF NEW.contador < 0 THEN SET NEW.contador = 0; END IF;
END$$

CREATE TRIGGER `trg_coletor_ad` AFTER DELETE ON `coletormaria` FOR EACH ROW BEGIN
    INSERT INTO log_coletor (idcoletor, nome, data_exclusao)
    VALUES (OLD.idcoletormaria, OLD.nome, NOW());
END$$

-- macmaria
CREATE TRIGGER `trg_macmaria_bi` BEFORE INSERT ON `macmaria` FOR EACH ROW BEGIN
    IF NEW.contador IS NULL THEN SET NEW.contador = 0; END IF;
    IF NEW.ativo    IS NULL THEN SET NEW.ativo    = 1; END IF;
    IF NEW.limite   IS NULL THEN SET NEW.limite   = 100; END IF;
END$$

CREATE TRIGGER `trg_macmaria_bu` BEFORE UPDATE ON `macmaria` FOR EACH ROW BEGIN
    IF NEW.contador < 0 THEN SET NEW.contador = 0; END IF;
END$$

CREATE TRIGGER `trg_macmaria_ad` AFTER DELETE ON `macmaria` FOR EACH ROW BEGIN
    INSERT INTO log_macmaria (idmacmaria, nome, data_exclusao)
    VALUES (OLD.idmacmaria, OLD.nome, NOW());
END$$

-- sensormaria
CREATE TRIGGER `trg_sensormaria_bi` BEFORE INSERT ON `sensormaria` FOR EACH ROW BEGIN
    IF NEW.contador IS NULL THEN SET NEW.contador = 0; END IF;
    IF NEW.ativo    IS NULL THEN SET NEW.ativo    = 1; END IF;
    IF NEW.limite   IS NULL THEN SET NEW.limite   = 100; END IF;
END$$

CREATE TRIGGER `trg_sensormaria_bu` BEFORE UPDATE ON `sensormaria` FOR EACH ROW BEGIN
    IF NEW.contador < 0 THEN SET NEW.contador = 0; END IF;
    IF NEW.limite   < 0 THEN SET NEW.limite   = 0; END IF;
END$$

CREATE TRIGGER `trg_sensormaria_ad` AFTER DELETE ON `sensormaria` FOR EACH ROW BEGIN
    INSERT INTO log_sensormaria (idsensormaria, nome, data_exclusao)
    VALUES (OLD.idsensormaria, OLD.nome, NOW());
END$$

-- leituramaria
CREATE TRIGGER `trg_leitura_ai` AFTER INSERT ON `leituramaria` FOR EACH ROW BEGIN
    UPDATE macmaria     SET contador = contador + 1 WHERE idmacmaria    = NEW.macmaria_idmacmaria;
    UPDATE sensormaria  SET contador = contador + 1 WHERE idsensormaria = NEW.sensormaria_idsensormaria;
    UPDATE coletormaria SET contador = contador + 1 WHERE idcoletormaria = NEW.coletormaria_idcoletormaria;
END$$

CREATE TRIGGER `trg_leitura_ad` AFTER DELETE ON `leituramaria` FOR EACH ROW BEGIN
    UPDATE macmaria     SET contador = contador - 1 WHERE idmacmaria    = OLD.macmaria_idmacmaria;
    UPDATE sensormaria  SET contador = contador - 1 WHERE idsensormaria = OLD.sensormaria_idsensormaria;
    UPDATE coletormaria SET contador = contador - 1 WHERE idcoletormaria = OLD.coletormaria_idcoletormaria;
END$$

CREATE TRIGGER `trg_leitura_au` AFTER UPDATE ON `leituramaria` FOR EACH ROW BEGIN
    IF OLD.macmaria_idmacmaria <> NEW.macmaria_idmacmaria THEN
        UPDATE macmaria SET contador = contador - 1 WHERE idmacmaria = OLD.macmaria_idmacmaria;
        UPDATE macmaria SET contador = contador + 1 WHERE idmacmaria = NEW.macmaria_idmacmaria;
    END IF;
    IF OLD.sensormaria_idsensormaria <> NEW.sensormaria_idsensormaria THEN
        UPDATE sensormaria SET contador = contador - 1 WHERE idsensormaria = OLD.sensormaria_idsensormaria;
        UPDATE sensormaria SET contador = contador + 1 WHERE idsensormaria = NEW.sensormaria_idsensormaria;
    END IF;
    IF OLD.coletormaria_idcoletormaria <> NEW.coletormaria_idcoletormaria THEN
        UPDATE coletormaria SET contador = contador - 1 WHERE idcoletormaria = OLD.coletormaria_idcoletormaria;
        UPDATE coletormaria SET contador = contador + 1 WHERE idcoletormaria = NEW.coletormaria_idcoletormaria;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------
-- Stored Procedures
-- --------------------------------------------------------

DELIMITER $$

CREATE PROCEDURE `sp_insert_sensormaria`(IN p_nome VARCHAR(45), IN p_limite INT, IN p_unidade VARCHAR(10), IN p_sigla VARCHAR(3))
BEGIN
    INSERT INTO sensormaria (nome, ativo, limite, contador, unidade, sigla)
    VALUES (p_nome, 1, p_limite, 0, p_unidade, p_sigla);
END$$

CREATE PROCEDURE `sp_update_sensormaria`(IN p_id INT, IN p_nome VARCHAR(45), IN p_ativo BOOLEAN, IN p_limite INT, IN p_unidade VARCHAR(10), IN p_sigla VARCHAR(3))
BEGIN
    UPDATE sensormaria SET nome = p_nome, ativo = p_ativo, limite = p_limite, unidade = p_unidade, sigla = p_sigla
    WHERE idsensormaria = p_id;
END$$

CREATE PROCEDURE `sp_delete_sensormaria`(IN p_id INT)
BEGIN
    DELETE FROM sensormaria WHERE idsensormaria = p_id;
END$$

CREATE PROCEDURE `sp_insert_macmaria`(IN p_idmacmaria VARCHAR(17), IN p_nome VARCHAR(45), IN p_limite INT)
BEGIN
    INSERT INTO macmaria (idmacmaria, nome, contador, ativo, limite)
    VALUES (p_idmacmaria, p_nome, 0, 1, p_limite);
END$$

CREATE PROCEDURE `sp_update_macmaria`(IN p_idmacmaria VARCHAR(17), IN p_nome VARCHAR(45), IN p_ativo BOOLEAN, IN p_limite INT)
BEGIN
    UPDATE macmaria SET nome = p_nome, ativo = p_ativo, limite = p_limite
    WHERE idmacmaria = p_idmacmaria;
END$$

CREATE PROCEDURE `sp_delete_macmaria`(IN p_idmacmaria VARCHAR(17))
BEGIN
    DELETE FROM macmaria WHERE idmacmaria = p_idmacmaria;
END$$

CREATE PROCEDURE `sp_insert_coletormaria`(IN p_nome VARCHAR(45), IN p_limite INT)
BEGIN
    INSERT INTO coletormaria (nome, contador, ativo, limite)
    VALUES (p_nome, 0, 1, p_limite);
END$$

CREATE PROCEDURE `sp_update_coletormaria`(IN p_id INT, IN p_nome VARCHAR(45), IN p_ativo BOOLEAN, IN p_limite INT)
BEGIN
    UPDATE coletormaria SET nome = p_nome, ativo = p_ativo, limite = p_limite
    WHERE idcoletormaria = p_id;
END$$

CREATE PROCEDURE `sp_delete_coletormaria`(IN p_id INT)
BEGIN
    DELETE FROM coletormaria WHERE idcoletormaria = p_id;
END$$

CREATE PROCEDURE `sp_insert_leituramaria`(IN p_sensor INT, IN p_mac VARCHAR(17), IN p_coletor INT, IN p_data DATE, IN p_hora TIME, IN p_valor INT)
BEGIN
    INSERT INTO leituramaria (sensormaria_idsensormaria, macmaria_idmacmaria, coletormaria_idcoletormaria, dataleitura, horaleitura, valor)
    VALUES (p_sensor, p_mac, p_coletor, p_data, p_hora, p_valor);
END$$

CREATE PROCEDURE `sp_update_leituramaria`(IN p_id INT, IN p_sensor INT, IN p_mac VARCHAR(17), IN p_coletor INT, IN p_data DATE, IN p_hora TIME, IN p_valor INT)
BEGIN
    UPDATE leituramaria
    SET sensormaria_idsensormaria = p_sensor, macmaria_idmacmaria = p_mac,
        coletormaria_idcoletormaria = p_coletor, dataleitura = p_data,
        horaleitura = p_hora, valor = p_valor
    WHERE idleituramaria = p_id;
END$$

CREATE PROCEDURE `sp_delete_leituramaria`(IN p_id INT)
BEGIN
    DELETE FROM leituramaria WHERE idleituramaria = p_id;
END$$

DELIMITER ;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;