"use server"

import { getRepositoriesForAccountCached } from "@/lib/services/repository-utils"
import {
  prisma,
  GetRepositoriesForAccountPayload,
  AccountPayload,
  checkAndResetSubscriptionPeriod,
} from "@codeflash-ai/common"
import { dedup } from "@/lib/request-dedup"

export interface RepositoryWithUsage {
  id: string
  github_repo_id: string
  name: string
  full_name: string
  is_private: boolean
  is_active: boolean
  has_github_action: boolean
  created_at: Date
  last_optimized: Date | null
  optimizations_limit: number | null
  optimizations_used: number
  organization: string
  membersCount?: number
  avatarUrl?: string
}

export async function getAllRepositories(
  payload: GetRepositoriesForAccountPayload,
): Promise<RepositoryWithUsage[]> {
  const key =
    "orgId" in payload ? `allRepos:org:${payload.orgId}` : `allRepos:user:${payload.userId}`

  return dedup(key, async () => {
    try {
      const { repos, repoIds } = await getRepositoriesForAccountCached(payload)

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

      const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)

      const activeRepoIds = await prisma.optimization_events.groupBy({
        by: ["repository_id"],
        where: {
          repository_id: { in: repoIds },
          created_at: { gte: thirtyDaysAgo },
        },
      })

      const activeRepoSet = new Set(activeRepoIds.map(r => r.repository_id))

      const result = repos.map(repo => ({
        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: activeRepoSet.has(repo.id),
        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: repo.full_name.split("/")[0],
        avatarUrl: `https://github.com/${repo.full_name.split("/")[0]}.png`,
      }))

      return result
    } catch (error) {
      console.error("Failed to fetch repositories:", error)
      throw new Error(`Database error: ${error instanceof Error ? error.message : "Unknown error"}`)
    }
  })
}

