-- =====================================================
-- PILF - Plataforma Integrada de Licitação Ferrari
-- Estrutura do Banco de Dados MySQL
-- =====================================================

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

-- =====================================================
-- TABELAS DE CONFIGURAÇÃO E PLANOS
-- =====================================================

-- Tabela de planos de assinatura
CREATE TABLE planos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    preco DECIMAL(10,2) NOT NULL,
    limite_editais INT DEFAULT NULL, -- NULL = ilimitado
    descricao TEXT,
    funcionalidades JSON,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Inserção dos planos padrão
INSERT INTO planos (nome, preco, limite_editais, descricao, funcionalidades) VALUES
('Básico', 99.00, 5, 'Ideal para empresas iniciantes', 
 JSON_OBJECT(
    'cadastro_inteligente', true,
    'busca_editais', true,
    'checklist_automatico', false,
    'geracao_documentos', false,
    'analise_ocr', false,
    'participacao_assistida', false,
    'suporte_24h', false,
    'api_access', false
 )),
('Avançado', 299.00, 20, 'Para empresas em crescimento',
 JSON_OBJECT(
    'cadastro_inteligente', true,
    'busca_editais', true,
    'checklist_automatico', true,
    'geracao_documentos', true,
    'analise_ocr', false,
    'participacao_assistida', false,
    'suporte_24h', false,
    'api_access', false
 )),
('Premium', 599.00, NULL, 'Solução completa para empresas estabelecidas',
 JSON_OBJECT(
    'cadastro_inteligente', true,
    'busca_editais', true,
    'checklist_automatico', true,
    'geracao_documentos', true,
    'analise_ocr', true,
    'participacao_assistida', true,
    'suporte_24h', true,
    'api_access', true
 ));

-- Tabela de empresas
CREATE TABLE empresas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cnpj VARCHAR(18) UNIQUE NOT NULL,
    razao_social VARCHAR(255) NOT NULL,
    nome_fantasia VARCHAR(255),
    inscricao_estadual VARCHAR(50),
    inscricao_municipal VARCHAR(50),
    endereco_completo TEXT,
    cep VARCHAR(10),
    cidade VARCHAR(100),
    estado VARCHAR(2),
    telefone VARCHAR(20),
    email VARCHAR(255),
    site VARCHAR(255),
    porte_empresa ENUM('MEI', 'ME', 'EPP', 'MEDIO', 'GRANDE') DEFAULT 'ME',
    ativa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_cnpj (cnpj),
    INDEX idx_ativa (ativa)
);

-- Tabela de áreas de interesse das empresas
CREATE TABLE areas_interesse (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    codigo VARCHAR(50),
    descricao TEXT,
    ativa BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inserção de áreas de interesse padrão
INSERT INTO areas_interesse (nome, codigo, descricao) VALUES
('Tecnologia da Informação', 'TI', 'Serviços e produtos de tecnologia'),
('Construção Civil', 'CONST', 'Obras e serviços de engenharia'),
('Serviços Gerais', 'SERV', 'Limpeza, segurança, manutenção'),
('Consultoria', 'CONS', 'Serviços de consultoria especializada'),
('Equipamentos', 'EQUIP', 'Fornecimento de equipamentos'),
('Materiais', 'MAT', 'Fornecimento de materiais diversos'),
('Saúde', 'SAUDE', 'Serviços e produtos de saúde'),
('Educação', 'EDU', 'Serviços educacionais'),
('Transporte', 'TRANSP', 'Serviços de transporte'),
('Alimentação', 'ALIM', 'Fornecimento de alimentação');

-- Tabela de relacionamento empresa-área de interesse
CREATE TABLE empresa_areas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_id INT NOT NULL,
    area_id INT NOT NULL,
    experiencia_anos INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES areas_interesse(id) ON DELETE CASCADE,
    UNIQUE KEY unique_empresa_area (empresa_id, area_id)
);

