"use server"
import { cache } from "react"
import { getRepositoriesForAccountCached } from "@/lib/services/repository-utils"
import { withTiming } from "@/lib/server-action-timing"
import { AccountPayload, prisma } from "@codeflash-ai/common"
import { Prisma } from "@prisma/client"

// Cached implementation for getRepositoriesWithStagingEvents
// React cache() ensures this is only executed once per unique payload within a single request
const getRepositoriesWithStagingEventsImpl = cache(
  async (payload: AccountPayload): Promise<Array<{ id: string; full_name: string }>> => {
    const { repoIds, repos: allRepos } = await getRepositoriesForAccountCached(payload)

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

    // For org accounts, use simple IN clause. For personal accounts, use UNION
    // to avoid bitmap OR merge (each branch uses its own composite index independently).
    let repoIdsWithStagingEvents: Array<{ repository_id: string | null }>

    if ("orgId" in payload) {
      // Organization account: simple IN clause
      const groupByResult = await prisma.optimization_events.groupBy({
        by: ["repository_id"],
        where: {
          is_staging: true,
          repository_id: { in: repoIds, not: null },
        },
      })
      repoIdsWithStagingEvents = groupByResult.map((g: { repository_id: string | null }) => ({
        repository_id: g.repository_id,
      }))
    } else {
      // Personal account: UNION query for efficient index usage
      // Each branch can use its own composite index independently
      const result = await prisma.$queryRaw<Array<{ repository_id: string | null }>>`
        SELECT DISTINCT repository_id
        FROM (
          SELECT repository_id
          FROM optimization_events
          WHERE is_staging = true
            AND repository_id IN (${Prisma.join(repoIds)})
            AND repository_id IS NOT NULL
          UNION
          SELECT repository_id
          FROM optimization_events
          WHERE is_staging = true
            AND user_id = ${payload.userId}
            AND repository_id IS NOT NULL
          UNION
          SELECT repository_id
          FROM optimization_events
          WHERE is_staging = true
            AND current_username = ${payload.username}
            AND repository_id IS NOT NULL
        ) AS combined_events
      `

      repoIdsWithStagingEvents = (result as Array<{ repository_id: string | null }>).map(row => ({
        repository_id: row.repository_id,
      }))
    }

    // Filter and map repos that have staging events (O(1) Set lookup instead of O(n) .some)
    const stagingRepoSet = new Set(repoIdsWithStagingEvents.map(g => g.repository_id))
    return allRepos
      .filter(repo => stagingRepoSet.has(repo.id))
      .map(repo => ({
        id: repo.id,
        full_name: repo.full_name,
      }))
      .sort((a, b) => a.full_name.localeCompare(b.full_name))
  },
)

export const getRepositoriesWithStagingEvents = withTiming(
  "getRepositoriesWithStagingEvents",
  getRepositoriesWithStagingEventsImpl,
)

