
-- Criação do banco de dados
CREATE DATABASE IF NOT EXISTS gerenciador_curriculos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gerenciador_curriculos;

-- Tabela de grupos
CREATE TABLE grupos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    cor VARCHAR(7) DEFAULT '#3498db',
    usuario_id INT NOT NULL,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

-- Tabela de etiquetas
CREATE TABLE etiquetas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(100) NOT NULL,
    cor VARCHAR(7) DEFAULT '#95a5a6',
    usuario_id INT NOT NULL,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    UNIQUE KEY unique_etiqueta_usuario (nome, usuario_id)
);

-- Tabela principal de currículos
CREATE TABLE curriculos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome_completo VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    telefone VARCHAR(20) NULL,
    celular VARCHAR(20) NULL,
    data_nascimento DATE NULL,
    endereco TEXT NULL,
    cidade VARCHAR(100) NULL,
    estado VARCHAR(50) NULL,
    cep VARCHAR(10) NULL,
    objetivo TEXT NULL,
    resumo TEXT NULL,
    linkedin VARCHAR(255) NULL,
    github VARCHAR(255) NULL,
    website VARCHAR(255) NULL,
    foto VARCHAR(255) NULL,
    usuario_criador_id INT NOT NULL,
    grupo_id INT NULL,
    status ENUM('novo', 'triagem', 'aprovado', 'rejeitado', 'entrevista', 'contratado') DEFAULT 'novo',
    pontuacao DECIMAL(3,2) DEFAULT 0.00,
    observacoes TEXT NULL,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_criador_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (grupo_id) REFERENCES grupos(id) ON DELETE SET NULL,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_data_criacao (data_criacao),
    INDEX idx_pontuacao (pontuacao)
);

-- Tabela de experiências profissionais
CREATE TABLE experiencias (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    empresa VARCHAR(255) NOT NULL,
    cargo VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NULL,
    atual BOOLEAN DEFAULT FALSE,
    salario DECIMAL(10,2) NULL,
    tipo ENUM('clt', 'pj', 'estagio', 'freelance', 'outros') DEFAULT 'clt',
    area VARCHAR(100) NULL,
    ordem_exibicao INT DEFAULT 0,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    INDEX idx_curriculo_ordem (curriculo_id, ordem_exibicao)
);

-- Tabela de formação acadêmica
CREATE TABLE formacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    instituicao VARCHAR(255) NOT NULL,
    curso VARCHAR(255) NOT NULL,
    nivel ENUM('ensino_medio', 'tecnico', 'superior', 'pos_graduacao', 'mestrado', 'doutorado') NOT NULL,
    area VARCHAR(100) NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NULL,
    concluido BOOLEAN DEFAULT FALSE,
    nota DECIMAL(4,2) NULL,
    ordem_exibicao INT DEFAULT 0,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    INDEX idx_curriculo_nivel (curriculo_id, nivel)
);

-- Tabela de habilidades
CREATE TABLE habilidades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    nome VARCHAR(255) NOT NULL,
    nivel ENUM('basico', 'intermediario', 'avancado', 'expert') DEFAULT 'intermediario',
    tipo ENUM('tecnica', 'comportamental', 'idioma') DEFAULT 'tecnica',
    certificado BOOLEAN DEFAULT FALSE,
    anos_experiencia INT DEFAULT 0,
    ordem_exibicao INT DEFAULT 0,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    INDEX idx_curriculo_tipo (curriculo_id, tipo)
);

-- Tabela de idiomas
CREATE TABLE idiomas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    idioma VARCHAR(100) NOT NULL,
    nivel_fala ENUM('basico', 'intermediario', 'avancado', 'fluente', 'nativo') DEFAULT 'intermediario',
    nivel_escrita ENUM('basico', 'intermediario', 'avancado', 'fluente', 'nativo') DEFAULT 'intermediario',
    nivel_leitura ENUM('basico', 'intermediario', 'avancado', 'fluente', 'nativo') DEFAULT 'intermediario',
    certificacao VARCHAR(255) NULL,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_curriculo_idioma (curriculo_id, idioma)
);

