import sql, { ConnectionPool, IRecordSet } from "mssql";
import { SQLServerConfigSchema } from "./internal/sql-server-config.js";
import { sdkLogger as logger } from "../reporting-service/logger.js";
import { ERPResponse } from "../../types/erp-types.js";
import { SQLServerConfiguration } from "./configuration.js";

type PagingParams = {
  limit?: number;
  offset?: number;
};

export class SqlServerService {
  private connection: ConnectionPool | null = null;
  private config: SQLServerConfiguration;

  constructor(config: SQLServerConfiguration) {
    this.config = config;
  }

  async dispose() {
    await this.closeConnection();
  }

  /**
   * Execute a prepared statement and return the results
   *
   * @param statementToPrepare The SQL query to prepare
   * @param params The parameters to bind to the prepared statement
   * @param paging Optional paging parameters
   * @returns The entities fetched from the database, along with paging information
   */
  public async executePreparedStatement(
    statementToPrepare: string,
    params: Record<string, string>,
    paging?: PagingParams
  ): Promise<ERPResponse | undefined> {
    const connection = await this.openConnection();

    const preparedStatement = new sql.PreparedStatement(connection);

    // Add all parameters as inputs
    Object.keys(params).forEach((key) => {
      preparedStatement.input(key, sql.VarChar);
    });

    // If limit is specified, try SQL paging
    const finalQuery =
      paging?.limit !== undefined
        ? `${statementToPrepare} OFFSET ${paging.offset || 0} ROWS FETCH NEXT ${paging.limit} ROWS ONLY`
        : statementToPrepare;

    await preparedStatement.prepare(finalQuery);

    let records;
    try {
      records = await preparedStatement.execute(params);
    } catch (error) {
      const errorInfo = error as Error;
      const errorMessage =
        "Error fetching data from ERP -> " +
        (errorInfo.cause ? `Cause: ${errorInfo.cause}. ` : "") +
        `Name: ${errorInfo.name}. ` +
        `Message: ${errorInfo.message}. ` +
        `Stack: ${errorInfo.stack}`;

      throw new Error(errorMessage);
    } finally {
      try {
        await preparedStatement.unprepare();
      } catch (unprepareError) {
        logger.error("Error during unprepare:", unprepareError);
      }
    }

    const allRecords = SqlServerService.recordsetToRecords(records?.recordset);
    const rowsFetched = records?.rowsAffected[0] || 0;

    // If SQL paging failed or we need to handle offset, apply in-memory paging
    const pagedData =
      paging?.offset !== undefined
        ? allRecords.slice(
            paging.offset,
            (paging.offset || 0) + (paging.limit || allRecords.length)
          )
        : allRecords;

    return {
      data: pagedData,
      paging: {
        count: rowsFetched,
        limit: paging?.limit || 0,
        offset: paging?.offset || 0,
        nextPage: (() => {
          const currentPageEnd = (paging?.offset || 0) + (paging?.limit || 0);
          return paging?.limit && currentPageEnd < rowsFetched
            ? String(currentPageEnd)
            : undefined;
        })(),
        previousPage: paging?.offset
          ? String(Math.max(0, (paging.offset || 0) - (paging.limit || 10)))
          : undefined,
      },
    };
  }

  /**
   * Opens a connection to SQL Server
   * Caches the connection so that it can be reused.
   * On failure to connect, throws
   */
  async openConnection(): Promise<ConnectionPool> {
    // If we have a connection and it's still connected, reuse it
    if (this.connection?.connected) {
      logger.debug("Reusing existing SQL Server connection");
      return this.connection;
    }

    // If we have a connection but it's not connected, just null it out
    if (this.connection) {
      logger.debug("Clearing disconnected SQL Server connection");
      this.connection = null;
    }

    try {
      logger.info("Opening new SQL Server connection");
      const sqlConfig = SQLServerConfigSchema.parse({
        user: this.config.username,
        password: this.config.password,
        database: this.config.database,
        server: this.config.host,
        port: parseInt(this.config.port),
        connectionTimeout: parseInt(this.config.connectionTimeout),
        requestTimeout: parseInt(this.config.requestTimeout),
        pool: {
          max: parseInt(this.config.poolMax),
          min: parseInt(this.config.poolMin),
          idleTimeoutMillis: parseInt(this.config.idleTimeoutMillis),
        },
        options: {
          encrypt: this.config.encrypt,
          trustServerCertificate: this.config.trustServer,
        },
      });

      this.connection = await sql.connect(sqlConfig);
      return this.connection;
    } catch (error) {
      logger.error("SqlServerService>>openConnection>> Connection failed", {
        error,
      });
      throw new Error("SQL Server connection failed");
    }
  }

  /**
   * Transform a IRecordSet from a SQL query into an array of Record<string, string> instances.
   * @param recordset IRecordSet from a SQL query
   * @returns array of Record<string, string> instances
   */
  public static recordsetToRecords(
    recordset?: IRecordSet<Record<string, unknown>>
  ): Record<string, string>[] {
    const data: Record<string, string>[] =
      recordset?.map((row) => {
        const transformedRow: Record<string, string> = {};
        Object.keys(row).forEach((key) => {
          const value = row[key];
          if (value instanceof Date) {
            // SQL datetimes are timezone-naive. toISOString() gives
            // consistent output; strip the 'Z' to keep it naive so
            // downstream converters apply the correct ERP timezone.
            transformedRow[key] = value.toISOString().replace('Z', '');
          } else {
            transformedRow[key] = value?.toString() || "";
          }
        });
        return transformedRow;
      }) || [];

    return data;
  }

  private async closeConnection(): Promise<void> {
    if (this.connection?.connected) {
      logger.info("Closing SQL Server connection");
      try {
        await this.connection.close();
      } catch (error) {
        logger.error(
          "SqlServerService::closeConnection: Error closing connection",
          {
            error,
          }
        );
      }
      this.connection = null;
    }
  }
}