"use server"

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

const VALID_EVENT_TYPES = new Set(["pr_created", "pr_merged", "pr_closed", "no-pr", "all"])

/** Validate an event_type filter value against the allowlist. */
function validateEventType(value: string): string {
  if (!VALID_EVENT_TYPES.has(value)) {
    throw new Error(`Invalid event type: ${value}`)
  }
  return value
}

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: { repository_id: string | null }) => r.repository_id),
      )

      const result = repos.map(repo => {
        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: 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,
          avatarUrl: `https://github.com/${organization}.png`,
        }
      })

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

/**
 * Build the base_events CTE for the statistics query.
 *
 * For org accounts this is a simple `WHERE repository_id IN (...)`.
 * For personal accounts the OR across repository_id / user_id /
 * current_username forces PostgreSQL into a slow bitmap OR merge when
 * there are 100+ repo UUIDs.  Instead we emit three UNION branches so
 * each hits its own composite index independently.
 *
 * Returns a `Prisma.Sql` fragment for safe composition via tagged templates.
 */
function buildBaseEventsCte(payload: AccountPayload, repoIds: string[], year?: number): Prisma.Sql {
  const safeYear = year != null ? Math.trunc(year) : undefined
  const yearCondition = safeYear
    ? Prisma.sql`AND EXTRACT(YEAR FROM created_at) = ${safeYear}`
    : Prisma.empty

  const repoInClause = Prisma.sql`repository_id IN (${Prisma.join(repoIds)})`

  if ("orgId" in payload) {
    return Prisma.sql`base_events AS (
        SELECT
          created_at,
          is_optimization_found,
          current_username,
          repository_id,
          event_type
        FROM optimization_events
        WHERE ${repoInClause} ${yearCondition}
      )`
  }

  // Personal account: UNION three index-backed scans, then deduplicate.
  // Each branch can seek on its leading index column.
  const { userId, username } = payload

  return Prisma.sql`base_events AS (
      SELECT
        created_at,
        is_optimization_found,
        current_username,
        repository_id,
        event_type
      FROM optimization_events
      WHERE ${repoInClause} ${yearCondition}
      UNION
      SELECT
        created_at,
        is_optimization_found,
        current_username,
        repository_id,
        event_type
      FROM optimization_events
      WHERE user_id = ${userId} ${yearCondition}
      UNION
      SELECT
        created_at,
        is_optimization_found,
        current_username,
        repository_id,
        event_type
      FROM optimization_events
      WHERE current_username = ${username} ${yearCondition}
    )`
}

