import type pg from "pg-promise/typescript/pg-subset";
import type { SQLHandler, SQLOptions } from "prostgles-types";
import { getSerialisableError, tryCatchV2 } from "prostgles-types";
import { getDBGeneratedSchema } from "../DBSchemaBuilder/getDBGeneratedSchema";
import { getFunctionsTypescriptSchema } from "../DBSchemaBuilder/getFunctionsTypescriptSchema";
import type { DB, Prostgles } from "../Prostgles";
import type { Join } from "../ProstglesTypes";
import { PubSubManager } from "../PubSubManager/PubSubManager";
import { getCreatePubSubManagerError } from "../PubSubManager/getCreatePubSubManagerError";
import type { PublishParser } from "../PublishParser/PublishParser";
import type { ServerFunctionDefinition } from "../PublishParser/defineServerFunction";
import { getQueryErrorPositionInfo } from "../TableConfig/runSQLFile";
import type { Graph } from "../shortestPath";
import { clone } from "../utils/utils";
import type {
  DBHandlerServer,
  DbTxTableHandlers,
  LocalParams,
  SQLHandlerServer,
  TableSchema,
  TxCB,
} from "./DboBuilderTypes";
import { QueryStreamer } from "./QueryStreamer";
import { TableHandler } from "./TableHandler/TableHandler";
import type { JoinPaths } from "./ViewHandler/ViewHandler";
import { parseJoinPath } from "./ViewHandler/parseJoinPath";
import type { PGConstraint } from "./dboBuilderUtils";
import {
  getCanExecute,
  getConstraints,
  getSerializedClientErrorFromPGError,
} from "./dboBuilderUtils";
import { prepareShortestJoinPaths } from "./prepareShortestJoinPaths";
import { cacheDBTypes, runSQL } from "./runSql/runSQL";
import { getDetailedFieldInfo, type getDbTypes } from "./runSql/runSqlUtils";
import { getTablesForSchemaPostgresSQL } from "./schema/getTablesForSchemaPostgresSQL";

export * from "./DboBuilderTypes";
export * from "./dboBuilderUtils";

export class DboBuilder {
  tablesOrViews?: TableSchema[];
  /**
   * Used in obtaining column names for error messages
   */
  constraints?: PGConstraint[];

  db: DB;

  /**
   * @deprecated
   * Use dboMap instead. Will be removed in future versions.
   */
  dbo: DBHandlerServer;

  dboMap: Map<string, TableHandler> = new Map();

  /**
   * Undefined if cannot create table triggers
   */
  private _pubSubManager?: PubSubManager;

  /**
   * Used for db.sql field type details
   */
  dbTypesCache?: Awaited<ReturnType<typeof getDbTypes>>;

  DATA_TYPES_DBKEY = "";

  queryStreamer: QueryStreamer;

  get tables(): TableSchema[] {
    return this.tablesOrViews ?? [];
    // .map(({ name, columns }) => {
    //   const info = this.dboMap.get(name)?.tableOrViewInfo;
    //   if (!info) return undefined;
    //   return {
    //     name,
    //     info,
    //     columns,
    //   } satisfies DbTableInfo;
    // })
    // .filter(isDefined);
  }

  getDetailedFieldInfo = async (fields: pg.IColumn[]) => {
    return getDetailedFieldInfo(await this.cacheDBTypes(), fields);
  };

  getPubSubManagerPromise?: Promise<PubSubManager>;
  getPubSubManager = async (): Promise<PubSubManager> => {
    this.getPubSubManagerPromise ??= (async () => {
      if (!this._pubSubManager) {
        const canExecute = await getCanExecute(this.db);
        if (!canExecute)
          throw "PubSubManager based subscriptions not possible: Cannot run EXECUTE statements on this connection";

        const {
          data: pubSubManager,
          error,
          hasError,
        } = await tryCatchV2(async () => {
          const pubSubManager = await PubSubManager.create(this);
          return pubSubManager;
        });
        this._pubSubManager = pubSubManager;
        if (hasError || !this._pubSubManager) {
          await this.prostgles.opts.onLog?.({
            type: "debug",
            command: "PubSubManager.create",
            duration: 0,
            error: getSerialisableError(error),
          });
          console.error("Could not create PubSubManager", getQueryErrorPositionInfo(error));
          throw "Could not create this._pubSubManager check logs";
        }
      }
      return this._pubSubManager;
    })();
    return this.getPubSubManagerPromise;
  };

  tableTsDefinitions?: string;
  functionTsDefinitions?: string;
  joinGraph?: Graph;
  private shortestJoinPaths: JoinPaths = [];

  prostgles: Prostgles;
  publishParser?: PublishParser;

