CREATE DATABASE habitacao CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE habitacao;

-- ============================================
-- RESPONSÁVEL
-- ============================================
CREATE TABLE inscrito (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
    cpf VARCHAR(14) NOT NULL,
    rg VARCHAR(20),
	email VARCHAR(100),
    sexo ENUM('F','M'),
    arrimo TINYINT DEFAULT 0,
    data_nascimento DATE,
    estado_civil ENUM('solteiro','casado','uniao_estavel','separado','viuvo'),
    cor_raca ENUM('amarela','branca','indigena','parda','preta'),
    escolaridade ENUM('analfabeto','fundamental_incompleto','fundamental_completo','medio_incompleto','medio_completo','superior_incompleto','superior_completo','pos_graduacao'),
    profissao VARCHAR(100),
    tipo_trabalho ENUM('formal','informal','desempregado'),
    renda_mensal DECIMAL(10,2),
    tipo_deficiencia TEXT,
    recebe_bpc TINYINT NOT NULL DEFAULT 0,
    tipo_bpc VARCHAR(100),
	protocolo VARCHAR(20) UNIQUE,
	data_envio DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================
-- MEMBROS DA FAMÍLIA
-- ============================================
CREATE TABLE membro_familiar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inscrito_id INT NOT NULL,
    nome VARCHAR(150),
    sexo ENUM('F','M'),
    data_nascimento DATE,
    parentesco VARCHAR(50),
    escolaridade ENUM('analfabeto','fundamental_incompleto','fundamental_completo','medio_incompleto','medio_completo','superior_incompleto','superior_completo','pos_graduacao'),
    ocupacao VARCHAR(100),
    renda DECIMAL(10,2),
    possui_deficiencia TINYINT NOT NULL DEFAULT 0,
    tipo_deficiencia TEXT,
    FOREIGN KEY (inscrito_id) REFERENCES inscrito(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- DADOS DA MORADIA
-- ============================================
CREATE TABLE moradia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inscrito_id INT NOT NULL,
    tipo_imovel ENUM('apartamento','casa','outro'),
    situacao_imovel ENUM('proprio_quitado','financiado','alugado','cedido','barraco'),
    numero_comodos INT,
    numero_pessoas INT,
    abastecimento_agua ENUM('rede_geral','poco','cisterna','outra'),
    esgoto ENUM('rede_geral','fossa','ceu_aberto'),
    energia_eletrica TINYINT NOT NULL DEFAULT 0,
    gasto_energia DECIMAL(10,2),
    gasto_agua DECIMAL(10,2),
    gasto_aluguel DECIMAL(10,2),
    gasto_transporte DECIMAL(10,2),
	outros_gastos DECIMAL(10,2),
    tarifa_social TINYINT NOT NULL DEFAULT 0,
    tempo_moradia_municipio INT COMMENT 'Tempo em anos',
    FOREIGN KEY (inscrito_id) REFERENCES inscrito(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- RENDA FAMILIAR
-- ============================================
CREATE TABLE renda_familiar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inscrito_id INT NOT NULL,
    fonte_principal ENUM('trabalho_formal','trabalho_informal','beneficio_social','aposentadoria','outra'),
    participa_programa_social TINYINT NOT NULL DEFAULT 0,
    FOREIGN KEY (inscrito_id) REFERENCES inscrito(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================
-- PROGRAMA
-- ============================================
CREATE TABLE programa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(150) NOT NULL,
	descricao TEXT,
	status TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- ============================================
-- PROGRAMA/INSCRITO
-- ============================================
CREATE TABLE inscrito_programa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inscrito_id INT NOT NULL,
    programa_id INT NOT NULL,
    status ENUM('ativo','pendente','aprovado','rejeitado','cancelado') DEFAULT 'pendente',
    data_inscricao DATETIME DEFAULT CURRENT_TIMESTAMP,
    data_saida DATETIME NULL,

    FOREIGN KEY (inscrito_id) REFERENCES inscrito(id) ON DELETE CASCADE,
    FOREIGN KEY (programa_id) REFERENCES programa(id) ON DELETE CASCADE
) ENGINE=InnoDB;


-- ============================================
-- USUÁRIO
-- ============================================
CREATE TABLE usuario (
	idUsuario INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	nome VARCHAR(100) NOT NULL,
	email VARCHAR(150) NOT NULL UNIQUE,
	login VARCHAR(50) NOT NULL UNIQUE,
	senha VARCHAR(255) NOT NULL,
	status TINYINT NOT NULL DEFAULT 0,
	grupo ENUM('admin','editor') NOT NULL DEFAULT 'editor',
	consentimento TINYINT NOT NULL DEFAULT 0,
	tokenRecuperacao VARCHAR(64) DEFAULT NULL,
	tokenExpira DATETIME DEFAULT NULL,
	dataCad TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	excluido TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO usuario (nome, email, login, senha, status, grupo, consentimento) VALUES ('Tecnologia da Informação', 'ti@cordeiropolis.sp.gov.br', 'ti', '$2y$10$cBe35yYz/IamHtPiSiD53.BP16nyIgV9fwPVLjqCenQG9wcvTbdzi', 1, 'admin', 1);


-- ============================================
-- LOG
-- ============================================
CREATE TABLE log (
    idLog INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    idUsuario INT UNSIGNED NOT NULL,
    tabela VARCHAR(50) NOT NULL,
    idRegistro INT UNSIGNED NOT NULL,
    acao ENUM('insert', 'update', 'delete', 'restore', 'access', 'toggle_status', 'login', 'restore_insert', 'restore_update', 'restore_delete', 'restore_status') NOT NULL,
    dadosAntes JSON NULL,
    dadosDepois JSON NULL,
    data TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (idUsuario) REFERENCES usuario(idUsuario) ON DELETE CASCADE
) ENGINE=InnoDB;