/**
 * SQL查询构建与执行工具
 * SQL Query Builder & Executor
 *
 * 提供两类能力：
 * 1) 基础的SQL字符串构建（用于预览）
 * 2) 基于 Prisma 的查询执行与表字段获取
 */

import type { PrismaClient } from '@prisma/client'
import { Prisma } from '@prisma/client'
import type { AdvancedQueryOptions } from '@nuxt4crud/shared/types'

export interface QueryBuilderOptions {
  table: string
  select?: string[]
  where?: Record<string, any>
  orderBy?: string
  orderDirection?: 'asc' | 'desc'
  limit?: number
  offset?: number
}

/**
 * SQL查询构建器类
 * SQL Query Builder Class
 */
// 仅用于生成基础 SQL 预览字符串的构建器（不直接执行）
export class SqlQueryBuilder {
  private options: QueryBuilderOptions

  constructor(options: QueryBuilderOptions) {
    this.options = options
  }

  /**
   * 构建SELECT查询
   * Build SELECT query
   */
  buildSelectQuery(): { sql: string; params: any[] } {
    const params: any[] = []
    let sql = 'SELECT '

    // SELECT 子句
    if (this.options.select && this.options.select.length > 0) {
      sql += this.options.select.join(', ')
    } else {
      sql += '*'
    }

    // FROM 子句
    sql += ` FROM ${this.escapeIdentifier(this.options.table)}`

    // WHERE 子句
    const whereClause = this.buildWhereClause(params)
    if (whereClause) {
      sql += ` WHERE ${whereClause}`
    }

    // ORDER BY 子句
    if (this.options.orderBy) {
      sql += ` ORDER BY ${this.escapeIdentifier(this.options.orderBy)} ${this.options.orderDirection || 'ASC'}`
    }

    // LIMIT 子句
    if (this.options.limit) {
      sql += ` LIMIT ${this.options.limit}`
    }

    // OFFSET 子句
    if (this.options.offset) {
      sql += ` OFFSET ${this.options.offset}`
    }

    return { sql, params }
  }

  /**
   * 构建COUNT查询
   * Build COUNT query
   */
  buildCountQuery(): { sql: string; params: any[] } {
    const params: any[] = []
    let sql = `SELECT COUNT(*) as count FROM ${this.escapeIdentifier(this.options.table)}`

    // WHERE 子句
    const whereClause = this.buildWhereClause(params)
    if (whereClause) {
      sql += ` WHERE ${whereClause}`
    }

    return { sql, params }
  }

  /**
   * 构建WHERE子句
   * Build WHERE clause
   */
  private buildWhereClause(params: any[]): string {
    if (!this.options.where || Object.keys(this.options.where).length === 0) {
      return ''
    }

    const conditions: string[] = []

    Object.entries(this.options.where).forEach(([key, value]) => {
      if (value !== undefined && value !== null) {
        conditions.push(`${this.escapeIdentifier(key)} = ?`)
        params.push(value)
      }
    })

    return conditions.join(' AND ')
  }

  /**
   * 转义标识符（防止SQL注入）
   * Escape identifier (prevent SQL injection)
   */
  private escapeIdentifier(identifier: string): string {
    // 简单的标识符转义，移除特殊字符
    return identifier.replace(/[^a-zA-Z0-9_]/g, '')
  }

  /**
   * 获取分页查询
   * Get paginated query
   */
  buildPaginatedQueries(): {
    selectQuery: { sql: string; params: any[] }
    countQuery: { sql: string; params: any[] }
  } {
    return {
      selectQuery: this.buildSelectQuery(),
      countQuery: this.buildCountQuery(),
    }
  }
}

/**
 * Prisma 驱动的查询构建器
 * Prisma-driven query builder and executor
 */
export class PrismaSqlQueryBuilder {
  constructor(private prisma: PrismaClient) {}

