import { PsqlConfiguration } from "./configuration.js";
import { ERPResponse } from "../../types/erp-types.js";
import { OdbcErrorResponse } from "./internal/types/psql-types.js";
import { sdkLogger as logger } from "../reporting-service/logger.js";

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

/**
 * ODBC connection interface for type safety
 */
interface OdbcConnection {
  query(sql: string): Promise<any[]>;
  close(): Promise<void>;
}

/**
 * ODBC module interface
 */
interface OdbcModule {
  connect(connectionString: string): Promise<OdbcConnection>;
}

export class PsqlService {
  private config: PsqlConfiguration;
  private static odbcModule: OdbcModule | null = null;
  private static odbcLoadError: Error | null = null;

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

  /**
   * Dynamically load the ODBC module with lazy initialization and caching
   * @throws Error with helpful message if ODBC package is not installed
   */
  private static async getOdbc(): Promise<OdbcModule> {
    // If we've already tried and failed, throw the cached error
    if (this.odbcLoadError) {
      throw this.odbcLoadError;
    }

    // If already loaded, return cached module
    if (this.odbcModule) {
      return this.odbcModule;
    }

    try {
      // Dynamic import - only loads when actually needed
      // @ts-ignore - odbc is an optional dependency, may not be installed at build time
      const odbcImport = await import("odbc");
      // Handle both default export and named export patterns
      const odbc = odbcImport.default || odbcImport;
      this.odbcModule = odbc as OdbcModule;
      return this.odbcModule;
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : String(error);
      this.odbcLoadError = new Error(
        `ODBC package is required for PSQL service but is not installed or failed to load.\n` +
        `Install it with: npm install odbc\n` +
        `Also install OS-level dependencies, e.g. on Alpine Linux:\n` +
        `  apk add --no-cache unixodbc unixodbc-dev python3 make g++\n` +
        `For other Linux distributions, install unixodbc and unixodbc-dev packages.\n` +
        `Original error: ${errorMessage}`
      );
      throw this.odbcLoadError;
    }
  }

  // REMOVED: dispose() method - not needed anymore
  // REMOVED: connection property - not needed anymore
  // REMOVED: openConnection() method - not needed anymore
  // REMOVED: closeConnection() method - not needed anymore

  /**
   * Build PSQL ODBC connection string
   * CRITICAL: ServerName must use IP.PORT format (e.g., 10.4.0.11.1583)
   */
  private buildConnectionString(): string {
    const serverName = `${this.config.host}.${this.config.port}`;

    return (
      [
        "Driver={Pervasive ODBC Interface}",
        `ServerName=${serverName}`,
        `DBQ=${this.config.database}`,
        `UID=${this.config.username}`,
        `PWD=${this.config.password}`,
        "AutoDoubleQuote=0",
      ].join(";") + ";"
    );
  }