-- Tabela de arquivos anexos
CREATE TABLE arquivos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    nome_original VARCHAR(255) NOT NULL,
    nome_arquivo VARCHAR(255) NOT NULL,
    tipo_mime VARCHAR(100) NOT NULL,
    tamanho INT NOT NULL,
    tipo ENUM('curriculo', 'foto', 'certificado', 'portfolio', 'outros') DEFAULT 'curriculo',
    data_upload TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    INDEX idx_curriculo_tipo (curriculo_id, tipo)
);

-- Tabela de marcações/anotações
CREATE TABLE marcacoes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    usuario_id INT NOT NULL,
    tipo ENUM('favorito', 'interesse', 'rejeitado', 'entrevistado', 'contratado') NOT NULL,
    observacao TEXT NULL,
    data_marcacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    UNIQUE KEY unique_marcacao (curriculo_id, usuario_id, tipo)
);

-- Tabela de relacionamento currículos-etiquetas
CREATE TABLE curriculo_etiquetas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    etiqueta_id INT NOT NULL,
    data_aplicacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    FOREIGN KEY (etiqueta_id) REFERENCES etiquetas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_curriculo_etiqueta (curriculo_id, etiqueta_id)
);

-- Tabela de histórico de alterações
CREATE TABLE historico_alteracoes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    curriculo_id INT NOT NULL,
    usuario_id INT NOT NULL,
    acao VARCHAR(100) NOT NULL,
    detalhes JSON NULL,
    data_alteracao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (curriculo_id) REFERENCES curriculos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    INDEX idx_curriculo_data (curriculo_id, data_alteracao)
);

-- Grupos
INSERT INTO grupos (nome, descricao, cor, usuario_id) VALUES
('Desenvolvedores', 'Candidatos para vagas de desenvolvimento', '#3498db', 1),
('Designers', 'Candidatos para vagas de design', '#e74c3c', 1),
('Marketing', 'Candidatos para vagas de marketing', '#f39c12', 2),
('Vendas', 'Candidatos para área comercial', '#2ecc71', 2);

-- Etiquetas
INSERT INTO etiquetas (nome, cor, usuario_id) VALUES
('Urgente', '#e74c3c', 1),
('Experiente', '#27ae60', 1),
('Junior', '#3498db', 2),
('Freelancer', '#f39c12', 2),
('Remoto', '#9b59b6', 3);

-- Currículos de exemplo
INSERT INTO curriculos (nome_completo, email, telefone, celular, data_nascimento, endereco, cidade, estado, cep, objetivo, resumo, linkedin, usuario_criador_id, grupo_id, status, pontuacao) VALUES
('Ana Costa', 'ana.costa@email.com', '(11) 3333-3333', '(11) 99999-1111', '1990-05-15', 'Rua das Flores, 123', 'São Paulo', 'SP', '01234-567', 'Busco oportunidade como desenvolvedora full-stack', 'Desenvolvedora com 5 anos de experiência em PHP, JavaScript e MySQL', 'https://linkedin.com/in/anacosta', 1, 1, 'aprovado', 8.50),
('Carlos Oliveira', 'carlos@email.com', '(11) 4444-4444', '(11) 99999-2222', '1985-12-10', 'Av. Paulista, 1000', 'São Paulo', 'SP', '01310-100', 'Designer UX/UI sênior', 'Designer com expertise em Figma, Adobe Creative Suite e prototipagem', 'https://linkedin.com/in/carlosoliveira', 2, 2, 'entrevista', 9.20),
('Fernanda Lima', 'fernanda@email.com', '(21) 5555-5555', '(21) 99999-3333', '1992-08-22', 'Rua Copacabana, 500', 'Rio de Janeiro', 'RJ', '22070-000', 'Analista de Marketing Digital', 'Especialista em campanhas Google Ads e Facebook Ads com ROI comprovado', 'https://linkedin.com/in/fernandalima', 3, 3, 'novo', 7.80);

