-- =========================================================
-- BANCO DE DADOS: projeto_um
-- =========================================================

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

START TRANSACTION;

-- =========================================================
-- TABELA: macpaola
-- =========================================================

CREATE TABLE IF NOT EXISTS `macpaola` (
    `idmacpaola` 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 (`idmacpaola`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

-- =========================================================
-- TABELA: leiturapaola
-- =========================================================

CREATE TABLE IF NOT EXISTS `leiturapaola` (
    `idleiturapaola` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `macpaola_idmacpaola` VARCHAR(17) NOT NULL,
    `datahora` DATETIME DEFAULT NULL,
    `valor` INT(10) UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`idleiturapaola`),
    KEY `leiturapaola_FKIndex1` (`macpaola_idmacpaola`),
    CONSTRAINT `leiturapaola_ibfk_1`
        FOREIGN KEY (`macpaola_idmacpaola`)
        REFERENCES `macpaola` (`idmacpaola`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

-- =========================================================
-- DADOS INICIAIS
-- =========================================================

INSERT INTO `macpaola`
    (`idmacpaola`, `nome`, `contador`, `limite`, `ativo`)
VALUES
    ('01:02:03', 'Placa Um', 0, 2, 1)
ON DUPLICATE KEY UPDATE
    `nome` = VALUES(`nome`),
    `limite` = VALUES(`limite`),
    `ativo` = VALUES(`ativo`);

-- =========================================================
-- PROCEDURE
-- =========================================================

DROP PROCEDURE IF EXISTS `validamacpaola`;

DELIMITER $$

CREATE PROCEDURE `validamacpaola` (
    IN p_idmac VARCHAR(17),
    OUT p_ativo BOOLEAN,
    OUT p_contador INT,
    OUT p_limite INT
)
BEGIN

    SELECT `ativo`, `contador`, `limite`
    INTO p_ativo, p_contador, p_limite
    FROM `macpaola`
    WHERE `idmacpaola` = p_idmac
    LIMIT 1;

END$$

DELIMITER ;

-- =========================================================
-- TRIGGER: DELETE
-- =========================================================

DROP TRIGGER IF EXISTS `tgdeleteleiturapaola`;

DELIMITER $$

CREATE TRIGGER `tgdeleteleiturapaola`
AFTER DELETE ON `leiturapaola`
FOR EACH ROW
BEGIN

    UPDATE `macpaola`
    SET `contador` = `contador` - 1
    WHERE `idmacpaola` = OLD.`macpaola_idmacpaola`;

END$$

DELIMITER ;

-- =========================================================
-- TRIGGER: INSERT
-- =========================================================

DROP TRIGGER IF EXISTS `tginsertleiturapaola`;

DELIMITER $$

CREATE TRIGGER `tginsertleiturapaola`
AFTER INSERT ON `leiturapaola`
FOR EACH ROW
BEGIN

    UPDATE `macpaola`
    SET `contador` = `contador` + 1
    WHERE `idmacpaola` = NEW.`macpaola_idmacpaola`;

END$$

DELIMITER ;

-- =========================================================
-- TRIGGER: VALIDAR MAC
-- =========================================================

DROP TRIGGER IF EXISTS `tgvalidamacpaola`;

DELIMITER $$

CREATE TRIGGER `tgvalidamacpaola`
BEFORE INSERT ON `leiturapaola`
FOR EACH ROW
BEGIN

    DECLARE v_ativo BOOLEAN DEFAULT 0;
    DECLARE v_contador INT DEFAULT 0;
    DECLARE v_limite INT DEFAULT 0;

    CALL `validamacpaola`(
        NEW.`macpaola_idmacpaola`,
        v_ativo,
        v_contador,
        v_limite
    );

    IF v_ativo = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'MAC INATIVO';
    END IF;

    IF v_contador >= v_limite THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'LIMITE DE LEITURAS ATINGIDO';
    END IF;

END$$

DELIMITER ;

COMMIT;