function buildOptimizationWhereClause(
  payload: AccountPayload,
  repoIds: string[],
  year?: number,
): string {
  const repoIdsString = repoIds.map(id => `'${id}'`).join(",")
  const yearCondition = year ? `AND EXTRACT(YEAR FROM created_at) = ${year}` : ""

  if ("orgId" in payload) {
    return `repository_id IN (${repoIdsString}) ${yearCondition}`
  } else {
    const userId = payload.userId.replace(/'/g, "''")
    const username = payload.username.replace(/'/g, "''")

    return `(
      repository_id IN (${repoIdsString})
      OR user_id = '${userId}'
      OR current_username = '${username}'
    ) ${yearCondition}`
  }
}

export async function statistics(payload: AccountPayload, year: number) {
  try {
    const { repoIds } = await getRepositoriesForAccountCached(payload)

    if (repoIds.length === 0) {
      return {
        optimizations: { total: 0, successful: 0, timeSeries: [], successfulTimeSeries: [] },
        activeUsersLast30Days: [],
        pullRequests: [],
        activeReposLast30Days: [],
      }
    }

    const since = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
    const whereClause = buildOptimizationWhereClause(payload, repoIds, year)

    const sinceFormatted = since.toISOString()
    const result = await prisma.$queryRawUnsafe<
      Array<{
        total_attempts: bigint
        successful_attempts: bigint
        daily_time_series: string
        active_users: string
        active_repos: string
        pr_stats: string
      }>
    >(
      `
      WITH 
      -- Step 1: Filter and prepare base data with dynamic WHERE
      base_events AS (
        SELECT 
          created_at,
          is_optimization_found,
          current_username,
          repository_id,
          event_type,
          DATE(created_at) as event_date,
          created_at >= '${sinceFormatted}'::timestamp as is_recent,
          EXTRACT(YEAR FROM created_at)::int = ${year} as is_target_year,
          EXTRACT(MONTH FROM created_at)::int as event_month
        FROM optimization_events
        WHERE ${whereClause}
      ),
      
      -- Step 2: Calculate total aggregates
      totals AS (
        SELECT 
          COUNT(*)::bigint as total_attempts,
          SUM(CASE WHEN is_optimization_found THEN 1 ELSE 0 END)::bigint as successful_attempts
        FROM base_events
      ),
      
      -- Step 3: Daily time series with cumulative counts (WINDOW FUNCTIONS!)
      daily_series AS (
        SELECT 
          event_date,
          COUNT(*) as daily_all,
          SUM(CASE WHEN is_optimization_found THEN 1 ELSE 0 END) as daily_success,
          SUM(COUNT(*)) OVER (
            ORDER BY event_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          ) as cumulative_all,
          SUM(SUM(CASE WHEN is_optimization_found THEN 1 ELSE 0 END)) OVER (
            ORDER BY event_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          ) as cumulative_success
        FROM base_events
        GROUP BY event_date
        ORDER BY event_date
      ),
      
      -- Step 4: Active users (last 30 days)
      active_users_agg AS (
        SELECT 
          current_username,
          COUNT(*)::bigint as event_count
        FROM base_events
        WHERE is_recent = true 
          AND current_username IS NOT NULL
        GROUP BY current_username
        ORDER BY event_count DESC
        LIMIT 100
      ),
      
      -- Step 5: Active repos (last 30 days)
      active_repos_agg AS (
        SELECT DISTINCT repository_id
        FROM base_events
        WHERE is_recent = true 
          AND repository_id IS NOT NULL
      ),
      
      -- Step 6: PR stats by month
      pr_stats_agg AS (
        SELECT 
          event_month as month,
          SUM(CASE WHEN event_type = 'pr_created' THEN 1 ELSE 0 END)::int as pr_created,
          SUM(CASE WHEN event_type = 'pr_merged' THEN 1 ELSE 0 END)::int as pr_merged,
          SUM(CASE WHEN event_type = 'pr_closed' THEN 1 ELSE 0 END)::int as pr_closed
        FROM base_events
        WHERE is_target_year = true
          AND event_type IN ('pr_created', 'pr_merged', 'pr_closed')
        GROUP BY event_month
      ),
      
      -- Step 7: Aggregate time series into JSON
      time_series_json AS (
        SELECT 
          COALESCE(
            json_agg(
              json_build_object(
                'date', event_date::text,
                'all_count', cumulative_all,
                'success_count', cumulative_success
              ) ORDER BY event_date
            ),
            '[]'::json
          ) as daily_time_series
        FROM daily_series
      ),
      
      -- Step 8: Aggregate active users into JSON
      users_json AS (
        SELECT 
          COALESCE(
            json_agg(
              json_build_object(
                'username', current_username,
                'event_count', event_count
              ) ORDER BY event_count DESC
            ),
            '[]'::json
          ) as active_users
        FROM active_users_agg
      ),
      
      -- Step 9: Aggregate active repos into JSON
      repos_json AS (
        SELECT 
          COALESCE(
            json_agg(repository_id::text),
            '[]'::json
          ) as active_repos
        FROM active_repos_agg
      ),
      
      -- Step 10: Aggregate PR stats into JSON
      pr_json AS (
        SELECT 
          COALESCE(
            json_agg(
              json_build_object(
                'month', month,
                'pr_created', pr_created,
                'pr_merged', pr_merged,
                'pr_closed', pr_closed
              ) ORDER BY month
            ),
            '[]'::json
          ) as pr_stats
        FROM pr_stats_agg
      )
      
      -- Final: Combine everything into single row
      SELECT 
        COALESCE(t.total_attempts, 0) as total_attempts,
        COALESCE(t.successful_attempts, 0) as successful_attempts,
        ts.daily_time_series::text as daily_time_series,
        u.active_users::text as active_users,
        r.active_repos::text as active_repos,
        p.pr_stats::text as pr_stats
      FROM totals t
      CROSS JOIN time_series_json ts
      CROSS JOIN users_json u
      CROSS JOIN repos_json r
      CROSS JOIN pr_json p
      `,
    )

    const data = result[0]

    if (!data) {
      return {
        optimizations: { total: 0, successful: 0, timeSeries: [], successfulTimeSeries: [] },
        activeUsersLast30Days: [],
        pullRequests: [],
        activeReposLast30Days: [],
      }
    }

    const dailyData = JSON.parse(data.daily_time_series || "[]") as Array<{
      date: string
      all_count: number
      success_count: number
    }>

    const activeUsersRaw = JSON.parse(data.active_users || "[]") as Array<{
      username: string
      event_count: number
    }>

    const activeReposRaw = JSON.parse(data.active_repos || "[]") as string[]

    const prStatsRaw = JSON.parse(data.pr_stats || "[]") as Array<{
      month: number
      pr_created: number
      pr_merged: number
      pr_closed: number
    }>

    const optimizationTimeSeries = dailyData.map(d => ({
      date: d.date,
      count: d.all_count,
    }))

    const successfulTimeSeries = dailyData
      .filter(d => d.success_count > 0)
      .map(d => ({
        date: d.date,
        count: d.success_count,
      }))

    const activeUsersLast30Days = activeUsersRaw.map(u => ({
      username: u.username,
      eventCount: u.event_count,
      avatarUrl: `https://github.com/${u.username}.png`,
    }))

    const prStatsMap = new Map(prStatsRaw.map(p => [p.month, p]))

    const pullRequests = Array.from({ length: 12 }, (_, i) => {
      const month = i + 1
      const stats = prStatsMap.get(month)
      return {
        month: `${year}-${month.toString().padStart(2, "0")}`,
        pr_created: stats?.pr_created || 0,
        pr_merged: stats?.pr_merged || 0,
        pr_closed: stats?.pr_closed || 0,
      }
    })
    return {
      optimizations: {
        total: Number(data.total_attempts),
        successful: Number(data.successful_attempts),
        timeSeries: optimizationTimeSeries,
        successfulTimeSeries,
      },
      activeUsersLast30Days,
      activeReposLast30Days: activeReposRaw,
      pullRequests,
    }
  } catch (error) {
    console.error("Failed generating statistics:", error)
    throw new Error(
      `Failed generating statistics: ${error instanceof Error ? error.message : "Unknown error"}`,
    )
  }
}

export interface OptimizationPREvent {
  id: string
  event_type: string
  pr_url: string | null
  function_name: string | null
  file_path: string | null
  speedup_x: number | null
  speedup_pct: number | null
  created_at: Date
  repository: {
    id: string
    name: string
    full_name: string
  } | null
}

export interface OptimizationPRsResponse {
  events: OptimizationPREvent[]
  totalCount: number
  page: number
  pageSize: number
  totalPages: number
}

export async function getOptimizationPRs(
  payload: AccountPayload,
  page: number = 1,
  pageSize: number = 10,
  eventTypeFilter?: string,
  repositoryId?: string,
): Promise<OptimizationPRsResponse> {
  try {
    const { repoIds } = await getRepositoriesForAccountCached(payload)

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

    // Build WHERE conditions with parameterized queries
    const repoIdsString = repoIds.map(id => `'${id.replace(/'/g, "''")}'`).join(",")

    let accountCondition: string
    if ("orgId" in payload) {
      accountCondition = `oe.repository_id IN (${repoIdsString})`
    } else {
      const userId = payload.userId.replace(/'/g, "''")
      const username = payload.username.replace(/'/g, "''")
      accountCondition = `(
        oe.repository_id IN (${repoIdsString})
        OR oe.user_id = '${userId}'
        OR oe.current_username = '${username}'
      )`
    }

    const eventTypeCondition =
      eventTypeFilter && eventTypeFilter !== "all"
        ? `AND oe.event_type = '${String(eventTypeFilter).replace(/'/g, "''")}'`
        : `AND oe.event_type IN ('pr_created','pr_merged','pr_closed')`

    const repositoryCondition = repositoryId
      ? `AND oe.repository_id = '${String(repositoryId).replace(/'/g, "''")}'`
      : ""

    const whereClause = `
      ${accountCondition}
      ${eventTypeCondition}
      ${repositoryCondition}
      AND oe.is_optimization_found = true
      AND (
        oe.pr_url IS NOT NULL
        OR of.pull_request IS NOT NULL
      )
    `

    const offset = (page - 1) * pageSize

    // Optimized query - simplified COALESCE chains and extracted common speedup parsing logic
    const [events, countRows] = await Promise.all([
      prisma.$queryRawUnsafe<
        Array<{
          id: string
          event_type: string
          pr_url: string | null
          function_name: string | null
          file_path: string | null
          speedup_x: number | null
          speedup_pct: number | null
          created_at: Date
          repository_id: string | null
          repo_name: string | null
          repo_full_name: string | null
        }>
      >(
        `
        SELECT
          oe.id,
          oe.event_type,
          COALESCE(
            oe.pr_url,
            of.pull_request ->> 'new_pr_url',
            of.pull_request ->> 'dependent_pr_url',
            of.pull_request ->> 'review_suggestion_pr_url'
          ) AS pr_url,
          COALESCE(
            oe.function_name,
            of.experiment_metadata -> 'prCommentFields' ->> 'function_name',
            oe.metadata -> 'prCommentFields' ->> 'function_name'
          ) AS function_name,
          COALESCE(
            oe.file_path,
            of.experiment_metadata -> 'prCommentFields' ->> 'file_path',
            oe.metadata -> 'prCommentFields' ->> 'file_path'
          ) AS file_path,
          COALESCE(
            oe.speedup_x,
            NULLIF(
              regexp_replace(
                COALESCE(
                  of.experiment_metadata -> 'prCommentFields' ->> 'speedup_x',
                  oe.metadata -> 'prCommentFields' ->> 'speedup_x',
                  ''
                )::text,
                '[^0-9\\.\\-]',
                '',
                'g'
              ),
              ''
            )::double precision
          ) AS speedup_x,
          COALESCE(
            oe.speedup_pct,
            NULLIF(
              regexp_replace(
                COALESCE(
                  of.experiment_metadata -> 'prCommentFields' ->> 'speedup_pct',
                  oe.metadata -> 'prCommentFields' ->> 'speedup_pct',
                  ''
                )::text,
                '[^0-9\\.\\-]',
                '',
                'g'
              ),
              ''
            )::double precision
          ) AS speedup_pct,
          oe.created_at,
          oe.repository_id,
          r.name AS repo_name,
          r.full_name AS repo_full_name
        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 oe.created_at DESC
        LIMIT ${pageSize} OFFSET ${offset}
        `,
      ),
      prisma.$queryRawUnsafe<Array<{ count: bigint }>>(
        `
        SELECT COUNT(*)::bigint AS count
        FROM optimization_events oe
        LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
        WHERE ${whereClause}
        `,
      ),
    ])

    const totalCount = Number(countRows?.[0]?.count ?? 0)
    const totalPages = Math.ceil(totalCount / pageSize)

    return {
      events: events.map(e => ({
        id: e.id,
        event_type: e.event_type,
        pr_url: e.pr_url,
        function_name: e.function_name,
        file_path: e.file_path,
        speedup_x: e.speedup_x,
        speedup_pct: e.speedup_pct,
        created_at: e.created_at,
        repository:
          e.repository_id && e.repo_full_name && e.repo_name
            ? { id: e.repository_id, name: e.repo_name, full_name: e.repo_full_name }
            : null,
      })),
      totalCount,
      page,
      pageSize,
      totalPages,
    }
  } catch (error) {
    console.error("Failed fetching optimization PRs:", error)
    throw new Error(
      `Failed fetching optimization PRs: ${error instanceof Error ? error.message : "Unknown error"}`,
    )
  }
}

export async function getDashboardData(payload: AccountPayload, year: number) {
  const key =
    "orgId" in payload
      ? `dashboard:org:${payload.orgId}:${year}`
      : `dashboard:user:${payload.userId}:${year}`
  return dedup(key, async () => {
    const [stats, repos] = await Promise.all([
      statistics(payload, year),
      getAllRepositories(payload),
    ])
    return { stats, repos }
  })
}

export async function getSubscriptionData(userId: string) {
  return dedup(`subscription:${userId}`, async () => {
    try {
      const subscription = await checkAndResetSubscriptionPeriod(userId)
      if (!subscription) {
        return null
      }
      return {
        optimizations_used: subscription.optimizations_used || 0,
        optimizations_limit: subscription.optimizations_limit || 0,
        current_period_end: subscription.current_period_end,
        plan_type: subscription.plan_type || "free",
      }
    } catch (error) {
      console.error("Failed to fetch subscription data:", error)
      return null
    }
  })
}

export async function getCurrentUserSubscriptionData() {
  return dedup("subscription:current", async () => {
    try {
      const { getUserIdAndUsername } = await import("@/app/utils/auth")
      const currentUser = await getUserIdAndUsername()
      if (!currentUser?.userId) {
        return null
      }
      return await getSubscriptionData(currentUser.userId)
    } catch (error) {
      console.error("Failed to fetch current user subscription data:", error)
      return null
    }
  })
}
