import {
  Brackets,
  DeepPartial,
  DeleteResult,
  EntityManager,
  FindOptionsWhere,
  Repository,
  SelectQueryBuilder,
} from "typeorm"
import { QueryDeepPartialEntity } from "typeorm/query-builder/QueryPartialEntity"
import { HasId } from "../interfaces/has-id.interface"
import { PaginatedResult } from "../interfaces/pagination-result.interface"
import { QueryParams } from "../interfaces/query-params.interface"
import { isEmpty } from "../../utils/helpers"

export abstract class BaseAbstractRepository<T extends HasId> {
  protected entity: Repository<T>

  protected constructor(entity: Repository<T>) {
    this.entity = entity
  }

  public async getByParams(
    params: QueryParams<T>,
  ): Promise<PaginatedResult<T> | T[] | T | number | undefined> {
    const {
      where,
      whereIn,
      whereNotIn,
      whereNull,
      whereNotNull,
      whereNullOrNotNull,
      select,
      findOne,
      relations,
      innerJoinRelations,
      search,
      take,
      skip,
      orderBy,
      groupBy,
      rawData,
      relationCount,
      sum,
      relationCountWithConditions,
      orWhere,
      whereLower,
      getCountOnly,
      withDeleted,
      distinctValues,
    } = params
    let query: SelectQueryBuilder<T> = this.entity.createQueryBuilder("entity")

    // Apply where conditions

    if (where) {
      Object.entries(where).forEach(([key, value]) => {
        const whereKey = key.startsWith("entity_") ? key : `entity.${key}`

        if (typeof value === "object" && value !== null) {
          // eslint-disable-next-line @typescript-eslint/no-unused-vars
          const { not, lt, gt, lte, gte, ilike, ...others } = value

          if (ilike !== undefined) {
            query = query.andWhere(`${whereKey} ILIKE :${key}_ilike`, {
              [`${key}_ilike`]: `%${ilike}%`,
            })
          }
          if (not !== undefined) {
            query = query.andWhere(`${whereKey} != :${key}_not`, {
              [`${key}_not`]: not,
            })
          }
          if (lt !== undefined) {
            query = query.andWhere(`${whereKey} < :${key}_lt`, {
              [`${key}_lt`]: lt,
            })
          }
          if (gt !== undefined) {
            query = query.andWhere(`${whereKey} > :${key}_gt`, {
              [`${key}_gt`]: gt,
            })
          }
          if (lte !== undefined) {
            query = query.andWhere(`${whereKey} <= :${key}_lte`, {
              [`${key}_lte`]: lte,
            })
          }
          if (gte !== undefined) {
            query = query.andWhere(`${whereKey} >= :${key}_gte`, {
              [`${key}_gte`]: gte,
            })
          }
        } else {
          query = query.andWhere(`${whereKey} = :${key}`, { [key]: value })
        }
      })
    }

    if (whereLower) {
      Object.entries(whereLower).forEach(([key, value]) => {
        const whereKey = key.startsWith("entity_") ? key : `entity.${key}`

        if (typeof value === "string") {
          query = query.andWhere(`LOWER(${whereKey}) = LOWER(:${key})`, {
            [`${key}`]: value,
          })
        } else {
          query = query.andWhere(`${whereKey} = :${key}`, { [key]: value })
        }
      })
    }

    // Apply orWhere conditions
    if (orWhere) {
      query = query.andWhere(
        new Brackets((qb) => {
          Object.entries(orWhere).forEach(([key, value]) => {
            qb.orWhere(`entity.${key} = :${key}`, { [key]: value })
          })
        }),
      )
    }

    // Apply wherein conditions
    if (whereIn) {
      Object.entries(whereIn).forEach(([key, values]) => {
        if (Array.isArray(values)) {
          query = query.andWhere(`entity.${key} IN (:...${key})`, {
            [key]: [null, ...values],
          })
        }
      })
    }

    // Apply whereNotIn conditions
    if (whereNotIn) {
      Object.entries(whereNotIn).forEach(([key, values]) => {
        if (Array.isArray(values) && values.length > 0) {
          query = query.andWhere(`entity.${key} NOT IN (:...${key})`, {
            [key]: values,
          })
        }
      })
    }

    // Apply where null conditions
    if (whereNull && whereNull.length > 0) {
      whereNull.forEach((key) => {
        query = query.andWhere(`entity.${String(key)} IS NULL`)
      })
    }
    // Apply where not null conditions
    if (whereNotNull && whereNotNull.length > 0) {
      whereNotNull.forEach((key: any) => {
        const whereKey = key.startsWith("entity_")
          ? key
          : `entity.${String(key)}`
        query = query.andWhere(`${String(whereKey)} IS NOT NULL`)
      })
    }

    if (whereNullOrNotNull && whereNullOrNotNull.length > 0) {
      whereNullOrNotNull.forEach((key: any) => {
        const whereKey = key.startsWith("entity_") ? key : `entity.${key}`
        query = query.andWhere(
          `${String(whereKey)} IS NOT NULL OR ${String(whereKey)} IS NULL`,
        )
      })
    }

    if (withDeleted) {
      query = query.withDeleted()
    }

    // Apply select fields for the main entity
    // if (select && select.length > 0) {
    //   query = query.select(select.map((field) => `entity.${String(field)}`))
    // }

    // Load relations with select fields
    // if (relations && relations.length > 0) {
    //   relations.forEach((relation) => {
    //     const parts = relation.split(".")
    //     let relationName = "entity"

    //     parts.forEach((part) => {
    //       // Handle pipe-separated sub-relations
    //       const subRelations = part.split("|")

    //       subRelations.forEach((subRelation) => {
    //         const [relationPath, fields] = subRelation.split(":")
    //         const alias = `${relationName}_${relationPath}`

    //         if (fields) {
    //           query = query.leftJoin(`${relationName}.${relationPath}`, alias)
    //           query = query.addSelect(
    //             fields.split(",").map((field) => `${alias}.${field.trim()}`),
    //           )
    //         } else {
    //           query = query.leftJoinAndSelect(
    //             `${relationName}.${relationPath}`,
    //             alias,
    //           )
    //         }
    //       })

    //       // Update relationName for next level
    //       relationName = `${relationName}_${subRelations[0].split(":")[0]}`
    //     })
    //   })
    // }

    if (select && select.length > 0) {
      // When using select, make sure we're not using distinctValues at the same time
      if (!distinctValues || distinctValues.length === 0) {
        // If orderBy is used, ensure those columns are included in the select
        if (orderBy && Object.keys(orderBy).length > 0) {
          // Create a set of fields to select to avoid duplicates
          const fieldsToSelect = new Set<string>(select as string[])

          // Add orderBy fields to the selection
          Object.keys(orderBy).forEach((key) => {
            fieldsToSelect.add(key)
          })

          // Convert set back to array and create the select
          query = query.select(
            [...fieldsToSelect].map((field) => `entity.${String(field)}`),
          )
        } else {
          // Normal select without orderBy
          query = query.select(select.map((field) => `entity.${String(field)}`))
        }
      }
      // If both select and distinctValues are used, we'll handle it in the distinctValues section
    }

    if (distinctValues && distinctValues.length > 0) {
      const result: any = {}

      for (const key of distinctValues) {
        // Create a new query for each distinct value to avoid conflicts with select
        let distinctQuery = this.entity.createQueryBuilder("entity")

        // Apply the same where conditions to the distinct query
        if (where) {
          Object.entries(where).forEach(([k, v]) => {
            const whereKey = k.startsWith("entity_") ? k : `entity.${k}`
            distinctQuery = distinctQuery.andWhere(`${whereKey} = :${k}`, {
              [k]: v,
            })
          })
        }

        // Apply the distinct selection
        const values: any = await distinctQuery
          .select(`DISTINCT entity.${String(key)}`, String(key))
          .getRawMany()

        // Extract unique values and store in the result object
        result[String(key)] = values.map((row) => row[String(key)])
      }

      return result
    }

    // Load relations with select fields
    if (relations && relations.length > 0) {
      // Track joined relations to avoid duplicates
      const joinedRelations = new Map<string, string>() // key: relation path, value: alias

      relations.forEach((relation) => {
        // Check if relation contains parallel paths (using & syntax)
        if (relation.includes("&")) {
          // Split the relation string by the base part and parallel branches
          const [basePath, ...parallelPaths] = relation
            .split("&")
            .map((p) => p.trim())

          // Process the base path first
          const parts = basePath.split(".")
          let relationName = "entity"
          let relationPath = ""

          parts.forEach((part, index) => {
            const [relationPathPart, fields] = part.split(":")

            // Support for with_soft_deleted_ prefix
            let condition, alias
            const withSoftDeleted = isEmpty(relationPathPart)
              ? false
              : relationPathPart.startsWith("with_soft_deleted_")
            const replacedPath = withSoftDeleted
              ? relationPathPart.replace("with_soft_deleted_", "")
              : relationPathPart

            // Build the full path to this relation
            const prevPath = relationPath
            relationPath = prevPath
              ? `${prevPath}.${replacedPath}`
              : replacedPath

            // Check if this relation is already joined
            if (joinedRelations.has(relationPath)) {
              relationName = joinedRelations.get(relationPath) || relationName

              // If fields are specified, add them to the select
              if (fields) {
                query = query.addSelect(
                  fields
                    .split(",")
                    .map((field) => `${relationName}.${field.trim()}`),
                )
              }
            } else {
              // Create a new join
              alias = `entity_${relationPath.replace(/\./g, "_")}`

              if (withSoftDeleted) {
                condition = `${alias}.deleted_at IS NOT NULL OR ${alias}.deleted_at IS NULL`
              }

              // Join the relation
              if (fields) {
                query = query.leftJoin(
                  index === 0
                    ? `entity.${replacedPath}`
                    : `${relationName}.${replacedPath}`,
                  alias,
                  condition,
                )
                query = query.addSelect(
                  fields.split(",").map((field) => `${alias}.${field.trim()}`),
                )
              } else {
                query = query.leftJoinAndSelect(
                  index === 0
                    ? `entity.${replacedPath}`
                    : `${relationName}.${replacedPath}`,
                  alias,
                  condition,
                )
              }

              // Store the alias for this relation path
              joinedRelations.set(relationPath, alias)
              relationName = alias
            }
          })

          // Process each parallel branch
          parallelPaths.forEach((parallelPath) => {
            // Find the parent relation from the parallel path
            const parallelParts = parallelPath.split(".")
            const parentRelationRaw = parallelParts[0].split(":")[0]

            // Support for with_soft_deleted_ prefix in parallel paths
            const withSoftDeleted = isEmpty(parentRelationRaw)
              ? false
              : parentRelationRaw.startsWith("with_soft_deleted_")
            const parentRelation = withSoftDeleted
              ? parentRelationRaw.replace("with_soft_deleted_", "")
              : parentRelationRaw

            // Find the parent alias
            let parentPath = ""
            let parentAlias = "entity"

            // Look for the parent relation in the already joined relations
            for (const [path, alias] of joinedRelations.entries()) {
              if (
                path === parentRelation ||
                path.endsWith(`.${parentRelation}`)
              ) {
                parentPath = path
                parentAlias = alias
                break
              }
            }

            // Process the parallel path starting from the parent
            let currentPath = parentPath
            let currentAlias = parentAlias

            // Skip the first part if it's the parent relation
            const startIdx =
              parentPath === parentRelation ||
              parentPath.endsWith(`.${parentRelation}`)
                ? 1
                : 0

            for (let i = startIdx; i < parallelParts.length; i++) {
              const part = parallelParts[i]
              const [relationPathPart, fields] = part.split(":")

              // Support for with_soft_deleted_ prefix
              let condition, alias
              const withSoftDeleted = isEmpty(relationPathPart)
                ? false
                : relationPathPart.startsWith("with_soft_deleted_")
              const replacedPath = withSoftDeleted
                ? relationPathPart.replace("with_soft_deleted_", "")
                : relationPathPart

              // Build the full path
              currentPath = currentPath
                ? `${currentPath}.${replacedPath}`
                : replacedPath

              // Check if already joined
              if (joinedRelations.has(currentPath)) {
                currentAlias = joinedRelations.get(currentPath) || currentAlias

                // Add fields if specified
                if (fields) {
                  query = query.addSelect(
                    fields
                      .split(",")
                      .map((field) => `${currentAlias}.${field.trim()}`),
                  )
                }
              } else {
                // Create a new join
                alias = `entity_${currentPath.replace(/\./g, "_")}`

                if (withSoftDeleted) {
                  condition = `${alias}.deleted_at IS NOT NULL OR ${alias}.deleted_at IS NULL`
                }

                // Join the relation
                if (fields) {
                  query = query.leftJoin(
                    `${currentAlias}.${replacedPath}`,
                    alias,
                    condition,
                  )
                  query = query.addSelect(
                    fields
                      .split(",")
                      .map((field) => `${alias}.${field.trim()}`),
                  )
                } else {
                  query = query.leftJoinAndSelect(
                    `${currentAlias}.${replacedPath}`,
                    alias,
                    condition,
                  )
                }

                // Store the alias
                joinedRelations.set(currentPath, alias)
                currentAlias = alias
              }
            }
          })
        } else {
          // Original behavior for dot-separated relations
          const parts = relation.split(".")
          let relationName = "entity"
          let relationPath = ""

          parts.forEach((part, index) => {
            const [relationPathPart, fields] = part.split(":")

            // Support for with_soft_deleted_ prefix
            let condition, alias
            const withSoftDeleted = isEmpty(relationPathPart)
              ? false
              : relationPathPart.startsWith("with_soft_deleted_")
            const replacedPath = withSoftDeleted
              ? relationPathPart.replace("with_soft_deleted_", "")
              : relationPathPart

            // Build the full path to this relation
            const prevPath = relationPath
            relationPath = prevPath
              ? `${prevPath}.${replacedPath}`
              : replacedPath

            // Check if this relation is already joined
            if (joinedRelations.has(relationPath)) {
              relationName = joinedRelations.get(relationPath) || relationName

              // If fields are specified, add them to the select
              if (fields) {
                query = query.addSelect(
                  fields
                    .split(",")
                    .map((field) => `${relationName}.${field.trim()}`),
                )
              }
            } else {
              // Create a new join
              alias = `entity_${relationPath.replace(/\./g, "_")}`

              if (withSoftDeleted) {
                condition = `${alias}.deleted_at IS NOT NULL OR ${alias}.deleted_at IS NULL`
              }

              // Join the relation
              if (fields) {
                query = query.leftJoin(
                  index === 0
                    ? `entity.${replacedPath}`
                    : `${relationName}.${replacedPath}`,
                  alias,
                  condition,
                )
                query = query.addSelect(
                  fields.split(",").map((field) => `${alias}.${field.trim()}`),
                )
              } else {
                query = query.leftJoinAndSelect(
                  index === 0
                    ? `entity.${replacedPath}`
                    : `${relationName}.${replacedPath}`,
                  alias,
                  condition,
                )
              }

              // Store the alias for this relation path
              joinedRelations.set(relationPath, alias)
              relationName = alias
            }
          })
        }
      })
    }

    if (innerJoinRelations && innerJoinRelations.length > 0) {
      innerJoinRelations.forEach((relation) => {
        const parts = relation.split(".")
        let relationName = "entity"

        parts.forEach((part) => {
          // Handle pipe-separated sub-relations
          const subRelations = part.split("|")

          subRelations.forEach((subRelation) => {
            const [relationPath, fields] = subRelation.split(":")
            const alias = `${relationName}_${relationPath}`

            if (fields) {
              query = query.innerJoin(`${relationName}.${relationPath}`, alias)
              query = query.addSelect(
                fields.split(",").map((field) => `${alias}.${field.trim()}`),
              )
            } else {
              query = query.innerJoinAndSelect(
                `${relationName}.${relationPath}`,
                alias,
              )
            }
          })

          // Update relationName for next level
          relationName = `${relationName}_${subRelations[0].split(":")[0]}`
        })
      })
    }
    // Apply search conditions with OR logic
    // if (search && Object.keys(search).length > 0) {
    //   query = query.andWhere(
    //     new Brackets((qb) => {
    //       Object.entries(search).forEach(([key, value]) => {
    //         const searchKey = key.startsWith("entity_") ? key : `entity.${key}`

    //         if (!isEmpty(value)) {
    //           const searchTerm = `%${value}%`
    //           qb.orWhere(`${searchKey} ILIKE :searchTerm`, { searchTerm })
    //         }
    //       })
    //     }),
    //   )
    // }

    if (search && Object.keys(search).length > 0) {
      query = query.andWhere(
        new Brackets((qb) => {
          Object.entries(search).forEach(([key, value]) => {
            const searchKey = key.startsWith("entity_") ? key : `entity.${key}`

            if (!isEmpty(value)) {
              if (typeof value === "number") {
                // For integers, exact match or cast to text for partial match
                const searchTerm = `%${value}%`
                qb.orWhere(`${searchKey}::text ILIKE :searchTerm`, {
                  searchTerm,
                })
              } else if (
                searchKey.includes("date") ||
                searchKey.includes("created_at") ||
                searchKey.includes("updated_at")
              ) {
                // timestamps/dates → cast to text
                qb.orWhere(`${searchKey}::text ILIKE :searchTerm`, {
                  searchTerm: `%${value}%`,
                })
              } else {
                // For strings, use ILIKE
                const searchTerm = `%${value}%`
                qb.orWhere(`${searchKey} ILIKE :searchTerm`, { searchTerm })
              }
            }
          })
        }),
      )
    }

    // Apply order by conditions
    if (orderBy && Object.keys(orderBy).length > 0) {
      Object.entries(orderBy).forEach(([key, value]) => {
        let entityName = `entity.${String(key)}`

        if (key.includes(".")) {
          entityName = `entity_${String(key)}`
        }

        query = query.addOrderBy(entityName, value as "ASC" | "DESC")
      })
    }

    // Apply group by conditions
    if (groupBy && groupBy.length > 0) {
      query = query.groupBy(
        groupBy.map((field) => `entity.${String(field)}`).join(", "),
      )

      // Add count selection for grouped fields
      query = query.select("COUNT(entity.id)", "count") // Adjust 'entity.id' to your unique field
      groupBy.forEach(
        (field) => (query = query.addSelect(`entity.${String(field)}`)),
      )
    }

    // Apply relation count dynamically
    if (relationCount && relationCount.length > 0) {
      relationCount.forEach((relation) => {
        query = query.loadRelationCountAndMap(
          `entity.${relation}_count`,
          `entity.${relation}`,
        )
      })
    }

    if (sum && sum.length > 0) {
      sum.forEach((key) => {
        const filed = key.startsWith("entity_") ? key : `entity.${key}`
        query = query.addSelect(`SUM(${filed})`, `${key}_total`)
      })
    }

    if (relationCountWithConditions) {
      relationCountWithConditions.forEach((relation) => {
        if (relation.condition) {
          query = query.loadRelationCountAndMap(
            `entity.${relation.alias}`, // Alias for the count field
            `entity.${relation.relation}`, // Relation to count
            relation.alias, // Alias for the relation
            (qb) => qb.andWhere(relation.condition, relation.params), // Apply condition
          )
        }
      })
    }

    if (findOne) return query.getOne()

    if (getCountOnly) return query.getCount()

    // Apply pagination
    if ((take && skip) || skip === 0) {
      query = query.take(take)
      query = query.skip(skip)

      const [data, count]: [T[], number] = await query
        .take(take)
        .skip(skip)
        .getManyAndCount()

      return { count, data }
    }

    if (rawData) return query.getRawMany()

    // Execute query based on findOne flag
    return query.getMany()
  }

