import {
  SelectQueryBuilder,
  EntityManager,
  WhereExpressionBuilder,
  Brackets,
} from 'typeorm';
import { SYSTEM_PROJECT_ID } from '../../constants';
import { AssetEntity } from '../../entities/asset.entity';
import { decodeBigNumberKey } from '../../utils/big-number-key';
import { AssetSelectorContext } from '../asset-selector.service';
import { AssetViewDTO } from '../dto/asset-dto';
import {
  AssetPropsSelectionField,
  AssetPropsSelectionOrder,
} from './PropsSelection';
import {
  AssetPropWhere,
  AssetPropWhereCondition,
  AssetPropWhereOpKind,
  convertAssetPropWhereConditionToOp,
} from './PropsWhere';
import { ApiError } from '../../common/error/api-error';
import { ApiErrorCodes } from '../../common/error/api-error-codes';
import {
  AssetPropKeyStruct,
  AssetPropValue,
  parseAssetBlockPropKeyRef,
} from './Props';
import { AssetSelectorQueryFieldBase } from './AssetSelectorQueryFieldBase';
import { AssetSelectorQueryFieldAssetId } from './fields/AssetSelectorQueryFieldAssetId';
import { AssetSelectorQueryFieldString } from './fields/AssetSelectorQueryFieldString';
import { AssetSelectorQueryFieldUuid } from './fields/AssetSelectorQueryFieldUuid';
import { AssetSelectorQueryFieldBool } from './fields/AssetSelectorQueryFieldBool';
import { AssetSelectorQueryFieldTimestamp } from './fields/AssetSelectorQueryFieldTimestamp';
import { AssetSelectorQueryFieldAccountId } from './fields/AssetSelectorQueryFieldAccountId';
import { assert } from '../../common/utils/typeUtils';
import { AssetSelectorQueryFieldIsSystem } from './fields/AssetSelectorQueryFieldIsSystem';
import { AssetSelectorQueryFieldWithDeleted } from './fields/AssetSelectorQueryFieldWithDeleted';
import { AssetSelectorQueryFieldIcon } from './fields/AssetSelectorQueryFieldIcon';
import { AssetSelectorQueryFieldParentIds } from './fields/AssetSelectorQueryFieldParentIds';
import { AssetSelectorQueryFieldTypeIds } from './fields/AssetSelectorQueryFieldTypeIds';
import { AssetSelectorQueryFieldBlock } from './fields/AssetSelectorQueryFieldBlock';
import { AssetSelectorQueryFieldQuery } from './fields/AssetSelectorQueryFieldWithQuery';
import { AssetSelectorQueryFieldScope } from './fields/AssetSelectorQueryFieldScope';
import { AssetSelectorQueryFieldRights } from './fields/AssetSelectorQueryFieldRights';
import { AssetSelectorQueryFieldTitle } from './fields/AssetSelectorQueryFieldTitle';
import { AssetSelectorQueryFieldReadinessRate } from './fields/AssetSelectorQueryFieldReadinessRate';
import { AssetSelectorQueryFieldFloat } from './fields/AssetSelectorQueryFieldFloat';
import { AssetSelectorQueryFieldIsAttractive } from './fields/AssetSelectorQueryFieldIsAttracting';

export type AssetSelectorQueryBuiltQuery = {
  query: SelectQueryBuilder<AssetEntity>;
  selection: AssetSelectorQueryFieldSelector[];
};

type AssetSelectorQueryFunc = {
  name: string;
  sql: (field) => string;
  agg: boolean;
};