-- Tabela de assinaturas
CREATE TABLE assinaturas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_id INT NOT NULL,
    plano_id INT NOT NULL,
    usuario_responsavel_id INT NOT NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL,
    status ENUM('ATIVA', 'CANCELADA', 'SUSPENSA', 'VENCIDA') DEFAULT 'ATIVA',
    valor_mensal DECIMAL(10,2) NOT NULL,
    forma_pagamento VARCHAR(50),
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    FOREIGN KEY (plano_id) REFERENCES planos(id),
    FOREIGN KEY (usuario_responsavel_id) REFERENCES usuarios(id),
    INDEX idx_empresa_status (empresa_id, status),
    INDEX idx_data_fim (data_fim)
);

-- Tabela de relacionamento usuário-empresa
CREATE TABLE usuario_empresas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    empresa_id INT NOT NULL,
    papel ENUM('ADMIN', 'USUARIO', 'VISUALIZADOR') DEFAULT 'USUARIO',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_usuario_empresa (usuario_id, empresa_id)
);

-- =====================================================
-- TABELAS DE EDITAIS E LICITAÇÕES
-- =====================================================

-- Tabela de órgãos públicos
CREATE TABLE orgaos_publicos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    cnpj VARCHAR(18),
    esfera ENUM('FEDERAL', 'ESTADUAL', 'MUNICIPAL') NOT NULL,
    estado VARCHAR(2),
    cidade VARCHAR(100),
    site VARCHAR(255),
    email VARCHAR(255),
    telefone VARCHAR(20),
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_esfera (esfera),
    INDEX idx_estado (estado)
);

-- Tabela de editais
CREATE TABLE editais (
    id INT PRIMARY KEY AUTO_INCREMENT,
    numero_edital VARCHAR(100) NOT NULL,
    orgao_id INT NOT NULL,
    titulo VARCHAR(500) NOT NULL,
    descricao TEXT,
    modalidade ENUM('PREGAO_ELETRONICO', 'PREGAO_PRESENCIAL', 'CONCORRENCIA', 'TOMADA_PRECOS', 'CONVITE', 'CONCURSO', 'LEILAO') NOT NULL,
    tipo_objeto ENUM('SERVICOS', 'MATERIAIS', 'OBRAS', 'SERVICOS_ENGENHARIA') NOT NULL,
    valor_estimado DECIMAL(15,2),
    data_publicacao DATE NOT NULL,
    data_abertura DATETIME,
    data_limite_proposta DATETIME,
    prazo_execucao VARCHAR(100),
    local_execucao VARCHAR(255),
    url_edital VARCHAR(500),
    url_portal VARCHAR(500),
    status ENUM('PUBLICADO', 'EM_ANDAMENTO', 'FINALIZADO', 'CANCELADO', 'SUSPENSO') DEFAULT 'PUBLICADO',
    areas_relacionadas JSON, -- Array de IDs das áreas
    palavras_chave TEXT,
    criterio_julgamento ENUM('MENOR_PRECO', 'MELHOR_TECNICA', 'TECNICA_PRECO', 'MAIOR_LANCE') DEFAULT 'MENOR_PRECO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (orgao_id) REFERENCES orgaos_publicos(id),
    INDEX idx_data_publicacao (data_publicacao),
    INDEX idx_data_abertura (data_abertura),
    INDEX idx_status (status),
    INDEX idx_modalidade (modalidade),
    INDEX idx_tipo_objeto (tipo_objeto),
    FULLTEXT idx_titulo_descricao (titulo, descricao)
);

-- Tabela de documentos do edital
CREATE TABLE edital_documentos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    edital_id INT NOT NULL,
    nome_arquivo VARCHAR(255) NOT NULL,
    tipo_documento ENUM('EDITAL', 'ANEXO', 'MINUTA_CONTRATO', 'TERMO_REFERENCIA', 'OUTROS') NOT NULL,
    url_download VARCHAR(500),
    tamanho_arquivo BIGINT,
    hash_arquivo VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (edital_id) REFERENCES editais(id) ON DELETE CASCADE,
    INDEX idx_edital_tipo (edital_id, tipo_documento)
);

