-- Store the TED daily package identifier directly on the Phase 3 TED notice projection.
-- This makes migration, audit, and repair flows package-aware without having to derive the
-- package membership from source paths at query time.

SET search_path TO TED, DOC, public;

ALTER TABLE IF EXISTS TED.ted_notice_projection
    ADD COLUMN IF NOT EXISTS package_identifier VARCHAR(20);

CREATE INDEX IF NOT EXISTS idx_ted_notice_projection_package_identifier
    ON TED.ted_notice_projection(package_identifier);

-- Backfill from the linked legacy TED document's source path when the path contains the
-- extracted package directory, e.g. .../extracted/202600003/123.xml
UPDATE TED.ted_notice_projection projection
SET package_identifier = substring(legacy.source_path from '(?:^|[\\/])((?:19|20)[0-9]{7})(?:[\\/]|$)')
FROM TED.procurement_document legacy
WHERE projection.legacy_procurement_document_id = legacy.id
  AND projection.package_identifier IS NULL
  AND legacy.source_path IS NOT NULL
  AND substring(legacy.source_path from '(?:^|[\\/])((?:19|20)[0-9]{7})(?:[\\/]|$)') IS NOT NULL;