const ASSET_VIEW_QUERY_FUNCS: AssetSelectorQueryFunc[] = [
  {
    name: 'count',
    sql: (f) => `COUNT(${f})`,
    agg: true,
  },
  {
    name: 'min',
    sql: (f) => `MIN(imc_to_double(${f}))`,
    agg: true,
  },
  {
    name: 'max',
    sql: (f) => `MAX(imc_to_double(${f}))`,
    agg: true,
  },
  {
    name: 'sum',
    sql: (f) => `SUM(imc_to_double(${f}))`,
    agg: true,
  },
  {
    name: 'avg',
    sql: (f) => `AVG(imc_to_double(${f}))`,
    agg: true,
  },
  {
    name: 'min_len',
    sql: (f) => `MIN(imc_len(${f}))`,
    agg: true,
  },
  {
    name: 'max_len',
    sql: (f) => `MAX(imc_len(${f}))`,
    agg: true,
  },
  {
    name: 'sum_len',
    sql: (f) => `SUM(imc_len(${f}))`,
    agg: true,
  },
  {
    name: 'avg_len',
    sql: (f) => `AVG(imc_len(${f}))`,
    agg: true,
  },
  {
    name: 'min_date',
    sql: (f) => `MIN(imc_to_timestamptz(${f}))`,
    agg: true,
  },
  {
    name: 'max_date',
    sql: (f) => `MAX(imc_to_timestamptz(${f}))`,
    agg: true,
  },
  {
    name: 'avg_date',
    sql: (f) => `AVG(imc_to_timestamptz(${f})`,
    agg: true,
  },
  {
    name: 'year',
    sql: (f) => `EXTRACT(YEAR FROM imc_to_timestamptz(${f}))`,
    agg: false,
  },
  {
    name: 'month',
    sql: (f) => `to_char(imc_to_timestamptz(${f}), 'YYYY-MM')`,
    agg: false,
  },
  {
    name: 'quarter',
    sql: (f) => `to_char(imc_to_timestamptz(${f}), 'YYYY-"Q"Q')`,
    agg: false,
  },
  {
    name: 'date',
    sql: (f) => `imc_to_timestamptz(${f})::date`,
    agg: false,
  },
  {
    name: 'string',
    sql: (f) => `imc_to_string(${f})`,
    agg: false,
  },
  {
    name: 'int',
    sql: (f) => `imc_to_double(${f})::int`,
    agg: false,
  },
  {
    name: 'bool',
    sql: (f) => `imc_to_boolean(${f})`,
    agg: false,
  },
  {
    name: 'double',
    sql: (f) => `imc_to_double(${f})`,
    agg: false,
  },
  {
    name: 'elapsed',
    sql: (f) => `EXTRACT(epoch FROM NOW() - imc_to_timestamptz(${f}))`,
    agg: false,
  },
];

export function isAggregateAssetPropsSelectionField(
  field: AssetPropsSelectionField,
) {
  if (field && typeof field === 'object') {
    const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(field.func);
    return func ? func.agg : false;
  } else return false;
}

const ASSET_VIEW_QUERY_FUNCS_MAP = new Map<string, AssetSelectorQueryFunc>();
for (const av_query_func of ASSET_VIEW_QUERY_FUNCS) {
  ASSET_VIEW_QUERY_FUNCS_MAP.set(av_query_func.name, av_query_func);
}

const SPECIAL_ASSET_VIEW_FIELDS: AssetSelectorQueryFieldBase[] = [
  new AssetSelectorQueryFieldAssetId('id', 'a.id'),
  new AssetSelectorQueryFieldString('name', 'a.name'),
  new AssetSelectorQueryFieldString('owntitle', 'a.title'),
  new AssetSelectorQueryFieldString('ownicon', 'a.icon'),
  new AssetSelectorQueryFieldUuid('workspaceid', 'a.workspace_id'),
  new AssetSelectorQueryFieldBool('isabstract', 'a.is_abstract'),
  new AssetSelectorQueryFieldTimestamp('createdat', 'a.created_at'),
  new AssetSelectorQueryFieldTimestamp('updatedat', 'a.updated_at'),
  new AssetSelectorQueryFieldTimestamp('deletedat', 'a.deleted_at'),
  new AssetSelectorQueryFieldAccountId('creatoruserid', 'a.creator_user_id'),
  new AssetSelectorQueryFieldFloat('index', 'a.index'),
  new AssetSelectorQueryFieldIsSystem(),
  new AssetSelectorQueryFieldWithDeleted(),
  new AssetSelectorQueryFieldIcon(),
  new AssetSelectorQueryFieldTitle(),
  new AssetSelectorQueryFieldParentIds(),
  new AssetSelectorQueryFieldTypeIds(),
  new AssetSelectorQueryFieldQuery(),
  new AssetSelectorQueryFieldScope(),
  new AssetSelectorQueryFieldRights(),
  new AssetSelectorQueryFieldReadinessRate(),
  new AssetSelectorQueryFieldIsAttractive(),
];
const SPECIAL_ASSET_VIEW_FIELDS_MAP = new Map<
  string,
  AssetSelectorQueryFieldBase
>();
for (const special_field of SPECIAL_ASSET_VIEW_FIELDS) {
  SPECIAL_ASSET_VIEW_FIELDS_MAP.set(special_field.name, special_field);
}

export type AssetSelectorQuerySelectionField = {
  field: AssetSelectorQueryFieldBase;
  as: string;
};

export type AssetSelectorQueryFieldSelector = {
  ref: string;
  as: string;
  reader: (
    row: Record<string, any>,
    out: Record<string, AssetPropValue>,
  ) => void;
};