  onSchemaChange?: (event: { command: string; query: string }) => void;

  private constructor(prostgles: Prostgles) {
    this.prostgles = prostgles;
    if (!this.prostgles.db) throw "db missing";
    this.db = this.prostgles.db;
    this.dbo = {} as unknown as DBHandlerServer;
    this.dboMap = new Map();
    this.queryStreamer = new QueryStreamer(this);
  }

  private init = async () => {
    await this.build();
    /* If watchSchema is enabled then PubSubManager must be created (if possible) because it creates the event trigger */
    if (this.prostgles.schemaWatch?.type.watchType === "DDL_trigger") {
      await this.getPubSubManager();
    }

    return this;
  };

  public static create = async (prostgles: Prostgles): Promise<DboBuilder> => {
    const res = new DboBuilder(prostgles);
    return await res.init();
  };

  destroy() {
    return this._pubSubManager?.destroy();
  }

  _joins?: Join[];
  get joins(): Join[] {
    return clone(this._joins ?? []).filter((j) => j.tables[0] !== j.tables[1]);
  }

  set joins(j: Join[]) {
    this._joins = clone(j);
  }

  getAllJoinPaths() {
    return this.shortestJoinPaths;
  }

  prepareShortestJoinPaths = async () => {
    const { joins, shortestJoinPaths, joinGraph } = await prepareShortestJoinPaths(this);
    this.joinGraph = joinGraph;
    this.joins = joins;
    this.shortestJoinPaths = shortestJoinPaths;
  };

  sql: SQLHandler = (q, args, options) => {
    return this.runSQL(q, args, options, undefined);
  };
  runSQL: SQLHandlerServer = async (
    query: string,
    params: unknown,
    options: SQLOptions | undefined,
    localParams: LocalParams | undefined,
  ) => {
    // eslint-disable-next-line @typescript-eslint/no-unsafe-return
    return runSQL
      .bind(this)(query, params, options, localParams)
      .catch((error) =>
        // eslint-disable-next-line @typescript-eslint/prefer-promise-reject-errors
        Promise.reject(
          getSerializedClientErrorFromPGError(error, {
            type: "sql",
            localParams,
            prostgles: this.prostgles,
          }),
        ),
      );
  };

  canSubscribe = false;
  checkingCanSubscribe = false;
  async build(): Promise<DBHandlerServer> {
    if (!this.canSubscribe && !this.checkingCanSubscribe) {
      this.checkingCanSubscribe = true;
      const subscribeError = await getCreatePubSubManagerError(this);
      if (subscribeError) {
        console.error(
          "Could not initiate PubSubManager. Realtime data/Subscriptions will not work. Error: ",
          subscribeError,
        );
        this.canSubscribe = false;
      } else {
        this.canSubscribe = true;
      }
      this.checkingCanSubscribe = false;
    }
    const start = Date.now();
    const tablesOrViewsReq = await getTablesForSchemaPostgresSQL(this, {
      schemaFilter: this.prostgles.opts.schemaFilter,
    });
    await this.prostgles.opts.onLog?.({
      type: "debug",
      command: "DboBuilder.getTablesForSchemaPostgresSQL",
      data: tablesOrViewsReq.durations,
      duration: Date.now() - start,
    });
    const tablesOrViews = tablesOrViewsReq.result;
    this.tablesOrViews = tablesOrViews;

    this.constraints = await getConstraints(this.db, this.prostgles.opts.schemaFilter);
    await this.prepareShortestJoinPaths();

    this.dbo = {};
    this.tablesOrViews.map((tov) => {
      const columnsForTypes = tov.columns.slice(0).sort((a, b) => a.name.localeCompare(b.name));

      const filterKeywords = Object.values(this.prostgles.keywords);
      const $filterCol = columnsForTypes.find((c) => filterKeywords.includes(c.name));
      if ($filterCol) {
        throw `DboBuilder init error: \n\nTable ${JSON.stringify(tov.name)} column ${JSON.stringify($filterCol.name)} is colliding with Prostgles filtering functionality ($filter keyword)
                Please provide a replacement keyword name using the $filter_keyName init option. 
                Alternatively you can rename the table column\n`;
      }

      const tableHandler = new TableHandler({
        db: this.db,
        tableOrViewInfo: tov,
        dboBuilder: this,
        tx: undefined,
        config: this.prostgles.opts.tableConfig?.[tov.name],
        joinPaths: this.shortestJoinPaths,
      });
      this.dbo[tov.name] = tableHandler;
      this.dboMap.set(tov.name, tableHandler);
    });

    if (this.prostgles.opts.transactions) {
      const txKey = "tx";

      //@ts-ignore
      this.dbo[txKey] = <R, TH extends DbTxTableHandlers & Pick<DBHandlerServer, "sql">>(
        cb: TxCB<Promise<R>, TH>,
      ) => this.getTX(cb);
    }

    const { functions } = this.prostgles.opts;
    let resolvedFunctions = {} as { [key: string]: ServerFunctionDefinition };
    if (functions) {
      try {
        resolvedFunctions = await functions(undefined);
      } catch (e) {
        console.error("Invalid functions:", e);
        throw e;
      }
    }

    this.tableTsDefinitions = getDBGeneratedSchema({
      config: this.prostgles.opts.tableConfig,
      tablesOrViews,
    });
    this.functionTsDefinitions = getFunctionsTypescriptSchema(
      this.prostgles.opts,
      tablesOrViews,
      resolvedFunctions,
    );

    return this.dbo;
  }