export async function statistics(payload: AccountPayload, year: number) {
  try {
    const safeYear = Math.trunc(year) // ensure integer for SQL interpolation
    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 baseEventsCte = buildBaseEventsCte(payload, repoIds, safeYear)

    const result = await prisma.$queryRaw<
      Array<{
        total_attempts: bigint
        successful_attempts: bigint
        daily_time_series: string
        active_users: string
        active_repos: string
        pr_stats: string
      }>
    >`
      WITH
      -- Step 1: Collect matching rows (UNION for personal accounts)
      ${baseEventsCte},

      -- Step 1b: Derive date-based columns from the base rows
      prepared_events AS (
        SELECT
          created_at,
          is_optimization_found,
          current_username,
          repository_id,
          event_type,
          DATE(created_at) as event_date,
          created_at >= ${since}::timestamp as is_recent,
          EXTRACT(YEAR FROM created_at)::int = ${safeYear} as is_target_year,
          EXTRACT(MONTH FROM created_at)::int as event_month
        FROM base_events
      ),

      -- 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 prepared_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 prepared_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 prepared_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 prepared_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 prepared_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 parameterized SQL fragments
    const repoInClause = Prisma.sql`oe.repository_id IN (${Prisma.join(repoIds)})`

    const repositoryCondition = repositoryId
      ? Prisma.sql`AND oe.repository_id = ${repositoryId}`
      : Prisma.empty

    const eventTypeCondition =
      eventTypeFilter && eventTypeFilter !== "all"
        ? Prisma.sql`AND oe.event_type = ${validateEventType(eventTypeFilter)}`
        : Prisma.sql`AND oe.event_type IN ('pr_created','pr_merged','pr_closed')`

    const safePageSize = Math.trunc(pageSize)
    const offset = Math.trunc((page - 1) * safePageSize)

    // Shared select fields fragment for the data query
    const selectFields = Prisma.sql`
          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`

    // Build count and data queries — for personal accounts, rewrite 3-way OR
    // as UNION so each branch uses its optimal composite index independently
    // instead of a slow bitmap OR merge across 100+ repo UUIDs.
    let countQuery: Prisma.Sql
    let dataQuery: Prisma.Sql

    if ("orgId" in payload) {
      // Org: LEFT JOIN with optimization_features to avoid EXISTS subquery evaluation per row
      countQuery = Prisma.sql`
        SELECT COUNT(*)::bigint AS count
        FROM optimization_events oe
        LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
        WHERE ${repoInClause}
          ${eventTypeCondition}
          ${repositoryCondition}
          AND oe.is_optimization_found = true
          AND (oe.pr_url IS NOT NULL OR of.pull_request IS NOT NULL)
      `

      // Two-phase: first identify the page of event IDs using LEFT JOIN instead
      // of EXISTS to avoid row-by-row subquery evaluation, then JOIN for display data.
      dataQuery = Prisma.sql`
        WITH page_ids AS (
          SELECT oe.id
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE ${repoInClause}
            ${eventTypeCondition}
            ${repositoryCondition}
            AND oe.is_optimization_found = true
            AND (oe.pr_url IS NOT NULL OR of.pull_request IS NOT NULL)
          ORDER BY oe.created_at DESC
          LIMIT ${safePageSize} OFFSET ${offset}
        )
        SELECT ${selectFields}
        FROM optimization_events oe
        INNER JOIN page_ids pi ON pi.id = oe.id
        LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
        LEFT JOIN repositories r ON oe.repository_id = r.id
        ORDER BY oe.created_at DESC
      `
    } else {
      // Personal: UNION for index-backed scans
      const { userId, username } = payload
      const repoInClauseNoAlias = Prisma.sql`oe.repository_id IN (${Prisma.join(repoIds)})`
      const repoFilterNoAlias = repositoryId
        ? Prisma.sql`AND repository_id = ${repositoryId}`
        : Prisma.empty
      const eventFilterNoAlias =
        eventTypeFilter && eventTypeFilter !== "all"
          ? Prisma.sql`event_type = ${validateEventType(eventTypeFilter)}`
          : Prisma.sql`event_type IN ('pr_created','pr_merged','pr_closed')`
      const branchFilters = Prisma.sql`AND ${eventFilterNoAlias} AND is_optimization_found = true ${repoFilterNoAlias}`

      countQuery = Prisma.sql`
        WITH candidate_events AS (
          SELECT oe.id, oe.trace_id, oe.pr_url,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE ${repoInClauseNoAlias} ${branchFilters}
          UNION
          SELECT oe.id, oe.trace_id, oe.pr_url,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE oe.user_id = ${userId} ${branchFilters}
          UNION
          SELECT oe.id, oe.trace_id, oe.pr_url,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE oe.current_username = ${username} ${branchFilters}
        )
        SELECT COUNT(*)::bigint AS count
        FROM candidate_events ce
        WHERE ce.pr_url IS NOT NULL OR ce.has_pr_in_features
      `

      // Personal: two-phase CTE approach to avoid joining large tables
      // before sorting and limiting.
      //
      // Phase 1 (candidates): UNION for index-backed scans, carrying
      //   id + created_at + pr_url + trace_id for filtering and sorting.
      // Phase 2 (page_ids): Filter for PR presence (pr_url OR optimization_features),
      //   sort by created_at DESC, and LIMIT — so the expensive JOINs only
      //   happen for the final page of results.
      dataQuery = Prisma.sql`
        WITH candidates AS (
          SELECT oe.id, oe.created_at, oe.pr_url, oe.trace_id,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE ${repoInClauseNoAlias} ${branchFilters}
          UNION
          SELECT oe.id, oe.created_at, oe.pr_url, oe.trace_id,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE oe.user_id = ${userId} ${branchFilters}
          UNION
          SELECT oe.id, oe.created_at, oe.pr_url, oe.trace_id,
                 of.pull_request IS NOT NULL AS has_pr_in_features
          FROM optimization_events oe
          LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
          WHERE oe.current_username = ${username} ${branchFilters}
        ),
        page_ids AS (
          SELECT id
          FROM candidates c
          WHERE c.pr_url IS NOT NULL OR c.has_pr_in_features
          ORDER BY c.created_at DESC
          LIMIT ${safePageSize} OFFSET ${offset}
        )
        SELECT ${selectFields}
        FROM optimization_events oe
        INNER JOIN page_ids pi ON pi.id = oe.id
        LEFT JOIN optimization_features of ON oe.trace_id = of.trace_id
        LEFT JOIN repositories r ON oe.repository_id = r.id
        ORDER BY oe.created_at DESC
      `
    }

    // Run data + count queries in parallel.
    // Both use UNION (personal) or flat WHERE (org) to avoid bitmap OR.
    const [events, countRows] = await Promise.all([
      prisma.$queryRaw<
        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
        }>
      >(dataQuery),
      prisma.$queryRaw<Array<{ count: bigint }>>(countQuery),
    ])

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

    return {
      events: (
        events as 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
        }>
      ).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
    }
  })
}
