#DEIXA BASE DE DADOS COMPATÍVEL COM MariaDB
ALTER DATABASE qualifica_brasil
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

# TABELAS COM X CONTRAINTS

DROP TABLE IF EXISTS titulos_imagens; # sobra?
DROP TABLE IF EXISTS tokens_ocupacoes;
DROP TABLE IF EXISTS tokens_imagens; # VPM 20250322: identifiquei que os tokens que aparecem uma única vez estão em certa de 10% dos titulos. Eles são mais representativos dos titulos e vou usar como fonte das imagens, relacionando com titulos atraves de campos_titulos e frases

DROP TABLE IF EXISTS `frases_opt`;
DROP TABLE IF EXISTS `classificacoes_tokens`;

DROP TABLE IF EXISTS `instancias_cursos`;
DROP TABLE IF EXISTS `campos_cursos`;
# TABELAS COM X CONTRAINTS

DROP TABLE IF EXISTS `enderecos_institucionais`;
DROP TABLE IF EXISTS `enderecos_cursos`;
DROP TABLE IF EXISTS `frases`;
DROP TABLE IF EXISTS `titulos`;


# TABELAS COM X CONTRAINTS

DROP TABLE IF EXISTS `localizacoes`;
DROP TABLE IF EXISTS `cursos`;
DROP TABLE IF EXISTS `tokens_em_avaliacao`;

DROP TABLE IF EXISTS `URLs`;
DROP TABLE IF EXISTS `tokens`;


# TABELAS COM X CONTRAINT

DROP TABLE IF EXISTS `cidades`;
DROP TABLE IF EXISTS `estados`;
DROP TABLE IF EXISTS `tipos_classificacoes`;
DROP TABLE IF EXISTS imagens;
DROP TABLE IF EXISTS `tipos_cursos`;
DROP TABLE IF EXISTS `segmentos`;
DROP TABLE IF EXISTS `tipos_tokens`;



# TABELAS SEM CONSTRAINTS

