import { getDb } from "../db.ts";
import { referrals, users } from "../../drizzle/schema.ts";
import { eq } from "drizzle-orm";

export interface ReferralAnalytics {
  totalReferrals: number;
  completedReferrals: number;
  pendingReferrals: number;
  conversionRate: number;
  totalEarnings: number;
  averageEarningsPerReferrer: number;
  topReferrers: Array<{
    userId: number;
    completedReferrals: number;
    earnings: number;
  }>;
  viralCoefficient: number;
  costPerAcquisition: number;
}

export interface ReferralFunnel {
  totalReferralCodes: number;
  codesUsed: number;
  signupsFromReferrals: number;
  completedReferrals: number;
  conversionRate: number;
}

export async function getReferralAnalytics(): Promise<ReferralAnalytics> {
  const db = await getDb();
  if (!db) {
    return {
      totalReferrals: 0,
      completedReferrals: 0,
      pendingReferrals: 0,
      conversionRate: 0,
      totalEarnings: 0,
      averageEarningsPerReferrer: 0,
      topReferrers: [],
      viralCoefficient: 0,
      costPerAcquisition: 0,
    };
  }

  const allReferrals = await db.select().from(referrals);
  const allUsers = await db.select().from(users);

  const totalReferrals = allReferrals.length;
  const completedReferrals = allReferrals.filter(
    (r) => r.status === "completed"
  ).length;
  const pendingReferrals = allReferrals.filter(
    (r) => r.status === "pending"
  ).length;
  const conversionRate =
    totalReferrals > 0 ? (completedReferrals / totalReferrals) * 100 : 0;
  const totalEarnings = completedReferrals * 1; // 1 SC per referral

  // Calculate referrer stats
  const referrerStats: Record<
    number,
    {
      userId: number;
      completedReferrals: number;
      earnings: number;
    }
  > = {};

  for (const ref of allReferrals) {
    if (!referrerStats[ref.referrerId]) {
      referrerStats[ref.referrerId] = {
        userId: ref.referrerId,
        completedReferrals: 0,
        earnings: 0,
      };
    }

    if (ref.status === "completed") {
      referrerStats[ref.referrerId].completedReferrals++;
      referrerStats[ref.referrerId].earnings += 1;
    }
  }

  const referrerArray = Object.values(referrerStats);
  const averageEarningsPerReferrer =
    referrerArray.length > 0
      ? totalEarnings / referrerArray.length
      : 0;

  const topReferrers = referrerArray
    .sort((a, b) => b.earnings - a.earnings)
    .slice(0, 10);

  // Calculate viral coefficient (new users from referrals / total referrals)
  const uniqueReferredUsers = new Set(
    allReferrals.map((r) => r.referredUserId)
  ).size;
  const viralCoefficient =
    totalReferrals > 0 ? uniqueReferredUsers / totalReferrals : 0;

  // Calculate cost per acquisition (assuming $0.01 per SC = $0.01 per referral)
  const costPerAcquisition = completedReferrals > 0 ? totalEarnings * 0.01 / completedReferrals : 0;

  return {
    totalReferrals,
    completedReferrals,
    pendingReferrals,
    conversionRate: Math.round(conversionRate * 100) / 100,
    totalEarnings,
    averageEarningsPerReferrer: Math.round(averageEarningsPerReferrer * 100) / 100,
    topReferrers,
    viralCoefficient: Math.round(viralCoefficient * 100) / 100,
    costPerAcquisition: Math.round(costPerAcquisition * 100) / 100,
  };
}

export async function getReferralFunnel(): Promise<ReferralFunnel> {
  const db = await getDb();
  if (!db) {
    return {
      totalReferralCodes: 0,
      codesUsed: 0,
      signupsFromReferrals: 0,
      completedReferrals: 0,
      conversionRate: 0,
    };
  }

  const allReferrals = await db.select().from(referrals);
  const allUsers = await db.select().from(users);

  const totalReferralCodes = allUsers.length; // Each user gets a referral code
  const codesUsed = new Set(allReferrals.map((r) => r.referralCode)).size;
  const signupsFromReferrals = new Set(
    allReferrals.map((r) => r.referredUserId)
  ).size;
  const completedReferrals = allReferrals.filter(
    (r) => r.status === "completed"
  ).length;
  const conversionRate =
    signupsFromReferrals > 0
      ? (completedReferrals / signupsFromReferrals) * 100
      : 0;

  return {
    totalReferralCodes,
    codesUsed,
    signupsFromReferrals,
    completedReferrals,
    conversionRate: Math.round(conversionRate * 100) / 100,
  };
}

export async function getReferralTrends(daysBack: number = 30): Promise<
  Array<{
    date: string;
    newReferrals: number;
    completedReferrals: number;
    cumulativeReferrals: number;
  }>
> {
  const db = await getDb();
  if (!db) return [];

  const allReferrals = await db.select().from(referrals);

  // Group by date
  const trendsByDate: Record<
    string,
    {
      newReferrals: number;
      completedReferrals: number;
    }
  > = {};

  for (const ref of allReferrals) {
    const date = new Date(ref.createdAt).toISOString().split("T")[0];
    if (!trendsByDate[date]) {
      trendsByDate[date] = {
        newReferrals: 0,
        completedReferrals: 0,
      };
    }
    trendsByDate[date].newReferrals++;

    if (ref.status === "completed" && ref.completedAt) {
      const completedDate = new Date(ref.completedAt)
        .toISOString()
        .split("T")[0];
      if (!trendsByDate[completedDate]) {
        trendsByDate[completedDate] = {
          newReferrals: 0,
          completedReferrals: 0,
        };
      }
      trendsByDate[completedDate].completedReferrals++;
    }
  }

  // Convert to array and sort by date
  const trends = Object.entries(trendsByDate)
    .map(([date, data]) => ({
      date,
      ...data,
      cumulativeReferrals: 0,
    }))
    .sort((a, b) => new Date(a.date).getTime() - new Date(b.date).getTime());

  // Calculate cumulative
  let cumulative = 0;
  for (const trend of trends) {
    cumulative += trend.completedReferrals;
    trend.cumulativeReferrals = cumulative;
  }

  // Return last N days
  return trends.slice(-daysBack);
}