-- =====================================================
-- TABELAS DE PARTICIPAÇÃO EM LICITAÇÕES
-- =====================================================

-- Tabela de interesse em editais
CREATE TABLE empresa_editais (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_id INT NOT NULL,
    edital_id INT NOT NULL,
    status_interesse ENUM('INTERESSADO', 'PARTICIPANDO', 'DESISTIU', 'FINALIZADO') DEFAULT 'INTERESSADO',
    score_compatibilidade DECIMAL(5,2), -- 0.00 a 100.00
    data_interesse TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    FOREIGN KEY (edital_id) REFERENCES editais(id) ON DELETE CASCADE,
    UNIQUE KEY unique_empresa_edital (empresa_id, edital_id),
    INDEX idx_status_interesse (status_interesse),
    INDEX idx_score (score_compatibilidade)
);

-- Tabela de propostas
CREATE TABLE propostas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_edital_id INT NOT NULL,
    numero_proposta VARCHAR(100),
    valor_proposta DECIMAL(15,2),
    prazo_execucao VARCHAR(100),
    data_submissao TIMESTAMP,
    status ENUM('RASCUNHO', 'ENVIADA', 'ACEITA', 'REJEITADA', 'CLASSIFICADA', 'DESCLASSIFICADA') DEFAULT 'RASCUNHO',
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_edital_id) REFERENCES empresa_editais(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_data_submissao (data_submissao)
);

-- =====================================================
-- TABELAS DE DOCUMENTOS E CHECKLIST
-- =====================================================

-- Tabela de templates de documentos
CREATE TABLE templates_documentos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    tipo ENUM('TERMO_REFERENCIA', 'PROPOSTA_TECNICA', 'PROPOSTA_COMERCIAL', 'HABILITACAO', 'OUTROS') NOT NULL,
    conteudo_template LONGTEXT NOT NULL,
    variaveis JSON, -- Variáveis que podem ser substituídas
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_tipo (tipo),
    INDEX idx_ativo (ativo)
);

-- Tabela de documentos gerados
CREATE TABLE documentos_gerados (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_edital_id INT NOT NULL,
    template_id INT,
    nome_documento VARCHAR(255) NOT NULL,
    tipo_documento ENUM('TERMO_REFERENCIA', 'PROPOSTA_TECNICA', 'PROPOSTA_COMERCIAL', 'HABILITACAO', 'OUTROS') NOT NULL,
    conteudo LONGTEXT,
    versao INT DEFAULT 1,
    status ENUM('RASCUNHO', 'REVISAO', 'APROVADO', 'ENVIADO') DEFAULT 'RASCUNHO',
    caminho_arquivo VARCHAR(500),
    gerado_por_ia BOOLEAN DEFAULT FALSE,
    prompt_ia TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_edital_id) REFERENCES empresa_editais(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES templates_documentos(id),
    INDEX idx_tipo_status (tipo_documento, status),
    INDEX idx_empresa_edital (empresa_edital_id)
);

-- Tabela de checklists
CREATE TABLE checklists (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_edital_id INT NOT NULL,
    nome VARCHAR(255) NOT NULL,
    descricao TEXT,
    gerado_automaticamente BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_edital_id) REFERENCES empresa_editais(id) ON DELETE CASCADE,
    INDEX idx_empresa_edital (empresa_edital_id)
);

-- Tabela de itens do checklist
CREATE TABLE checklist_itens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    checklist_id INT NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT,
    fase ENUM('HABILITACAO', 'PROPOSTA_TECNICA', 'PROPOSTA_COMERCIAL', 'DOCUMENTACAO', 'SUBMISSAO') NOT NULL,
    obrigatorio BOOLEAN DEFAULT TRUE,
    concluido BOOLEAN DEFAULT FALSE,
    data_conclusao TIMESTAMP NULL,
    observacoes TEXT,
    ordem INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (checklist_id) REFERENCES checklists(id) ON DELETE CASCADE,
    INDEX idx_checklist_fase (checklist_id, fase),
    INDEX idx_concluido (concluido)
);

