SET NAMES utf8mb4;
SET time_zone = '-03:00';

CREATE TABLE IF NOT EXISTS kuaapy_pesquisas (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 slug VARCHAR(120) NOT NULL UNIQUE,
 titulo VARCHAR(180) NOT NULL,
 resumo VARCHAR(600) NULL,
 tipo_destino ENUM('externo','mapeamento','dashboard') NOT NULL DEFAULT 'externo',
 url_destino VARCHAR(500) NULL,
 imagem_url VARCHAR(500) NULL,
 icone VARCHAR(40) NULL,
 ordem SMALLINT NOT NULL DEFAULT 0,
 publicada TINYINT(1) NOT NULL DEFAULT 1,
 criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 alterado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_municipios_sc (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 codigo_ibge VARCHAR(7) NULL UNIQUE,
 municipio VARCHAR(120) NOT NULL UNIQUE,
 municipio_normalizado VARCHAR(120) NOT NULL,
 regiao_intermediaria VARCHAR(80) NULL,
 ativo TINYINT(1) NOT NULL DEFAULT 1,
 INDEX idx_municipio_norm (municipio_normalizado),
 INDEX idx_regiao (regiao_intermediaria)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_fontes (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 titulo VARCHAR(300) NOT NULL,
 url VARCHAR(1000) NOT NULL,
 orgao_publicador VARCHAR(220) NULL,
 tipo_fonte VARCHAR(80) NULL,
 data_referencia DATE NULL,
 data_acesso DATE NOT NULL,
 observacao TEXT NULL,
 UNIQUE KEY uk_fonte_url (url(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_conselhos_juventude (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 municipio_id SMALLINT UNSIGNED NOT NULL UNIQUE,
 nome VARCHAR(220) NULL,
 sigla VARCHAR(40) NULL,
 lei_numero VARCHAR(80) NULL,
 lei_data DATE NULL,
 status VARCHAR(60) NOT NULL DEFAULT 'a_verificar',
 ultima_atividade DATE NULL,
 ultima_verificacao DATE NULL,
 fonte_url VARCHAR(1000) NULL,
 observacao TEXT NULL,
 alterado_por_sis_id INT UNSIGNED NULL,
 alterado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_conselho_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_conselho_status (status), INDEX idx_conselho_verif (ultima_verificacao)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_conselhos_historico (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 municipio_id SMALLINT UNSIGNED NOT NULL,
 ano_referencia SMALLINT NOT NULL,
 nome VARCHAR(220) NULL,
 data_criacao_informada DATE NULL,
 fonte_descricao VARCHAR(300) NULL,
 observacao TEXT NULL,
 CONSTRAINT fk_ch_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_ch_mun_ano (municipio_id,ano_referencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_orgaos_gestores_juventude (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 municipio_id SMALLINT UNSIGNED NOT NULL UNIQUE,
 nome_orgao VARCHAR(300) NULL,
 tipo_estrutura VARCHAR(80) NULL,
 orgao_vinculado VARCHAR(300) NULL,
 responsavel VARCHAR(220) NULL,
 email VARCHAR(220) NULL,
 telefone VARCHAR(100) NULL,
 site VARCHAR(1000) NULL,
 status VARCHAR(60) NOT NULL DEFAULT 'a_verificar',
 ultima_verificacao DATE NULL,
 fonte_url VARCHAR(1000) NULL,
 observacao TEXT NULL,
 alterado_por_sis_id INT UNSIGNED NULL,
 alterado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_org_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_org_status (status), INDEX idx_org_verif (ultima_verificacao)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_orgaos_historico (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 municipio_id SMALLINT UNSIGNED NOT NULL,
 ano_referencia SMALLINT NOT NULL,
 nome_orgao VARCHAR(300) NULL,
 responsavel VARCHAR(220) NULL,
 fonte_descricao VARCHAR(300) NULL,
 observacao TEXT NULL,
 CONSTRAINT fk_oh_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_oh_mun_ano (municipio_id,ano_referencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_evidencias (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tipo_entidade ENUM('conselho','orgao') NOT NULL,
 municipio_id SMALLINT UNSIGNED NOT NULL,
 afirmacao TEXT NOT NULL,
 data_referencia DATE NULL,
 url VARCHAR(1000) NOT NULL,
 grau_verificacao ENUM('oficial','oficial_indireta','secundaria','historica') NOT NULL DEFAULT 'oficial',
 registrado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_ev_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_ev_entidade (tipo_entidade,municipio_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_sugestoes_correcao (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 municipio_id SMALLINT UNSIGNED NOT NULL,
 tipo_entidade ENUM('conselho','orgao','outro') NOT NULL,
 campo VARCHAR(120) NULL,
 descricao TEXT NOT NULL,
 fonte_url VARCHAR(1000) NULL,
 nome_contato VARCHAR(180) NULL,
 email_contato VARCHAR(220) NULL,
 status ENUM('pendente','em_analise','aceita','rejeitada') NOT NULL DEFAULT 'pendente',
 resposta_admin TEXT NULL,
 analisado_por_sis_id INT UNSIGNED NULL,
 criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 analisado_em DATETIME NULL,
 ip_hash CHAR(64) NULL,
 CONSTRAINT fk_corr_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_corr_status (status,criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_usuarios (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sis_usuario_id INT UNSIGNED NOT NULL UNIQUE,
 nome_cache VARCHAR(220) NULL,
 email_cache VARCHAR(220) NULL,
 papel ENUM('consulta','editor','admin') NOT NULL DEFAULT 'consulta',
 ativo TINYINT(1) NOT NULL DEFAULT 1,
 criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 alterado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_sso_tokens_usados (
 jti CHAR(32) PRIMARY KEY,
 sis_usuario_id INT UNSIGNED NOT NULL,
 expira_em DATETIME NOT NULL,
 usado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_sso_expira (expira_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_clima_perguntas (
 id TINYINT UNSIGNED PRIMARY KEY,
 codigo VARCHAR(20) NOT NULL,
 texto TEXT NOT NULL,
 tema ENUM('perfil','percepcoes','conhecimento','acoes','territorio','privado') NOT NULL,
 tipo ENUM('categorica','multipla','escala','texto_privado') NOT NULL,
 publica TINYINT(1) NOT NULL DEFAULT 1,
 ordem SMALLINT NOT NULL,
 INDEX idx_clima_q_tema (tema,publica,ordem)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_clima_opcoes (
 id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 pergunta_id TINYINT UNSIGNED NOT NULL,
 rotulo VARCHAR(500) NOT NULL,
 ordem SMALLINT NOT NULL,
 CONSTRAINT fk_op_q FOREIGN KEY (pergunta_id) REFERENCES kuaapy_clima_perguntas(id) ON DELETE CASCADE,
 UNIQUE KEY uk_q_opcao (pergunta_id,rotulo(190))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_clima_respostas (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 faixa_etaria VARCHAR(80) NULL,
 raca_cor VARCHAR(80) NULL,
 genero VARCHAR(120) NULL,
 escolaridade VARCHAR(180) NULL,
 zona VARCHAR(80) NULL,
 regiao VARCHAR(80) NULL,
 municipio_id SMALLINT UNSIGNED NULL,
 municipio_texto VARCHAR(140) NULL,
 dados_json JSON NOT NULL,
 criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_cr_municipio FOREIGN KEY (municipio_id) REFERENCES kuaapy_municipios_sc(id),
 INDEX idx_cr_faixa (faixa_etaria), INDEX idx_cr_raca (raca_cor), INDEX idx_cr_genero (genero),
 INDEX idx_cr_escolaridade (escolaridade), INDEX idx_cr_zona (zona), INDEX idx_cr_regiao (regiao)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kuaapy_logs_admin (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sis_usuario_id INT UNSIGNED NOT NULL,
 acao VARCHAR(100) NOT NULL,
 entidade VARCHAR(80) NULL,
 entidade_id VARCHAR(80) NULL,
 detalhes_json JSON NULL,
 ip_hash CHAR(64) NULL,
 criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_log_user_data (sis_usuario_id,criado_em), INDEX idx_log_entidade (entidade,entidade_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;