import type { Entity } from "../../classes/entity.ts";
import { Postgres } from "https://viapak.xyz/@db/pg/mod.ts";
import { TranslateMongoishToPostgreSQL } from "./mpg_translator.ts";

export class EntityQuery<T extends Entity> implements PromiseLike<T[]> {
  constructor(
    private entity: typeof Entity,
    private table: string,
    private _filter: DB.Filterable<T>
  ) {}

  private _limit?: number;
  private _offset?: number;
  private _sort?: Partial<Record<keyof T, 'asc' | 'desc'>>;
  private _with_deleted?: boolean;

  sort(fields: Partial<Record<keyof T, 'asc' | 'desc'>>) {
    this._sort = fields;
    return this;
  }

  limit(n: number) {
    this._limit = n;
    return this;
  }

  skip(n: number) {
    this._offset = n;
    return this;
  }

  withDeleted(flag: boolean = true) {
    this._with_deleted = flag;
    return this;
  }

  private get filter() {
    if(this._with_deleted) return this._filter;
    return { $and: [ this._filter, { deleted_at: null }] } as DB.Filterable<T>;
  }

  async then<TResult1 = T[], TResult2 = never>(
    onfulfilled?: ((value: T[]) => TResult1 | PromiseLike<TResult1>) | undefined,
    onrejected?: ((reason: unknown) => TResult2 | PromiseLike<TResult2>) | undefined
  ): Promise<TResult1 | TResult2> {
    const { clause, args } = TranslateMongoishToPostgreSQL(this.filter);

    const orderClause = this._sort
      ? 'ORDER BY ' + Object.entries(this._sort).map(([k, v]) => `${k} ${(v as string).toUpperCase()}`).join(', ')
      : '';
    const limitClause = this._limit != null ? `LIMIT ${this._limit}` : '';
    const offsetClause = this._offset != null ? `OFFSET ${this._offset}` : '';

    const sql = `SELECT * FROM "${this.table}" ${clause} ${orderClause} ${limitClause} ${offsetClause}`;

    try {
      const { rows } = await Postgres.query<T>(sql, args);
  
      const entity_rows = rows.map(r => new this.entity(r as Entity)) as T[];
  
      return Promise.resolve(entity_rows).then(onfulfilled, onrejected);
    } catch(e) {
      return Promise.reject(e).catch(onrejected);
    }
  }

  async update(values: Partial<DB.Entity.Clean<T>>): Promise<number> {
    const setClauses: string[] = [];
    const args: unknown[] = [];
    let paramIndex = 1;

    for (const [key, value] of Object.entries(values)) {
      setClauses.push(`${key} = $${paramIndex++}`);
      args.push(value);
    }

    const { clause: whereClause, args: whereArgs } = TranslateMongoishToPostgreSQL(this.filter, paramIndex);
    args.push(...whereArgs);

    const sql = `UPDATE "${this.table}" SET ${setClauses.join(', ')} ${whereClause}`;
    const result = await Postgres.query(sql, args);

    return result.rowCount ?? 0;
  }

  async delete(permanent?: boolean): Promise<number> {
    const { clause, args } = TranslateMongoishToPostgreSQL(this.filter);

    let sql!: string;
    if(permanent) {
      sql = `DELETE FROM "${this.table}" ${clause}`;
    } else {
      sql = `UPDATE "${this.table}" SET deleted_at = NOW() ${clause}`;
    }

    const result = await Postgres.query(sql, args);
    return result.rowCount ?? 0;
  }

  async restore(): Promise<number> {
    const { clause, args } = TranslateMongoishToPostgreSQL(this.filter);
    const sql = `UPDATE "${this.table}" SET deleted_at = NULL ${clause}`;
    const result = await Postgres.query(sql, args);
    return result.rowCount ?? 0;
  }

  async count(): Promise<number> {
    const { clause, args } = TranslateMongoishToPostgreSQL(this.filter);
    const sql = `SELECT COUNT(*)::int as count FROM "${this.table}" ${clause}`;
    const { rows: [{ count }] } = await Postgres.query<{count: number}>(sql, args);
    return Number(count ?? 0);
  }

  async ids<F extends NumericalProperties<T>>(field: F = 'id' as F): Promise<number[]> {
    const { clause, args } = TranslateMongoishToPostgreSQL(this.filter);

    const sql = `SELECT ${field.toString()} FROM "${this.table}" ${clause}`;

    const { rows } = await Postgres.query<Record<string, number>>(sql, args);

    return rows.map(r => r[field.toString()]!);
  }

  async exists(): Promise<boolean> {
    const{ clause, args } = TranslateMongoishToPostgreSQL(this.filter);
    const sql = `SELECT 1 FROM "${this.table}" ${clause} LIMIT 1`;
    const { rows } = await Postgres.query<{count: number}>(sql, args);
    return rows.length > 0;
  }
}

type NumericalProperties<T> = {[K in keyof T]: T[K] extends number ? K : never}[keyof T];