-- =====================================================
-- TABELAS DE ANÁLISE OCR E IA
-- =====================================================

-- Tabela de análises OCR
CREATE TABLE analises_ocr (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_edital_id INT NOT NULL,
    nome_arquivo VARCHAR(255) NOT NULL,
    caminho_arquivo VARCHAR(500) NOT NULL,
    tipo_documento VARCHAR(100),
    texto_extraido LONGTEXT,
    confianca_ocr DECIMAL(5,2), -- 0.00 a 100.00
    status_analise ENUM('PENDENTE', 'PROCESSANDO', 'CONCLUIDO', 'ERRO') DEFAULT 'PENDENTE',
    resultado_analise JSON, -- Estrutura com erros encontrados, sugestões, etc.
    tempo_processamento INT, -- em segundos
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_edital_id) REFERENCES empresa_editais(id) ON DELETE CASCADE,
    INDEX idx_status (status_analise),
    INDEX idx_empresa_edital (empresa_edital_id)
);

-- Tabela de logs de IA
CREATE TABLE logs_ia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_id INT NOT NULL,
    tipo_operacao ENUM('GERACAO_DOCUMENTO', 'ANALISE_EDITAL', 'CHECKLIST_AUTOMATICO', 'ANALISE_OCR', 'BUSCA_EDITAIS') NOT NULL,
    prompt_enviado TEXT,
    resposta_ia LONGTEXT,
    tokens_utilizados INT,
    tempo_resposta INT, -- em milissegundos
    modelo_utilizado VARCHAR(100),
    custo_operacao DECIMAL(10,4),
    sucesso BOOLEAN DEFAULT TRUE,
    erro_mensagem TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    INDEX idx_tipo_operacao (tipo_operacao),
    INDEX idx_empresa_data (empresa_id, created_at),
    INDEX idx_sucesso (sucesso)
);

-- =====================================================
-- TABELAS DE NOTIFICAÇÕES E COMUNICAÇÃO
-- =====================================================

-- Tabela de notificações
CREATE TABLE notificacoes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    empresa_id INT,
    tipo ENUM('NOVO_EDITAL', 'PRAZO_VENCENDO', 'DOCUMENTO_GERADO', 'ANALISE_CONCLUIDA', 'SISTEMA', 'MARKETING') NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    mensagem TEXT NOT NULL,
    lida BOOLEAN DEFAULT FALSE,
    data_leitura TIMESTAMP NULL,
    url_acao VARCHAR(500),
    prioridade ENUM('BAIXA', 'MEDIA', 'ALTA', 'URGENTE') DEFAULT 'MEDIA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    INDEX idx_usuario_lida (usuario_id, lida),
    INDEX idx_tipo_prioridade (tipo, prioridade),
    INDEX idx_created_at (created_at)
);

-- Tabela de configurações de notificação
CREATE TABLE configuracoes_notificacao (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    tipo_notificacao ENUM('NOVO_EDITAL', 'PRAZO_VENCENDO', 'DOCUMENTO_GERADO', 'ANALISE_CONCLUIDA', 'SISTEMA', 'MARKETING') NOT NULL,
    email_ativo BOOLEAN DEFAULT TRUE,
    sms_ativo BOOLEAN DEFAULT FALSE,
    push_ativo BOOLEAN DEFAULT TRUE,
    whatsapp_ativo BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    UNIQUE KEY unique_usuario_tipo (usuario_id, tipo_notificacao)
);

-- =====================================================
-- TABELAS DE AUDITORIA E LOGS
-- =====================================================