  /**
   * 获取当前数据库方言（postgres/mysql/sqlite）
   * Get current database dialect from DATABASE_URL
   */
  private getDialect(): 'postgres' | 'mysql' | 'sqlite' {
    const url = process.env.DATABASE_URL || 'file:./dev.db'
    if (url.startsWith('postgres') || url.includes('postgresql')) return 'postgres'
    if (url.startsWith('mysql')) return 'mysql'
    // Prisma sqlite 通常是 file:./xxx.db
    return 'sqlite'
  }

  /**
   * 获取指定表的字段列表（通过数据库系统表）
   * Get table column names using database system views
   */
  async getTableFields(tableName: string): Promise<string[]> {
    const dialect = this.getDialect()
    try {
      if (dialect === 'postgres') {
        const rows: Array<{ column_name: string }> = await (this.prisma.$queryRawUnsafe as any)(
          `SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '${tableName}' ORDER BY ordinal_position;`
        )
        return rows.map(r => r.column_name)
      }

      if (dialect === 'mysql') {
        const rows: Array<{ Field: string }> = await (this.prisma.$queryRawUnsafe as any)(
          `SHOW COLUMNS FROM \`${tableName}\`;`
        )
        return rows.map(r => r.Field)
      }

      // sqlite
      const rows: Array<{ name: string }> = await (this.prisma.$queryRawUnsafe as any)(
        `PRAGMA table_info('${tableName}');`
      )
      return rows.map(r => r.name)
    } catch (e) {
      console.warn('getTableFields failed, falling back to defaults:', e)
      // 尝试返回常见字段集合作为降级方案
      if (tableName === 'users') {
        return ['id', 'name', 'email', 'age', 'createdAt', 'updatedAt']
      }
      return ['id', 'createdAt', 'updatedAt']
    }
  }

  /**
   * 验证高级查询选项的合法性
   * Validate AdvancedQueryOptions against available fields
   */
  validateQueryOptions(options: AdvancedQueryOptions, availableFields: string[]): string[] {
    const errors: string[] = []
    const fieldSet = new Set(availableFields)

    // where 条件校验
    if (Array.isArray(options.where)) {
      for (const cond of options.where) {
        if (!cond || !cond.field) {
          errors.push('存在缺少字段的条件')
          continue
        }
        if (!fieldSet.has(cond.field)) {
          errors.push(`字段不存在: ${cond.field}`)
        }
        if (!cond.operator) {
          errors.push(`字段 ${cond.field} 缺少操作符`)
        }
        const nullOps = new Set(['is_null', 'is_not_null'])
        const multiOps = new Set(['in', 'not_in', 'between', 'date_between'])
        if (!nullOps.has(cond.operator)) {
          if (multiOps.has(cond.operator)) {
            if (!Array.isArray(cond.value) || cond.value.length === 0) {
              errors.push(`字段 ${cond.field} 的操作符 ${cond.operator} 需要数组值`)
            }
          } else if (cond.value === undefined || cond.value === null || cond.value === '') {
            errors.push(`字段 ${cond.field} 的操作符 ${cond.operator} 需要提供值`)
          }
        }
      }
    }

    // 排序校验
    if (Array.isArray(options.orderBy)) {
      for (const s of options.orderBy) {
        if (!s || !s.field) {
          errors.push('存在缺少字段的排序项')
          continue
        }
        if (!fieldSet.has(s.field)) errors.push(`排序字段不存在: ${s.field}`)
        const dir = String(s.direction || '').toUpperCase()
        if (dir !== 'ASC' && dir !== 'DESC') errors.push(`排序方向非法: ${s.direction}`)
      }
    }

    // 分页校验
    const page = Number(options.pagination?.page || 1)
    const limit = Number(options.pagination?.limit || 20)
    if (!Number.isFinite(page) || page < 1) errors.push('分页页码必须为正整数')
    if (!Number.isFinite(limit) || limit < 1 || limit > 1000)
      errors.push('分页数量必须在1-1000之间')

    return errors
  }

