BEGIN;

ALTER TABLE platform.agentic_workspace_files
    ADD COLUMN IF NOT EXISTS source_file_id INTEGER,
    ADD COLUMN IF NOT EXISTS render_status VARCHAR(40),
    ADD COLUMN IF NOT EXISTS render_metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS delivery_metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS legacy_artifact_id INTEGER,
    ADD COLUMN IF NOT EXISTS business_operation_type VARCHAR(80);

ALTER TABLE platform.agentic_workspace_files
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_kind,
    ADD CONSTRAINT chk_agentic_workspace_files_kind CHECK (
        file_kind IN (
            'free', 'markdown', 'html', 'code', 'readme', 'source',
            'rendered_pdf', 'rendered_xlsx', 'rendered_docx', 'incoming_media',
            'business_internal'
        )
    );

ALTER TABLE platform.agentic_workspace_files
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_render_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_files_render_metadata_object CHECK (jsonb_typeof(render_metadata) = 'object'),
    DROP CONSTRAINT IF EXISTS chk_agentic_workspace_files_delivery_metadata_object,
    ADD CONSTRAINT chk_agentic_workspace_files_delivery_metadata_object CHECK (jsonb_typeof(delivery_metadata) = 'object');

CREATE UNIQUE INDEX IF NOT EXISTS agentic_workspace_files_legacy_artifact_uq
    ON platform.agentic_workspace_files (workspace_id, legacy_artifact_id)
    WHERE legacy_artifact_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_kind_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, file_kind, updated_at DESC)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_content_type_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, content_type)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS agentic_workspace_files_scope_workspace_idx
    ON platform.agentic_workspace_files (business_id, sucursal_id, workspace_id, login_id, user_id)
    WHERE soft_delete IS NOT TRUE;