  /**
   * Execute a query and return the results
   * Creates a fresh connection for each query to avoid handle corruption
   *
   * SECURITY WARNING: This method executes the provided SQL string as-is.
   * - Parameter binding is NOT implemented; the `params` argument is currently ignored.
   * - Never concatenate untrusted/user-supplied input into `query`.
   * - Doing so can result in SQL injection vulnerabilities and data exposure.
   * If dynamic values are required, ensure they are strictly validated/escaped
   * or implement proper parameterized execution before accepting untrusted input.
   *
   * @param query The SQL query to execute
   * @param params Query parameters (currently unused for PSQL read operations)
   * @param paging Optional paging parameters
   * @returns The entities fetched from the database, along with paging information
   */
  public async executePreparedStatement(
    query: string,
    params: Record<string, string> = {},
    paging?: PagingParams
  ): Promise<ERPResponse | undefined> {
    // Dynamically load ODBC module (will throw helpful error if not installed)
    const odbc = await PsqlService.getOdbc();
    let connection: OdbcConnection | null = null;

    try {
      // Create fresh connection for THIS query only
      const connStr = this.buildConnectionString();
      logger.debug("Creating fresh PSQL connection for query");
      connection = await odbc.connect(connStr);

      if (Object.keys(params).length > 0) {
        logger.warn(
          "PsqlService: Query parameters provided but parameter binding not yet implemented. " +
            "Using direct query execution."
        );
      }

      const records = await connection.query(query);
      const allRecords = PsqlService.recordsetToRecords(records);
      const rowsFetched = allRecords.length;

      // Apply paging if requested
      const pagedData =
        paging?.offset !== undefined || paging?.limit !== undefined
          ? allRecords.slice(
              paging.offset || 0,
              (paging.offset || 0) + (paging.limit || allRecords.length)
            )
          : allRecords;

      return {
        data: pagedData,
        paging: {
          count: rowsFetched,
          limit: paging?.limit || 0,
          offset: paging?.offset || 0,
          nextPage:
            paging?.limit && (paging.offset || 0) + paging.limit < rowsFetched
              ? String((paging.offset || 0) + paging.limit)
              : undefined,
          previousPage: paging?.offset
            ? String(Math.max(0, (paging.offset || 0) - (paging.limit || 10)))
            : undefined,
        },
      };
    } catch (error) {
      // If this is an ODBC load error (from getOdbc), re-throw it as-is
      // since it already has a helpful error message
      if (error instanceof Error && error.message.includes("ODBC package is required")) {
        throw error;
      }

      // Otherwise, handle as ODBC runtime error
      const errorInfo = error as OdbcErrorResponse;
      logger.error("Error fetching data from PSQL", {
        error: errorInfo.message,
        odbcErrors: errorInfo.odbcErrors,
        query: query.substring(0, 200), // Log first 200 chars of query
      });

      throw this.handleOdbcError(errorInfo);
    } finally {
      // CRITICAL: Always close connection, even on error
      if (connection) {
        try {
          await connection.close();
          logger.debug("PSQL connection closed successfully");
        } catch (err) {
          // Don't throw on close errors, just log
          logger.warn("Error closing PSQL connection (non-fatal)", {
            error: err,
          });
        }
      }
    }
  }

  /**
   * Transform ODBC result set to array of Record<string, string> instances.
   * IMPORTANT: PSQL CHAR fields are often padded with spaces - we trim them
   */
  public static recordsetToRecords(recordset: any[]): Record<string, string>[] {
    if (!Array.isArray(recordset)) {
      return [];
    }

    const data: Record<string, string>[] = recordset.map((row) => {
      const transformedRow: Record<string, string> = {};
      Object.keys(row).forEach((key) => {
        const value = row[key];
        transformedRow[key] =
          value !== null && value !== undefined ? String(value).trim() : "";
      });
      return transformedRow;
    });

    return data;
  }

  /**
   * Handle ODBC errors and provide meaningful messages
   */
  private handleOdbcError(error: OdbcErrorResponse): Error {
    const odbcError = error.odbcErrors?.[0];
    const errorCode = odbcError?.state;
    const message = odbcError?.message || error.message;

    switch (errorCode) {
      case "08S01":
        return new Error(
          "PSQL connection failed. Check: " +
            "1) PVSW environment variable set to /usr/local/psql/etc/pvsw.ini, " +
            "2) Network connectivity to ports 1583/3351, " +
            "3) ODBC configuration files in /usr/local/psql/etc/ and /etc/. " +
            `Original error: ${message}`
        );
      case "28000":
        return new Error(
          `PSQL authentication failed. Check username/password. Original error: ${message}`
        );
      case "42000":
        return new Error(`PSQL SQL syntax error. Original error: ${message}`);
      case "42S02":
        return new Error(
          `PSQL table or view not found. Check table names in query. Original error: ${message}`
        );
      default:
        return new Error(`PSQL error (${errorCode || "unknown"}): ${message}`);
    }
  }
}