import {
	sql,
	type AnyColumn,
	type SQL,
	type InferSelectModel,
} from "drizzle-orm";
import {
	type SelectedFields,
	type PgTable,
	type TableConfig,
} from "drizzle-orm/pg-core";
import { type SelectResultFields } from "node_modules/drizzle-orm/query-builders/select.types";

export function jsonBuildObject<T extends SelectedFields>(shape: T) {
    const chunks: SQL[] = [];

    Object.entries(shape).forEach(([key, value]) => {
        if (chunks.length > 0) {
            chunks.push(sql.raw(`,`));
        }
        chunks.push(sql.raw(`'${key}',`));
        chunks.push(sql`${value}`);
    });

    return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
        chunks,
    )}), '{}')`;
}

export function jsonAggBuildObject<
    T extends SelectedFields,
    Column extends AnyColumn,
>(
    shape: T,
    options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } },
) {
    return sql<SelectResultFields<T>[]>`coalesce(jsonb_agg(${jsonBuildObject(
        shape,
    )}${
        options?.orderBy
            ? sql`order by ${options.orderBy.colName} ${sql.raw(
                  options.orderBy.direction,
              )}`
            : undefined
    }), '[]')`;
}

// with filter non-null
export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
  return sql<
    InferModel<Table>[]
  >`coalesce(json_agg(${table}) filter (where ${table} is not null), '[]')`;
}

// generalist
export function jsonAgg<Table extends AnyTable<TableConfig>>(table: Table) {
    return sql<InferModel<Table>[]>`coalesce(json_agg(${table}), '[]')`;
}

// you use it like that:
const result = await db
      .select({
        post,
        // keep only what you need from table theme
        themes: jsonAggBuildObject({
          id: theme.id,
          label: theme.label,
        }),
      })
      .leftJoin(
        postsThemes,
        eq(postsThemes.theme_id, post.theme_id)
      )
      .leftJoin(theme, eq(theme.id, postsThemes.theme_id))
      .groupBy(post.id);