-- Legacy compatibility backfill for pre-catalog public.s3_credentials rows.
-- Safe to re-run after the canonical Athena storage tables already exist.

DO $$
BEGIN
    IF to_regclass('public.s3_credentials') IS NOT NULL THEN
        INSERT INTO athena.s3 (
            id,
            name,
            description,
            endpoint,
            region,
            bucket,
            provider,
            is_active,
            metadata,
            created_at,
            updated_at
        )
        SELECT
            legacy.id,
            CONCAT(
                COALESCE(
                    NULLIF(
                        regexp_replace(lower(btrim(legacy.description)), '[^a-z0-9]+', '-', 'g'),
                        ''
                    ),
                    'legacy-s3'
                ),
                '-',
                substr(legacy.id::text, 1, 8)
            ),
            COALESCE(legacy.description, ''),
            legacy.endpoint,
            legacy.region,
            legacy.bucket,
            's3',
            legacy.is_active,
            jsonb_build_object(
                'migratedFrom', 'public.s3_credentials',
                'legacyId', legacy.id
            ),
            legacy.created_at,
            legacy.updated_at
        FROM public.s3_credentials AS legacy
        ON CONFLICT (id) DO UPDATE
        SET
            description = EXCLUDED.description,
            endpoint = EXCLUDED.endpoint,
            region = EXCLUDED.region,
            bucket = EXCLUDED.bucket,
            is_active = EXCLUDED.is_active,
            metadata = athena.s3.metadata || EXCLUDED.metadata,
            updated_at = EXCLUDED.updated_at;

        INSERT INTO athena.s3_credentials (
            id,
            s3_id,
            description,
            access_key_id,
            secret_key,
            is_active,
            metadata,
            created_at,
            updated_at
        )
        SELECT
            legacy.id,
            legacy.id,
            COALESCE(legacy.description, ''),
            legacy.access_key_id,
            legacy.secret_key,
            legacy.is_active,
            jsonb_build_object(
                'migratedFrom', 'public.s3_credentials',
                'legacyId', legacy.id
            ),
            legacy.created_at,
            legacy.updated_at
        FROM public.s3_credentials AS legacy
        ON CONFLICT (id) DO UPDATE
        SET
            description = EXCLUDED.description,
            access_key_id = EXCLUDED.access_key_id,
            secret_key = EXCLUDED.secret_key,
            is_active = EXCLUDED.is_active,
            metadata = athena.s3_credentials.metadata || EXCLUDED.metadata,
            updated_at = EXCLUDED.updated_at;
    END IF;
END $$;
