-- Script SQL para criar as tabelas do sistema de pesquisas

-- Tabela de pesquisas
CREATE TABLE IF NOT EXISTS pesquisas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    id_questionario_template_fk INT NOT NULL,
    id_projeto_fk INT NULL,
    idbase_fk INT NULL,
    id_modelo_departamento_fk INT NULL,
    id_modelo_faixa_funcionario_fk INT NULL,
    id_modelo_classificacao_lead_fk INT NULL,
    id_modelo_contato_status_fk INT NULL,
    data_pesquisa DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'ativa', -- 'ativa', 'encerrada'
    descricao TEXT,
    liberado_cliente CHAR(1) DEFAULT 'N', -- 'S' para liberado, 'N' para não liberado
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_questionario_template_fk) REFERENCES questionario_template(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modelos de departamentos (padrões reutilizáveis por pesquisa)
CREATE TABLE IF NOT EXISTS departamentos_modelos (
    id_modelo INT AUTO_INCREMENT PRIMARY KEY,
    nome_modelo VARCHAR(120) NOT NULL UNIQUE,
    ativo CHAR(1) DEFAULT 'S',
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS departamentos_modelos_itens (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_modelo_fk INT NOT NULL,
    nome_departamento VARCHAR(120) NOT NULL,
    ativo CHAR(1) DEFAULT 'S',
    ordem INT DEFAULT 0,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_modelo_departamento (id_modelo_fk, nome_departamento),
    FOREIGN KEY (id_modelo_fk) REFERENCES departamentos_modelos(id_modelo) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modelos de faixas de funcionários (padrões reutilizáveis por pesquisa)
CREATE TABLE IF NOT EXISTS faixa_funcionarios_modelos (
    id_modelo INT AUTO_INCREMENT PRIMARY KEY,
    nome_modelo VARCHAR(120) NOT NULL UNIQUE,
    ativo CHAR(1) DEFAULT 'S',
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS faixa_funcionarios_modelos_itens (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_modelo_fk INT NOT NULL,
    nome_faixa VARCHAR(120) NOT NULL,
    quantidade_minima INT NOT NULL,
    quantidade_maxima INT NOT NULL,
    ativo CHAR(1) DEFAULT 'S',
    ordem INT DEFAULT 0,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_modelo_faixa_nome (id_modelo_fk, nome_faixa),
    FOREIGN KEY (id_modelo_fk) REFERENCES faixa_funcionarios_modelos(id_modelo) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modelos de classificação de leads (padrões reutilizáveis por pesquisa)
CREATE TABLE IF NOT EXISTS classificacao_leads_modelos (
    id_modelo INT AUTO_INCREMENT PRIMARY KEY,
    nome_modelo VARCHAR(120) NOT NULL UNIQUE,
    ativo CHAR(1) DEFAULT 'S',
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS classificacao_leads_modelos_itens (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_modelo_fk INT NOT NULL,
    faixa_minima DECIMAL(10,2) NOT NULL,
    faixa_maxima DECIMAL(10,2) NOT NULL,
    classificacao VARCHAR(50) NOT NULL,
    cor VARCHAR(20) DEFAULT '#cccccc',
    ordem INT NOT NULL DEFAULT 0,
    ativo CHAR(1) DEFAULT 'S',
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_modelo_classificacao_nome (id_modelo_fk, classificacao),
    FOREIGN KEY (id_modelo_fk) REFERENCES classificacao_leads_modelos(id_modelo) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modelos de status do contato (padrões reutilizáveis por pesquisa)
CREATE TABLE IF NOT EXISTS contato_status_modelos (
    id_modelo INT AUTO_INCREMENT PRIMARY KEY,
    nome_modelo VARCHAR(120) NOT NULL UNIQUE,
    ativo CHAR(1) DEFAULT 'S',
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contato_status_modelos_itens (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_modelo_fk INT NOT NULL,
    contato_status VARCHAR(120) NOT NULL,
    ativo CHAR(1) DEFAULT 'S',
    ordem INT DEFAULT 0,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_modelo_contato_status (id_modelo_fk, contato_status),
    FOREIGN KEY (id_modelo_fk) REFERENCES contato_status_modelos(id_modelo) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE pesquisas
    ADD CONSTRAINT fk_pesquisa_modelo_departamento
    FOREIGN KEY (id_modelo_departamento_fk) REFERENCES departamentos_modelos(id_modelo)
    ON DELETE SET NULL;

ALTER TABLE pesquisas
    ADD CONSTRAINT fk_pesquisa_modelo_faixa_funcionario
    FOREIGN KEY (id_modelo_faixa_funcionario_fk) REFERENCES faixa_funcionarios_modelos(id_modelo)
    ON DELETE SET NULL;

ALTER TABLE pesquisas
    ADD CONSTRAINT fk_pesquisa_modelo_classificacao_lead
    FOREIGN KEY (id_modelo_classificacao_lead_fk) REFERENCES classificacao_leads_modelos(id_modelo)
    ON DELETE SET NULL;

ALTER TABLE pesquisas
    ADD CONSTRAINT fk_pesquisa_modelo_contato_status
    FOREIGN KEY (id_modelo_contato_status_fk) REFERENCES contato_status_modelos(id_modelo)
    ON DELETE SET NULL;

-- Tabela de respostas por empresa
CREATE TABLE IF NOT EXISTS pesquisa_empresa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_pesquisa_fk INT NOT NULL,
    id_empresa_fk INT NOT NULL,
    data_resposta TIMESTAMP NULL,
    responsavel_pesquisa VARCHAR(255),
    id_status_questionario_fk INT NULL,
    status VARCHAR(20) DEFAULT 'pendente', -- 'pendente', 'respondida', 'cancelada'
    nota_total DECIMAL(10,2) DEFAULT 0,
    classificacao VARCHAR(50),
    quem_entrevistou VARCHAR(255),
    data_entrevista DATE,
    hora_entrevista TIME,
    agendamento_data_retorno DATE,
    agendamento_hora_retorno TIME,
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pesquisa_fk) REFERENCES pesquisas(id) ON DELETE CASCADE,
    FOREIGN KEY (id_empresa_fk) REFERENCES empresas(ID_EMPRESA) ON DELETE CASCADE,
    FOREIGN KEY (id_status_questionario_fk) REFERENCES statusdoquestionario(id_status) ON DELETE SET NULL,
    UNIQUE KEY unique_pesquisa_empresa (id_pesquisa_fk, id_empresa_fk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de respostas às perguntas
CREATE TABLE IF NOT EXISTS pesquisa_respostas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_pesquisa_empresa_fk INT NOT NULL,
    id_pergunta_fk INT NOT NULL,
    resposta TEXT,
    data_resposta TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pesquisa_empresa_fk) REFERENCES pesquisa_empresa(id) ON DELETE CASCADE,
    FOREIGN KEY (id_pergunta_fk) REFERENCES questionario_template_perguntas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de contatos para as pesquisas
CREATE TABLE IF NOT EXISTS pesquisa_contatos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_pesquisa_empresa_fk INT NOT NULL,
    id_contato_empresa_fk INT NOT NULL,
    nome VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    telefone VARCHAR(20),
    area VARCHAR(100),
    status VARCHAR(20) DEFAULT 'ativo', -- 'ativo', 'inativo'
    data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_pesquisa_empresa_fk) REFERENCES pesquisa_empresa(id) ON DELETE CASCADE,
    FOREIGN KEY (id_contato_empresa_fk) REFERENCES contatos_empresa(idcontatos_empresa) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Índices para melhorar performance
CREATE INDEX idx_pesquisa_template ON pesquisas(id_questionario_template_fk);
CREATE INDEX idx_pesquisa_projeto ON pesquisas(id_projeto_fk);
CREATE INDEX idx_pesquisa_base ON pesquisas(idbase_fk);
CREATE INDEX idx_pesquisa_status ON pesquisas(status);
CREATE INDEX idx_pesquisa_empresa ON pesquisa_empresa(id_pesquisa_fk);
CREATE INDEX idx_pesquisa_empresa_status ON pesquisa_empresa(status);
CREATE INDEX idx_pesquisa_respostas ON pesquisa_respostas(id_pesquisa_empresa_fk);
CREATE INDEX idx_pesquisa_contatos ON pesquisa_contatos(id_pesquisa_empresa_fk);

-- Scripts para adicionar colunas em tabelas existentes (use apenas se as tabelas já existem sem estes campos)
-- ALTER TABLE pesquisas ADD COLUMN descricao TEXT AFTER status;
-- ALTER TABLE pesquisas ADD COLUMN liberado_cliente CHAR(1) DEFAULT 'N' AFTER descricao;
-- ALTER TABLE pesquisa_empresa ADD COLUMN responsavel_pesquisa VARCHAR(255) AFTER data_resposta;
-- ALTER TABLE pesquisa_empresa ADD COLUMN id_status_questionario_fk INT NULL AFTER responsavel_pesquisa;
-- ALTER TABLE pesquisa_empresa ADD CONSTRAINT fk_pesquisa_empresa_status FOREIGN KEY (id_status_questionario_fk) REFERENCES statusdoquestionario(id_status) ON DELETE SET NULL;
-- ALTER TABLE pesquisa_empresa ADD COLUMN nota_total DECIMAL(10,2) DEFAULT 0 AFTER id_status_questionario_fk;
-- ALTER TABLE pesquisa_empresa ADD COLUMN classificacao VARCHAR(50) AFTER nota_total;
