CREATE OR REPLACE FUNCTION microsharding_debug_fdw_create(
  dst_prefix text,
  src_hosts text[]
) RETURNS TABLE (
  server text,
  shard_count integer
)
LANGUAGE plpgsql
AS $$
DECLARE
  host_spec text;
  host text;
  port text;
  dbname text;
  match_result text[];
  server_name text;
  schemas text[];
  src_schema text;
  dst_schema text;
  notice_prefix text := 'Creating foreign tables';
  notice_prev_shown_at timestamptz := NULL;
BEGIN
  notice_prev_shown_at := microsharding_notice_locks_(notice_prefix, notice_prev_shown_at);

  FOREACH host_spec IN ARRAY src_hosts LOOP
    match_result := regexp_match(host_spec, '^(.*?)(?::(\d*))?(?:/(.*))?$');
    host := match_result[1];
    port := COALESCE(NULLIF(match_result[2], ''), current_setting('port'));
    dbname := COALESCE(NULLIF(match_result[3], ''), current_database());

    server_name := regexp_replace(dst_prefix || '_' || host || '_' || port || '_' || dbname, '[^a-zA-Z0-9_]+', '_', 'g');
    EXECUTE format(
      'CREATE SERVER %I FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host %L, port %L, dbname %L)',
      server_name,
      host,
      port,
      dbname
    );
    EXECUTE format(
      'COMMENT ON SERVER %I IS %L',
      server_name,
      'pg-microsharding:' || host_spec -- Keep original host_spec in comment for clarity
    );
    EXECUTE format(
      'CREATE USER MAPPING FOR CURRENT_USER SERVER %I OPTIONS(user %L)',
      server_name,
      current_user
    );

    SELECT *
      FROM public.dblink(server_name, format('SELECT %I.microsharding_list_active_shards()', current_schema())) AS t(schemas text[])
      INTO schemas;

    FOREACH src_schema IN ARRAY schemas LOOP
      notice_prev_shown_at := microsharding_notice_locks_(notice_prefix, notice_prev_shown_at);
      dst_schema := dst_prefix || '_' || src_schema;
      EXECUTE format(
        'CREATE SCHEMA IF NOT EXISTS %I',
        dst_schema
      );
      EXECUTE format(
        'COMMENT ON SCHEMA %I IS %L',
        dst_schema,
        'pg-microsharding:' || host_spec -- Keep original host_spec in comment for clarity
      );
      EXECUTE format(
        'IMPORT FOREIGN SCHEMA %I FROM SERVER %I INTO %I',
        src_schema,
        server_name,
        dst_schema
      );
    END LOOP;

    server := server_name;
    shard_count := cardinality(schemas);
    RETURN NEXT;
  END LOOP;

  PERFORM microsharding_notice_locks_(notice_prefix);
END;
$$;

COMMENT ON FUNCTION microsharding_debug_fdw_create(text, text[])
  IS 'Created debug foreign shards schemas. For each host in the list, '
     'enumerates all its shards schemas and runs IMPORT FOREIGN SCHEMA '
     'for them adding the prefix to the destination schema''s name.';