DROP TABLE IF EXISTS `paises`;
DROP TABLE IF EXISTS `tipos_elementos_sintaticos`;
DROP TABLE IF EXISTS `tipos_flexoes`;
DROP TABLE IF EXISTS `paises`;
DROP TABLE IF EXISTS arquivos_fonte;
DROP TABLE IF EXISTS `tipos_arquivos`;
DROP TABLE IF EXISTS `instituicoes`;
DROP TABLE IF EXISTS `tipos_instituicoes`;
DROP TABLE IF EXISTS `esferas_institucionais`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `esferas_institucionais` (
  `id_chave_esfera_institucional` int NOT NULL AUTO_INCREMENT,
  `nome_esfera_institucional` varchar(40) NOT NULL,
  `descricao` text,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_esfera_institucional`),
  UNIQUE KEY `nome_esfera_institucional` (`nome_esfera_institucional`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE URLs (
    id_chave_url INT NOT NULL AUTO_INCREMENT,
    nome_url VARCHAR(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    nome_url_hash CHAR(64) GENERATED ALWAYS AS (SHA2(nome_url, 256)) STORED,
    valida ENUM('S', 'N') DEFAULT 'N', -- Melhor do que VARCHAR(3)
    id_arquivo_fonte INT,
    data_teste DATE, -- Melhor do que VARCHAR(10)
    comentarios TEXT,
    PRIMARY KEY (id_chave_url),
    UNIQUE (nome_url_hash)
    #atenção: o add constraint relativo a arquivos_fonte mais adiante
);

# SELECT * FROM URLs WHERE nome_url_hash = UNHEX(SHA2('https://exemplo.com', 256));
# SELECT * FROM URLs WHERE nome_url_hash = UNHEX(SHA2('https://exemplo.com', 256));
# SELECT * FROM URLs WHERE nome_url LIKE '%exemplo%';


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `paises` (
  `id_chave_pais` int NOT NULL AUTO_INCREMENT,
  `nome_pais` varchar(255) DEFAULT NULL,
  `codigo_iso` varchar(3),
  `nome_pais_iso3166_2` VARCHAR(255) NOT NULL UNIQUE,   
  `codigo_alpha2` CHAR(2) NOT NULL UNIQUE,             
  `codigo_alpha3` CHAR(3) NOT NULL UNIQUE,            
  `codigo_numerico` CHAR(3) NOT NULL UNIQUE,    
  `populacao_estimada` bigint DEFAULT NULL,
  `area_total` decimal(12,2) DEFAULT NULL,
  `densidade_demografica` decimal(10,2) DEFAULT NULL,
  `capital` varchar(100) ,
  `regiao` varchar(100) ,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_pais`),
  UNIQUE KEY `codigo_iso` (`codigo_iso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;



/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_elementos_sintaticos` (
  `id_chave_tipo_elemento_sintatico` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_elemento_sintatico` varchar(255) NOT NULL,
  `descricao` text NOT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_tipo_elemento_sintatico`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_flexoes` (
  `id_chave_tipo_flexao` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_flexao` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `acentuada` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `data_insercao` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id_chave_tipo_flexao`),
  UNIQUE KEY `nome_tipo_flexao` (`nome_tipo_flexao`),
  UNIQUE KEY `acentuada` (`acentuada`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_classificacoes` (
  `id_chave_tipo_classificacao` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_classificacao` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `acentuada` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ordem` int DEFAULT 1,
  `caption_substituto` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `serve_para_busca` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "sim",
  `mostra_caption` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "sim",
  `mostra_valor` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "sim",
  `verso` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "nao",
  `mostra_caption_tamanho` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "16px", 
  `mostra_valor_tamanho` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "16px", 
  `bold_valor` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "normal",
  `color_valor`VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "white",
  `border_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "none",
  `border_radius_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `background_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "none",
  `isolamento_valor` VARCHAR(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "",
  `padding_left_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `padding_right_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `padding_top_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `padding_bottom_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `margin_left_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `margin_right_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `margin_top_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `margin_bottom_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "0px",
  `caracter_depois_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "",
  `forca_quebra_linha` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "nao",
  `junta_caption_valor` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "nao",
  `limita_largura` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT "nao_limita",
  id_arquivo_fonte int,
  `data_insercao` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_tipo_classificacao`)
  # atenção ao ADD CONSTRAINT relativo a arquivos_fonte mais adiante
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
# nao_mostra não mostra nada... 

CREATE INDEX idx_nome_tipo_classificacao ON tipos_classificacoes (nome_tipo_classificacao);

# TABELAS COM X CONTRAINT


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `estados` (
  `id_chave_estado` int NOT NULL AUTO_INCREMENT,
  `nome_estado` varchar(255) NOT NULL,
  `categoria` VARCHAR(50) NOT NULL,
  `codigo_iso3166_2` VARCHAR(10) NOT NULL UNIQUE,
  `codigo_estado` CHAR(2) NOT NULL UNIQUE,
  `idioma` VARCHAR(50) NOT NULL,
  `codigo_pais` CHAR(2) NOT NULL,
  `sigla_estado` char(2) ,
  `codigo_ibge` int ,
  `regiao` varchar(100) DEFAULT NULL,
  `populacao_estimada` int DEFAULT NULL,
  `area_total` decimal(10,2) DEFAULT NULL,
  `densidade_demografica` decimal(8,2) DEFAULT NULL,
  `capital` varchar(100) DEFAULT NULL,
  `id_pais` int DEFAULT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_estado`),
  KEY `id_pais` (`id_pais`),
  UNIQUE(`codigo_ibge`),
  CONSTRAINT `estados_ibfk_1` FOREIGN KEY (`id_pais`) REFERENCES `paises` (`id_chave_pais`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cidades` (
  `id_chave_cidade` int NOT NULL AUTO_INCREMENT,
  `codigo_ibge` varchar(30) DEFAULT NULL, 
  `nome_cidade` varchar(255) NOT NULL,
  `eh_cidade` varchar(3) DEFAULT "sim",
  `cidade_sem_acentuacao` varchar(300) NOT NULL,
  `id_estado` int NOT NULL,
  `populacao` int DEFAULT NULL,
  `area_km2` decimal(10,2) DEFAULT NULL,
  `densidade_demografica` VARCHAR(200) DEFAULT NULL,
  `latitude` decimal(9,6) DEFAULT NULL,
  `longitude` decimal(9,6) DEFAULT NULL,
  `gentilico` varchar(100) DEFAULT NULL,
  `idhm` varchar(200) DEFAULT NULL,
  `idhm_decimal` varchar(200),
  `escolarizacao` varchar(200) DEFAULT NULL,
  `mortalidade_infantil` varchar(200) DEFAULT NULL,
  `pib_per_capita` varchar(200) DEFAULT NULL,
  `pib_per_capita_decimal` varchar(200) DEFAULT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_cidade`),
  UNIQUE KEY `codigo_ibge` (`codigo_ibge`),
  KEY `id_estado` (`id_estado`),
  CONSTRAINT `cidades_ibfk_1` FOREIGN KEY (`id_estado`) REFERENCES `estados` (`codigo_ibge`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE INDEX idx_lat_long ON cidades (latitude, longitude);

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_instituicoes` (
  `id_chave_tipo_instituicao` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_instituicao` varchar(255) NOT NULL,
  `descricao` text,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `id_esfera_institucional` int DEFAULT NULL,
  PRIMARY KEY (`id_chave_tipo_instituicao`),
  UNIQUE KEY `nome_tipo_instituicao` (`nome_tipo_instituicao`),
  KEY `id_esfera_institucional` (`id_esfera_institucional`),
  CONSTRAINT `tipos_instituicoes_ibfk_1` FOREIGN KEY (`id_esfera_institucional`) REFERENCES `esferas_institucionais` (`id_chave_esfera_institucional`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_arquivos` (
  `id_chave_tipo_arquivo` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_arquivo` varchar(255) NOT NULL,
  extensao varchar(10) DEFAULT NULL,
  `descricao` text,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_tipo_arquivo`),
  UNIQUE KEY `nome_tipo_arquivo` (`nome_tipo_arquivo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO tipos_arquivos (nome_tipo_arquivo, extensao, descricao) VALUES
('Imagem JPEG', 'jpg', 'image/jpeg'),
('Imagem PNG', 'png', 'image/png'),
('Imagem SVG', 'svg', 'image/svg+xml'),
('Imagem WebP', 'webp', 'image/webp'),
('Arquivo PDF', 'pdf', 'application/pdf'),
('HTML', 'html', 'text/html; charset=UTF-8'),
('Texto simples', 'txt', 'text/plain'),
('JSON', 'json', 'application/json'),
('XML', 'xml', 'application/xml ou text/xml'),
('Arquivo ZIP', 'zip', 'application/zip'),
('Arquivo JavaScript', 'js', 'application/javascript'),
('Arquivo CSS', 'css', 'text/css'),
('MP3', 'mp3', 'audio/mpeg'),
('MP4', 'mp4', 'video/mp4');



/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `instituicoes` (
  `id_chave_instituicao` int NOT NULL AUTO_INCREMENT,
  `nome_instituicao` varchar(255) NOT NULL,
  `sigla` varchar(10) DEFAULT NULL,
  `identificador` varchar(10) DEFAULT NULL UNIQUE,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `id_tipo_instituicao` int DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `largura_logo_instituicao` VARCHAR(30) DEFAULT "20%",
  PRIMARY KEY (`id_chave_instituicao`),
  UNIQUE KEY `nome_instituicao` (`nome_instituicao`),
  KEY `id_tipo_instituicao` (`id_tipo_instituicao`),
  CONSTRAINT `instituicoes_ibfk_1` FOREIGN KEY (`id_tipo_instituicao`) REFERENCES `tipos_instituicoes` (`id_chave_tipo_instituicao`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE TABLE arquivos_fonte (
	id_chave_arquivo_fonte int not null auto_increment,
	nome_arquivo_fonte_SQL varchar(500),
	nome_arquivo_fonte_CSV varchar(500),
	data_entrada VARCHAR(50), # data de origem do arquivo AAAAMMDD
	fornecedor VARCHAR(200),
	descricao TEXT,
	id_instituicao int,
	#id_tipo_arquivo int, não faz sentido porque há dois tipos de arquivos como fonte: a "fonte" (normalmente SQL) e a "fonte da fonte" (normalmente CSV) 
	primary key (id_chave_arquivo_fonte),
	unique(nome_arquivo_fonte_SQL),
	unique(nome_arquivo_fonte_CSV),
	CONSTRAINT arquivos_fonte_ibfk_1 FOREIGN KEY (id_instituicao) REFERENCES instituicoes(id_chave_instituicao)
	#CONSTRAINT arquivos_fonte_ibfk_2 FOREIGN KEY (id_tipo_arquivo) REFERENCES tipos_arquivos(id_chave_tipo_arquivo)
);

ALTER TABLE `tipos_classificacoes`
ADD CONSTRAINT `tipos_classificacoes_ibfk_1`
FOREIGN KEY (`id_arquivo_fonte`)
REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`);

ALTER TABLE tipos_classificacoes
ADD CONSTRAINT tipos_classificacoes_csv UNIQUE (nome_tipo_classificacao, id_arquivo_fonte);


ALTER TABLE `URLs`
ADD CONSTRAINT `URLs_ibfk_1`
FOREIGN KEY (`id_arquivo_fonte`)
REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`);




CREATE TABLE imagens (
	id_chave_imagem int not null auto_increment,
	nome_imagem varchar(500)  COLLATE utf8mb4_bin, # para deixar a tabela sensível a acentuação e case sensitive PORQUE este campo È UM NOME DE ARQUIVO
	nome_imagem_tratada VARCHAR(500) COLLATE utf8mb4_bin, # Nome do arquivo com tamanho, razao de aspecto e tratamento adequados
	id_tipo_arquivo int,
	primary key (id_chave_imagem),
	unique(nome_imagem),
	CONSTRAINT imagens_ibfk_1 FOREIGN KEY (id_tipo_arquivo) REFERENCES tipos_arquivos(id_chave_tipo_arquivo)
);




/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_cursos` (
  `id_chave_tipo_curso` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_curso` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  id_arquivo_fonte int,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_tipo_curso`),
  UNIQUE (`nome_tipo_curso`),
  CONSTRAINT `tipos_cursos_ibfk_1` FOREIGN KEY (`id_arquivo_fonte`) REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `segmentos` (
  `id_chave_segmento` int NOT NULL AUTO_INCREMENT,
  `nome_segmento` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_segmento`),
  UNIQUE (`nome_segmento`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cursos` (
  `id_chave_curso` int NOT NULL AUTO_INCREMENT,
  `nome_curso` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id_tipo_curso` int DEFAULT NULL,
   `id_instituicao` INT DEFAULT NULL,
   `id_cidade` INT DEFAULT NULL,
   `id_url` INT DEFAULT NULL,
   id_arquivo_fonte int,
  `data_insercao` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id_chave_curso`),
  UNIQUE KEY `nome_curso` (`nome_curso`), 
  CONSTRAINT instituicoes_ibfk_3 FOREIGN KEY (id_instituicao) REFERENCES instituicoes (id_chave_instituicao),
  CONSTRAINT `cursos_ibfk_2` FOREIGN KEY (`id_tipo_curso`) REFERENCES `tipos_cursos` (`id_chave_tipo_curso`),
  CONSTRAINT `cursos_cidades` FOREIGN KEY (`id_cidade`) REFERENCES `cidades` (`id_chave_cidade`),
  CONSTRAINT `cursos_url` FOREIGN KEY (`id_url`) REFERENCES `URLs` (`id_chave_url`),
  CONSTRAINT `cursos_arquivos_fonte` FOREIGN KEY (`id_arquivo_fonte`) REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tipos_tokens` (
  `id_chave_tipo_token` int NOT NULL AUTO_INCREMENT,
  `nome_tipo_token` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `acentuada` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tipo_token_pai` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `data_insercao` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id_chave_tipo_token`),
  UNIQUE KEY `nome_tipo_token` (`nome_tipo_token`),
  KEY `tipo_token_pai` (`tipo_token_pai`),
  CONSTRAINT `tipos_tokens_ibfk_1` FOREIGN KEY (`tipo_token_pai`) REFERENCES `tipos_tokens` (`nome_tipo_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

create table classificacoes_tokens (
	id_chave_classificacao_token int not null auto_increment,
	nome_token varchar(200) not null,
	id_tipo_token int not null,
	caracter_antes varchar(10) default "",
	caracter_depois varchar(10) default " ",
	id_arquivo_fonte int,
	data_insercao timestamp null default current_timestamp,
	primary key (id_chave_classificacao_token),
	unique key nome_token (nome_token), # o correto seria cria um índice com nome_token e id_tipo_token, mas o sistema não consegue discrimiar a função sintática do token e não teria como fazer uma busca pelos dois campos
	CONSTRAINT classificacoes_tokens_ibfk_1 FOREIGN KEY (id_tipo_token) REFERENCES tipos_tokens (id_chave_tipo_token),
	CONSTRAINT classificacoes_tokens_ibfk_2 FOREIGN KEY (id_arquivo_fonte) REFERENCES arquivos_fonte (id_chave_arquivo_fonte)
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_general_ci;
ALTER TABLE classificacoes_tokens MODIFY nome_token VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL; #para deixar a tabela sensível a acentuação

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `localizacoes` (
  `id_chave_localizacao` int NOT NULL AUTO_INCREMENT,
  `latitude` decimal(9,6) NOT NULL,
  `longitude` decimal(9,6) NOT NULL,
  `bounding_box_lat_min` decimal(9,6) DEFAULT NULL,
  `bounding_box_lat_max` decimal(9,6) DEFAULT NULL,
  `bounding_box_long_min` decimal(9,6) DEFAULT NULL,
  `bounding_box_long_max` decimal(9,6) DEFAULT NULL,
  `display_name` text NOT NULL,
  `road` varchar(255) DEFAULT NULL,
  `neighbourhood` varchar(255) DEFAULT NULL,
  `suburb` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `postcode` varchar(20) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `country_code` varchar(2) DEFAULT NULL,
  `id_cidade` int DEFAULT NULL,
  `id_estado` int DEFAULT NULL,
  `id_pais` int DEFAULT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_localizacao`),
  KEY `id_cidade` (`id_cidade`),
  KEY `id_estado` (`id_estado`),
  KEY `id_pais` (`id_pais`),
  CONSTRAINT `localizacoes_ibfk_1` FOREIGN KEY (`id_cidade`) REFERENCES `cidades` (`id_chave_cidade`),
  CONSTRAINT `localizacoes_ibfk_2` FOREIGN KEY (`id_estado`) REFERENCES `estados` (`id_chave_estado`),
  CONSTRAINT `localizacoes_ibfk_3` FOREIGN KEY (`id_pais`) REFERENCES `paises` (`id_chave_pais`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;



# TABELAS COM X CONTRAINTS

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `enderecos_institucionais` (
  `id_chave_endereco_institucional` int NOT NULL AUTO_INCREMENT,
  `id_instituicao` int DEFAULT NULL,
  `id_localizacao` int NOT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_endereco_institucional`),
  KEY `id_instituicao` (`id_instituicao`),
  KEY `id_localizacao` (`id_localizacao`),
  CONSTRAINT `enderecos_institucionais_ibfk_1` FOREIGN KEY (`id_instituicao`) REFERENCES `instituicoes` (`id_chave_instituicao`),
  CONSTRAINT `enderecos_institucionais_ibfk_2` FOREIGN KEY (`id_localizacao`) REFERENCES `localizacoes` (`id_chave_localizacao`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `enderecos_cursos` (
  `id_chave_endereco_curso` int NOT NULL AUTO_INCREMENT,
  `id_instituicao` int DEFAULT NULL,
  `id_localizacao` int NOT NULL,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_endereco_curso`),
  KEY `id_instituicao` (`id_instituicao`),
  KEY `id_localizacao` (`id_localizacao`),
  CONSTRAINT `enderecos_cursos_ibfk_1` FOREIGN KEY (`id_instituicao`) REFERENCES `instituicoes` (`id_chave_instituicao`),
  CONSTRAINT `enderecos_cursos_ibfk_2` FOREIGN KEY (`id_localizacao`) REFERENCES `localizacoes` (`id_chave_localizacao`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;



# TABELAS COM X CONTRAINTS

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tokens` (
  `id_chave_token` int NOT NULL AUTO_INCREMENT,
  `nome_token` varchar(200) NOT NULL,
  `acentuada` varchar(200) DEFAULT NULL,
  `id_tipo_token` int DEFAULT NULL,
  `id_tipo_token_spaCy` int DEFAULT NULL,
  `id_raiz` int DEFAULT NULL,
  `id_tipo_flexao` int DEFAULT NULL,
  `data_insercao` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `caracter_antes` varchar(10) COLLATE utf8mb4_general_ci DEFAULT '',
  `caracter_depois` varchar(10) COLLATE utf8mb4_general_ci DEFAULT ' ',
  id_arquivo_fonte int,
  PRIMARY KEY (`id_chave_token`),
  #UNIQUE KEY `acentuada` (`acentuada`,`id_tipo_token`),
  UNIQUE KEY `acentuada` (`acentuada`,`id_tipo_token_spaCy`),
  KEY `id_tipo_token` (`id_tipo_token`),
  KEY `id_raiz` (`id_raiz`),
  KEY `id_tipo_flexao` (`id_tipo_flexao`),
  CONSTRAINT `fk_raiz_token` FOREIGN KEY (`id_raiz`) REFERENCES `tokens` (`id_chave_token`),
  CONSTRAINT `fk_tipo_flexao` FOREIGN KEY (`id_tipo_flexao`) REFERENCES `tipos_flexoes` (`id_chave_tipo_flexao`),
  CONSTRAINT `fk_tipo_token` FOREIGN KEY (`id_tipo_token`) REFERENCES `tipos_tokens` (`id_chave_tipo_token`),
  CONSTRAINT `fk_tipo_token_spacy` FOREIGN KEY (`id_tipo_token_spaCy`) REFERENCES `tipos_tokens` (`id_chave_tipo_token`),
  CONSTRAINT `fk_arquivo_fonte` FOREIGN KEY (`id_arquivo_fonte`) REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
ALTER TABLE tokens MODIFY acentuada VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL; #para deixar a tabela sensível a acentuação
#ALTER TABLE tokens MODIFY nome_token VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; -- importante para permitir diferenciar acentuação, como crase, que dá tokens diferentes. No select é preciso mudar a collation para uma que não diferencia acentuação

-- SELECT * FROM tokens WHERE nome_token COLLATE utf8mb4_unicode_ci LIKE '%autogestão%';
-- SELECT * FROM tokens WHERE nome_token COLLATE utf8mb4_general_ci = 'à autogestão';

CREATE TABLE titulos (
	id_chave_titulo INT NOT NULL AUTO_INCREMENT,
	nome_titulo VARCHAR(255) NOT NULL,
	id_tipo_elemento_sintatico INT DEFAULT NULL,
	id_arquivo_fonte INT DEFAULT NULL,
	data_insercao TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id_chave_titulo),
	UNIQUE KEY nome_titulo (nome_titulo),
	CONSTRAINT titulos_ibfk_2 FOREIGN KEY (id_tipo_elemento_sintatico) REFERENCES tipos_elementos_sintaticos (id_chave_tipo_elemento_sintatico),
	CONSTRAINT titulos_ibfk_3 FOREIGN KEY (id_arquivo_fonte) REFERENCES arquivos_fonte (id_chave_arquivo_fonte)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE tokens_imagens (
	id_chave_token_imagem int not null auto_increment,
	nome_token_imagem varchar(500),
	id_imagem int,
	id_token int,
	CONSTRAINT imagem_token FOREIGN KEY (id_imagem) REFERENCES imagens(id_chave_imagem),
	CONSTRAINT token_imagem FOREIGN KEY (id_token) REFERENCES tokens(id_chave_token),
	PRIMARY KEY (id_chave_token_imagem),
        UNIQUE(id_imagem, id_token)
);


/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `frases` (
  `id_chave_frase` int NOT NULL AUTO_INCREMENT,
  `nome_frase` varchar(255) DEFAULT NULL,
  `id_token` int DEFAULT NULL,
  `ordem` int DEFAULT NULL,
  `id_titulo` int DEFAULT NULL,
  id_arquivo_fonte int,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_frase`),
  UNIQUE KEY `id_token` (`nome_frase`),
  CONSTRAINT `frases_ibfk_1` FOREIGN KEY (`id_token`) REFERENCES `tokens` (`id_chave_token`),
  CONSTRAINT `frases_ibfk_3` FOREIGN KEY (`id_titulo`) REFERENCES `titulos` (`id_chave_titulo`),
  CONSTRAINT `frases_ibfk_4` FOREIGN KEY (`id_arquivo_fonte`) REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE INDEX idx_id_titulo ON frases (id_titulo);

# TABLES COM X CONTRAINTS

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `instancias_cursos` (
  `id_chave_instancia_curso` int NOT NULL AUTO_INCREMENT,
  `id_localizacao` int DEFAULT NULL,
  `id_curso` int DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `data_instancia_curso` date NOT NULL,
  `hora_instancia_curso` time NOT NULL,
  `descricao` text,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_chave_instancia_curso`),
  KEY `id_localizacao` (`id_localizacao`),
  KEY `id_curso` (`id_curso`),
  CONSTRAINT `instancias_cursos_ibfk_3` FOREIGN KEY (`id_localizacao`) REFERENCES `localizacoes` (`id_chave_localizacao`),
  CONSTRAINT `instancias_cursos_ibfk_4` FOREIGN KEY (`id_curso`) REFERENCES `cursos` (`id_chave_curso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `campos_cursos` (
  `id_chave_campo_curso` int NOT NULL AUTO_INCREMENT,
  `nome_campo_curso` varchar(255) NOT NULL,
  `id_curso` int DEFAULT NULL,
  `id_titulo` int DEFAULT NULL,
  `id_tipo_classificacao` INT DEFAULT NULL,
  `descricao` text,
  `data_insercao` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  id_arquivo_fonte int,
  PRIMARY KEY (`id_chave_campo_curso`),
  UNIQUE KEY `nome_campo_curso` (`nome_campo_curso`),
  CONSTRAINT `campos_cursos_ibfk_2` FOREIGN KEY (`id_curso`) REFERENCES `cursos` (`id_chave_curso`),
  CONSTRAINT campos_cursos_ibfk1 FOREIGN KEY (id_tipo_classificacao) REFERENCES tipos_classificacoes (id_chave_tipo_classificacao),
  CONSTRAINT `campos_cursos_ibfk_3` FOREIGN KEY (`id_titulo`) REFERENCES `titulos` (`id_chave_titulo`),
  CONSTRAINT `campos_cursos_ibfk_4` FOREIGN KEY (`id_arquivo_fonte`) REFERENCES `arquivos_fonte` (`id_chave_arquivo_fonte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
CREATE INDEX idx_curso_tipo_classificacao ON campos_cursos (id_curso, id_tipo_classificacao);
CREATE INDEX idx_id_titulo ON campos_cursos (id_titulo);


-- Criação da tabela frases_opt para MySQL
CREATE TABLE frases_opt (
    id_chave_frase_opt INT AUTO_INCREMENT PRIMARY KEY,
    id_titulo INT UNIQUE,
    id_arquivo_fonte INT,
    titulo TEXT,
    CONSTRAINT fk_frases_opt_titulos FOREIGN KEY (id_titulo) REFERENCES titulos(id_chave_titulo),
    CONSTRAINT fk_frases_opt_arquivos_fonte FOREIGN KEY (id_arquivo_fonte) REFERENCES arquivos_fonte(id_chave_arquivo_fonte)
);

CREATE INDEX idx_ordem ON tipos_classificacoes (ordem);
CREATE INDEX idx_cidades_lat_long ON cidades(latitude, longitude);
CREATE INDEX idx_cidades_estado ON cidades(id_estado);
CREATE INDEX idx_estados_codigo_ibge ON estados(codigo_ibge);
CREATE INDEX idx_cc_id_curso ON campos_cursos(id_curso);
CREATE INDEX idx_cc_id_titulo ON campos_cursos(id_titulo);
CREATE INDEX idx_cc_tipo_classificacao ON campos_cursos(id_tipo_classificacao);
CREATE INDEX idx_frases_opt_id_titulo ON frases_opt(id_titulo);
CREATE INDEX idx_frases_opt_titulo ON frases_opt(titulo);
CREATE INDEX idx_tc_id_chave_tipo_classificacao ON tipos_classificacoes(id_chave_tipo_classificacao);
CREATE INDEX idx_tc_ordem ON tipos_classificacoes(ordem);
CREATE INDEX idx_cursos_id_chave_curso ON cursos(id_chave_curso);
CREATE INDEX idx_cursos_id_instituicao ON cursos(id_instituicao);
CREATE INDEX idx_instituicoes_id_chave_instituicao ON instituicoes(id_chave_instituicao);




