-- Agentic Workspace legacy artifact retirement preflight.
--
-- Run after migrations/agentic_workspace_dynamic_files.sql and before any
-- destructive artifact-table retirement. This script is intentionally
-- non-destructive: it blocks if any legacy artifact cannot be represented as a
-- canonical workspace_file.

WITH legacy_artifacts AS (
    SELECT
        a.id_artifact,
        a.business_id,
        a.sucursal_id,
        a.workspace_id,
        a.filename,
        a.storage_backend,
        a.s3_bucket,
        a.s3_key,
        a.protected_path,
        a.pdf_size_bytes,
        a.sha256,
        f.id_file AS canonical_file_id
    FROM platform.agentic_workspace_artifacts a
    LEFT JOIN platform.agentic_workspace_files f
      ON f.workspace_id = a.workspace_id
     AND f.legacy_artifact_id = a.id_artifact
),
summary AS (
    SELECT
        COUNT(*) AS artifact_count,
        COUNT(*) FILTER (WHERE canonical_file_id IS NOT NULL) AS migrated_count,
        COUNT(*) FILTER (WHERE canonical_file_id IS NULL) AS unmigrated_count,
        COUNT(*) FILTER (
            WHERE canonical_file_id IS NULL
              AND (
                  (storage_backend = 's3' AND COALESCE(s3_bucket, '') <> '' AND COALESCE(s3_key, '') <> '')
                  OR (storage_backend = 'protected_frontend_path' AND COALESCE(protected_path, '') <> '')
                  OR (storage_backend = 'postgres' AND COALESCE(pdf_size_bytes, 0) > 0)
              )
        ) AS unmigrated_recoverable_count,
        COUNT(*) FILTER (
            WHERE canonical_file_id IS NULL
              AND NOT (
                  (storage_backend = 's3' AND COALESCE(s3_bucket, '') <> '' AND COALESCE(s3_key, '') <> '')
                  OR (storage_backend = 'protected_frontend_path' AND COALESCE(protected_path, '') <> '')
                  OR (storage_backend = 'postgres' AND COALESCE(pdf_size_bytes, 0) > 0)
              )
        ) AS unmigrated_blocking_count
    FROM legacy_artifacts
)
SELECT * FROM summary;

DO $$
DECLARE
    blocking_count integer;
    missing_count integer;
BEGIN
    SELECT COUNT(*)
      INTO missing_count
      FROM platform.agentic_workspace_artifacts a
      LEFT JOIN platform.agentic_workspace_files f
        ON f.workspace_id = a.workspace_id
       AND f.legacy_artifact_id = a.id_artifact
     WHERE f.id_file IS NULL;

    SELECT COUNT(*)
      INTO blocking_count
      FROM platform.agentic_workspace_artifacts a
      LEFT JOIN platform.agentic_workspace_files f
        ON f.workspace_id = a.workspace_id
       AND f.legacy_artifact_id = a.id_artifact
     WHERE f.id_file IS NULL
       AND NOT (
           (a.storage_backend = 's3' AND COALESCE(a.s3_bucket, '') <> '' AND COALESCE(a.s3_key, '') <> '')
           OR (a.storage_backend = 'protected_frontend_path' AND COALESCE(a.protected_path, '') <> '')
           OR (a.storage_backend = 'postgres' AND COALESCE(a.pdf_size_bytes, 0) > 0)
       );

    IF blocking_count > 0 THEN
        RAISE EXCEPTION 'Agentic Workspace artifact retirement blocked: % legacy artifacts have no recoverable bytes/storage for file migration', blocking_count;
    END IF;

    IF missing_count > 0 THEN
        RAISE EXCEPTION 'Agentic Workspace artifact retirement blocked: % legacy artifacts still lack workspace_file rows. Run agentic_workspace_dynamic_files.sql first.', missing_count;
    END IF;
END $$;
