-- IAPTEL — Schema inicial (MySQL/MariaDB)
-- Charset recomendado: utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS TB_DOCUMENTO_SECOES;
DROP TABLE IF EXISTS TB_DOCUMENTOS;
DROP TABLE IF EXISTS TB_ARQUIVOS;
DROP TABLE IF EXISTS TB_FONTES;
DROP TABLE IF EXISTS TB_ROTAS;
DROP TABLE IF EXISTS TB_COMUNIDADES;
DROP TABLE IF EXISTS TB_NOS_LOGISTICOS;
DROP TABLE IF EXISTS TB_CORREDORES;
DROP TABLE IF EXISTS TB_RELEASES;
DROP TABLE IF EXISTS TB_LOG_EVENTOS;
DROP TABLE IF EXISTS TB_USUARIOS;
DROP TABLE IF EXISTS TB_CONFIG_SISTEMA;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE TB_USUARIOS (
  ID_USUARIO INT AUTO_INCREMENT PRIMARY KEY,
  NOME VARCHAR(150) NOT NULL,
  EMAIL VARCHAR(180) NOT NULL,
  SENHA_HASH VARCHAR(255) NOT NULL,
  PERFIL VARCHAR(50) NOT NULL DEFAULT 'VISUALIZADOR',
  ATIVO TINYINT NOT NULL DEFAULT 1,
  ULTIMO_LOGIN DATETIME NULL,
  TENTATIVAS_LOGIN INT NOT NULL DEFAULT 0,
  BLOQUEADO_ATE DATETIME NULL,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY UK_USUARIOS_EMAIL (EMAIL),
  KEY IDX_USUARIOS_ATIVO (ATIVO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_LOG_EVENTOS (
  ID_LOG BIGINT AUTO_INCREMENT PRIMARY KEY,
  ID_USUARIO INT NULL,
  EVENTO VARCHAR(100) NOT NULL,
  MODULO VARCHAR(100) NOT NULL,
  ENTIDADE VARCHAR(100) NULL,
  ID_ENTIDADE VARCHAR(100) NULL,
  DETALHES TEXT NULL,
  IP VARCHAR(60) NULL,
  USER_AGENT TEXT NULL,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY IDX_LOG_USUARIO (ID_USUARIO),
  KEY IDX_LOG_EVENTO (EVENTO),
  KEY IDX_LOG_MODULO (MODULO),
  KEY IDX_LOG_CRIADO (CRIADO_EM),
  CONSTRAINT FK_LOG_USUARIO FOREIGN KEY (ID_USUARIO) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_RELEASES (
  ID_RELEASE INT AUTO_INCREMENT PRIMARY KEY,
  VERSAO VARCHAR(30) NOT NULL,
  NOME VARCHAR(180) NOT NULL,
  DESCRICAO TEXT NULL,
  TIPO VARCHAR(50) NOT NULL DEFAULT 'inicial',
  STATUS VARCHAR(50) NOT NULL DEFAULT 'planejado',
  DATA_RELEASE DATE NULL,
  PUBLICADO TINYINT NOT NULL DEFAULT 1,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  UNIQUE KEY UK_RELEASE_VERSAO (VERSAO),
  KEY IDX_RELEASE_STATUS (STATUS),
  CONSTRAINT FK_RELEASE_CRIADO_POR FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_CORREDORES (
  ID_CORREDOR INT AUTO_INCREMENT PRIMARY KEY,
  NOME VARCHAR(180) NOT NULL,
  DESCRICAO TEXT NULL,
  REGIAO VARCHAR(180) NULL,
  UF VARCHAR(2) NULL,
  PRIORIDADE ENUM('BAIXA','MEDIA','ALTA','CRITICA') NOT NULL DEFAULT 'MEDIA',
  STATUS ENUM('RASCUNHO','EM_PESQUISA','VALIDADO','REJEITADO') NOT NULL DEFAULT 'RASCUNHO',
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  UNIQUE KEY UK_CORREDOR_NOME (NOME),
  KEY IDX_CORREDOR_ATIVO (ATIVO),
  CONSTRAINT FK_CORREDOR_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_CORREDOR_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_COMUNIDADES (
  ID_COMUNIDADE INT AUTO_INCREMENT PRIMARY KEY,
  ID_CORREDOR INT NULL,
  NOME VARCHAR(180) NOT NULL,
  MUNICIPIO VARCHAR(180) NULL,
  UF VARCHAR(2) NOT NULL DEFAULT 'AM',
  DSEI VARCHAR(180) NULL,
  POLO_BASE VARCHAR(180) NULL,
  UNIDADE_SAUDE_REFERENCIA VARCHAR(180) NULL,
  TIPO_UNIDADE_SAUDE VARCHAR(100) NULL,
  LATITUDE DECIMAL(10,7) NULL,
  LONGITUDE DECIMAL(10,7) NULL,
  COORDENADA_STATUS ENUM('NAO_INFORMADA','ESTIMADA','DOCUMENTAL','VALIDADA_CAMPO') NOT NULL DEFAULT 'NAO_INFORMADA',
  POPULACAO_ESTIMADA INT NULL,
  POPULACAO_FONTE TEXT NULL,
  RIO_PRINCIPAL VARCHAR(180) NULL,
  MODAL_ATUAL_ESTIMADO VARCHAR(180) NULL,
  TEMPO_FLUVIAL_ESTIMADO_HORAS DECIMAL(8,2) NULL,
  TEMPO_FLUVIAL_FONTE TEXT NULL,
  HUB_INICIAL_SUGERIDO VARCHAR(180) NULL,
  BASE_AVANCADA_SUGERIDA VARCHAR(180) NULL,
  PAYLOAD_PRIORITARIO VARCHAR(180) NULL,
  CRITICIDADE_SANITARIA ENUM('BAIXA','MEDIA','ALTA','CRITICA') NOT NULL DEFAULT 'MEDIA',
  CRITICIDADE_LOGISTICA ENUM('BAIXA','MEDIA','ALTA','CRITICA') NOT NULL DEFAULT 'MEDIA',
  VIABILIDADE_VTOL ENUM('BAIXA','MEDIA','ALTA','PENDENTE') NOT NULL DEFAULT 'PENDENTE',
  NECESSIDADE_BASE_LOCAL TINYINT NOT NULL DEFAULT 0,
  RISCO_REGULATORIO ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  RISCO_METEOROLOGICO ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  RISCO_TELECOM ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  OBSERVACOES TEXT NULL,
  STATUS_VALIDACAO ENUM('RASCUNHO','PESQUISADA','VALIDADA_DOCUMENTAL','VALIDADA_CAMPO','REJEITADA') NOT NULL DEFAULT 'RASCUNHO',
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_COM_CORREDOR (ID_CORREDOR),
  KEY IDX_COM_MUNICIPIO (MUNICIPIO),
  KEY IDX_COM_STATUS (STATUS_VALIDACAO),
  CONSTRAINT FK_COM_CORREDOR FOREIGN KEY (ID_CORREDOR) REFERENCES TB_CORREDORES (ID_CORREDOR) ON DELETE SET NULL,
  CONSTRAINT FK_COM_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_COM_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_NOS_LOGISTICOS (
  ID_NO INT AUTO_INCREMENT PRIMARY KEY,
  TIPO_NO ENUM('HUB_REGIONAL','BASE_AVANCADA','BASE_DERIVADA','PONTO_POUSO','UNIDADE_SAUDE','PONTO_COLETA','BASE_ENERGIA_TELECOM') NOT NULL,
  NOME VARCHAR(180) NOT NULL,
  MUNICIPIO VARCHAR(180) NULL,
  UF VARCHAR(2) NOT NULL DEFAULT 'AM',
  LATITUDE DECIMAL(10,7) NULL,
  LONGITUDE DECIMAL(10,7) NULL,
  FONTE TEXT NULL,
  INFRAESTRUTURA_EXISTENTE TEXT NULL,
  ENERGIA_EXISTENTE TEXT NULL,
  TELECOM_EXISTENTE TEXT NULL,
  SAUDE_EXISTENTE TEXT NULL,
  ACESSO_FLUVIAL TEXT NULL,
  ACESSO_AEREO TEXT NULL,
  RISCO_CHEIA ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  RISCO_SEGURANCA ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  CAPACIDADE_SER_BASE ENUM('BAIXA','MEDIA','ALTA','PENDENTE') NOT NULL DEFAULT 'PENDENTE',
  OBSERVACOES TEXT NULL,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_NO_TIPO (TIPO_NO),
  KEY IDX_NO_ATIVO (ATIVO),
  CONSTRAINT FK_NO_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_NO_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_ROTAS (
  ID_ROTA INT AUTO_INCREMENT PRIMARY KEY,
  ID_CORREDOR INT NULL,
  ORIGEM_TIPO ENUM('COMUNIDADE','NO_LOGISTICO') NOT NULL,
  ORIGEM_ID INT NOT NULL,
  DESTINO_TIPO ENUM('COMUNIDADE','NO_LOGISTICO') NOT NULL,
  DESTINO_ID INT NOT NULL,
  NOME_ROTA VARCHAR(220) NOT NULL,
  DISTANCIA_AEREA_KM DECIMAL(10,2) NULL,
  LIMITE_200KM_OK TINYINT NOT NULL DEFAULT 0,
  CLASSE_MISSAO ENUM('ZONA_A','ZONA_B','ZONA_C','ZONA_D','ZONA_E','FORA_LIMITE','PENDENTE') NOT NULL DEFAULT 'PENDENTE',
  PAYLOAD_RECOMENDADO_KG DECIMAL(8,2) NULL,
  TIPO_MISSAO VARCHAR(180) NULL,
  NECESSITA_BASE_INTERMEDIARIA TINYINT NOT NULL DEFAULT 0,
  NECESSITA_RECARGA TINYINT NOT NULL DEFAULT 0,
  NECESSITA_TROCA_PACK TINYINT NOT NULL DEFAULT 0,
  NECESSITA_AUTORIZACAO_BVLOS TINYINT NOT NULL DEFAULT 1,
  RISCO_OPERACIONAL ENUM('BAIXO','MEDIO','ALTO','CRITICO') NOT NULL DEFAULT 'MEDIO',
  PRIORIDADE ENUM('BAIXA','MEDIA','ALTA','CRITICA') NOT NULL DEFAULT 'MEDIA',
  JUSTIFICATIVA TEXT NULL,
  OBSERVACOES TEXT NULL,
  STATUS ENUM('RASCUNHO','CALCULADA','VALIDADA','REJEITADA') NOT NULL DEFAULT 'RASCUNHO',
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_ROTA_CORREDOR (ID_CORREDOR),
  KEY IDX_ROTA_CLASSE (CLASSE_MISSAO),
  KEY IDX_ROTA_ATIVO (ATIVO),
  CONSTRAINT FK_ROTA_CORREDOR FOREIGN KEY (ID_CORREDOR) REFERENCES TB_CORREDORES (ID_CORREDOR) ON DELETE SET NULL,
  CONSTRAINT FK_ROTA_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_ROTA_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_FONTES (
  ID_FONTE INT AUTO_INCREMENT PRIMARY KEY,
  TITULO VARCHAR(220) NOT NULL,
  ORGAO VARCHAR(180) NULL,
  TIPO_FONTE ENUM('OFICIAL','ACADEMICA','TECNICA','MAPA','NOTICIA','CAMPO','OUTRA') NOT NULL DEFAULT 'OFICIAL',
  URL TEXT NULL,
  DATA_ACESSO DATE NULL,
  TRECHO_RELEVANTE TEXT NULL,
  CONFIABILIDADE ENUM('BAIXA','MEDIA','ALTA','OFICIAL') NOT NULL DEFAULT 'MEDIA',
  OBSERVACOES TEXT NULL,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_FONTE_ATIVO (ATIVO),
  CONSTRAINT FK_FONTE_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_FONTE_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_ARQUIVOS (
  ID_ARQUIVO BIGINT AUTO_INCREMENT PRIMARY KEY,
  NOME_ORIGINAL VARCHAR(255) NOT NULL,
  NOME_ARMAZENADO VARCHAR(255) NOT NULL,
  CAMINHO_STORAGE TEXT NOT NULL,
  MIME_TYPE VARCHAR(180) NULL,
  EXTENSAO VARCHAR(20) NULL,
  TAMANHO_BYTES BIGINT NOT NULL,
  HASH_SHA256 VARCHAR(64) NULL,
  MODULO VARCHAR(100) NULL,
  ENTIDADE VARCHAR(100) NULL,
  ID_ENTIDADE VARCHAR(100) NULL,
  DESCRICAO TEXT NULL,
  VERSAO VARCHAR(30) NOT NULL DEFAULT '1.0',
  VISIBILIDADE ENUM('PRIVADO','INTERNO','PUBLICO') NOT NULL DEFAULT 'PRIVADO',
  STATUS ENUM('ATIVO','ARQUIVADO','REJEITADO') NOT NULL DEFAULT 'ATIVO',
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_ARQ_ATIVO (ATIVO),
  KEY IDX_ARQ_MOD (MODULO, ENTIDADE, ID_ENTIDADE),
  CONSTRAINT FK_ARQ_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_DOCUMENTOS (
  ID_DOCUMENTO INT AUTO_INCREMENT PRIMARY KEY,
  TITULO VARCHAR(220) NOT NULL,
  TIPO_DOCUMENTO VARCHAR(100) NULL,
  DESCRICAO TEXT NULL,
  STATUS ENUM('RASCUNHO','EM_REVISAO','APROVADO','PUBLICADO','ARQUIVADO') NOT NULL DEFAULT 'RASCUNHO',
  VERSAO VARCHAR(30) NOT NULL DEFAULT '0.1',
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_DOC_ATIVO (ATIVO),
  CONSTRAINT FK_DOC_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_DOC_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_DOCUMENTO_SECOES (
  ID_SECAO INT AUTO_INCREMENT PRIMARY KEY,
  ID_DOCUMENTO INT NOT NULL,
  ORDEM INT NOT NULL DEFAULT 1,
  TITULO VARCHAR(220) NULL,
  CONTEUDO_MEDIUMTEXT MEDIUMTEXT NULL,
  CRIADO_EM DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  CRIADO_POR INT NULL,
  ATUALIZADO_POR INT NULL,
  ATIVO TINYINT NOT NULL DEFAULT 1,
  KEY IDX_SEC_DOC (ID_DOCUMENTO),
  CONSTRAINT FK_SEC_DOC FOREIGN KEY (ID_DOCUMENTO) REFERENCES TB_DOCUMENTOS (ID_DOCUMENTO) ON DELETE CASCADE,
  CONSTRAINT FK_SEC_CRIADO FOREIGN KEY (CRIADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL,
  CONSTRAINT FK_SEC_ATUAL FOREIGN KEY (ATUALIZADO_POR) REFERENCES TB_USUARIOS (ID_USUARIO) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE TB_CONFIG_SISTEMA (
  ID_CONFIG INT AUTO_INCREMENT PRIMARY KEY,
  CHAVE VARCHAR(100) NOT NULL,
  VALOR TEXT NULL,
  DESCRICAO TEXT NULL,
  ATUALIZADO_EM DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY UK_CONFIG_CHAVE (CHAVE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;