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 */;

--
-- Estrutura para tabela `macbruna`
--
CREATE TABLE `macbruna` (
  `idmacbruna` varchar(17) NOT NULL,
  `nome` varchar(25) DEFAULT NULL,
  `contador` int(10) UNSIGNED DEFAULT 0,
  `limite` int(10) UNSIGNED DEFAULT 0,
  `ativo` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`idmacbruna`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Despejando dados para a tabela `macbruna`
--
INSERT INTO `macbruna` (`idmacbruna`, `nome`, `contador`, `limite`, `ativo`) VALUES
('01:02:03', 'Placa 01', 0, 2, 1);

--
-- Estrutura para tabela `leiturabruna`
--
CREATE TABLE `leiturabruna` (
  `idleiturabruna` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `macbruna_idmacbruna` varchar(17) NOT NULL,
  `datahora` datetime DEFAULT NULL,
  `valor` int(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`idleiturabruna`),
  KEY `leiturabruna_FKIndex1` (`macbruna_idmacbruna`),
  CONSTRAINT `leiturabruna_ibfk_1` FOREIGN KEY (`macbruna_idmacbruna`) REFERENCES `macbruna` (`idmacbruna`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci AUTO_INCREMENT=5;

--
-- Procedimento `validamacbruna`
--
DELIMITER $$
CREATE PROCEDURE `validamacbruna` (
  IN `$idmac` VARCHAR(17), 
  OUT `$ativo` BOOLEAN, 
  OUT `$contador` INT, 
  OUT `$limite` INT
)
BEGIN
  SELECT ativo INTO $ativo FROM macbruna WHERE idmacbruna=$idmac;
  SELECT contador INTO $contador FROM macbruna WHERE idmacbruna=$idmac;
  SELECT limite INTO $limite FROM macbruna WHERE idmacbruna=$idmac;
END$$
DELIMITER ;

--
-- Triggers para `leiturabruna`
--
DELIMITER $$
CREATE TRIGGER `tgdeleteleiturabruna` AFTER DELETE ON `leiturabruna` FOR EACH ROW 
BEGIN
  UPDATE macbruna SET contador = contador - 1 WHERE idmacbruna = old.macbruna_idmacbruna;
END$$

CREATE TRIGGER `tginsertleiturabruna` AFTER INSERT ON `leiturabruna` FOR EACH ROW 
BEGIN
  UPDATE macbruna SET contador = contador + 1 WHERE idmacbruna = new.macbruna_idmacbruna;
END$$

CREATE TRIGGER `tgvalidamacbruna` BEFORE INSERT ON `leiturabruna` FOR EACH ROW 
BEGIN
  CALL validamacbruna(new.macbruna_idmacbruna, @ativo, @contador, @limite);
  IF @ativo = 0 THEN 
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'MAC INATIVO';
  END IF;

  IF @contador >= @limite THEN 
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'LIMITE DE LEITURAS ATINGIDO!';
  END IF;
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 */;