-- Experiências
INSERT INTO experiencias (curriculo_id, empresa, cargo, descricao, data_inicio, data_fim, atual, tipo, area, ordem_exibicao) VALUES
(1, 'Tech Solutions Ltda', 'Desenvolvedora Full-stack', 'Desenvolvimento de aplicações web com PHP, Laravel, JavaScript e MySQL', '2019-03-01', NULL, TRUE, 'clt', 'Tecnologia', 1),
(1, 'StartupXYZ', 'Desenvolvedora Junior', 'Manutenção e desenvolvimento de features em WordPress', '2018-01-15', '2019-02-28', FALSE, 'clt', 'Tecnologia', 2),
(2, 'Agência Criativa', 'Designer UX/UI', 'Criação de interfaces e experiências digitais para clientes diversos', '2020-06-01', NULL, TRUE, 'clt', 'Design', 1),
(2, 'Freelancer', 'Designer Gráfico', 'Projetos de identidade visual e materiais gráficos', '2017-09-01', '2020-05-30', FALSE, 'freelance', 'Design', 2);

-- Formação
INSERT INTO formacao (curriculo_id, instituicao, curso, nivel, area, data_inicio, data_fim, concluido, ordem_exibicao) VALUES
(1, 'Universidade de São Paulo', 'Ciência da Computação', 'superior', 'Tecnologia', '2015-02-01', '2018-12-15', TRUE, 1),
(1, 'Alura', 'Desenvolvimento Web Full-stack', 'tecnico', 'Tecnologia', '2018-01-01', '2018-06-30', TRUE, 2),
(2, 'FAAP', 'Design Gráfico', 'superior', 'Design', '2013-02-01', '2016-12-15', TRUE, 1),
(2, 'Google', 'UX Design Certificate', 'pos_graduacao', 'Design', '2019-03-01', '2019-12-15', TRUE, 2);

-- Habilidades
INSERT INTO habilidades (curriculo_id, nome, nivel, tipo, certificado, anos_experiencia, ordem_exibicao) VALUES
(1, 'PHP', 'avancado', 'tecnica', TRUE, 5, 1),
(1, 'JavaScript', 'avancado', 'tecnica', FALSE, 5, 2),
(1, 'MySQL', 'avancado', 'tecnica', TRUE, 4, 3),
(1, 'Laravel', 'intermediario', 'tecnica', TRUE, 3, 4),
(1, 'Trabalho em equipe', 'avancado', 'comportamental', FALSE, 5, 5),
(2, 'Figma', 'expert', 'tecnica', TRUE, 4, 1),
(2, 'Adobe Photoshop', 'avancado', 'tecnica', TRUE, 6, 2),
(2, 'UX Research', 'avancado', 'tecnica', TRUE, 3, 3),
(2, 'Criatividade', 'expert', 'comportamental', FALSE, 6, 4);

-- Idiomas
INSERT INTO idiomas (curriculo_id, idioma, nivel_fala, nivel_escrita, nivel_leitura, certificacao) VALUES
(1, 'Inglês', 'intermediario', 'avancado', 'avancado', 'TOEFL 85'),
(1, 'Espanhol', 'basico', 'basico', 'intermediario', NULL),
(2, 'Inglês', 'fluente', 'fluente', 'fluente', 'Cambridge C1'),
(2, 'Francês', 'intermediario', 'intermediario', 'avancado', NULL);

-- Marcações
INSERT INTO marcacoes (curriculo_id, usuario_id, tipo, observacao) VALUES
(1, 1, 'favorito', 'Candidata com excelente perfil técnico'),
(1, 2, 'interesse', 'Agendar entrevista técnica'),
(2, 2, 'favorito', 'Portfolio impressionante'),
(3, 3, 'interesse', 'Experiência relevante em marketing digital');

-- Relacionamentos currículos-etiquetas
INSERT INTO curriculo_etiquetas (curriculo_id, etiqueta_id) VALUES
(1, 2), -- Ana Costa - Experiente
(1, 5), -- Ana Costa - Remoto
(2, 2), -- Carlos - Experiente
(2, 1), -- Carlos - Urgente
(3, 3), -- Fernanda - Junior
(3, 4); -- Fernanda - Freelancer

-- Criação de índices adicionais para performance
CREATE INDEX idx_curriculos_search ON curriculos(nome_completo, email, telefone);
CREATE INDEX idx_experiencias_empresa ON experiencias(empresa);
CREATE INDEX idx_formacao_instituicao ON formacao(instituicao);
CREATE INDEX idx_habilidades_nome ON habilidades(nome);
