Referral program

export const referralCodes = pgTable('referral_codes', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  code: varchar('code', { length: 10 }).unique().notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

export const referrals = pgTable('referrals', {
  id: uuid('id').primaryKey().defaultRandom(),
  referrerId: uuid('referrer_id').references(() => users.id),
  refereeId: uuid('referee_id').references(() => users.id),
  code: varchar('code', { length: 10 }),
  status: varchar('status', { length: 20 }).default('pending'),
  // pending → signed_up → first_purchase → first_sale
  signedUpAt: timestamp('signed_up_at'),
  firstPurchaseAt: timestamp('first_purchase_at'),
  firstSaleAt: timestamp('first_sale_at'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const credits = pgTable('credits', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  amount: integer('amount').notNull(),
  type: varchar('type', { length: 20 }), // referral, bonus, refund
  referralId: uuid('referral_id').references(() => referrals.id),
  expiresAt: timestamp('expires_at'),
  usedAt: timestamp('used_at'),
  createdAt: timestamp('created_at').defaultNow(),
});

async function getOrCreateReferralCode(userId: string): Promise<string> {
  const existing = await db.query.referralCodes.findFirst({
    where: eq(referralCodes.userId, userId),
  });

  if (existing) return existing.code;

  const code = generateCode(6); // e.g., "CUTE7X"
  await db.insert(referralCodes).values({ userId, code });
  return code;
}

// Apply referral on signup
async function applyReferral(refereeId: string, code: string) {
  const referralCode = await db.query.referralCodes.findFirst({
    where: eq(referralCodes.code, code.toUpperCase()),
  });

  if (!referralCode) throw new Error('Invalid referral code');
  if (referralCode.userId === refereeId) throw new Error('Cannot refer yourself');

  // Create referral record
  const referral = await db.insert(referrals).values({
    referrerId: referralCode.userId,
    refereeId,
    code,
    status: 'signed_up',
    signedUpAt: new Date(),
  }).returning();

  // Award signup credits
  await awardCredits(referralCode.userId, 1000, 'referral', referral[0].id);
  await awardCredits(refereeId, 1000, 'referral', referral[0].id);
}

async function awardCredits(
  userId: string,
  amount: number,
  type: string,
  referralId?: string
) {
  await db.insert(credits).values({
    userId,
    amount,
    type,
    referralId,
    expiresAt: addMonths(new Date(), 6), // 6 month expiry
  });
}

// Get user's available credits
async function getAvailableCredits(userId: string): Promise<number> {
  const result = await db
    .select({ total: sum(credits.amount) })
    .from(credits)
    .where(and(
      eq(credits.userId, userId),
      isNull(credits.usedAt),
      gt(credits.expiresAt, new Date()),
    ));

  return result[0]?.total ?? 0;
}

// Apply credits at checkout
async function applyCreditsToOrder(userId: string, orderId: string, amount: number) {
  const available = await getAvailableCredits(userId);
  if (available < amount) throw new Error('Insufficient credits');

  // Mark credits as used (FIFO)
  const creditsToUse = await db.query.credits.findMany({
    where: and(
      eq(credits.userId, userId),
      isNull(credits.usedAt),
    ),
    orderBy: asc(credits.createdAt),
  });

  let remaining = amount;
  for (const credit of creditsToUse) {
    if (remaining <= 0) break;
    const useAmount = Math.min(credit.amount, remaining);
    await db.update(credits)
      .set({ usedAt: new Date() })
      .where(eq(credits.id, credit.id));
    remaining -= useAmount;
  }
}