import type { AnyObject, FieldFilter } from "prostgles-types/dist";
import { asName, getKeys, isEmpty, isObject, pickKeys } from "prostgles-types/dist";
import type { DBHandlerServer } from "../../Prostgles";
import type { ValidateRowArgsCommon, ValidateRowBasic } from "../../PublishParser/PublishParser";
import { asValue } from "../../PubSubManager/PubSubManagerUtils";
import type { LocalParams, TableSchemaColumn } from "../DboBuilder";
import { pgp } from "../DboBuilder";
import { parseFunctionObject } from "../QueryBuilder/QueryBuilder";
import { validateObj } from "../ViewHandler/ViewHandler";
import type { TableHandler, ValidatedParams } from "./TableHandler";

type RowFieldDataPlain = {
  type: "plain";
  column: TableSchemaColumn;
  fieldValue: any;
};

type RowFieldDataFunction = {
  type: "function";
  column: TableSchemaColumn;
  funcName: string;
  args: any[];
};
type RowFieldData = RowFieldDataPlain | RowFieldDataFunction;

type ParsedRowFieldData = {
  escapedCol: string;
  escapedVal: string;
};

type ParseDataArgs = {
  rows: AnyObject[];
  allowedCols: string[];
  dbTx: DBHandlerServer;
  tx: ValidateRowArgsCommon["tx"];
  command: "update" | "insert";
  validationOptions: {
    localParams: undefined | LocalParams;
    validate: undefined | ValidateRowBasic;
  };
};

export class DataValidator {
  rowFieldData?: RowFieldData[][];
  parsedRowFieldData?: ParsedRowFieldData[][];
  tableHandler: TableHandler;
  constructor(tableHandler: TableHandler) {
    this.tableHandler = tableHandler;
  }

  parse = async (args: ParseDataArgs) => {
    const { command } = args;
    const rowFieldData = await getValidatedRowFieldData(args, this.tableHandler);
    const parsedRowFieldData = getParsedRowFieldData(rowFieldData, args);
    if (command === "update") {
      if (rowFieldData.some((rowParts) => rowParts.length === 0)) {
        throw "Empty row. No data provided for update";
      }
    }

    return {
      parsedRowFieldData,
      getQuery: () => getQuery(command, parsedRowFieldData, this.tableHandler.escapedName),
    };
  };
}

const getQuery = (
  type: "insert" | "update",
  parsedRowFieldData: ParsedRowFieldData[][],
  escapedTableName: string,
): string => {
  if (type === "insert") {
    const uniqueColumns = Array.from(
      new Set(parsedRowFieldData.flatMap((row) => row.map((r) => r.escapedCol))),
    );
    const values = parsedRowFieldData
      .map(
        (row) =>
          `(${uniqueColumns.map((colName) => row.find((r) => r.escapedCol === colName)?.escapedVal ?? "DEFAULT")})`,
      )
      .join(",\n");
    const whatToInsert =
      !uniqueColumns.length ? "DEFAULT VALUES" : `(${uniqueColumns}) VALUES ${values}`;
    return `INSERT INTO ${escapedTableName} ${whatToInsert} `;
  } else {
    const query =
      parsedRowFieldData
        .map((rowParts) => {
          return (
            `UPDATE ${escapedTableName} SET ` +
            rowParts.map((r) => `${r.escapedCol} = ${r.escapedVal} `).join(",\n")
          );
        })
        .join(";\n") + " ";

    return query;
  }
};

type PrepareFieldValuesArgs = {
  row: AnyObject | undefined;
  forcedData: AnyObject | undefined;
  allowedCols: FieldFilter | undefined;
  removeDisallowedFields?: boolean;
  tableHandler: TableHandler;
};
/**
 * Apply forcedData, remove disallowed columns, validate against allowed columns:
 * @example ({ item_id: 1 }, { user_id: 32 }) => { item_id: 1, user_id: 32 }
 * OR
 * ({ a: 1 }, { b: 32 }, ["c", "d"]) => throw "a field is not allowed"
 * @param {Object} obj - initial data
 * @param {Object} forcedData - set/override property
 * @param {string[]} allowed_cols - allowed columns (excluding forcedData) from table rules
 */
