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

// demo https://drizzle.run/se2noay5mhdu24va3xhv0lqo

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

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

    chunks.push(sql.raw(`'${key}',`));

    // json_build_object formats to ISO 8601 ...
    if (is(value, PgTimestampString)) {
      chunks.push(sql`timezone('UTC', ${value})`);
    } else {
      chunks.push(sql`${value}`);
    }
  });

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

export function jsonAggBuildObject<
  T extends SelectedFields<any, any>,
  Column extends AnyColumn,
>(
  shape: T,
  options?: { orderBy?: { colName: Column; direction: "ASC" | "DESC" } },
) {
  return sql<SelectResultFields<T>[]>`coalesce(
    json_agg(${jsonBuildObject(shape)}
    ${
      options?.orderBy
        ? sql`ORDER BY ${options.orderBy.colName} ${sql.raw(
            options.orderBy.direction,
          )}`
        : undefined
    })
    FILTER (WHERE ${and(
      sql.join(
        Object.values(shape).map((value) => sql`${sql`${value}`} IS NOT NULL`),
        sql` AND `,
      ),
    )})
    ,'${sql`[]`}')`;
}

// with filter non-null + distinct
export function jsonAgg<Column extends AnyColumn>(column: Column) {
  return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`,
    sql`'[]'`
  );
}

// generalist
export function jsonAgg<Column extends AnyColumn>(column: Column) {
  return coalesce<GetColumnData<Column, "raw">[]>(
    sql`json_agg(${sql`${column}`})`,
    sql`'[]'`
  );
}

// Sometimes you want an array and not a json
export function arrayAgg<Column extends AnyColumn>(column: Column) {
  return sql<
    GetColumnData<Column, "raw">[]
  >`array_agg(distinct ${sql`${column}`}) filter (where ${column} is not null)`;
}

// To be completed
type PGCastTypes = "uuid" | "uuid[]" | "text" | "text[]";

type PGArrayCastTypes = {
  [P in PGCastTypes]: P extends `${infer _T}[]` ? P : never;
}[PGCastTypes];

// Transform an array of values (from a function params) into a postgres array
export function toArray<Values>(values: Values[], cast: PGArrayCastTypes) {
  const chunks: SQL[] = [];

  values.forEach((column) => {
    if (chunks.length > 0) {
      chunks.push(sql.raw(`,`));
    }

    chunks.push(sql`${column}`);
  });

  return sql`array[${sql.join(chunks)}]::${sql.raw(cast)}`;
}
// exemple:
await db
  .select()
  .from(existingDiscussionQuery)
  .where(
    arrayContained(
      // from Drizzle
      existingDiscussionQuery.participants,
      toArray(
        [
          "c3b1399f-2c6b-40d7-9d37-cfaf9a7c6164",
          "77c75084-7123-481b-a326-49c9ebceb431",
        ],
        "uuid[]"
      )
    )
  );

// 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);