export class AssetSelectorQueryBuilding {
  query: SelectQueryBuilder<AssetEntity>;
  selection: AssetSelectorQueryFieldSelector[] = [];
  joinedTableMarks = new Map<any, string>();
  namesCounter = 0;
  context: AssetSelectorContext;
  private _querySelectAdded = new Set<string>();

  constructor(
    query: SelectQueryBuilder<AssetEntity>,
    context: AssetSelectorContext,
  ) {
    this.query = query;
    this.context = context;
  }

  public applyWhere(where: AssetPropWhere) {
    this._applyPartialWhere(this.query, where);
  }

  public applyOrder(order: AssetPropsSelectionOrder[]) {
    for (const ord of order) {
      if (typeof ord === 'string') {
        const ref = this.requestProp(ord);
        this.query.addOrderBy(ref);
      } else {
        const ref = this.requestProp(ord.prop);
        if (ord.func) {
          const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(ord.func);
          assert(func);
          this.query.addOrderBy(func.sql(ref), ord.desc ? 'DESC' : 'ASC');
        } else {
          this.query.addOrderBy(ref, ord.desc ? 'DESC' : 'ASC');
        }
      }
    }
  }

  private _queryAddSelect(sql: string, as: string) {
    if (this._querySelectAdded.has(`${sql}\\---\\${as}`)) {
      return;
    }
    this.query.addSelect(sql, as);
    this._querySelectAdded.add(`${sql}\\---\\${as}`);
  }

  public applyGroup(group: AssetPropsSelectionField[], for_count: boolean) {
    for (const g of group) {
      if (typeof g === 'string') {
        const ref = this.requestProp(g);
        this.query.addGroupBy(ref);
        if (!for_count) {
          this._queryAddSelect(ref, g);
          this.selection.push({
            as: g,
            ref,
            reader: (row, res) => (res[g] = row[g]),
          });
        }
      } else {
        const ref = this.requestProp(g.prop);
        const as = g.as ?? g.prop;
        if (g.func) {
          const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(g.func);
          assert(func);
          this.query.addGroupBy(func.sql(ref));
          if (!for_count) {
            this._queryAddSelect(func.sql(ref), as);
            this.selection.push({
              as,
              ref,
              reader: (row, res) => (res[as] = row[as]),
            });
          }
        } else {
          this.query.addGroupBy(ref);
          if (!for_count) {
            this._queryAddSelect(ref, as);
            this.selection.push({
              as,
              ref,
              reader: (row, res) => (res[as] = row[as]),
            });
          }
        }
      }
    }
  }

  private _addSelectField(field: AssetPropsSelectionField) {
    if (typeof field === 'string') {
      const sel = this.selectProp(field, field);
      this._queryAddSelect(sel.ref, sel.as);
      this.selection.push(sel);
    } else {
      if (field.func) {
        const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(field.func);
        assert(func);
        const ref = this.requestProp(field.prop);
        const as = field.as ?? field.prop;
        this._queryAddSelect(func.sql(ref), as);
        this.selection.push({
          as,
          ref,
          reader: (row, res) => (res[as] = row[as]),
        });
      } else {
        const sel = this.selectProp(field.prop, field.as ?? field.prop);
        this._queryAddSelect(sel.ref, sel.as);
        this.selection.push(sel);
      }
    }
  }

  public applySelect(select: AssetPropsSelectionField[]) {
    for (const s of select) {
      this._addSelectField(s);
    }
  }

  public selectProp(prop: string, as: string): AssetSelectorQueryFieldSelector {
    const field = this.getPropField(prop);
    if (!field.allowSelect) {
      throw new ApiError(
        'Asset search: prop cannot be used to get value',
        ApiErrorCodes.PARAM_BAD_VALUE,
        {
          prop: prop,
        },
      );
    }
    return field.selectProp(this, as);
  }

  public requestProp(prop: string): string {
    const field = this.getPropField(prop);
    if (!field.allowSelect) {
      throw new ApiError(
        'Asset search: prop cannot be used to get value',
        ApiErrorCodes.PARAM_BAD_VALUE,
        {
          prop: prop,
        },
      );
    }
    return field.requestProp(this);
  }

  public getPropField(prop: string): AssetSelectorQueryFieldBase {
    let special_field = SPECIAL_ASSET_VIEW_FIELDS_MAP.get(prop);
    if (!special_field) {
      special_field = SPECIAL_ASSET_VIEW_FIELDS_MAP.get(prop.toLowerCase());
    }
    if (special_field) {
      return special_field;
    } else return new AssetSelectorQueryFieldBlock(prop);
  }