  getSchema = () => {
    if (!this.tablesOrViews) {
      throw new Error("Unexpected error: tablesOrViews is undefined");
    }
    return this.tablesOrViews;
  };
  getTsDefinitions = <DDL extends string | undefined = undefined>({
    excludeFunctions,
    extraTables = [],
    ddlWithRollback,
  }: {
    excludeFunctions?: boolean;
    extraTables?: TableSchema[];
    /**
     * Optionally provide a TRUSTED DDL statement obtain an updated schema after applying the DDL.
     * It is crucial that the statement is trusted and does not container commits.
     * This is useful for generating an updated TypeScript schema after running a migration SQL file, for example.
     * Note that this will be run within a transaction that is rolled back, so it SHOULD not affect the actual database schema.
     */
    ddlWithRollback?: DDL;
  } = {}): DDL extends string ? Promise<{ tsSchema: string; tablesOrViews: TableSchema[] }>
  : { tsSchema: string; tablesOrViews: TableSchema[] } => {
    const getSchema = (tablesOrViews: TableSchema[]) => {
      const tableTsDefinitions = getDBGeneratedSchema({
        config: this.prostgles.opts.tableConfig,
        tablesOrViews: [
          ...tablesOrViews,
          ...extraTables.filter(
            (et) => !(this.tablesOrViews ?? []).some((tov) => tov.name === et.name),
          ),
        ],
      });

      const tsSchema = [
        `/* Schema definition generated by prostgles-server */`,
        tableTsDefinitions,
        excludeFunctions ? "" : this.functionTsDefinitions,
      ]
        .filter((v) => v)
        .join("\n");
      return { tsSchema, tablesOrViews };
    };
    if (ddlWithRollback) {
      return new Promise<{ tsSchema: string; tablesOrViews: TableSchema[] }>((resolve, reject) => {
        getTablesForSchemaPostgresSQL(this, {
          schemaFilter: this.prostgles.opts.schemaFilter,
          ddlWithRollback,
        })
          .then((res) => {
            const tablesOrViews = res.result;
            resolve(getSchema(tablesOrViews));
          })
          .catch(reject);
      }) as DDL extends string ? Promise<{ tsSchema: string; tablesOrViews: TableSchema[] }>
      : { tsSchema: string; tablesOrViews: TableSchema[] };
    }
    const tablesOrViews = this.tablesOrViews;
    if (!tablesOrViews) {
      throw new Error("Unexpected error: tablesOrViews is undefined");
    }
    return getSchema(tablesOrViews) as DDL extends string ?
      Promise<{ tsSchema: string; tablesOrViews: TableSchema[] }>
    : { tsSchema: string; tablesOrViews: TableSchema[] };
  };

  getShortestJoinPath = (
    viewHandler: TableHandler,
    target: string,
  ): JoinPaths[number] | undefined => {
    const source = viewHandler.name;
    if (source === target) {
      parseJoinPath({
        rawPath: target,
        rootTable: source,
        viewHandler,
      });

      return {
        t1: source,
        t2: target,
        path: [source],
      };
    }

    const jp = this.shortestJoinPaths.find((jp) => jp.t1 === source && jp.t2 === target);
    return jp;
  };

  getTX = async <R, TH extends DbTxTableHandlers>(cb: TxCB<Promise<R> | R, TH>) => {
    return this.db.tx((t) => {
      const dbTX: DbTxTableHandlers = {};
      this.tablesOrViews?.map((tov) => {
        dbTX[tov.name] = new TableHandler({
          db: this.db,
          tableOrViewInfo: tov,
          dboBuilder: this,
          tx: { t, dbTX },
          config: this.prostgles.opts.tableConfig?.[tov.name],
          joinPaths: this.shortestJoinPaths,
        });
      });

      return cb(dbTX as TH, t);
    });
  };

  cacheDBTypes = cacheDBTypes.bind(this);
}