WITH prepared AS (
    SELECT
        a.*,
        COALESCE(a.folder_id, root_folder.id_folder) AS target_folder_id,
        COALESCE(NULLIF(a.filename, ''), 'agentic-workspace-artifact-' || a.id_artifact::text || '.bin') AS safe_filename,
        CASE
            WHEN a.artifact_type = 'pdf' THEN 'rendered_pdf'
            WHEN a.artifact_type = 'xlsx' THEN 'rendered_xlsx'
            WHEN a.artifact_type = 'docx' THEN 'rendered_docx'
            WHEN COALESCE(a.delivery_metadata, '{}'::jsonb)->>'incoming_media' = 'true' THEN 'incoming_media'
            ELSE 'business_internal'
        END AS target_file_kind
    FROM platform.agentic_workspace_artifacts a
    LEFT JOIN platform.agentic_workspace_folders root_folder
      ON root_folder.workspace_id = a.workspace_id
     AND root_folder.business_id = a.business_id
     AND root_folder.sucursal_id = a.sucursal_id
     AND root_folder.path = '/workspace'
     AND root_folder.soft_delete IS NOT TRUE
    WHERE NOT EXISTS (
        SELECT 1
        FROM platform.agentic_workspace_files f
        WHERE f.workspace_id = a.workspace_id
          AND f.legacy_artifact_id = a.id_artifact
    )
),
named AS (
    SELECT
        p.*,
        CASE
            WHEN p.safe_filename LIKE '%.%' THEN regexp_replace(p.safe_filename, '\.[^.]*$', '')
            ELSE p.safe_filename
        END AS filename_base,
        CASE
            WHEN p.safe_filename LIKE '%.%' THEN lower(regexp_replace(p.safe_filename, '^.*\.', ''))
            WHEN p.artifact_type IN ('pdf', 'xlsx', 'docx') THEN p.artifact_type
            ELSE 'bin'
        END AS filename_extension,
        COALESCE(NULLIF(trim(both '-' FROM regexp_replace(lower(p.safe_filename), '[^a-z0-9]+', '-', 'g')), ''), 'artifact') AS base_slug,
        ROW_NUMBER() OVER (
            PARTITION BY p.target_folder_id, COALESCE(NULLIF(trim(both '-' FROM regexp_replace(lower(p.safe_filename), '[^a-z0-9]+', '-', 'g')), ''), 'artifact')
            ORDER BY p.id_artifact
        ) AS artifact_slug_index
    FROM prepared p
),
deduped AS (
    SELECT
        n.*,
        EXISTS (
            SELECT 1
            FROM platform.agentic_workspace_files f
            WHERE f.folder_id IS NOT DISTINCT FROM n.target_folder_id
              AND f.slug = n.base_slug
              AND f.soft_delete IS NOT TRUE
              AND f.trashed_at IS NULL
              AND f.archived IS NOT TRUE
        ) AS slug_conflict
    FROM named n
)
INSERT INTO platform.agentic_workspace_files (
    business_id, sucursal_id, empleado_digital_id, login_id, user_id, client_id,
    workspace_id, folder_id, file_kind, slug, name, extension, content_text,
    content_sha256, metadata, archived, soft_delete, created_at, updated_at,
    storage_backend, s3_bucket, s3_key, s3_region, kms_key_id, version_id, etag,
    content_type, size_bytes, sha256, storage_metadata, content_text_preview,
    search_text, archived_at, soft_delete_date, trashed_at, trash_expires_at,
    trashed_by_login_id, trashed_by_user_id, trash_batch_id, trash_original_path,
    trash_restored_at, trash_finalized_at, trash_metadata, source_file_id,
    render_status, render_metadata, delivery_metadata, legacy_artifact_id,
    business_operation_type
)
SELECT
    business_id, sucursal_id, empleado_digital_id, login_id, user_id, client_id,
    workspace_id, target_folder_id, target_file_kind,
    CASE WHEN slug_conflict OR artifact_slug_index > 1 THEN base_slug || '-artifact-' || id_artifact::text ELSE base_slug END,
    CASE
        WHEN slug_conflict OR artifact_slug_index > 1 THEN filename_base || '-artifact-' || id_artifact::text || '.' || filename_extension
        ELSE safe_filename
    END,
    filename_extension,
    '',
    sha256,
    jsonb_build_object(
        'binary_workspace_file', true,
        'downloadable', true,
        'legacy_artifact_id', id_artifact,
        'legacy_artifact_type', artifact_type,
        'legacy_layout_type', layout_type
    ),
    archived,
    soft_delete,
    created_at,
    updated_at,
    storage_backend,
    s3_bucket,
    s3_key,
    s3_region,
    kms_key_id,
    version_id,
    etag,
    mime_type,
    pdf_size_bytes,
    sha256,
    COALESCE(storage_metadata, '{}'::jsonb),
    safe_filename,
    safe_filename || ' ' || COALESCE(artifact_type, '') || ' ' || COALESCE(layout_type, ''),
    archived_at,
    soft_delete_date,
    trashed_at,
    trash_expires_at,
    trashed_by_login_id,
    trashed_by_user_id,
    trash_batch_id,
    trash_original_path,
    trash_restored_at,
    trash_finalized_at,
    COALESCE(trash_metadata, '{}'::jsonb),
    source_file_id,
    render_status,
    COALESCE(source_snapshot, '{}'::jsonb) || jsonb_build_object(
        'artifact_type', artifact_type,
        'layout_type', layout_type,
        'legacy_artifact_id', id_artifact
    ),
    COALESCE(delivery_metadata, '{}'::jsonb) || jsonb_build_object(
        'downloadable', true,
        'webchat_api_download', true,
        'legacy_artifact_compat', true
    ),
    id_artifact,
    CASE
        WHEN artifact_type = 'pdf' THEN 'document.render_pdf'
        WHEN artifact_type = 'xlsx' THEN 'document.render_excel'
        WHEN artifact_type = 'docx' THEN 'document.render_word'
        ELSE 'workspace.save_incoming_media'
    END
FROM deduped;

COMMENT ON COLUMN platform.agentic_workspace_files.legacy_artifact_id IS
    'Temporary compatibility id for artifact-era routes/actions. New writes use workspace_files as source of truth.';
COMMENT ON COLUMN platform.agentic_workspace_files.render_metadata IS
    'Renderer/source snapshot metadata for generated binary files.';
COMMENT ON COLUMN platform.agentic_workspace_files.delivery_metadata IS
    'Delivery/download metadata for generated or received workspace files.';

COMMIT;