-- Tabela de auditoria
CREATE TABLE auditoria (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT,
    empresa_id INT,
    tabela_afetada VARCHAR(100) NOT NULL,
    registro_id INT NOT NULL,
    acao ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    dados_anteriores JSON,
    dados_novos JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE SET NULL,
    INDEX idx_tabela_registro (tabela_afetada, registro_id),
    INDEX idx_usuario_data (usuario_id, created_at),
    INDEX idx_empresa_data (empresa_id, created_at)
);

-- Tabela de sessões de usuário
CREATE TABLE sessoes_usuario (
    id VARCHAR(128) PRIMARY KEY,
    usuario_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    dados_sessao JSON,
    ativa BOOLEAN DEFAULT TRUE,
    ultimo_acesso TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    INDEX idx_usuario_ativa (usuario_id, ativa),
    INDEX idx_expires_at (expires_at)
);

-- =====================================================
-- TABELAS DE RELATÓRIOS E ANALYTICS
-- =====================================================

-- Tabela de métricas de uso
CREATE TABLE metricas_uso (
    id INT PRIMARY KEY AUTO_INCREMENT,
    empresa_id INT NOT NULL,
    data_referencia DATE NOT NULL,
    editais_visualizados INT DEFAULT 0,
    documentos_gerados INT DEFAULT 0,
    analises_ocr_realizadas INT DEFAULT 0,
    checklists_criados INT DEFAULT 0,
    tempo_plataforma_minutos INT DEFAULT 0,
    funcionalidades_utilizadas JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (empresa_id) REFERENCES empresas(id) ON DELETE CASCADE,
    UNIQUE KEY unique_empresa_data (empresa_id, data_referencia),
    INDEX idx_data_referencia (data_referencia)
);

-- =====================================================
-- VIEWS ÚTEIS
-- =====================================================

-- View para empresas com assinatura ativa
CREATE VIEW empresas_ativas AS
SELECT 
    e.*,
    a.plano_id,
    p.nome as plano_nome,
    a.data_inicio,
    a.data_fim,
    a.valor_mensal
FROM empresas e
JOIN assinaturas a ON e.id = a.empresa_id
JOIN planos p ON a.plano_id = p.id
WHERE a.status = 'ATIVA' AND a.data_fim >= CURDATE();

-- View para editais com score de compatibilidade
CREATE VIEW editais_com_score AS
SELECT 
    e.*,
    ee.empresa_id,
    ee.score_compatibilidade,
    ee.status_interesse,
    emp.razao_social as empresa_nome
FROM editais e
LEFT JOIN empresa_editais ee ON e.id = ee.edital_id
LEFT JOIN empresas emp ON ee.empresa_id = emp.id;

-- View para dashboard de métricas
CREATE VIEW dashboard_metricas AS
SELECT 
    e.id as empresa_id,
    e.razao_social,
    COUNT(DISTINCT ee.edital_id) as total_editais_interesse,
    COUNT(DISTINCT CASE WHEN ee.status_interesse = 'PARTICIPANDO' THEN ee.edital_id END) as editais_participando,
    COUNT(DISTINCT dg.id) as documentos_gerados,
    COUNT(DISTINCT ao.id) as analises_ocr,
    AVG(ee.score_compatibilidade) as score_medio
FROM empresas e
LEFT JOIN empresa_editais ee ON e.id = ee.empresa_id
LEFT JOIN documentos_gerados dg ON ee.id = dg.empresa_edital_id
LEFT JOIN analises_ocr ao ON ee.id = ao.empresa_edital_id
WHERE e.ativa = TRUE
GROUP BY e.id, e.razao_social;
END//

-- Trigger para auditoria de empresas
CREATE TRIGGER tr_empresas_audit_insert AFTER INSERT ON empresas
FOR EACH ROW
BEGIN
    INSERT INTO auditoria (tabela_afetada, registro_id, acao, dados_novos)
    VALUES ('empresas', NEW.id, 'INSERT', JSON_OBJECT(
        'cnpj', NEW.cnpj,
        'razao_social', NEW.razao_social,
        'ativa', NEW.ativa
    ));
