import { SQLTransactionManager } from "./internal/sql-transaction-manager.js";
import {
  SQLLaborTicketInsertOperation,
  SQLLaborTicketUpdateOperation,
} from "./internal/sql-labor-ticket-operations.js";
import { MMReceiveLaborTicket } from "../mm-api-service/types/receive-types.js";
import { SqlServerService } from "./sql-server-service.js";
import { sdkLogger as logger } from "../reporting-service/logger.js";
import { SQLInput } from "./types/sql-input-param.js";

export class SqlServerHelper {
  /**
   * Creates a new labor ticket in the SQL Server database
   * Opens but does not close the connection
   *
   * @param sqlServerService  The SQL Server service to use for connections
   * @param laborTicket       The labor ticket to create
   * @param sqlInputs         The SQL query parameters
   * @param insertQuery       The SQL query to execute for insertion
   *
   * @returns The processed labor ticket, with the ERP's laborTicketId set
   * Throws exceptions on failures, including if the query does not return a laborTicketId
   */
  static async createLaborTicket(
    sqlServerService: SqlServerService,
    laborTicket: MMReceiveLaborTicket,
    sqlInputs: SQLInput[],
    insertQuery: string
  ): Promise<MMReceiveLaborTicket> {
    const connection = await sqlServerService.openConnection();
    if (!connection) {
      throw new Error(
        "Unable to establish database connection to create labor ticket."
      );
    }

    const transactionManager = new SQLTransactionManager(connection);
    const laborTicketId = await transactionManager.executeTransaction(
      new SQLLaborTicketInsertOperation(insertQuery),
      sqlInputs
    );

    if (laborTicketId) {
      laborTicket.laborTicketId = laborTicketId.toString();
      return laborTicket;
    } else {
      throw new Error(
        `The query did not return the labor ticket id for labor ticket ref: ${laborTicket.laborTicketRef}`
      );
    }
  }

  /**
   * Updates an existing labor ticket in the SQL Server database
   * Opens but does not close the connection
   * @param sqlServerService  The SQL Server service to use for connections
   * @param laborTicket       The labor ticket to update
   * @param sqlInputs         The SQL query parameters
   * @param updateQuery       The SQL query to execute for update
   *
   * @returns The processed labor ticket
   * Throws exceptions on failures
   */
  static async updateLaborTicket(
    sqlServerService: SqlServerService,
    laborTicket: MMReceiveLaborTicket,
    sqlInputs: SQLInput[],
    updateQuery: string
  ): Promise<MMReceiveLaborTicket> {
    const connection = await sqlServerService.openConnection();
    if (!connection) {
      throw new Error(
        "Unable to establish database connection to update labor ticket."
      );
    }

    const transactionManager = new SQLTransactionManager(connection);
    await transactionManager.executeTransaction(
      new SQLLaborTicketUpdateOperation(updateQuery),
      sqlInputs
    );

    return laborTicket;
  }

  /**
   * Logs the SQL input parameters for debugging
   */
  static logQueryInputs(inputs: SQLInput[]): void {
    logger.info("SQL Input Variables:");
    logger.info("----------------------------------------");
    logger.info("Variable Name         | Value");
    logger.info("----------------------------------------");
    inputs.forEach((input) => {
      logger.info(`${input.name.padEnd(20)} | ${input.value}`);
    });
    logger.info("----------------------------------------");
  }
}