  /**
   * 将条件映射为 Prisma where 片段
   * Map one condition to Prisma where fragment
   */
  private mapConditionToPrisma(cond: any): any {
    const { field, operator, value } = cond || {}

    // 如果值为null、undefined或空字符串（除了isNull/isNotNull操作符），返回空对象
    if (
      (value === null || value === undefined || value === '') &&
      operator !== 'is_null' &&
      operator !== 'is_not_null'
    ) {
      return {}
    }
    switch (operator) {
      case 'equals':
      case 'eq': // 支持简写形式
        return { [field]: value }
      case 'not_equals':
      case 'ne': // 支持简写形式
        return { [field]: { not: value } }
      case 'contains':
      case 'like': // 支持简写形式
        // 确保值不为空字符串，否则会导致不匹配任何记录
        if (String(value) === '') return {}
        return { [field]: { contains: String(value) } }
      case 'not_contains':
      case 'notLike': // 支持简写形式
        // 确保值不为空字符串，否则会导致不匹配任何记录
        if (String(value) === '') return {}
        return { [field]: { not: { contains: String(value) } } }
      case 'starts_with':
        // 确保值不为空字符串，否则会导致不匹配任何记录
        if (String(value) === '') return {}
        return { [field]: { startsWith: String(value) } }
      case 'ends_with':
        // 确保值不为空字符串，否则会导致不匹配任何记录
        if (String(value) === '') return {}
        return { [field]: { endsWith: String(value) } }
      case 'gt':
      case 'greater_than':
        return { [field]: { gt: value } }
      case 'gte':
      case 'greater_than_or_equal':
        return { [field]: { gte: value } }
      case 'lt':
      case 'less_than':
        return { [field]: { lt: value } }
      case 'lte':
      case 'less_than_or_equal':
        return { [field]: { lte: value } }
      case 'in': {
        const arr = Array.isArray(value)
          ? value
          : String(value)
              .split(',')
              .map(v => v.trim())
        // 确保数组不为空
        if (arr.length === 0 || (arr.length === 1 && arr[0] === '')) return {}
        return { [field]: { in: arr } }
      }
      case 'not_in': {
        const arr = Array.isArray(value)
          ? value
          : String(value)
              .split(',')
              .map(v => v.trim())
        // 确保数组不为空
        if (arr.length === 0 || (arr.length === 1 && arr[0] === '')) return {}
        return { [field]: { notIn: arr } }
      }
      case 'is_null':
        return { [field]: null }
      case 'is_not_null':
        return { [field]: { not: null } }
      case 'between': {
        const [from, to] = Array.isArray(value) ? value : []
        // 确保from和to都有效
        if (from === undefined || to === undefined || from === '' || to === '') return {}
        return { [field]: { gte: from, lte: to } }
      }
      case 'date_between': {
        const [from, to] = Array.isArray(value) ? value : []
        // 确保from和to都有效
        if (from === undefined || to === undefined || from === '' || to === '') return {}
        return { [field]: { gte: new Date(from), lte: new Date(to) } }
      }
      default:
        return {}
    }
  }