  private _applyPartialWhere(
    query: WhereExpressionBuilder,
    where: AssetPropWhere,
  ) {
    for (const [prop, cond] of Object.entries(where)) {
      if (cond === undefined) continue;

      const cond_op = convertAssetPropWhereConditionToOp(cond);
      if (cond_op.op === AssetPropWhereOpKind.AND) {
        for (const w of cond_op.v) {
          this._applyPartialWhere(query, w);
        }
      } else if (cond_op.op === AssetPropWhereOpKind.OR) {
        query.andWhere(
          new Brackets((subQCond1) => {
            for (const w of cond_op.v) {
              subQCond1.orWhere(
                new Brackets((subQCond2) => {
                  this._applyPartialWhere(subQCond2, w);
                }),
              );
            }
          }),
        );
      } else {
        const field = this.getPropField(prop);
        if (!field.allowWhere) {
          throw new ApiError(
            'Asset search: prop cannot be used in where',
            ApiErrorCodes.PARAM_BAD_VALUE,
            {
              prop: prop,
            },
          );
        }
        field.where(this, query, cond_op);
      }
    }
  }
}

export class AssetSelectorQuery {
  private _where: AssetPropWhere = {};
  private _context: AssetSelectorContext & { tx: EntityManager };
  private _count: number | undefined;
  private _offset: number | undefined;
  private _select: AssetPropsSelectionField[] = [];
  private _group: AssetPropsSelectionField[] = [];
  private _order: AssetPropsSelectionOrder[] = [];
  //private _project_ids: string[];

  constructor(
    context: AssetSelectorContext & { tx: EntityManager },
    //pq?: ProjectQuery,
  ) {
    this._context = context;
    //this._project_ids = pq?.ids ? [...pq.ids] : [context.projectId];
  }

  where(where: AssetPropWhere) {
    this._where = where;
    return this;
  }

  select(select: AssetPropsSelectionField[]) {
    this._select = select;
    return this;
  }

  group(group: AssetPropsSelectionField[]) {
    this._group = group;
    return this;
  }

  order(order: AssetPropsSelectionOrder[]) {
    this._order = order;
    return this;
  }

  count(count: number) {
    this._count = count;
    return this;
  }

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

  private _getBoolCondStaticValue(
    val: AssetPropWhereCondition,
  ): boolean | null {
    if (val === undefined || val === null) return null;
    if (typeof val === 'boolean') return val;
    const cond_op = convertAssetPropWhereConditionToOp(val);
    if (cond_op.op === AssetPropWhereOpKind.EQUAL) {
      if (cond_op.v === null) return null;
      else return !!cond_op.v;
    } else if (cond_op.op === AssetPropWhereOpKind.EQUAL_NOT) {
      if (cond_op.v === null) return null;
      else return !cond_op.v;
    } else if (cond_op.op === AssetPropWhereOpKind.EMPTY) {
      return !cond_op.v;
    } else if (cond_op.op === AssetPropWhereOpKind.CHECKED) {
      return !!cond_op.v;
    } else {
      throw new ApiError(
        'Unexpected static boolean value',
        ApiErrorCodes.PARAM_BAD_VALUE,
      );
    }
  }

  private _getBoolCondStaticValueVariants(
    where: AssetPropWhere,
    variants: string[],
  ): boolean | null {
    for (const v of variants) {
      const r = this._getBoolCondStaticValue(where[v]);
      if (r !== null) return r;
    }
    return null;
  }

