import { AccountPayload, prisma } from "@codeflash-ai/common"
import { Prisma } from "@prisma/client"
import * as Sentry from "@sentry/nextjs"
import { eachDayOfInterval, startOfDay } from "date-fns"
import { RepositoryWithUsage } from "@/app/(dashboard)/dashboard/action"
import { trackRepositoryConnected } from "@/lib/analytics/tracking"
import { getRepositoriesForAccountCached } from "@/lib/services/repository-utils"
import { withTiming } from "@/lib/server-action-timing"

export async function getOptimizationsTimeSeriesData(repoId: string, onlySuccessful?: boolean) {
  try {
    // Use SQL GROUP BY to aggregate on the database side instead of fetching every row
    const successFilter =
      onlySuccessful === true ? Prisma.sql`AND is_optimization_found = true` : Prisma.empty
    const dailyCounts = await prisma.$queryRaw<Array<{ day: string; cnt: bigint }>>`
      SELECT DATE(created_at) AS day, COUNT(*)::bigint AS cnt
       FROM optimization_events
       WHERE repository_id = ${repoId} ${successFilter}
       GROUP BY DATE(created_at)
       ORDER BY day`

    if (dailyCounts.length === 0) return []

    const groupedByDay: Record<string, number> = {}
    for (const row of dailyCounts) {
      // DATE columns come back as Date objects from Prisma; format to YYYY-MM-DD
      const dayStr =
        typeof row.day === "string"
          ? row.day
          : (row.day as unknown as Date).toISOString().slice(0, 10)
      groupedByDay[dayStr] = Number(row.cnt)
    }

    const sortedDays = Object.keys(groupedByDay).sort()
    const allDates = eachDayOfInterval({
      start: new Date(sortedDays[0] + "T00:00:00"),
      end: startOfDay(new Date()),
    }).map(d => d.toISOString().slice(0, 10))

    let cumulativeCount = 0
    const completeData = allDates.map(date => {
      cumulativeCount += groupedByDay[date] || 0
      return { date, count: cumulativeCount }
    })

    return completeData
  } catch (error) {
    console.error("Failed to fetch optimization time series data:", error)
    return []
  }
}

export async function getPullRequestEventTimeSeriesData(year: number, repoId: string) {
  try {
    // Use SQL GROUP BY to aggregate on the database side instead of fetching every row
    const startDate = new Date(`${year}-01-01T00:00:00.000Z`)
    const endDate = new Date(`${year + 1}-01-01T00:00:00.000Z`)
    const monthlyStats = await prisma.$queryRaw<
      Array<{
        month: number
        pr_created: bigint
        pr_merged: bigint
        pr_closed: bigint
      }>
    >`SELECT
         EXTRACT(MONTH FROM created_at)::int AS month,
         SUM(CASE WHEN event_type = 'pr_created' THEN 1 ELSE 0 END)::bigint AS pr_created,
         SUM(CASE WHEN event_type = 'pr_merged' THEN 1 ELSE 0 END)::bigint AS pr_merged,
         SUM(CASE WHEN event_type = 'pr_closed' THEN 1 ELSE 0 END)::bigint AS pr_closed
       FROM optimization_events
       WHERE event_type IN ('pr_created', 'pr_merged', 'pr_closed')
         AND created_at >= ${startDate}
         AND created_at < ${endDate}
         AND repository_id = ${repoId}
       GROUP BY EXTRACT(MONTH FROM created_at)`

    type MonthStat = { month: number; pr_created: bigint; pr_merged: bigint; pr_closed: bigint }
    const statsMap = new Map<number, MonthStat>(
      (monthlyStats as MonthStat[]).map((r: MonthStat) => [r.month, r]),
    )

    return Array.from({ length: 12 }, (_, i) => {
      const month = i + 1
      const stats = statsMap.get(month)
      return {
        month: `${year}-${month.toString().padStart(2, "0")}`,
        pr_created: Number(stats?.pr_created ?? 0),
        pr_merged: Number(stats?.pr_merged ?? 0),
        pr_closed: Number(stats?.pr_closed ?? 0),
      }
    })
  } catch (error) {
    console.error("Failed to fetch pull request event time series data:", error)
    return []
  }
}

export async function getOptimizationCountsByRepo(
  repoId: string,
): Promise<{ total: number; successful: number }> {
  const result = await prisma.$queryRaw<[{ total: bigint; successful: bigint }]>`
    SELECT
       COUNT(*)::bigint AS total,
       SUM(CASE WHEN is_optimization_found THEN 1 ELSE 0 END)::bigint AS successful
     FROM optimization_events
     WHERE repository_id = ${repoId}`
  return {
    total: Number(result[0].total),
    successful: Number(result[0].successful),
  }
}

export async function getActiveUserLeaderboardLast30DaysForRepo(
  repoId: string,
): Promise<{ username: string; eventCount: number; avatarUrl: string }[]> {
  const since = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)

  const groupedCounts = await prisma.optimization_events.groupBy({
    by: ["current_username"],
    where: {
      repository_id: repoId,
      created_at: {
        gte: since,
      },
      current_username: {
        not: null,
      },
    },
    _count: {
      id: true,
    },
    orderBy: {
      _count: {
        id: "desc",
      },
    },
  })

  return groupedCounts.map(
    (entry: { current_username: string | null; _count: { id: number } }) => ({
      username: entry.current_username!,
      eventCount: entry._count.id,
      avatarUrl: `https://github.com/${entry.current_username}.png`,
    }),
  )
}

export const getRepositoryByIdForPayload = withTiming(
  "getRepositoryById",
  async (payload: AccountPayload, repoId: string): Promise<RepositoryWithUsage | null> => {
    try {
      // Fetch repo, authorized repoIds, and recent activity count in parallel
      const [repo, { repoIds }, recentEventCount] = await Promise.all([
        prisma.repositories.findUnique({
          where: { id: repoId },
          include: { _count: { select: { repository_members: true } } },
        }),
        getRepositoriesForAccountCached(payload),
        prisma.optimization_events.count({
          where: {
            repository_id: repoId,
            created_at: {
              gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
            },
          },
        }),
      ])

      if (!repo || !repoIds.includes(repo.id)) return null

      // Track repository view as a connection/engagement signal
      const userId = "userId" in payload ? payload.userId : undefined
      if (userId) {
        trackRepositoryConnected(userId, {
          repositoryId: repo.id,
          repositoryName: repo.full_name,
        })
      }

      const organization = repo.full_name.split("/")[0]
      return {
        id: repo.id,
        github_repo_id: repo.github_repo_id,
        name: repo.name,
        full_name: repo.full_name,
        is_private: repo.is_private,
        is_active: recentEventCount > 0,
        has_github_action: repo.has_github_action,
        created_at: repo.created_at,
        last_optimized: repo.last_optimized,
        optimizations_limit: repo.optimizations_limit,
        optimizations_used: repo.optimizations_used,
        organization,
        avatarUrl: `https://github.com/${organization}.png`,
        membersCount: repo._count.repository_members,
      }
    } catch (error) {
      console.error("Failed to fetch repository by ID:", error)
      return null
    }
  },
)
