-- Migração de schema e preservação de dados legados do módulo EstatpescaPrepesc.
--
-- Estratégia:
-- 1. Preserva o conteúdo atual de cod_especie em cod_especie_original.
-- 2. Mantém cod_especie inalterado no banco.
-- 3. A aplicação passa a interpretar cod_especie como FK para
--    especie_categoria_pescado e cod_especie_original como FK para especie.
--
-- Observação:
-- Para os registros já existentes, cod_especie_original guarda exatamente o valor
-- que estava em cod_especie antes da troca semântica do campo.

ALTER TABLE estatpesca_prepesc
    ADD COLUMN IF NOT EXISTS cod_especie_original INTEGER NULL;

CREATE INDEX IF NOT EXISTS idx_estatpesca_prepesc_cod_especie_original
    ON estatpesca_prepesc (cod_especie_original);

DO $$
DECLARE
    _constraint_name TEXT;
BEGIN
    FOR
        _constraint_name IN
        SELECT c.conname
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY (c.conkey)
        WHERE t.relname = 'estatpesca_prepesc'
          AND c.contype = 'f'
          AND a.attname = 'cod_especie'
    LOOP
        EXECUTE 'ALTER TABLE estatpesca_prepesc DROP CONSTRAINT ' || quote_ident(_constraint_name);
    END LOOP;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_estatpesca_prepesc_cod_especie_categoria'
    ) THEN
        ALTER TABLE estatpesca_prepesc
            ADD CONSTRAINT fk_estatpesca_prepesc_cod_especie_categoria
            FOREIGN KEY (cod_especie)
            REFERENCES especie_categoria_pescado (id);
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_estatpesca_prepesc_cod_especie_original'
    ) THEN
        ALTER TABLE estatpesca_prepesc
            ADD CONSTRAINT fk_estatpesca_prepesc_cod_especie_original
            FOREIGN KEY (cod_especie_original)
            REFERENCES especie (id);
    END IF;
END $$;

UPDATE estatpesca_prepesc
SET cod_especie_original = cod_especie
WHERE cod_especie IS NOT NULL
  AND cod_especie_original IS NULL;

COMMENT ON COLUMN estatpesca_prepesc.cod_especie IS
'Campo reaproveitado para armazenar a categoria de pescado (especie_categoria_pescado.id).';

COMMENT ON COLUMN estatpesca_prepesc.cod_especie_original IS
'Preserva o valor histórico originalmente salvo em cod_especie antes da mudança semântica do campo.';