  private _checkAggMixing() {
    const grouping_keys = new Set<string>();
    for (const g of this._group) {
      if (typeof g === 'string') {
        grouping_keys.add(`\\\\${g}`);
      } else if (g.func) {
        const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(g.func);
        if (!func) {
          throw new ApiError(
            'Unknow function in asset selection',
            ApiErrorCodes.PARAM_BAD_VALUE,
            {
              field: g,
            },
          );
        }
        if (func.agg) {
          throw new ApiError(
            'Group cannot contain agg funcitons',
            ApiErrorCodes.PARAM_BAD_VALUE,
            {
              field: g,
            },
          );
        }
        grouping_keys.add(`${g.func}\\\\${g.prop}`);
      } else {
        grouping_keys.add(`\\\\${g.prop}`);
      }
    }
    let sel_has_agg = false;
    let sel_has_noagg = false;
    for (const sel of [...this._select, ...this._order]) {
      let noagg_key = '';
      if (typeof sel === 'string') {
        noagg_key = `\\\\${sel}`;
      } else if (sel.func) {
        const func = ASSET_VIEW_QUERY_FUNCS_MAP.get(sel.func);
        if (!func) {
          throw new ApiError(
            'Unknow function in asset selection',
            ApiErrorCodes.PARAM_BAD_VALUE,
            {
              field: sel,
            },
          );
        }
        if (!func.agg) {
          noagg_key = `${sel.func}\\\\${sel.prop}`;
        }
      } else {
        noagg_key = `\\\\${sel.prop}`;
      }
      if (noagg_key) {
        sel_has_noagg = true;
        if (this._group.length > 0) {
          if (!grouping_keys.has(noagg_key)) {
            throw new ApiError(
              'Cannot use not aggregated prop with group',
              ApiErrorCodes.PARAM_BAD_VALUE,
              {
                field: sel,
              },
            );
          }
        }
      } else {
        sel_has_agg = true;
      }
    }
    if (this._group.length === 0 && sel_has_agg && sel_has_noagg) {
      throw new ApiError(
        'Cannot use both aggregated values and not without group',
        ApiErrorCodes.PARAM_BAD_VALUE,
      );
    }
  }

  private _buildQueryInner(
    for_count: boolean,
    qb: AssetSelectorQueryBuilding,
  ): void {
    const with_deleted = this._getBoolCondStaticValueVariants(this._where, [
      'withDeleted',
      'withdeleted',
    ]);
    if (with_deleted !== true) {
      qb.query.andWhere('a.deleted_at IS NULL');
    }
    const is_system = this._getBoolCondStaticValueVariants(this._where, [
      'isSystem',
      'issystem',
    ]);
    if (is_system === false) {
      qb.query.andWhere('a.project_id = :project_id', {
        project_id: decodeBigNumberKey(this._context.projectId),
      });
      // qb.query.andWhere('a.project_id = ANY(:project_ids)', {
      //   project_ids: this._project_ids.map((p_id) => decodeBigNumberKey(p_id)),
      // });
    } else {
      qb.query.andWhere(
        '(a.project_id = :project_id OR a.project_id = :system_project_id)',
        {
          project_id: decodeBigNumberKey(this._context.projectId),
          system_project_id: SYSTEM_PROJECT_ID,
        },
      );
      // qb.query.andWhere(
      //   '(a.project_id = ANY(:project_ids) OR a.project_id = :system_project_id)',
      //   {
      //     project_ids: this._project_ids.map((p_id) =>
      //       decodeBigNumberKey(p_id),
      //     ),
      //     system_project_id: SYSTEM_PROJECT_ID,
      //   },
      // );
    }

    qb.applyWhere(this._where);

    if (this._count !== undefined) {
      qb.query.limit(this._count);
    }
    if (this._offset !== undefined) {
      qb.query.offset(this._offset);
    }

    qb.applyGroup(this._group, for_count);
    if (!for_count) {
      qb.applySelect(this._select);
      qb.applyOrder(this._order);
    }
  }

  private _buildQuery(for_count: boolean): AssetSelectorQueryBuiltQuery {
    this._checkAggMixing();

    let out_query;
    let building: AssetSelectorQueryBuilding;
    if (for_count && this._group.length > 0) {
      out_query = this._context.tx.createQueryBuilder().from((sq) => {
        const db_query = sq.from(AssetEntity, 'a');
        building = new AssetSelectorQueryBuilding(db_query, this._context);
        building.query.select('1');
        this._buildQueryInner(for_count, building);
        return db_query;
      }, 'x');
    } else {
      out_query = this._context.tx
        .createQueryBuilder()
        .from(AssetEntity, 'a')
        .select();

      building = new AssetSelectorQueryBuilding(out_query, this._context);
      this._buildQueryInner(for_count, building);
    }
    if (for_count) {
      out_query.addSelect('COUNT(*) as cnt');
    }
    return {
      query: out_query,
      selection: building.selection,
    };
  }

  async fetchRows<T extends AssetViewDTO>(): Promise<T[]> {
    const q = this._buildQuery(false);
    const rows = await q.query.getRawMany();
    return rows.map((row) => {
      const res: any = {};
      for (const sel of q.selection) {
        sel.reader(row, res);
      }
      return res;
    });
  }

  async fetchCount(): Promise<number> {
    if (
      this._group.length === 0 &&
      this._select.length > 0 &&
      this._select.every((s) => isAggregateAssetPropsSelectionField(s))
    ) {
      return 1;
    }
    const q = this._buildQuery(true);
    const res = await q.query.getRawOne();
    return res ? parseInt(res.cnt) : 0;
  }
}