-- =============================================================================
-- DC_REFERENCES_ALTER.sql
-- Ergänzt dc_project_documents um Felder für heruntergeladene externe Referenzen.
--   parent_document_id  → Self-FK auf das Originaldokument (NULL bei Originalen)
--   is_reference        → 0 = Originaldokument, 1 = heruntergeladene Referenz
--   source_url          → Ursprungs-URL der heruntergeladenen Referenz
-- Idempotent: Fehler "Spalte/Constraint existiert bereits" wird ignoriert.
-- =============================================================================

BEGIN
  EXECUTE IMMEDIATE '
    ALTER TABLE dc_project_documents ADD (
      parent_document_id  NUMBER,
      is_reference        NUMBER DEFAULT 0,
      source_url          VARCHAR2(2000 CHAR)
    )
  ';
  DBMS_OUTPUT.PUT_LINE('Spalten parent_document_id/is_reference/source_url angelegt.');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1430 THEN
      DBMS_OUTPUT.PUT_LINE('Spalten existieren bereits – kein Änderungsbedarf.');
    ELSE
      RAISE;
    END IF;
END;
/

-- Bestehende Zeilen initialisieren
UPDATE dc_project_documents
SET    is_reference = 0
WHERE  is_reference IS NULL;

COMMIT;

-- Self-FK (idempotent)
BEGIN
  EXECUTE IMMEDIATE '
    ALTER TABLE dc_project_documents
    ADD CONSTRAINT dc_project_documents_parent_fk
    FOREIGN KEY (parent_document_id) REFERENCES dc_project_documents (id)
  ';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE IN (-2264, -2261, -2275) THEN NULL; -- Constraint existiert bereits
    ELSE RAISE;
    END IF;
END;
/

-- Check-Constraint is_reference (idempotent)
BEGIN
  EXECUTE IMMEDIATE '
    ALTER TABLE dc_project_documents
    ADD CONSTRAINT dc_project_documents_is_ref_ck
    CHECK (is_reference IN (0, 1))
  ';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE IN (-2264, -2261) THEN NULL; -- Constraint existiert bereits
    ELSE RAISE;
    END IF;
END;
/

-- Index auf parent_document_id (idempotent)
BEGIN
  EXECUTE IMMEDIATE '
    CREATE INDEX dc_project_documents_parent_i ON dc_project_documents (parent_document_id)
  ';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN NULL; -- Index existiert bereits
    ELSE RAISE;
    END IF;
END;
/

COMMIT;

EXIT;