// Note: React cache() is NOT used here because this function takes an object argument
// (reference equality means cache never hits). Deduplication happens at a higher level.
const getAllOptimizationEventsImpl = async ({
  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

  if (repoIds.length === 0) {
    return { events: [], totalCount: 0 }
  }

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

  if (needsOptimizationFeaturesJoin) {
    // Raw SQL path for review_quality sorting/filtering
    const whereFragments: Prisma.Sql[] = [Prisma.sql`oe.is_staging = true`]

    if ("orgId" in payload) {
      whereFragments.push(Prisma.sql`oe.repository_id IN (${Prisma.join(repoIds)})`)
    } else {
      // For personal accounts, use OR pattern in WHERE (raw SQL already, so bitmap merge is acceptable here
      // since it's joined with optimization_features anyway). The primary bottleneck was the groupBy,
      // which is now fixed above. This path is rarely hit (only when sorting by review_quality).
      whereFragments.push(
        Prisma.sql`(
        oe.repository_id IN (${Prisma.join(repoIds)})
        OR oe.user_id = ${payload.userId}
        OR oe.current_username = ${payload.username}
      )`,
      )
    }

    // Add search conditions
    if (search) {
      const searchPattern = `%${search}%`
      whereFragments.push(
        Prisma.sql`(oe.function_name ILIKE ${searchPattern} OR oe.file_path ILIKE ${searchPattern} OR r.full_name ILIKE ${searchPattern})`,
      )
    }
    // Add filter conditions
    if (filter) {
      if (filter.status) {
        whereFragments.push(Prisma.sql`oe.status = ${filter.status}`)
      }
      if (filter.event_type) {
        whereFragments.push(Prisma.sql`oe.event_type = ${filter.event_type}`)
      }
      if (filter.review_quality) {
        whereFragments.push(Prisma.sql`of.review_quality = ${filter.review_quality}`)
      }
      if (filter.repository_id !== undefined) {
        if (filter.repository_id === null) {
          whereFragments.push(Prisma.sql`oe.repository_id IS NULL`)
        } else if (filter.repository_id.not !== undefined && filter.repository_id.not === null) {
          whereFragments.push(Prisma.sql`oe.repository_id IS NOT NULL`)
        } else if (typeof filter.repository_id === "string") {
          whereFragments.push(Prisma.sql`oe.repository_id = ${filter.repository_id}`)
        }
      }
    }
    const whereClause = Prisma.join(whereFragments, " AND ")
    const orderByClauses: Prisma.Sql[] = []
    if (sort && Object.keys(sort).length > 0) {
      Object.entries(sort).forEach(([key, direction]) => {
        const dir = direction.toUpperCase() === "ASC" ? Prisma.sql`ASC` : Prisma.sql`DESC`
        if (key.toLowerCase() === "review_quality") {
          orderByClauses.push(Prisma.sql`
            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 {
          const col = key === "created_at" ? Prisma.sql`oe.created_at` : Prisma.raw(`oe.${key}`)
          orderByClauses.push(Prisma.sql`${col} ${dir}`)
        }
      })
    }
    if (!sort) {
      orderByClauses.push(Prisma.sql`oe.created_at DESC`)
    }
    const orderByClause = Prisma.join(orderByClauses, ", ")
    const paginationLimit = pageSize
    const paginationOffset = (page - 1) * pageSize
    const [events, countResult] = await Promise.all([
      prisma.$queryRaw<any[]>`
        SELECT
          oe.*,
          of.review_quality,
          of.review_explanation,
          r.full_name as repo_full_name,
          r.name as repo_name,
          r.id as repo_id
        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 ${paginationLimit} OFFSET ${paginationOffset}
        `,
      prisma.$queryRaw<[{ 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}
        `,
    ])
    const totalCount = Number(countResult[0].count)
    // Repository data is already included from the JOIN
    const eventsWithRepo = events.map(
      (
        event: Record<string, unknown> & {
          repo_id?: string
          repo_full_name?: string
          repo_name?: string
        },
      ) => ({
        ...event,
        repository: event.repo_id
          ? { id: event.repo_id, full_name: event.repo_full_name, name: event.repo_name }
          : null,
      }),
    )
    return { events: eventsWithRepo, totalCount }
  } else {
    // Standard Prisma query with native orderBy (optimized with UNION for personal accounts)
    const orderBy = sort || { created_at: "desc" as const }

    let events
    let totalCount

    if ("orgId" in payload) {
      // Organization account: simple IN clause
      const where = {
        is_staging: true,
        repository_id: { in: repoIds },
      } as any

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

      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]
          }
        })
      }

      ;[events, totalCount] = await Promise.all([
        prisma.optimization_events.findMany({
          where,
          orderBy,
          skip: (page - 1) * pageSize,
          take: pageSize,
          include: {
            repository: {
              select: { id: true, full_name: true, name: true },
            },
          },
        }),
        prisma.optimization_events.count({ where }),
      ])
    } else {
      // Personal account: Use raw SQL with UNION for efficient index seeks
      let searchCondition = Prisma.empty
      if (search) {
        const searchPattern = `%${search}%`
        searchCondition = Prisma.sql`AND (oe.function_name ILIKE ${searchPattern} OR oe.file_path ILIKE ${searchPattern} OR r.full_name ILIKE ${searchPattern})`
      }

      const filterFragments: Prisma.Sql[] = []
      if (filter) {
        Object.entries(filter).forEach(([key, value]) => {
          if (key === "status") {
            filterFragments.push(Prisma.sql`AND oe.status = ${value}`)
          } else if (key === "event_type") {
            filterFragments.push(Prisma.sql`AND oe.event_type = ${value}`)
          } else if (key === "repository_id") {
            if (value === null) {
              filterFragments.push(Prisma.sql`AND oe.repository_id IS NULL`)
            } else if (value?.not === null) {
              filterFragments.push(Prisma.sql`AND oe.repository_id IS NOT NULL`)
            } else if (typeof value === "string") {
              filterFragments.push(Prisma.sql`AND oe.repository_id = ${value}`)
            }
          }
        })
      }
      const filterConditions =
        filterFragments.length > 0 ? Prisma.join(filterFragments, " ") : Prisma.empty

      const orderByDir =
        typeof orderBy === "object" && orderBy.created_at === "asc"
          ? Prisma.sql`ASC`
          : Prisma.sql`DESC`

      const paginationLimit = pageSize
      const paginationOffset = (page - 1) * pageSize

      const unionSubquery = Prisma.sql`
          SELECT id FROM (
            SELECT id FROM optimization_events
            WHERE is_staging = true
              AND repository_id IN (${Prisma.join(repoIds)})
            UNION
            SELECT id FROM optimization_events
            WHERE is_staging = true AND user_id = ${payload.userId}
            UNION
            SELECT id FROM optimization_events
            WHERE is_staging = true AND current_username = ${payload.username}
          ) AS combined_ids
        `

      const [eventsResult, countResult] = await Promise.all([
        prisma.$queryRaw<any[]>`
          WITH base_events AS (
            SELECT oe.*, r.id as repo_id, r.full_name as repo_full_name, r.name as repo_name
            FROM optimization_events oe
            LEFT JOIN repositories r ON oe.repository_id = r.id
            WHERE oe.id IN (${unionSubquery})
            ${searchCondition}
            ${filterConditions}
            ORDER BY oe.created_at ${orderByDir}
            LIMIT ${paginationLimit} OFFSET ${paginationOffset}
          )
          SELECT * FROM base_events
          `,
        prisma.$queryRaw<[{ count: bigint }]>`
          SELECT COUNT(*) as count
          FROM optimization_events oe
          LEFT JOIN repositories r ON oe.repository_id = r.id
          WHERE oe.id IN (${unionSubquery})
          ${searchCondition}
          ${filterConditions}
          `,
      ])

      totalCount = Number(countResult[0].count)
      events = eventsResult.map(
        (
          event: Record<string, unknown> & {
            repo_id?: string
            repo_full_name?: string
            repo_name?: string
          },
        ) => ({
          ...event,
          repository: event.repo_id
            ? { id: event.repo_id, full_name: event.repo_full_name, name: event.repo_name }
            : null,
        }),
      )
    }

    // Batch-fetch review data for all events in a single query
    const traceIds = (events as Array<Record<string, unknown>>).map(
      (e: Record<string, unknown>) => e.trace_id as string,
    )
    const features =
      traceIds.length > 0
        ? await prisma.optimization_features.findMany({
            where: { trace_id: { in: traceIds } },
            select: {
              trace_id: true,
              review_quality: true,
              review_explanation: true,
            },
          })
        : []
    type ReviewFeature = {
      trace_id: string
      review_quality: string | null
      review_explanation: string | null
    }
    const featuresMap = new Map<string, ReviewFeature>(
      (features as ReviewFeature[]).map((f: ReviewFeature) => [f.trace_id, f]),
    )

    const eventsWithReviewData = (events as Array<Record<string, unknown>>).map(
      (event: Record<string, unknown>) => {
        const f = featuresMap.get(event.trace_id as string)
        return {
          ...event,
          review_quality: f?.review_quality || null,
          review_explanation: f?.review_explanation || null,
        }
      },
    )

    return { events: eventsWithReviewData, totalCount }
  }
}

export const getAllOptimizationEvents = withTiming(
  "getAllOptimizationEvents",
  getAllOptimizationEventsImpl,
)
