-- =====================================================
-- MIGRAÇÃO PARA ADICIONAR CHAVES PRIMÁRIAS SIMPLES
-- Sistema: SIGO Laravel - Pesquisas
-- Banco: Oracle
-- =====================================================

-- 1. CRIAR SEQUENCES PARA AUTO-INCREMENT
-- =====================================================

-- Sequence para web_pesquisa
CREATE SEQUENCE seq_web_pesquisa_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para web_questoes_da_pesquisa  
CREATE SEQUENCE seq_web_questoes_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para web_opcoes_da_pesquisa
CREATE SEQUENCE seq_web_opcoes_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Sequence para web_respostas_da_pesquisa
CREATE SEQUENCE seq_web_respostas_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- 2. ADICIONAR COLUNAS ID NAS TABELAS
-- =====================================================

-- Adicionar coluna id na tabela web_pesquisa
ALTER TABLE web_pesquisa ADD id NUMBER(10);

-- Adicionar coluna id na tabela web_questoes_da_pesquisa
ALTER TABLE web_questoes_da_pesquisa ADD id NUMBER(10);

-- Adicionar coluna id na tabela web_opcoes_da_pesquisa
ALTER TABLE web_opcoes_da_pesquisa ADD id NUMBER(10);

-- Adicionar coluna id na tabela web_respostas_da_pesquisa
ALTER TABLE web_respostas_da_pesquisa ADD id NUMBER(10);

-- 3. POPULAR AS COLUNAS ID COM VALORES ÚNICOS
-- =====================================================

-- Popular id na web_pesquisa (baseado no pes_codigo existente)
UPDATE web_pesquisa SET id = pes_codigo;

-- Popular id na web_questoes_da_pesquisa (usando sequence)
UPDATE web_questoes_da_pesquisa SET id = seq_web_questoes_id.NEXTVAL;

-- Popular id na web_opcoes_da_pesquisa (usando sequence)
UPDATE web_opcoes_da_pesquisa SET id = seq_web_opcoes_id.NEXTVAL;

-- Popular id na web_respostas_da_pesquisa (usando sequence)
UPDATE web_respostas_da_pesquisa SET id = seq_web_respostas_id.NEXTVAL;

-- 4. ADICIONAR CHAVES ESTRANGEIRAS TEMPORÁRIAS
-- =====================================================

-- Adicionar coluna questao_id na web_opcoes_da_pesquisa
ALTER TABLE web_opcoes_da_pesquisa ADD questao_id NUMBER(10);

-- Adicionar coluna opcao_id na web_respostas_da_pesquisa
ALTER TABLE web_respostas_da_pesquisa ADD opcao_id NUMBER(10);

-- 5. POPULAR AS CHAVES ESTRANGEIRAS
-- =====================================================

-- Popular questao_id na web_opcoes_da_pesquisa
UPDATE web_opcoes_da_pesquisa o 
SET questao_id = (
    SELECT q.id 
    FROM web_questoes_da_pesquisa q 
    WHERE q.qpe_cod_questao = o.opp_qpe_cod_questao 
    AND q.qpe_pes_cod_pesquisa = o.opp_qpe_pes_cod_pesq
);

-- Popular opcao_id na web_respostas_da_pesquisa
UPDATE web_respostas_da_pesquisa r 
SET opcao_id = (
    SELECT o.id 
    FROM web_opcoes_da_pesquisa o 
    WHERE o.opp_codigo_opcao = r.rpe_opp_codigo_opcao 
    AND o.opp_qpe_cod_questao = r.rpe_opp_qpe_cod_questao 
    AND o.opp_qpe_pes_cod_pesq = r.rpe_opp_qpe_pes_cod_pesq
);

-- 6. DEFINIR NOT NULL E CHAVES PRIMÁRIAS
-- =====================================================

-- Tornar colunas id NOT NULL
ALTER TABLE web_pesquisa MODIFY id NOT NULL;
ALTER TABLE web_questoes_da_pesquisa MODIFY id NOT NULL;
ALTER TABLE web_opcoes_da_pesquisa MODIFY id NOT NULL;
ALTER TABLE web_respostas_da_pesquisa MODIFY id NOT NULL;

-- Tornar chaves estrangeiras NOT NULL
ALTER TABLE web_opcoes_da_pesquisa MODIFY questao_id NOT NULL;
ALTER TABLE web_respostas_da_pesquisa MODIFY opcao_id NOT NULL;

-- 7. REMOVER CHAVES PRIMÁRIAS COMPOSTAS EXISTENTES
-- =====================================================

-- Remover chave primária composta de web_questoes_da_pesquisa
ALTER TABLE web_questoes_da_pesquisa DROP CONSTRAINT SYS_C0012345; -- Substitua pelo nome real da constraint

