import { PoolClient, Pool, escapeIdentifier } from 'pg';
import { Repositories } from './Repositories';
import { InvalidUpdateValueError } from '@squiz/dx-common-lib';

export interface Reader<T> {
  find(item: Partial<T>): Promise<T[]>;
  findOne(id: string | Partial<T>): Promise<T | undefined>;
}

export interface Writer<T> {
  create(value: Partial<T>): Promise<T>;
  update(where: Partial<T>, newValue: Partial<T>): Promise<T[]>;
  delete(where: Partial<T>): Promise<number>;
}

export type Repository<T> = Reader<T> & Writer<T>;

export type PageResult<T> = {
  items: T[];
  totalCount: number;
  pageSize: number;
};

export type SortDirection = 'desc' | 'asc';
export const DEFAULT_PAGE_SIZE = 20;

export abstract class AbstractRepository<SHAPE extends object, DATA_CLASS extends SHAPE>
  implements Reader<SHAPE>, Writer<SHAPE>
{
  protected tableName: string;

  /** object where the key is the model property name amd the value is sql column name */
  protected modelPropertyToSqlColumn: { [key in keyof SHAPE]: string };
  /** object where the key is the sql column name and the value is the model property name */
  protected sqlColumnToModelProperty: { [key: string]: string };

  constructor(
    protected repositories: Repositories,
    protected pool: Pool,
    tableName: string,
    mapping: { [key in keyof SHAPE]: string },
    protected classRef: { new (data?: Record<string, unknown>): DATA_CLASS },
  ) {
    this.tableName = `"${tableName}"`;

    this.modelPropertyToSqlColumn = mapping;

    this.sqlColumnToModelProperty = Object.entries(mapping).reduce((prev, curr) => {
      const [modelProp, columnName] = curr as [string, string];
      prev[columnName] = modelProp;
      return prev;
    }, {} as { [key: string]: string });
  }

  protected async getConnection(): Promise<PoolClient> {
    return await this.pool.connect();
  }

  private sanitiseValue(value: Partial<SHAPE>) {
    const sanitisedValue = Object.entries(value)
      .map(([key, value]) => [this.modelPropertyToSqlColumn[key as keyof SHAPE], value] as const)
      .filter(([column, _v]) => !!column);

    const columns = sanitisedValue.map(([column]) => escapeIdentifier(column));
    const bindingParams = sanitisedValue.map((_, index) => `$${index + 1}`);
    const values = sanitisedValue.map(([_column, value]) => value);

    return {
      columns,
      bindingParams,
      values,
    };
  }

  async create(value: SHAPE, transactionClient: PoolClient | null = null): Promise<SHAPE> {
    const valueAsClass = new this.classRef(value as Record<string, unknown>);
    const { columns, bindingParams, values } = this.sanitiseValue(valueAsClass);

    const result = await this.executeQuery(
      `INSERT INTO ${this.tableName} (${columns.join(', ')}) VALUES (${bindingParams.join(', ')}) RETURNING *`,
      values,
      transactionClient,
    );

    return result[0];
  }

  async update(
    where: Partial<SHAPE>,
    newValue: Exclude<Partial<SHAPE>, Record<string, never>>,
    transactionClient: PoolClient | null = null,
  ): Promise<SHAPE[]> {
    if (Object.keys(newValue).length === 0) {
      throw new InvalidUpdateValueError('Failed updating the repository, update values cannot be empty');
    }
    const whereValues = Object.values(where);
    const newValues = this.sanitiseValue(newValue);
    const setValues = newValues.columns.map((c, i) => `${c} = ${newValues.bindingParams[i]}`);
    const whereString = this.createWhereStringFromPartialModel(where, setValues.length);

    const result = await this.executeQuery(
      `UPDATE ${this.tableName} 
        SET ${setValues.join(', ')} 
        WHERE ${whereString}
        RETURNING *`,
      [...newValues.values, ...whereValues],
      transactionClient,
    );

    return result;
  }

  async delete(where: Partial<SHAPE>, transactionClient: PoolClient | null = null): Promise<number> {
    const client = transactionClient ?? (await this.getConnection());

    try {
      const whereString = this.createWhereStringFromPartialModel(where);

      const result = await client.query(`DELETE FROM ${this.tableName} WHERE ${whereString}`, Object.values(where));

      return result.rowCount ?? 0;
    } finally {
      if (client && !transactionClient) {
        client.release();
      }
    }
  }

  protected createWhereStringFromPartialModel(values: Partial<SHAPE>, initialIndex: number = 0) {
    const keys = Object.keys(values);

    if (keys.length == 0) {
      throw new Error(`Values cannot be an empty object. It must have at least one property`);
    }

    const sql = keys.reduce((acc, key, index) => {
      const condition = `"${this.modelPropertyToSqlColumn[key as keyof SHAPE]}" = $${1 + index + initialIndex}`;

      return acc === '' ? `${acc} ${condition}` : `${acc} AND ${condition}`;
    }, '');

    return sql;
  }

  protected async executeQueryRaw(
    query: string,
    values: any[],
    transactionClient: PoolClient | null = null,
  ): Promise<any[]> {
    const client = transactionClient ?? (await this.getConnection());
    try {
      const result = await client.query(query, values);

      return result.rows;
    } finally {
      if (client && !transactionClient) {
        client.release();
      }
    }
  }

  async executeQuery(query: string, values: any[], transactionClient: PoolClient | null = null): Promise<SHAPE[]> {
    const rows = await this.executeQueryRaw(query, values, transactionClient);
    return rows.map((a) => this.createAndHydrateModel(a));
  }

  protected createAndHydrateModel(row: any): SHAPE {
    const inputData: Record<string, unknown> = {};

    for (const key of Object.keys(row)) {
      const translatedKey = this.sqlColumnToModelProperty[key];
      inputData[translatedKey] = row[key];
    }

    return new this.classRef(inputData);
  }

  async findOne(item: Partial<SHAPE>, transactionClient?: PoolClient): Promise<SHAPE | undefined> {
    const result = await this.executeQuery(
      `SELECT *
      FROM ${this.tableName}
      WHERE ${this.createWhereStringFromPartialModel(item)}
      LIMIT 1`,
      Object.values(item),
      transactionClient,
    );

    return result[0];
  }

  async find(item: Partial<SHAPE>): Promise<SHAPE[]> {
    const result = await this.executeQuery(
      `SELECT *
      FROM ${this.tableName}
      WHERE ${this.createWhereStringFromPartialModel(item)}`,
      Object.values(item),
    );

    return result;
  }

  async findAll(): Promise<SHAPE[]> {
    const result = await this.executeQuery(
      `SELECT *
      FROM ${this.tableName}`,
      [],
    );

    return result;
  }

  async getCount(item: Partial<SHAPE> | null = null): Promise<number> {
    let whereClause = '';

    if (item) {
      whereClause = `WHERE ${this.createWhereStringFromPartialModel(item)}`;
    }

    return this.getCountRaw(whereClause, item ? Object.values(item) : []);
  }

  async getPage(
    pageNumber: number,
    sortBy: (keyof SHAPE)[] = [],
    direction: SortDirection = 'asc',
    pageSize: number | null = null,
    item: Partial<SHAPE> | null = null,
  ): Promise<PageResult<SHAPE>> {
    let whereClause = '';
    if (item) {
      whereClause = `WHERE ${this.createWhereStringFromPartialModel(item)}`;
    }

    return this.getPageRaw(
      pageNumber,
      sortBy,
      direction,
      whereClause,
      this.tableName,
      Object.values(item ?? {}),
      pageSize,
    );
  }

  async getCountRaw(whereClause: string = '', values: any[] = [], tableRef: string = ''): Promise<number> {
    const result = await this.executeQueryRaw(
      `SELECT COUNT(*) FROM ${this.tableName} ${tableRef} ${whereClause}`,
      values,
    );

    return parseInt(result[0].count);
  }

  async getPageRaw(
    pageNumber: number,
    sortBy: (keyof SHAPE)[] = [],
    direction: SortDirection = 'asc',
    whereClause: string = '',
    tableRef: string = '',
    values: any[] = [],
    pageSize: number | null = null,
    searchFields: Partial<SHAPE> | null = null,
  ): Promise<PageResult<SHAPE>> {
    if (pageSize === null) {
      pageSize = DEFAULT_PAGE_SIZE;
    }
    if (pageNumber <= 0) {
      throw new Error(`Page number value cannot be less than 1`);
    }
    if (pageSize <= 0) {
      throw new Error(`Page size value cannot be less than 1`);
    }

    let orderByClause = '';
    if (sortBy.length) {
      orderByClause = `ORDER BY ${sortBy
        .map((a) => this.modelPropertyToSqlColumn[a as keyof SHAPE])
        .join(',')} ${direction}`;
    }

    const offset = (pageNumber - 1) * pageSize;

    if (searchFields !== null) {
      const searchFieldsWhere: string[] = [];
      for (const [key, value] of Object.entries(searchFields)) {
        if (typeof value !== 'string') {
          throw new Error(`Search field ${key} needs to be of type string`);
        }
        searchFieldsWhere.push(`${this.modelPropertyToSqlColumn[key as keyof SHAPE]} LIKE $${values.length + 1}`);
        values.push(`%${value}%`);
      }
      if (whereClause.length) {
        whereClause = `${whereClause} AND`;
      }
      whereClause = `${whereClause} (${searchFieldsWhere.join(' OR ')})`;
    }

    const query = `
      SELECT *
      FROM ${this.tableName} ${tableRef} ${whereClause} ${orderByClause}
      OFFSET ${offset}
      LIMIT ${pageSize}
    `;

    const items = await this.executeQuery(query, values);

    return {
      items,
      totalCount: await this.getCountRaw(whereClause, values, tableRef),
      pageSize,
    };
  }
}