import * as sql from 'mssql'
import { ErrorUtils } from '../../../utils/error'
import { parseFilterToSql } from '../../../utils/api/defineQueryBigQuery'

const $error = new ErrorUtils()

// Connection pool management
const connectionPools = new Map<string, sql.ConnectionPool>()
let adapterConfig: TMSSQLAdapterConfig = {}

/**
 * Initialize the SQL adapter with configuration
 * @param config Configuration for different service types
 */
export const initializeSQLAdapter = (config: TMSSQLAdapterConfig): void => {
  adapterConfig = config
}

/**
 * Get or create a connection pool for a specific service type
 * @param serviceType The type of service/database to connect to
 * @returns A connected SQL connection pool
 */
export const getConnection = async (
  serviceType: string
): Promise<sql.ConnectionPool> => {
  try {
    // Check if connection exists and is connected
    if (connectionPools.has(serviceType)) {
      const pool = connectionPools.get(serviceType)
      if (pool && pool.connected) {
        return pool
      }
    }

    // Get config for this service type
    const config =
      adapterConfig.dbConfig?.[serviceType] || adapterConfig.defaultConfig
    if (!config) {
      throw new Error(
        `No database configuration found for service type: ${serviceType}`
      )
    }

    // Create new connection
    const pool = new sql.ConnectionPool(config)
    await pool.connect()
    connectionPools.set(serviceType, pool)
    return pool
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Close all database connections
 */
export const disconnectAll = async (): Promise<void> => {
  try {
    const closePromises: Promise<void>[] = []
    // Fix iteration issue by converting entries() to Array first
    Array.from(connectionPools.entries()).forEach(([_, pool]) => {
      if (pool && pool.connected) {
        closePromises.push(pool.close())
      }
    })
    await Promise.all(closePromises)
    connectionPools.clear()
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Execute a raw SQL query
 * @param props Query properties including service type, SQL query, and parameters
 * @returns Query results
 */
export const query = async (props: TMSSQLQueryProps): Promise<any> => {
  try {
    const { serviceType, query, params = {} } = props
    const pool = await getConnection(serviceType)

    const request = pool.request()

    // Add parameters to request
    Object.entries(params).forEach(([key, value]) => {
      request.input(key, value)
    })

    const result = await request.query(query)
    return result.recordset
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Get a single row from a table
 * @param props Properties including service type, table, columns, and where conditions
 * @returns The first matching row or null if not found
 */
export const getRow = async (props: TMSSQLGetRowProps): Promise<any> => {
  try {
    const { serviceType, table, columns = ['*'], where = {} } = props

    // Build query
    const columnsStr = columns.join(', ')
    let whereClause = ''
    const params: Record<string, any> = {}

    if (Object.keys(where).length > 0) {
      const whereParts: string[] = []
      Object.entries(where).forEach(([key, value], index) => {
        const paramName = `p${index}`
        whereParts.push(`${key} = @${paramName}`)
        params[paramName] = value
      })
      whereClause = `WHERE ${whereParts.join(' AND ')}`
    }

    const sql = `SELECT TOP 1 ${columnsStr} FROM ${table} ${whereClause}`

    const results = await query({
      serviceType,
      query: sql,
      params,
    })

    return results.length > 0 ? results[0] : null
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Get multiple rows from a table
 * @param props Properties including service type, table, columns, where conditions, ordering, and pagination
 * @returns Array of matching rows
 */
export const getRows = async (props: TMSSQLGetRowsProps): Promise<any[]> => {
  try {
    const {
      serviceType,
      table,
      columns = ['*'],
      where = {},
      orderBy = [],
      limit,
      offset,
    } = props

    // Build query
    const columnsStr = columns.join(', ')
    let whereClause = ''
    const params: Record<string, any> = {}

    if (Object.keys(where).length > 0) {
      const whereParts: string[] = []
      Object.entries(where).forEach(([key, value], index) => {
        const paramName = `p${index}`
        whereParts.push(`${key} = @${paramName}`)
        params[paramName] = value
      })
      whereClause = `WHERE ${whereParts.join(' AND ')}`
    }

    // Build ORDER BY clause
    let orderByClause = ''
    if (orderBy && (Array.isArray(orderBy) ? orderBy.length > 0 : orderBy)) {
      const orders: string[] = Array.isArray(orderBy) ? orderBy : [orderBy]
      orderByClause = `ORDER BY ${orders.join(', ')}`
    }

    // Build pagination
    let paginationClause = ''
    if (limit !== undefined) {
      paginationClause = `OFFSET ${offset || 0} ROWS FETCH NEXT ${limit} ROWS ONLY`

      // If we have pagination but no ordering, add a default order by
      if (!orderByClause) {
        orderByClause = 'ORDER BY (SELECT NULL)'
      }
    }

    const sqlQuery = `
      SELECT ${columnsStr}
      FROM ${table}
      ${whereClause}
      ${orderByClause}
      ${paginationClause}
    `.trim()

    return await query({
      serviceType,
      query: sqlQuery,
      params,
    })
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}
/**
 * Get rows from multiple tables
 * @param props Properties including service type, base table, fields, where conditions, ordering, includes, type, primaryKey, foreignKey and pagination
 * @returns Object of matching rows and count
 */
export const fetchQueryResult = async (
  props: TMSSQLfetchQueryResultProps
): Promise<any> => {
  try {
    const {
      serviceType,
      baseTable,
      fields = {},
      where = {},
      orderBy = [],
      includes = [],
      limit,
      offset,
      type = 'INNER',
      primaryKey = '',
      foreignKey = {},
    } = props
    if (
      includes.length > 0 &&
      includes.length !== Object.keys(foreignKey).length
    ) {
      throw new Error(`Includes and foreignKey should be equal length`)
    }
    if (includes.length > 0 && !primaryKey) {
      throw new Error('Primary key is required')
    }
    const columns: any = []
    const tableAlias: Record<string, string> = ({} = {})
    let [base, baseAlias] = baseTable.split(' ')
    const baseTableName = base.includes('.') ? base.split('.')[1] : base
    if (!baseAlias) {
      baseAlias = `t1`
      base = `${base} ${baseAlias}`
    }

    //Join query
    let joinStr = ''
    includes.forEach((table, index) => {
      const alias = `m${index + 1}`
      table = table.trim()
      const foreignTable = table.includes('.') ? table.split('.')[1] : table
      tableAlias[foreignTable] = alias
      joinStr += `${type.toUpperCase()} JOIN ${table} ${alias} ON ${baseAlias}.${primaryKey} = ${alias}.${foreignKey[foreignTable]} `
    })

    // Build column projection
    if (Object.keys(fields).length > 0) {
      Object.entries(fields).forEach(([key, value]) => {
        if (key === baseTableName) {
          value.forEach((field) => columns.push(`${baseAlias}.${field}`))
        } else {
          value.forEach((field) => columns.push(`${tableAlias[key]}.${field}`))
        }
      })
    } else {
      columns.push('*')
    }

    const columnsStr = columns.join(', ')
    let whereClause = ''
    const params: Record<string, any> = {}

    // Build where clause
    if (Object.keys(where).length > 0) {
      whereClause = `WHERE ${parseFilterToSql(where[baseTableName], baseAlias)}`
    }

    // Build ORDER BY clause
    let orderByClause = ''
    if (orderBy && (Array.isArray(orderBy) ? orderBy.length > 0 : orderBy)) {
      const orders: string[] = Array.isArray(orderBy)
        ? orderBy.flatMap((order) => {
            if (typeof order === 'object' && order !== null) {
              return Object.entries(order).map(
                ([column, direction]) =>
                  `${tableAlias[column] ?? baseAlias}.${direction}`
              )
            } else if (typeof order === 'string') {
              return [`${baseAlias}.${order}`]
            } else {
              throw new Error('Invalid orderBy format')
            }
          })
        : [`${baseAlias}.${orderBy}`]
      orderByClause = `ORDER BY ${orders.join(', ')}`
    }

    // Build pagination
    let paginationClause = ''
    if (limit !== undefined) {
      paginationClause = `OFFSET ${offset || 0} ROWS FETCH NEXT ${limit} ROWS ONLY`

      // If we have pagination but no ordering, add a default order by
      if (!orderByClause) {
        orderByClause = 'ORDER BY (SELECT NULL)'
      }
    }

    // SQL query for rows
    const sqlQuery = `
      SELECT ${columnsStr}
      FROM ${base}
      ${joinStr}
      ${whereClause}
      ${orderByClause}
      ${paginationClause}
    `.trim()
    // SQL query for total rows
    const queryCount = `
      SELECT COUNT(*) AS count FROM ${base} ${joinStr} ${whereClause}
    `.trim()

    const [rows, recordsCount] = await Promise.all([
      query({
        serviceType,
        query: sqlQuery,
        params,
      }),
      query({
        serviceType,
        query: queryCount,
        params,
      }),
    ])
    return { rows, count: recordsCount[0].count }
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Insert a row into a table
 * @param props Properties including service type, table, data, and whether to return the inserted row
 * @returns The ID of the inserted row or the full inserted row if returnInserted is true
 */
export const insertRow = async (props: TMSSQLInsertRowProps): Promise<any> => {
  try {
    const { serviceType, table, data, returnInserted = false } = props

    const columns = Object.keys(data)
    const paramNames = columns.map((_, i) => `@p${i}`)

    const params: Record<string, any> = {}
    columns.forEach((col, i) => {
      params[`p${i}`] = data[col]
    })

    let sqlQuery = `
      INSERT INTO ${table} (${columns.join(', ')})
      VALUES (${paramNames.join(', ')})
    `

    if (returnInserted) {
      sqlQuery += '; SELECT SCOPE_IDENTITY() AS id'
    }

    const result = await query({
      serviceType,
      query: sqlQuery,
      params,
    })

    if (returnInserted && result.length > 0) {
      const id = result[0].id
      if (id) {
        return await getRow({
          serviceType,
          table,
          where: { id },
        })
      }
    }

    return result
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Update rows in a table
 * @param props Properties including service type, table, data, where conditions, and whether to return updated rows
 * @returns Boolean success indicator or the updated rows if returnUpdated is true
 */
export const updateRow = async (props: TMSSQLUpdateRowProps): Promise<any> => {
  try {
    const { serviceType, table, data, where, returnUpdated = false } = props

    // Build SET clause
    const setParts: string[] = []
    const params: Record<string, any> = {}

    Object.entries(data).forEach(([key, value], index) => {
      const paramName = `set${index}`
      setParts.push(`${key} = @${paramName}`)
      params[paramName] = value
    })

    // Build WHERE clause
    const whereParts: string[] = []
    Object.entries(where).forEach(([key, value], index) => {
      const paramName = `where${index}`
      whereParts.push(`${key} = @${paramName}`)
      params[paramName] = value
    })

    const sqlQuery = `
      UPDATE ${table}
      SET ${setParts.join(', ')}
      WHERE ${whereParts.join(' AND ')}
    `

    await query({
      serviceType,
      query: sqlQuery,
      params,
    })

    if (returnUpdated) {
      return await getRows({
        serviceType,
        table,
        where,
      })
    }

    return true
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Delete rows from a table
 * @param props Properties including service type, table, where conditions, and whether to return deleted rows
 * @returns Boolean success indicator or the deleted rows if returnDeleted is true
 */
export const deleteRow = async (props: TMSSQLDeleteRowProps): Promise<any> => {
  try {
    const { serviceType, table, where, returnDeleted = false } = props

    let deletedRows
    if (returnDeleted) {
      deletedRows = await getRows({
        serviceType,
        table,
        where,
      })
    }

    // Build WHERE clause
    const whereParts: string[] = []
    const params: Record<string, any> = {}

    Object.entries(where).forEach(([key, value], index) => {
      const paramName = `p${index}`
      whereParts.push(`${key} = @${paramName}`)
      params[paramName] = value
    })

    const sqlQuery = `
      DELETE FROM ${table}
      WHERE ${whereParts.join(' AND ')}
    `

    await query({
      serviceType,
      query: sqlQuery,
      params,
    })

    return returnDeleted ? deletedRows : true
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

/**
 * Execute multiple queries in a transaction
 * @param serviceType The service type to connect to
 * @param callback Function that receives a transaction object and executes queries
 * @returns Result of the callback function
 */
export const withTransaction = async (
  serviceType: string,
  callback: (transaction: sql.Transaction) => Promise<any>
): Promise<any> => {
  const pool = await getConnection(serviceType)
  const transaction = new sql.Transaction(pool)

  try {
    await transaction.begin()
    const result = await callback(transaction)
    await transaction.commit()
    return result
  } catch (error) {
    await transaction.rollback()
    throw $error.errorHandler({ error })
  }
}

/**
 * Execute a batch of queries in a single request
 * @param props Properties including service type and an array of queries
 * @returns Array of results for each query
 */
export const executeBatch = async (props: TMSSQLBatchProps): Promise<any[]> => {
  return await withTransaction(props.serviceType, async (transaction) => {
    const results: any[] = []

    for (const queryInfo of props.queries) {
      const request = transaction.request()

      // Add parameters to request
      if (queryInfo.params) {
        Object.entries(queryInfo.params).forEach(([key, value]) => {
          request.input(key, value)
        })
      }

      const result = await request.query(queryInfo.query)
      results.push(result.recordset)
    }

    return results
  })
}

/**
 * Get the count of rows in a table matching the given conditions
 * @param props Properties including service type, table, and where conditions
 * @returns The count of matching rows
 */
export const getRowCount = async (
  props: TMSSQLGetRowProps
): Promise<number> => {
  try {
    const { serviceType, table, where = {} } = props

    // Build query
    let whereClause = ''
    const params: Record<string, any> = {}

    if (Object.keys(where).length > 0) {
      const whereParts: string[] = []
      Object.entries(where).forEach(([key, value], index) => {
        const paramName = `p${index}`
        whereParts.push(`${key} = @${paramName}`)
        params[paramName] = value
      })
      whereClause = `WHERE ${whereParts.join(' AND ')}`
    }

    const sqlQuery = `SELECT COUNT(*) AS count FROM ${table} ${whereClause}`

    const results = await query({
      serviceType,
      query: sqlQuery,
      params,
    })

    return results[0].count
  } catch (error) {
    throw $error.errorHandler({ error })
  }
}

// Export all functions
export default {
  initializeSQLAdapter,
  getConnection,
  disconnectAll,
  query,
  getRow,
  getRows,
  fetchQueryResult,
  getRowCount,
  insertRow,
  updateRow,
  deleteRow,
  withTransaction,
  executeBatch,
}