"use server"
import { getRepositoriesForAccountCached } from "@/lib/services/repository-utils"
import { AccountPayload, buildOptimizationOrCondition, prisma } from "@codeflash-ai/common"

export async function getRepositoriesWithStagingEvents(
  payload: AccountPayload,
): Promise<Array<{ id: string; full_name: string }>> {
  const { repoIds, repos: allRepos } = await getRepositoriesForAccountCached(payload)

  if (repoIds.length === 0) {
    return []
  }

  // Get distinct repository IDs that have staging events using groupBy (more efficient than findMany with distinct)
  const repoIdsWithStagingEvents = await prisma.optimization_events.groupBy({
    by: ["repository_id"],
    where: {
      is_staging: true,
      ...buildOptimizationOrCondition(payload, repoIds),
      repository_id: { not: null },
    },
  })

  // Filter and map repos that have staging events
  return allRepos
    .filter(repo => repoIdsWithStagingEvents.some(group => group.repository_id === repo.id))
    .map(repo => ({
      id: repo.id,
      full_name: repo.full_name,
    }))
    .sort((a, b) => a.full_name.localeCompare(b.full_name))
}

export async function getAllOptimizationEvents({
  payload,
  search,
  filter,
  sort,
  page = 1,
  pageSize = 10,
}: {
  payload: AccountPayload
  search?: string
  filter?: Record<string, any>
  sort?: { [key: string]: "asc" | "desc" }
  page?: number
  pageSize?: number
}) {
  const repoIds = (await getRepositoriesForAccountCached(payload)).repoIds

  const where: any = {
    is_staging: true,
    ...buildOptimizationOrCondition(payload, repoIds),
  }

  if (search) {
    where.AND = where.AND || []
    where.AND.push({
      OR: [
        {
          function_name: {
            contains: search,
            mode: "insensitive",
          },
        },
        {
          file_path: {
            contains: search,
            mode: "insensitive",
          },
        },
        {
          repository: {
            full_name: {
              contains: search,
              mode: "insensitive",
            },
          },
        },
      ],
    })
  }

  if (filter) {
    Object.keys(filter).forEach(key => {
      if (key === "repository_id") {
        where.AND = where.AND || []
        where.AND.push({ [key]: filter[key] })
      } else if (key !== "review_quality") {
        where[key] = filter[key]
      }
    })
  }

  const needsOptimizationFeaturesJoin =
    (sort && Object.keys(sort).some(k => k.toLowerCase() === "review_quality")) ||
    (filter && Object.keys(filter).some(k => k.toLowerCase() === "review_quality"))

  if (needsOptimizationFeaturesJoin) {
    const whereConditions = []
    const params: any[] = []
    let paramIndex = 1
    whereConditions.push(`oe.is_staging = true`)
    if ("orgId" in payload) {
      whereConditions.push(`oe.repository_id IN (${repoIds.map(id => `'${id}'`).join(",")})`)
    } else {
      whereConditions.push(
        `(
      oe.repository_id IN (${repoIds.map(id => `'${id}'`).join(",")})
      OR oe.user_id = '${payload.userId}'
      OR oe.current_username = '${payload.username}'
    )`,
      )
    }
    // Add search conditions
    if (search) {
      whereConditions.push(
        `(oe.function_name ILIKE $${paramIndex} OR oe.file_path ILIKE $${paramIndex} OR r.full_name ILIKE $${paramIndex})`,
      )
      params.push(`%${search}%`)
      paramIndex += 1
    }
    // Add filter conditions
    if (filter) {
      if (filter.status) {
        whereConditions.push(`oe.status = $${paramIndex}`)
        params.push(filter.status)
        paramIndex += 1
      }
      if (filter.event_type) {
        whereConditions.push(`oe.event_type = $${paramIndex}`)
        params.push(filter.event_type)
        paramIndex += 1
      }
      if (filter.review_quality) {
        whereConditions.push(`of.review_quality = $${paramIndex}`)
        params.push(filter.review_quality)
        paramIndex += 1
      }
      if (filter.repository_id !== undefined) {
        if (filter.repository_id === null) {
          whereConditions.push(`oe.repository_id IS NULL`)
        } else if (filter.repository_id.not !== undefined && filter.repository_id.not === null) {
          whereConditions.push(`oe.repository_id IS NOT NULL`)
        }
      }
    }
    const whereClause = whereConditions.join(" AND ")
    const orderByClauses: string[] = []
    if (sort && Object.keys(sort).length > 0) {
      Object.entries(sort).forEach(([key, direction]) => {
        const dir = direction.toUpperCase()
        if (key.toLowerCase() === "review_quality") {
          orderByClauses.push(`
            CASE
              WHEN LOWER(of.review_quality) = 'high' THEN 3
              WHEN LOWER(of.review_quality) = 'medium' THEN 2
              WHEN LOWER(of.review_quality) = 'low' THEN 1
              ELSE 0
            END ${dir}
          `)
        } else {
          orderByClauses.push(`oe.${key} ${dir}`)
        }
      })
    }
    if (!sort) {
      orderByClauses.push("oe.created_at DESC")
    }
    const orderByClause = orderByClauses.join(", ")
    const events = await prisma.$queryRawUnsafe<any[]>(
      `
      SELECT
        oe.*,
        of.review_quality,
        of.review_explanation
      FROM optimization_events oe
      LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
      LEFT JOIN repositories r ON oe.repository_id = r.id
      WHERE ${whereClause}
      ORDER BY ${orderByClause}
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
      `,
      ...params,
      pageSize,
      (page - 1) * pageSize,
    )
    // Get total count
    const countResult = await prisma.$queryRawUnsafe<[{ count: bigint }]>(
      `
      SELECT COUNT(*) as count
      FROM optimization_events oe
      LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
      LEFT JOIN repositories r ON oe.repository_id = r.id
      WHERE ${whereClause}
      `,
      ...params,
    )
    const totalCount = Number(countResult[0].count)
    // Fetch repository data for the events
    const eventsWithRepo = await Promise.all(
      events.map(async event => {
        if (event.repository_id) {
          const repository = await prisma.repositories.findUnique({
            where: { id: event.repository_id },
          })
          return { ...event, repository }
        }
        return { ...event, repository: null }
      }),
    )
    return { events: eventsWithRepo, totalCount }
  } else {
    // Standard Prisma query with native orderBy
    const orderBy = sort || { created_at: "desc" }

    const events = await prisma.optimization_events.findMany({
      where,
      orderBy,
      skip: (page - 1) * pageSize,
      take: pageSize,
      include: {
        repository: true,
      },
    })

    // Fetch review_quality and review_explanation for each event
    const eventsWithReviewData = await Promise.all(
      events.map(async event => {
        const features = await prisma.optimization_features.findUnique({
          where: { trace_id: event.trace_id },
          select: {
            review_quality: true,
            review_explanation: true,
          },
        })

        return {
          ...event,
          review_quality: features?.review_quality || null,
          review_explanation: features?.review_explanation || null,
        }
      }),
    )

    const totalCount = await prisma.optimization_events.count({ where })

    return { events: eventsWithReviewData, totalCount }
  }
}