  /**
   * 根据查询选项构建 Prisma where 条件及 SQL 预览
   * Build Prisma where clause and a basic SQL preview from options
   */
  private buildWhereFromOptions(
    tableName: string,
    options: AdvancedQueryOptions,
    searchFields: string[]
  ): { where: any; sqlPreview: string } {
    const where: any = {}
    const andConditions: any[] = []
    const orConditions: any[] = []
    const sqlPreviewParts: string[] = []

    // 搜索条件
    const search = options.search?.trim()
    if (search && searchFields.length > 0) {
      const searchOr = searchFields.map(f => ({ [f]: { contains: search } }))
      // 默认与其它条件 AND 组合
      andConditions.push({ OR: searchOr })
      sqlPreviewParts.push(`(${searchFields.map(f => `${f} LIKE '%${search}%'`).join(' OR ')})`)
    }

    // where 条件
    if (Array.isArray(options.where)) {
      for (const cond of options.where) {
        const { field, operator, value } = cond

        // 特殊处理posts表的title字段，因为在Prisma模型中title是必填字段（String）
        if (tableName === 'posts' && field === 'title') {
          if (operator === 'isNull' || operator === 'is_null') {
            // posts表的title字段不可能为null，所以如果有这个条件，直接返回空结果
            return { where: { id: -1 }, sqlPreview: 'WHERE title IS NULL' }
          } else if (operator === 'isNotNull' || operator === 'is_not_null') {
            // posts表的title字段总是不为null，所以这个条件可以忽略
            continue
          }
        }

        const frag = this.mapConditionToPrisma(cond)
        if (!frag || Object.keys(frag).length === 0) continue

        // 普通条件处理
        if (String(cond.logicalOperator || 'AND').toUpperCase() === 'OR') {
          orConditions.push(frag)
        } else {
          andConditions.push(frag)
        }

        // 生成SQL预览
        let sqlPart = ''
        switch (operator) {
          case 'equals':
            sqlPart = `${field} = '${value}'`
            break
          case 'not_equals':
            sqlPart = `${field} <> '${value}'`
            break
          case 'contains':
            sqlPart = `${field} LIKE '%${value}%'`
            break
          case 'not_contains':
            sqlPart = `${field} NOT LIKE '%${value}%'`
            break
          case 'starts_with':
            sqlPart = `${field} LIKE '${value}%'`
            break
          case 'ends_with':
            sqlPart = `${field} LIKE '%${value}'`
            break
          case 'gt':
            sqlPart = `${field} > ${value}`
            break
          case 'gte':
            sqlPart = `${field} >= ${value}`
            break
          case 'lt':
            sqlPart = `${field} < ${value}`
            break
          case 'lte':
            sqlPart = `${field} <= ${value}`
            break
          case 'in':
            sqlPart = `${field} IN (${Array.isArray(value) ? value.map(v => `'${v}'`).join(', ') : `'${value}'`})`
            break
          case 'not_in':
            sqlPart = `${field} NOT IN (${Array.isArray(value) ? value.map(v => `'${v}'`).join(', ') : `'${value}'`})`
            break
          case 'is_null':
            sqlPart = `${field} IS NULL`
            break
          case 'is_not_null':
            sqlPart = `${field} IS NOT NULL`
            break
          case 'between':
            const [from, to] = Array.isArray(value) ? value : []
            sqlPart = `${field} BETWEEN ${from} AND ${to}`
            break
        }
        if (sqlPart) {
          if (sqlPreviewParts.length > 0) {
            sqlPreviewParts.push(String(cond.logicalOperator || 'AND').toUpperCase())
          }
          sqlPreviewParts.push(sqlPart)
        }
      }
    }

    let finalConditions: any[] = []

    // 处理搜索条件
    if (search && searchFields.length > 0) {
      const searchOr = searchFields.map(f => ({ [f]: { contains: search } }))
      finalConditions.push({ OR: searchOr })
    }

    // 正确处理逻辑操作符序列
    // 前端发送的条件数组中，每个条件（除了第一个）都有一个logicalOperator
    // 表示该条件与前一个条件的连接方式
    if (Array.isArray(options.where) && options.where.length > 0) {
      // 第一个条件
      let currentCondition = this.mapConditionToPrisma(options.where[0])

      // 如果只有一个条件，直接使用它
      if (options.where.length === 1) {
        if (currentCondition && Object.keys(currentCondition).length > 0) {
          finalConditions.push(currentCondition)
        }
      } else {
        // 处理多个条件
        for (let i = 1; i < options.where.length; i++) {
          const nextCondition = this.mapConditionToPrisma(options.where[i])
          const logicalOperator = String(options.where[i].logicalOperator || 'AND').toUpperCase()

          if (!currentCondition || Object.keys(currentCondition).length === 0) {
            currentCondition = nextCondition
            continue
          }

          if (!nextCondition || Object.keys(nextCondition).length === 0) {
            continue
          }

          // 根据逻辑操作符构建新的条件结构
          if (logicalOperator === 'OR') {
            // 如果当前条件已经是OR结构，添加新条件
            if (currentCondition.OR) {
              currentCondition.OR.push(nextCondition)
            } else {
              // 否则创建新的OR结构
              currentCondition = {
                OR: [currentCondition, nextCondition],
              }
            }
          } else {
            // AND
            // 如果当前条件已经是AND结构，添加新条件
            if (currentCondition.AND) {
              currentCondition.AND.push(nextCondition)
            } else {
              // 否则创建新的AND结构
              currentCondition = {
                AND: [currentCondition, nextCondition],
              }
            }
          }
        }

        if (currentCondition && Object.keys(currentCondition).length > 0) {
          finalConditions.push(currentCondition)
        }
      }
    }

    // 组合所有条件
    if (finalConditions.length > 1) {
      where.AND = finalConditions
    } else if (finalConditions.length === 1) {
      Object.assign(where, finalConditions[0])
    }

    // 构建非常基础的 SQL 预览
    const whereParts: string[] = []
    if (search && searchFields.length > 0) {
      const like = search.replace(/'/g, "''")
      whereParts.push('(' + searchFields.map(f => `${f} ILIKE '%${like}%'`).join(' OR ') + ')')
    }

    if (Array.isArray(options.where)) {
      for (const cond of options.where) {
        const op = String(cond.operator || '').toLowerCase()
        const f = cond.field
        const v = cond.value
        const logic = String(cond.logicalOperator || 'AND').toUpperCase()
        let exp = ''
        switch (op) {
          case 'equals':
            exp = `${f} = '${v}'`
            break
          case 'not_equals':
            exp = `${f} != '${v}'`
            break
          case 'contains':
            exp = `${f} ILIKE '%${String(v)}%'`
            break
          case 'not_contains':
            exp = `${f} NOT ILIKE '%${String(v)}%'`
            break
          case 'starts_with':
            exp = `${f} ILIKE '${String(v)}%'`
            break
          case 'ends_with':
            exp = `${f} ILIKE '%${String(v)}'`
            break
          case 'gt':
          case 'greater_than':
            exp = `${f} > ${v}`
            break
          case 'gte':
          case 'greater_than_or_equal':
            exp = `${f} >= ${v}`
            break
          case 'lt':
          case 'less_than':
            exp = `${f} < ${v}`
            break
          case 'lte':
          case 'less_than_or_equal':
            exp = `${f} <= ${v}`
            break
          case 'in': {
            const arr = Array.isArray(v)
              ? v
              : String(v)
                  .split(',')
                  .map(s => s.trim())
            exp = `${f} IN (${arr.map(x => `'${x}'`).join(', ')})`
            break
          }
          case 'not_in': {
            const arr = Array.isArray(v)
              ? v
              : String(v)
                  .split(',')
                  .map(s => s.trim())
            exp = `${f} NOT IN (${arr.map(x => `'${x}'`).join(', ')})`
            break
          }
          case 'is_null':
            exp = `${f} IS NULL`
            break
          case 'is_not_null':
            exp = `${f} IS NOT NULL`
            break
          case 'between': {
            const [from, to] = Array.isArray(v) ? v : []
            exp = `${f} BETWEEN '${from}' AND '${to}'`
            break
          }
          case 'date_between': {
            const [from, to] = Array.isArray(v) ? v : []
            exp = `${f} BETWEEN '${from}' AND '${to}'`
            break
          }
        }
        if (exp) whereParts.push((whereParts.length > 0 ? logic + ' ' : '') + exp)
      }
    }

    const whereSql = whereParts.length > 0 ? ' WHERE ' + whereParts.join(' ') : ''
    const baseSql = `SELECT * FROM ${tableName}${whereSql}`
    return { where, sqlPreview: baseSql }
  }

  /**
   * 执行普通查询，返回分页数据以及SQL预览
   * Execute normal query with pagination and a SQL preview
   */
  async executeQuery(
    tableName: string,
    options: AdvancedQueryOptions,
    searchFields: string[]
  ): Promise<{
    data: any[]
    total: number
    pagination: { page: number; limit: number; totalPages: number }
    executedAt: string
    table: string
    query: { sql: string }
  }> {
    const page = Number(options.pagination?.page || 1)
    const limit = Number(options.pagination?.limit || 20)
    const skip = (page - 1) * limit

    const { where, sqlPreview } = this.buildWhereFromOptions(tableName, options, searchFields)

    // 如果where条件包含id: -1，说明是posts表的title IS NULL查询，直接返回空结果
    if (where.id === -1) {
      return {
        data: [],
        total: 0,
        pagination: {
          page: Number(options.pagination?.page || 1),
          limit: Number(options.pagination?.limit || 20),
          totalPages: 0,
        },
        executedAt: new Date().toISOString(),
        table: tableName,
        query: { sql: sqlPreview },
      }
    }

    // 映射到 Prisma 模型名称（简单复数到单数映射）
    // 将表名转换为Prisma模型名（通常是单数形式）
    const modelName = tableName === 'users' ? 'user' : tableName === 'posts' ? 'post' : tableName
    const model = (this.prisma as any)[modelName]

    // 检查模型是否存在
    if (!model) {
      const err: any = new Error(`未找到 Prisma 模型: ${modelName}`)
      err.statusCode = 400
      throw err
    }

    // 排序处理
    const orderBy =
      Array.isArray(options.orderBy) && options.orderBy.length > 0
        ? options.orderBy.map(s => ({
            [s.field]: String(s.direction || 'ASC').toLowerCase(),
          }))
        : undefined

    // 执行查询
    const [data, total] = await Promise.all([
      model.findMany({ where, skip, take: limit, orderBy }),
      model.count({ where }),
    ])

    return {
      data,
      total,
      pagination: { page, limit, totalPages: Math.max(Math.ceil(total / limit), 1) },
      executedAt: new Date().toISOString(),
      table: tableName,
      query: { sql: sqlPreview + ` ORDER BY ... LIMIT ${limit} OFFSET ${skip}` },
    }
  }

  /**
   * 执行聚合查询（count/sum/avg/min/max）
   * Execute aggregate query (count/sum/avg/min/max)
   */
  async executeAggregateQuery(tableName: string, options: AdvancedQueryOptions): Promise<any> {
    const { where } = this.buildWhereFromOptions(tableName, options, [])

    const modelName = tableName === 'users' ? 'user' : tableName
    const model = (this.prisma as any)[modelName]
    if (!model) {
      const err: any = new Error(`未找到 Prisma 模型: ${modelName}`)
      err.statusCode = 400
      throw err
    }

    const agg = options.aggregate || {}
    const params: any = { where }

    if (agg.count) params._count = { _all: true }

    const toFieldObj = (fields?: string[]) => {
      if (!fields || fields.length === 0) return undefined
      return Object.fromEntries(fields.map(f => [f, true]))
    }

    params._sum = toFieldObj(agg.sum)
    params._avg = toFieldObj(agg.avg)
    params._min = toFieldObj(agg.min)
    params._max = toFieldObj(agg.max)

    const result = await model.aggregate(params)

    return {
      executedAt: new Date().toISOString(),
      result,
    }
  }
}

/**
 * 创建基于 Prisma 的查询构建器实例
 * Create Prisma-based SQL query builder instance
 */
export function createSqlQueryBuilder(prisma: PrismaClient): PrismaSqlQueryBuilder {
  return new PrismaSqlQueryBuilder(prisma)
}