-- Remover chave primária composta de web_opcoes_da_pesquisa  
ALTER TABLE web_opcoes_da_pesquisa DROP CONSTRAINT SYS_C0012346; -- Substitua pelo nome real da constraint

-- Remover chave primária composta de web_respostas_da_pesquisa
ALTER TABLE web_respostas_da_pesquisa DROP CONSTRAINT SYS_C0012347; -- Substitua pelo nome real da constraint

-- 8. ADICIONAR NOVAS CHAVES PRIMÁRIAS
-- =====================================================

-- Adicionar chave primária simples em web_pesquisa (se não existir)
ALTER TABLE web_pesquisa ADD CONSTRAINT pk_web_pesquisa PRIMARY KEY (id);

-- Adicionar chave primária simples em web_questoes_da_pesquisa
ALTER TABLE web_questoes_da_pesquisa ADD CONSTRAINT pk_web_questoes PRIMARY KEY (id);

-- Adicionar chave primária simples em web_opcoes_da_pesquisa
ALTER TABLE web_opcoes_da_pesquisa ADD CONSTRAINT pk_web_opcoes PRIMARY KEY (id);

-- Adicionar chave primária simples em web_respostas_da_pesquisa
ALTER TABLE web_respostas_da_pesquisa ADD CONSTRAINT pk_web_respostas PRIMARY KEY (id);

-- 9. ADICIONAR CHAVES ESTRANGEIRAS
-- =====================================================

-- Adicionar FK de questões para pesquisa
ALTER TABLE web_questoes_da_pesquisa 
ADD CONSTRAINT fk_questoes_pesquisa 
FOREIGN KEY (qpe_pes_cod_pesquisa) REFERENCES web_pesquisa(pes_codigo);

-- Adicionar FK de opções para questão
ALTER TABLE web_opcoes_da_pesquisa 
ADD CONSTRAINT fk_opcoes_questao 
FOREIGN KEY (questao_id) REFERENCES web_questoes_da_pesquisa(id);

-- Adicionar FK de respostas para opção
ALTER TABLE web_respostas_da_pesquisa 
ADD CONSTRAINT fk_respostas_opcao 
FOREIGN KEY (opcao_id) REFERENCES web_opcoes_da_pesquisa(id);

-- 10. CRIAR TRIGGERS PARA AUTO-INCREMENT
-- =====================================================

-- Trigger para web_pesquisa (usar pes_codigo como base)
CREATE OR REPLACE TRIGGER trg_web_pesquisa_id
    BEFORE INSERT ON web_pesquisa
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := :NEW.pes_codigo;
    END IF;
END;
/

-- Trigger para web_questoes_da_pesquisa
CREATE OR REPLACE TRIGGER trg_web_questoes_id
    BEFORE INSERT ON web_questoes_da_pesquisa
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := seq_web_questoes_id.NEXTVAL;
    END IF;
END;
/

-- Trigger para web_opcoes_da_pesquisa
CREATE OR REPLACE TRIGGER trg_web_opcoes_id
    BEFORE INSERT ON web_opcoes_da_pesquisa
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := seq_web_opcoes_id.NEXTVAL;
    END IF;
END;
/

-- Trigger para web_respostas_da_pesquisa
CREATE OR REPLACE TRIGGER trg_web_respostas_id
    BEFORE INSERT ON web_respostas_da_pesquisa
    FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := seq_web_respostas_id.NEXTVAL;
    END IF;
END;
/

-- 11. VERIFICAÇÕES FINAIS
-- =====================================================

-- Verificar se todas as tabelas têm chaves primárias
SELECT table_name, constraint_name, constraint_type 
FROM user_constraints 
WHERE table_name IN ('WEB_PESQUISA', 'WEB_QUESTOES_DA_PESQUISA', 'WEB_OPCOES_DA_PESQUISA', 'WEB_RESPOSTAS_DA_PESQUISA')
AND constraint_type = 'P'
ORDER BY table_name;

-- Verificar contagem de registros
SELECT 'WEB_PESQUISA' as tabela, COUNT(*) as registros FROM web_pesquisa
UNION ALL
SELECT 'WEB_QUESTOES_DA_PESQUISA', COUNT(*) FROM web_questoes_da_pesquisa
UNION ALL  
SELECT 'WEB_OPCOES_DA_PESQUISA', COUNT(*) FROM web_opcoes_da_pesquisa
UNION ALL
SELECT 'WEB_RESPOSTAS_DA_PESQUISA', COUNT(*) FROM web_respostas_da_pesquisa;

-- =====================================================
-- FIM DA MIGRAÇÃO
-- =====================================================

-- NOTAS IMPORTANTES:
-- 1. Execute este script em ambiente de teste primeiro
-- 2. Faça backup completo antes de executar em produção
-- 3. Verifique os nomes das constraints existentes antes de remover
-- 4. Ajuste os nomes das sequences se necessário
-- 5. Teste os relacionamentos após a migração
