CREATE TABLE IF NOT EXISTS `diarias`.`orgao` (
  `codigo` INT NOT NULL,
  `nome` VARCHAR(200) NOT NULL,
  `orgao_sup` INT NULL,
  PRIMARY KEY (`codigo`),
  INDEX `fk_orgao_sup_idx` (`orgao_sup` ASC),
  CONSTRAINT `fk_orgao_sup` FOREIGN KEY (`orgao_sup`) REFERENCES `diarias`.`orgao` (`codigo`)
)
-- ...
CREATE TABLE IF NOT EXISTS `diarias`.`favorecido` (
  `nome` VARCHAR(200) NOT NULL,
  `cpf` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`cpf`)
)
-- ...
CREATE TABLE IF NOT EXISTS `diarias`.`diaria` (
  `codigo` INT NOT NULL AUTO_INCREMENT,
  `documento` VARCHAR(15) NOT NULL,
  `gestao` VARCHAR(10) NOT NULL,
  `dt_diaria` DATE NOT NULL,
  `valor` DECIMAL(10,2) NOT NULL,
  `favorecido` VARCHAR(11) NOT NULL,
  `ug_pagadora` INT NOT NULL,
  `acao` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`codigo`),
  INDEX `fk_diaria_ug_pagadora_idx` (`ug_pagadora` ASC),
  INDEX `fk_acao_idx` (`acao` ASC),
  INDEX `fk_favorecido_idx` (`favorecido` ASC),
  CONSTRAINT `fk_diaria_ug_pagadora` FOREIGN KEY (`ug_pagadora`)
                                     REFERENCES `diarias`.`unidade_gestora` (`codigo`),
  CONSTRAINT `fk_acao` FOREIGN KEY (`acao`) REFERENCES `diarias`.`acao` (`codigo`),
  CONSTRAINT `fk_favorecido` FOREIGN KEY (`favorecido`) REFERENCES `diarias`.`favorecido` (`cpf`)
)

CREATE OR REPLACE VIEW vw_diarias AS
SELECT
    osup.codigo cd_orgao_superior, osup.nome nm_orgao_superior,
    osub.codigo cd_orgao_subordinado, osub.nome nm_orgao_subordinado,
    ug.codigo cd_unidade_gestora, ug.nome nm_unidade_gestora,
    fun.codigo cd_funcao, fun.nome nm_funcao,
    sf.codigo cd_subfuncao, sf.nome nm_subfuncao,
    p.codigo cd_programa, p.nome nm_programa,
    a.codigo cd_acao, a.nome nm_acao, a.linguagem_cidada linguagem_cidada,
    f.cpf cpf_favorecido, f.nome nm_favorecido,
    d.documento, d.gestao, d.dt_diaria, d.valor
FROM diaria d 
    JOIN favorecido f ON d.favorecido = f.cpf
    JOIN unidade_gestora ug ON d.ug_pagadora = ug.codigo
    JOIN orgao osub ON ug.orgao = osub.codigo
    JOIN orgao osup ON osub.orgao_sup = osup.codigo
    JOIN acao a ON d.acao = a.codigo
    JOIN programa p ON a.programa = p.codigo
    JOIN subfuncao sf ON a.subfuncao = sf.codigo
    JOIN funcao fun ON sf.funcao = fun.codigo

DELIMITER $$
USE `diarias`$$

CREATE DEFINER = CURRENT_USER TRIGGER `diarias`.`diaria_AFTER_INSERT`
                                       AFTER INSERT ON `diaria` FOR EACH ROW
BEGIN
    if new.valor >= 500 then
        insert into log(data, operacao)
        values (now(), concat('Nova diária de alto valor - documento: ', new.documento));
    end if;
END$$

DELIMITER $$
USE `diarias`$$
CREATE PROCEDURE inserir_diaria (
    IN cd_org_sup INT, IN nm_org_sup VARCHAR(200),
    IN cd_org_sub INT, IN nm_org_sub VARCHAR(200),
    IN cd_un_gest INT, IN nm_un_gest VARCHAR(200),
    IN cd_funcao INT, IN nm_funcao VARCHAR(200),
    IN cd_subfuncao INT, IN nm_subfuncao VARCHAR(200),
    IN cd_prog INT, IN nm_prog VARCHAR(200),
    IN cd_acao INT, IN nm_acao VARCHAR(200),
    IN ling_cidada VARCHAR(200),
    IN cpf_fav VARCHAR(11), IN nm_fav VARCHAR(300), 
    IN doc VARCHAR(15), IN gestao VARCHAR(11), IN dt DATE, IN val DECIMAL(10,2))
BEGIN
    DECLARE cd_ling_cidada INT;
    DECLARE cd_fav INT;
    
    CALL inserir_orgao_superior(cd_org_sup, nm_org_sup);
    CALL inserir_orgao_subordinado(cd_org_sub, nm_org_sub, cd_org_sup);
    CALL inserir_unidade_gestora(cd_un_gest, nm_un_gest, cd_org_sub);
    CALL inserir_funcao(cd_funcao, nm_funcao);
    CALL inserir_subfuncao(cd_subfuncao, nm_subfuncao, cd_funcao);
    CALL inserir_programa(cd_prog, nm_prog);
    CALL inserir_acao(cd_acao, nm_acao, ling_cidada, cd_prog, cd_subfuncao);
       
    CALL inserir_favorecido(nm_fav, cpf_fav);
    
    insert into diaria(
		favorecido, ug_pagadora, acao, 
        documento, gestao, dt_diaria, valor)
	values(
		cpf_fav, cd_un_gest, cd_acao, 
        doc, gestao, dt, val);
	
END$$

-- Diárias por Favorecido
SELECT d.documento, d.dt_diaria, d.valor, f.nome, f.cpf
FROM diaria d
INNER JOIN favorecido f ON d.favorecido = f.cpf
WHERE f.nome LIKE '%<<PARAMETRO>>%' or f.cpf LIKE '%<<PARAMETRO>>%'

-- Diárias por Órgão (UG/Órgão/Org.Sup)
SELECT nm_unidade_gestora, nm_orgao_subordinado, nm_orgao_superior, sum(d.valor) as valor
FROM vw_diarias d
WHERE
nm_unidade_gestora LIKE '%<<PARAMETRO>>%'
or nm_orgao_subordinado LIKE '%<<PARAMETRO>>%'
or nm_orgao_superior LIKE '%<<PARAMETRO>>%'
GROUP BY  nm_unidade_gestora, nm_orgao_subordinado, nm_orgao_superior

-- Valor por Programa
SELECT p.nome, sum(d.valor) as valor
FROM diaria d
INNER JOIN acao a ON d.acao = a.codigo
INNER JOIN programa p ON a.programa = p.codigo
GROUP BY p.nome
HAVING sum(d.valor) >= <<PARAMETRO>>

-- Valor por Função
SELECT f.nome, sum(d.valor) as valor
FROM diaria d
INNER JOIN acao a ON d.acao = a.codigo
INNER JOIN subfuncao sf ON sf.codigo = a.subfuncao
INNER JOIN funcao f ON f.codigo = sf.funcao
GROUP BY f.nome
HAVING sum(d.valor) >= <<PARAMETRO>>

-- Valor por Dia
SELECT dt_diaria, sum(valor) as valor FROM diaria
GROUP BY dt_diaria ORDER BY dt_diaria