const getValidatedRow = ({
  row = {},
  forcedData = {},
  allowedCols,
  removeDisallowedFields = false,
  tableHandler,
}: PrepareFieldValuesArgs): AnyObject => {
  const column_names = tableHandler.column_names.slice(0);
  if (!column_names.length) {
    throw "table column_names mising";
  }
  const validatedAllowedColumns = tableHandler.parseFieldFilter(allowedCols, false);

  let finalRow = { ...row };
  if (removeDisallowedFields && !isEmpty(finalRow)) {
    finalRow = pickKeys(finalRow, validatedAllowedColumns);
  }

  /* If has keys check against allowed_cols */
  validateObj(finalRow, validatedAllowedColumns);

  /** Apply forcedData */
  if (!isEmpty(forcedData)) {
    finalRow = { ...finalRow, ...forcedData };
  }

  /** Validate forcedData */
  validateObj(finalRow, column_names.slice(0));
  return finalRow;
};

/**
 * Add synced_field value if missing
 * prepareFieldValues(): Apply forcedData, remove disallowed columns, validate against allowed columns
 * tableConfigurator?.checkColVal(): Validate column min/max/isText/lowerCased/trimmed values
 */
export const prepareNewData = ({
  row,
  forcedData,
  allowedFields,
  tableRules,
  removeDisallowedFields = false,
  tableConfigurator,
  tableHandler,
}: ValidatedParams) => {
  const synced_field = tableHandler.config?.syncConfig?.synced_field;

  /* Update synced_field if sync is on and missing */
  if (synced_field && !row[synced_field]) {
    row[synced_field] = Date.now();
  }

  const data = getValidatedRow({
    tableHandler,
    row,
    forcedData,
    allowedCols: allowedFields,
    removeDisallowedFields,
  });
  const dataKeys = getKeys(data);

  dataKeys.forEach((col) => {
    tableConfigurator?.checkColVal({
      table: tableHandler.name,
      col,
      value: data[col],
    });
    const colConfig = tableConfigurator?.getColumnConfig(tableHandler.name, col);
    if (colConfig && isObject(colConfig) && "isText" in colConfig && data[col]) {
      if (colConfig.lowerCased) {
        data[col] = data[col].toString().toLowerCase();
      }
      if (colConfig.trimmed) {
        data[col] = data[col].toString().trim();
      }
    }
  });

  const allowedCols = tableHandler.columns
    .filter((c) => dataKeys.includes(c.name))
    .map((c) => c.name);
  return { data, allowedCols };
};

/**
 * Ensures:
 *  - allowedCols are valid and checked against data
 *  - validate()
 *  - update is not empty
 *  - no duplicate column names ( could update with $func and plain value for same column )
 */
const getValidatedRowFieldData = async (
  { allowedCols, rows, validationOptions, dbTx, tx, command }: ParseDataArgs,
  tableHandler: TableHandler,
) => {
  if (!allowedCols.length && command === "update") {
    throw "allowedColumns cannot be empty";
  }
  const rowFieldData = await Promise.all(
    rows.map(async (nonvalidatedRow) => {
      let row = pickKeys(nonvalidatedRow, allowedCols);
      const initialRowKeys = Object.keys(row);
      if (validationOptions.validate) {
        if (!validationOptions.localParams) {
          throw "localParams missing for validate";
        }
        row = await validationOptions.validate({
          row,
          dbx: dbTx,
          tx,
          localParams: validationOptions.localParams,
          command,
          data: row,
        });
      }
      const keysAddedDuringValidate = Object.keys(row).filter(
        (newKey) => !initialRowKeys.includes(newKey),
      );

      const getColumn = (fieldName: string) => {
        if (!allowedCols.concat(keysAddedDuringValidate).includes(fieldName)) {
          throw `Unexpected/Dissallowed column name: ${fieldName}`;
        }
        const column = tableHandler.columns.find((c) => c.name === fieldName);
        if (!column) {
          throw `Invalid column: ${fieldName}`;
        }
        return column;
      };

      const rowPartValues = Object.entries(row).map(([fieldName, fieldValue]) => {
        const column = getColumn(fieldName);
        if (isObject(fieldValue)) {
          // const textPatch = getTextPatch(column, fieldValue);
          // if(textPatch){
          //   return {
          //     type: "plain",
          //     column,
          //     fieldValue: textPatch,
          //   } satisfies RowFieldData;
          // }

          const [firstKey, ...otherkeys] = Object.keys(fieldValue);
          const func =
            firstKey && !otherkeys.length ?
              convertionFuncs.some((f) => `$${f.name}` === firstKey)
            : undefined;
          if (func) {
            const { funcName, args } = parseFunctionObject(fieldValue);
            return {
              type: "function",
              column,
              funcName,
              args,
            } satisfies RowFieldData;
          }
        }
        return {
          type: "plain",
          column: getColumn(fieldName),
          fieldValue,
        } satisfies RowFieldData;
      });

      return rowPartValues;
    }),
  );

  return rowFieldData;
};

