import type { Entity } from "../../classes/entity.ts";
import { Postgres, type QueryObjectResult } from "@db/pg";
import { TranslateMongoishToPostgreSQL } from "./mpg_translator.ts";

export type QueryCache<T> = QueryObjectResult<T> | { rows: [], rowCount: 0 };

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;
  // deno-lint-ignore no-explicit-any
  private _lookups: DB.Lookup<any, any, any, any>[] = [];

  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;
  }

  lookup<Target extends DB.Entity.typeofClass, Field extends string, IsSingular extends boolean = false>(entity: Target, as: Field, config: DB.Lookup.Config<T, Target, Field, IsSingular>): DB.Lookup.ReturnType<T, Target, Field, IsSingular>
  lookup<Target extends DB.Entity.typeofClass, Field extends string, IsSingular extends boolean = false>(entity: Target, as: Field, isSingular: IsSingular, config: DB.Lookup.Config<T, Target, Field, IsSingular>): DB.Lookup.ReturnType<T, Target, Field, IsSingular>
  lookup<Target extends DB.Entity.typeofClass, Field extends string, IsSingular extends boolean = false>(entity: Target, as: Field, config_or_singular: DB.Lookup.Config<T, Target, Field, IsSingular> | boolean, config?: DB.Lookup.Config<T, Target, Field, IsSingular>): DB.Lookup.ReturnType<T, Target, Field, IsSingular> {
    let isArray: boolean;
    if(typeof config_or_singular !== 'boolean') {
      config = config_or_singular;
      isArray = true;
    } else {
      isArray = !config_or_singular;
    }

    this._lookups.push({
      entity,
      as,
      localField: config!.localField,
      foreignField: config!.foreignField,
      filter: config!.filter,
      required: config!.required,
      isArray
    });

    return this as unknown as DB.Lookup.ReturnType<T, Target, Field, IsSingular>;
  }

  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 [sql, args] = this.toFindQuery();

    try {
      const key = this.cache_key;

      const { rows } = (await this.entity.QueryCache().passthrough(key, Postgres.query<T>(sql, args)))!;

      const entity_rows = rows.map(r => {
        // deno-lint-ignore no-explicit-any
        const e = new this.entity(r as Entity) as any;

        for(const lookup of this._lookups) {
          if(!e[lookup.as]) continue;

          if(lookup.isArray) {
            e[lookup.as] = (e[lookup.as] as unknown[]).map(l => new lookup.entity(l));
          } else {
            e[lookup.as] = new lookup.entity(e[lookup.as]);
          }
        }
        return e;
      }) 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<F, number>>(sql, args);

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

  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;
  }

  /**
   * Clears query cache for the provided query
   */
  bust(deep?: boolean) {
    const { cache_key } = this;

    if(!deep) {
      return !!this.entity.QueryCache().delete(cache_key);
    }

    const deep_key = cache_key.replace(/\s+WHERE.+$/, '');
    const entries_to_delete: string[] = [];
    for(const key of this.entity.QueryCache().keys()) {
      if(key.startsWith(deep_key)) entries_to_delete.push(key);
    }

    for(const entry of entries_to_delete) {
      this.entity.QueryCache().delete(entry);
    }
  }

  get cache_key() {
    const [sql, args] = this.toFindQuery();

    return (sql.trim() + '::' + JSON.stringify(args).trim());
  }

  private toFindQuery() {
    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 select_fields: string[] = ['*'];

    let paramIndex = args.length + 1;

    for(const lookup of this._lookups) {
      const alias = lookup.as;
      const subFilter = TranslateMongoishToPostgreSQL(lookup.filter || {}, paramIndex);

      paramIndex += subFilter.args.length;

      const clause = subFilter.clause ? `WHERE ${subFilter.clause} AND` : `WHERE`;

      const subQuery = lookup.isArray === false
        ? `(SELECT to_jsonb(${alias}.*) FROM "${lookup.entity.table}" as lkp_${alias} ${clause} "lkp_${alias}.${lookup.foreignField.toString()}" = "${lookup.localField.toString()}" LIMIT 1)`
        : `(SELECT json_agg(${alias}.*) FROM "${lookup.entity.table}" as lkp_${alias} ${clause} "lkp_${alias}.${lookup.foreignField.toString()}" = "${lookup.localField.toString()}")`;

      select_fields.push(`${subQuery} as ${alias}`);

      args.push(...subFilter.args);
    }

    const sql = `SELECT ${select_fields.join(', ')} FROM "${this.table}" ${clause} ${orderClause} ${limitClause} ${offsetClause}`;

    return [sql, args] as const;
  }
}

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