SET search_path TO TED, DOC, public;

WITH legacy_package_map AS (
    SELECT
        d.id AS legacy_procurement_document_id,
        p.document_id AS child_document_id,
        substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})') AS package_identifier
    FROM TED.procurement_document d
    JOIN TED.ted_notice_projection p
      ON p.legacy_procurement_document_id = d.id
    WHERE substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})') IS NOT NULL
), package_documents AS (
    SELECT DISTINCT
        l.package_identifier,
        'TED:package:' || l.package_identifier AS business_key,
        encode(digest('TED:package:' || l.package_identifier, 'sha256'), 'hex') AS dedup_hash
    FROM legacy_package_map l
)
INSERT INTO DOC.doc_document (
    id, visibility, document_type, document_family, status, title, summary, mime_type, business_key, dedup_hash,
    created_at, updated_at
)
SELECT
    gen_random_uuid(),
    'PUBLIC',
    'TED_PACKAGE',
    'PROCUREMENT',
    CASE
        WHEN pkg.download_status = 'COMPLETED' THEN 'CLASSIFIED'
        WHEN pkg.download_status IN ('FAILED', 'NOT_FOUND') THEN 'FAILED'
        ELSE 'RECEIVED'
    END,
    'TED Daily Package ' || pd.package_identifier,
    CASE
        WHEN pkg.package_identifier IS NULL THEN 'TED daily package ' || pd.package_identifier
        ELSE 'TED daily package ' || pd.package_identifier ||
             ' (status=' || coalesce(pkg.download_status::text, 'UNKNOWN') ||
             ', xmlFileCount=' || coalesce(pkg.xml_file_count::text, 'null') ||
             ', processedCount=' || coalesce(pkg.processed_count::text, 'null') ||
             ', failedCount=' || coalesce(pkg.failed_count::text, 'null') || ')'
    END,
    'application/gzip',
    pd.business_key,
    pd.dedup_hash,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM package_documents pd
LEFT JOIN TED.ted_daily_package pkg
  ON pkg.package_identifier = pd.package_identifier
WHERE NOT EXISTS (
    SELECT 1
    FROM DOC.doc_document existing
    WHERE existing.business_key = pd.business_key
);

UPDATE TED.ted_notice_projection p
SET package_identifier = substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})')
FROM TED.procurement_document d
WHERE p.legacy_procurement_document_id = d.id
  AND substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})') IS NOT NULL
  AND p.package_identifier IS DISTINCT FROM substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})');

WITH legacy_package_map AS (
    SELECT
        p.document_id AS child_document_id,
        substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})') AS package_identifier
    FROM TED.procurement_document d
    JOIN TED.ted_notice_projection p
      ON p.legacy_procurement_document_id = d.id
    WHERE substring(coalesce(d.source_path, d.source_filename) from '(20[0-9]{7})') IS NOT NULL
), package_documents AS (
    SELECT
        doc.id AS parent_document_id,
        substring(doc.business_key from '(20[0-9]{7})') AS package_identifier
    FROM DOC.doc_document doc
    WHERE doc.document_type = 'TED_PACKAGE'
      AND doc.business_key LIKE 'TED:package:%'
)
INSERT INTO DOC.doc_relation (
    id, parent_document_id, child_document_id, relation_type, sort_order, relation_metadata, created_at
)
SELECT
    gen_random_uuid(),
    pkg.parent_document_id,
    l.child_document_id,
    'CONTAINS',
    NULL,
    'packageIdentifier=' || l.package_identifier,
    CURRENT_TIMESTAMP
FROM legacy_package_map l
JOIN package_documents pkg
  ON pkg.package_identifier = l.package_identifier
WHERE NOT EXISTS (
    SELECT 1
    FROM DOC.doc_relation rel
    WHERE rel.parent_document_id = pkg.parent_document_id
      AND rel.child_document_id = l.child_document_id
      AND rel.relation_type = 'CONTAINS'
);