// const getTextPatch = (c: TableSchemaColumn, fieldValue: any) => {
//   if (
//     c.data_type === "text" &&
//     fieldValue &&
//     isObject(fieldValue) &&
//     !["from", "to"].find((key) => typeof fieldValue[key] !== "number")
//   ) {
//     const unrecProps = Object.keys(fieldValue).filter(
//       (k) => !["from", "to", "text", "md5"].includes(k)
//     );
//     if (unrecProps.length) {
//       throw "Unrecognised params in textPatch field: " + unrecProps.join(", ");
//     }
//     const patchedTextData: {
//       fieldName: string;
//       from: number;
//       to: number;
//       text: string;
//       md5: string;
//     } = {
//       ...fieldValue,
//       fieldName: c.name,
//     } as any;

//     // if (tableRules && !tableRules.select) throw "Select needs to be permitted to patch data";
//     // const rows = await this.find(filter, { select: patchedTextData.reduce((a, v) => ({ ...a, [v.fieldName]: 1 }), {}) }, undefined, tableRules);

//     // if (rows.length !== 1) {
//     //   throw "Cannot patch data within a filter that affects more/less than 1 row";
//     // }
//     // return unpatchText(rows[0][p.fieldName], patchedTextData);
//     const rawValue = `OVERLAY(${asName(c.name)} PLACING ${asValue(patchedTextData.text)} FROM ${asValue(patchedTextData.from)} FOR ${asValue(patchedTextData.to - patchedTextData.from + 1)})`;
//     return rawValue;
//   }

//   return undefined;
// };

const getParsedRowFieldDataFunction = (rowPart: RowFieldDataFunction, args: ParseDataArgs) => {
  const func = convertionFuncs.find((f) => `$${f.name}` === rowPart.funcName);
  if (!func) {
    throw `Unknown function: ${rowPart.funcName}. Expecting one of: ${convertionFuncs.map((f) => f.name).join(", ")}`;
  }
  if (func.onlyAllowedFor && func.onlyAllowedFor !== args.command) {
    throw `Function ${rowPart.funcName} is only allowed for ${func.onlyAllowedFor} but not ${args.command}`;
  }
  return func.getQuery(rowPart);
};

const getParsedRowFieldData = (rowFieldData: RowFieldData[][], args: ParseDataArgs) => {
  const parsedRowFieldData = rowFieldData.map((rowParts) => {
    return rowParts.map((rowPart) => {
      let escapedVal: string;
      if (rowPart.type === "function") {
        escapedVal = getParsedRowFieldDataFunction(rowPart, args);
      } else {
        /** Prevent pg-promise formatting jsonb */
        const colIsJSON = ["json", "jsonb"].includes(rowPart.column.data_type);
        escapedVal = pgp.as.format(colIsJSON ? "$1:json" : "$1", [rowPart.fieldValue]);
      }

      /**
       * Cast to type to avoid array errors (they do not cast automatically)
       */
      escapedVal += `::${rowPart.column.udt_name}`;

      return {
        escapedCol: asName(rowPart.column.name),
        escapedVal,
      };
    });
  });

  return parsedRowFieldData;
};

type ConvertionFunc = {
  name: string;
  description?: string;
  onlyAllowedFor?: "insert" | "update";
  getQuery: (fieldPart: RowFieldDataFunction) => string;
};

const convertionFuncs: ConvertionFunc[] = [
  ...[
    "ST_GeomFromText",
    "ST_Point",
    "ST_MakePoint",
    "ST_MakePointM",
    "ST_PointFromText",
    "ST_GeomFromEWKT",
    "ST_GeomFromGeoJSON",
  ].map(
    (name) =>
      ({
        name,
        getQuery: ({ args }) => {
          const argList = args.map((arg) => asValue(arg)).join(", ");
          return `${name}(${argList})`;
        },
      }) satisfies ConvertionFunc,
  ),
  {
    name: "to_timestamp",
    getQuery: ({ args }) => `to_timestamp(${asValue(args[0])}::BIGINT/1000.0)::timestamp`,
  },
  {
    name: "merge",
    description:
      "Merge the provided jsonb objects into the existing column value, ensuring that a null source value will be coalesced with provided values",
    onlyAllowedFor: "update",
    getQuery: ({ args, column }) => {
      if (!args.length) throw "merge function requires at least one argument";
      const argVals = args.map((arg) => asValue(arg));
      const mergedArgs = argVals.join(" || ");
      return `COALESCE(${asName(column.name)}, ${argVals.join(", ")}) || ${mergedArgs}`;
    },
  },
];