SET search_path TO TED, DOC, public;

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

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

ALTER TABLE IF EXISTS TED.organization
    ALTER COLUMN city TYPE TEXT;

ALTER TABLE IF EXISTS TED.organization
    ALTER COLUMN company_id TYPE TEXT;

ALTER TABLE IF EXISTS TED.procurement_document
    ALTER COLUMN buyer_city TYPE TEXT;

ALTER TABLE IF EXISTS TED.ted_notice_organization
    ALTER COLUMN city TYPE TEXT;

ALTER TABLE IF EXISTS TED.ted_notice_projection
    ALTER COLUMN buyer_city TYPE TEXT;

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

SET search_path TO DOC, public;

CREATE TABLE IF NOT EXISTS DOC.doc_legacy_ted_migration_run (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status VARCHAR(32) NOT NULL,
    import_batch_id VARCHAR(255),
    queue_embeddings BOOLEAN NOT NULL DEFAULT FALSE,
    batch_size INTEGER NOT NULL DEFAULT 100,
    max_documents_per_run BIGINT,
    processed_count BIGINT NOT NULL DEFAULT 0,
    success_count BIGINT NOT NULL DEFAULT 0,
    failed_count BIGINT NOT NULL DEFAULT 0,
    last_legacy_created_at TIMESTAMP WITH TIME ZONE,
    last_legacy_document_id UUID,
    last_doc_document_id UUID,
    last_projection_id UUID,
    last_error TEXT,
    started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_ted_mig_run_status
    ON DOC.doc_legacy_ted_migration_run(status);
CREATE INDEX IF NOT EXISTS idx_doc_legacy_ted_mig_run_started
    ON DOC.doc_legacy_ted_migration_run(started_at DESC);

CREATE TABLE IF NOT EXISTS DOC.doc_legacy_ted_migration_checkpoint (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id UUID NOT NULL REFERENCES DOC.doc_legacy_ted_migration_run(id) ON DELETE CASCADE,
    batch_number INTEGER NOT NULL,
    batch_processed_count INTEGER NOT NULL DEFAULT 0,
    cumulative_processed_count BIGINT NOT NULL DEFAULT 0,
    last_legacy_created_at TIMESTAMP WITH TIME ZONE,
    last_legacy_document_id UUID,
    last_doc_document_id UUID,
    last_projection_id UUID,
    note TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_doc_legacy_ted_mig_ckpt_run
    ON DOC.doc_legacy_ted_migration_checkpoint(run_id);
CREATE INDEX IF NOT EXISTS idx_doc_legacy_ted_mig_ckpt_batch
    ON DOC.doc_legacy_ted_migration_checkpoint(batch_number);