  public async save(
    data: QueryDeepPartialEntity<T>,
    conditions?: FindOptionsWhere<T>,
    manager?: EntityManager,
    useForceSave?: boolean,
  ): Promise<T> {
    const repository = manager
      ? manager.getRepository(this.entity.target)
      : this.entity

    if (!isEmpty(conditions)) {
      const record = await repository.findOne({ where: conditions })

      if (record) {
        if (!isEmpty(useForceSave) && useForceSave)
          await repository.save(data as DeepPartial<T>)
        else await repository.update(conditions, data)

        return repository.findOneOrFail({ where: conditions })
      }
    }
    return repository.save(data as DeepPartial<T>)
  }

  public async saveMany(
    data: DeepPartial<T>[],
    conditions?: FindOptionsWhere<T>,
    manager?: EntityManager,
  ): Promise<T[]> {
    const repository = manager
      ? manager.getRepository(this.entity.target)
      : this.entity

    return repository.save(data)
  }

  public async remove(
    conditions: FindOptionsWhere<T>,
    manager?: EntityManager,
    hardDelete?: boolean,
  ): Promise<T | DeleteResult> {
    const repository = manager
      ? manager.getRepository(this.entity.target)
      : this.entity
    if (hardDelete) {
      return repository.delete({ ...conditions })
    }
    return repository.softDelete({ ...conditions })
  }
}