-- name: onboarding_signal_reconciliation__upsert :one
INSERT INTO project_catalog_onboarding_signal_reconciliations (
    id,
    project_id,
    repository_id,
    signal_kind,
    signal_key,
    signal_fingerprint,
    provider_slug,
    repository_full_name,
    provider_repository_id,
    base_branch,
    source_ref,
    source_commit_sha,
    artifact_ref,
    artifact_digest,
    artifact_version,
    content_hash,
    status,
    error_code,
    error_summary,
    summary,
    services_policy_id,
    services_policy_version,
    observed_at,
    completed_at,
    version,
    created_at,
    updated_at
) VALUES (
    @id,
    @project_id,
    @repository_id,
    @signal_kind,
    @signal_key,
    @signal_fingerprint,
    @provider_slug,
    @repository_full_name,
    @provider_repository_id,
    @base_branch,
    @source_ref,
    @source_commit_sha,
    @artifact_ref,
    @artifact_digest,
    @artifact_version,
    @content_hash,
    @status,
    @error_code,
    @error_summary,
    @summary,
    @services_policy_id,
    @services_policy_version,
    @observed_at,
    @completed_at,
    @version,
    @created_at,
    @updated_at
)
ON CONFLICT (project_id, signal_kind, signal_key) DO UPDATE SET
    provider_slug = EXCLUDED.provider_slug,
    repository_full_name = EXCLUDED.repository_full_name,
    provider_repository_id = EXCLUDED.provider_repository_id,
    base_branch = EXCLUDED.base_branch,
    source_ref = EXCLUDED.source_ref,
    source_commit_sha = EXCLUDED.source_commit_sha,
    artifact_ref = EXCLUDED.artifact_ref,
    artifact_digest = EXCLUDED.artifact_digest,
    artifact_version = EXCLUDED.artifact_version,
    content_hash = EXCLUDED.content_hash,
    status = CASE
        WHEN project_catalog_onboarding_signal_reconciliations.status = 'imported'
            AND EXCLUDED.status = 'processing'
            THEN project_catalog_onboarding_signal_reconciliations.status
        ELSE EXCLUDED.status
    END,
    error_code = EXCLUDED.error_code,
    error_summary = EXCLUDED.error_summary,
    summary = EXCLUDED.summary,
    services_policy_id = COALESCE(EXCLUDED.services_policy_id, project_catalog_onboarding_signal_reconciliations.services_policy_id),
    services_policy_version = CASE
        WHEN EXCLUDED.services_policy_version > 0 THEN EXCLUDED.services_policy_version
        ELSE project_catalog_onboarding_signal_reconciliations.services_policy_version
    END,
    observed_at = EXCLUDED.observed_at,
    completed_at = COALESCE(EXCLUDED.completed_at, project_catalog_onboarding_signal_reconciliations.completed_at),
    updated_at = EXCLUDED.updated_at,
    version = project_catalog_onboarding_signal_reconciliations.version + 1
WHERE project_catalog_onboarding_signal_reconciliations.signal_fingerprint = EXCLUDED.signal_fingerprint
RETURNING
    id,
    project_id,
    repository_id,
    signal_kind,
    signal_key,
    signal_fingerprint,
    provider_slug,
    repository_full_name,
    provider_repository_id,
    base_branch,
    source_ref,
    source_commit_sha,
    artifact_ref,
    artifact_digest,
    artifact_version,
    content_hash,
    status,
    error_code,
    error_summary,
    summary,
    services_policy_id,
    services_policy_version,
    observed_at,
    completed_at,
    version,
    created_at,
    updated_at;