END//

CREATE TRIGGER tr_empresas_audit_update AFTER UPDATE ON empresas
FOR EACH ROW
BEGIN
    INSERT INTO auditoria (tabela_afetada, registro_id, acao, dados_anteriores, dados_novos)
    VALUES ('empresas', NEW.id, 'UPDATE', 
        JSON_OBJECT('cnpj', OLD.cnpj, 'razao_social', OLD.razao_social, 'ativa', OLD.ativa),
        JSON_OBJECT('cnpj', NEW.cnpj, 'razao_social', NEW.razao_social, 'ativa', NEW.ativa)
    );
END//

DELIMITER ;

-- =====================================================
-- ÍNDICES ADICIONAIS PARA PERFORMANCE
-- =====================================================

-- Índices compostos para consultas frequentes
CREATE INDEX idx_empresa_editais_status_score ON empresa_editais (empresa_id, status_interesse, score_compatibilidade);
CREATE INDEX idx_editais_data_status ON editais (data_publicacao, status, modalidade);
CREATE INDEX idx_documentos_empresa_tipo ON documentos_gerados (empresa_edital_id, tipo_documento, status);
CREATE INDEX idx_notificacoes_usuario_tipo_lida ON notificacoes (usuario_id, tipo, lida, created_at);

-- =====================================================
-- PROCEDURES ÚTEIS
-- =====================================================

DELIMITER //

-- Procedure para calcular score de compatibilidade
CREATE PROCEDURE CalcularScoreCompatibilidade(
    IN p_empresa_id INT,
    IN p_edital_id INT,
    OUT p_score DECIMAL(5,2)
)
BEGIN
    DECLARE v_areas_empresa INT DEFAULT 0;
    DECLARE v_areas_comuns INT DEFAULT 0;
    DECLARE v_experiencia_media DECIMAL(5,2) DEFAULT 0;
    
    -- Contar áreas da empresa
    SELECT COUNT(*) INTO v_areas_empresa
    FROM empresa_areas ea
    WHERE ea.empresa_id = p_empresa_id;
    
    -- Contar áreas em comum (simulação - seria baseado nas áreas do edital)
    SELECT COUNT(*), AVG(ea.experiencia_anos) 
    INTO v_areas_comuns, v_experiencia_media
    FROM empresa_areas ea
    WHERE ea.empresa_id = p_empresa_id;
    
    -- Calcular score (fórmula simplificada)
    IF v_areas_empresa > 0 THEN
        SET p_score = LEAST(100, (v_areas_comuns / v_areas_empresa * 60) + (v_experiencia_media * 4));
    ELSE
        SET p_score = 0;
    END IF;
END//

-- Procedure para limpeza de dados antigos
CREATE PROCEDURE LimparDadosAntigos()
BEGIN
    -- Limpar sessões expiradas
    DELETE FROM sessoes_usuario WHERE expires_at < NOW();
    
    -- Limpar notificações antigas (mais de 6 meses)
    DELETE FROM notificacoes WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH) AND lida = TRUE;
    
    -- Limpar logs de auditoria antigos (mais de 2 anos)
    DELETE FROM auditoria WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);
    
    -- Limpar logs de IA antigos (mais de 1 ano)
    DELETE FROM logs_ia WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END//

DELIMITER ;

-- =====================================================
-- CONFIGURAÇÕES FINAIS
-- =====================================================

-- Configurar timezone
SET time_zone = '-03:00'; -- Horário de Brasília

-- Configurar charset padrão
ALTER DATABASE pilf_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE empresas COMMENT = 'Tabela de empresas cadastradas';
ALTER TABLE editais COMMENT = 'Tabela de editais de licitação';
ALTER TABLE planos COMMENT = 'Tabela de planos de assinatura';
ALTER TABLE assinaturas COMMENT = 'Tabela de assinaturas ativas das empresas';

-- =====================================================
-- FIM DO SCRIPT
-- =====================================================

