// D1 schema for campaign management
export const campaigns = sqliteTable("campaigns", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  type: text("type", {
    enum: ["holiday", "seasonal", "launch", "retention", "reactivation"],
  }).notNull(),
  discountPercent: real("discount_percent").notNull(),
  discountType: text("discount_type", {
    enum: ["percentage", "fixed", "free_month", "tiered"],
  }).notNull(),
  appliesTo: text("applies_to", {
    enum: ["annual", "monthly", "all_plans", "specific_plans"],
  }).notNull(),
  specificPlans: text("specific_plans"), // JSON array of plan IDs
  startDate: integer("start_date", { mode: "timestamp" }).notNull(),
  endDate: integer("end_date", { mode: "timestamp" }).notNull(),
  maxRedemptions: integer("max_redemptions"),
  currentRedemptions: integer("current_redemptions").default(0),
  isActive: integer("is_active", { mode: "boolean" }).default(true),
  couponCode: text("coupon_code"),
  metadata: text("metadata"), // JSON for campaign assets
  createdAt: integer("created_at", { mode: "timestamp" }),
})

// server/campaigns.ts
export const createCampaign = createServerFn({ method: "POST" }).handler(
  async ({ data, context }: { data: CreateCampaignInput }) => {
    const id = crypto.randomUUID()

    // Create Stripe coupon
    const coupon = await stripe.coupons.create({
      percent_off: data.discountPercent,
      duration: "once",
      max_redemptions: data.maxRedemptions,
      applies_to: {
        products: data.specificPlans.length > 0
          ? data.specificPlans
          : undefined,
      },
    })

    await context.env.DB.prepare(`
      INSERT INTO campaigns
        (id, name, slug, type, discount_percent, discount_type,
         applies_to, start_date, end_date, max_redemptions, coupon_code)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).bind(
      id, data.name, data.slug, data.type,
      data.discountPercent, data.discountType,
      data.appliesTo, data.startDate.getTime(),
      data.endDate.getTime(), data.maxRedemptions,
      coupon.id
    ).run()

    return { id, couponId: coupon.id }
  }
)

// Prepare campaign assets and notifications
export const prepareCampaign = createServerFn({ method: "GET" }).handler(
  async ({}, { context }) => {
    const upcoming = await context.env.DB.prepare(`
      SELECT * FROM campaigns
      WHERE start_date > ? AND start_date < ?
      AND is_active = 1
    `).bind(Date.now(), Date.now() + 14 * 24 * 60 * 60 * 1000).all()

    for (const campaign of upcoming.results) {
      // Send announcement emails to subscribers
      await sendCampaignAnnouncement(campaign)

      // Update pricing page to show upcoming sale
      // Update in KV for zero-downtime
      await context.env.KV.put("campaign:upcoming", JSON.stringify({
        name: campaign.name,
        discountPercent: campaign.discount_percent,
        startDate: campaign.start_date,
        slug: campaign.slug,
      }))
    }

    return { prepared: true, count: upcoming.results.length }
  }
)

// Apply discount during checkout
export const applyCampaignDiscount = createServerFn({ method: "POST" }).handler(
  async ({ data, context }: {
    data: { campaignSlug: string; priceId: string }
  }) => {
    const campaign = await context.env.DB.prepare(`
      SELECT * FROM campaigns
      WHERE slug = ? AND is_active = 1
      AND start_date <= ? AND end_date >= ?
    `).bind(data.campaignSlug, Date.now(), Date.now()).first()

    if (!campaign) {
      return { valid: false, error: "Campaign not active" }
    }

    // Check redemption limit
    if (campaign.max_redemptions && campaign.current_redemptions >= campaign.max_redemptions) {
      return { valid: false, error: "Campaign fully redeemed" }
    }

    // Increment counter
    await context.env.DB.prepare(`
      UPDATE campaigns SET current_redemptions = current_redemptions + 1
      WHERE id = ?
    `).bind(campaign.id).run()

    return {
      valid: true,
      couponCode: campaign.coupon_code,
      discountPercent: campaign.discount_percent,
    }
  }
)

Percentage off annual plan:
  Normal: $29/mo × 12 = $348/year
  With 30% off: $243.60/year (saves $104.40)
  ✅ User commits for 12 months
  ✅ You get cash upfront
  ✅ Lower churn risk

Free months on monthly plan:
  Normal: $29/mo × 12 = $348/year
  With 2 months free: $29/mo × 10 = $290/year (saves $58)
  ❌ User can cancel any time
  ❌ Revenue is deferred
  ❌ Higher churn risk

Winner: Percentage off annual plan

// Track campaign performance
export const getCampaignAnalytics = createServerFn({ method: "GET" }).handler(
  async ({ data, context }: { data: { campaignId: string } }) => {
    const [campaign, conversions, revenue] = await Promise.all([
      context.env.DB.prepare("SELECT * FROM campaigns WHERE id = ?")
        .bind(data.campaignId).first(),
      context.env.DB.prepare(`
        SELECT COUNT(*) as conversions, SUM(mrr) as total_mrr
        FROM subscriptions
        WHERE coupon_id = (SELECT coupon_code FROM campaigns WHERE id = ?)
        AND created_at BETWEEN
          (SELECT start_date FROM campaigns WHERE id = ?) AND
          (SELECT end_date FROM campaigns WHERE id = ?)
      `).bind(data.campaignId, data.campaignId, data.campaignId).first(),
      context.env.DB.prepare(`
        SELECT SUM(amount) as total_revenue
        FROM invoices
        WHERE subscription_id IN (
          SELECT id FROM subscriptions
          WHERE coupon_id = (SELECT coupon_code FROM campaigns WHERE id = ?)
        )
      `).bind(data.campaignId).first(),
    ])

    return { campaign, conversions, revenue }
  }
)

Q1 (Jan-Mar): New Year Annual Plan Push
  - "New Year, New Stack" — 25% off annual
  - Target: Free users on monthly, upgrade to annual

Q2 (Apr-Jun): Spring Refresh
  - "Spring Cleaning" — 20% off annual
  - Target: Inactive users reactivation

Q3 (Jul-Sep): Back to Business
  - "Back to School for Your Business" — 20% off team plans
  - Target: Team/org plan upgrades

Q4 (Oct-Dec): Black Friday + Year-End
  - Pre-BF: "Early Black Friday" — 30% off annual (limited quantity)
  - BF/CM: "Black Friday Deal" — 35% off annual
  - Year-End: "Last Chance 2026" — 25% off annual
  - Target: All segments, maximum conversion