/**
 * Daily subscription trial-check cron — zync-subscription spec (Task 11).
 *
 * POST /api/cron/subscription-trial-check
 *
 * Guarded by CRON_SECRET (timing-safe comparison). Runs daily.
 *
 * For each row where status='trialing' AND trial_ends_at <= now():
 *   - daysSinceExpiry < 7 (grace period):
 *       If grace_period_started_at is null → set it now, notify tenant
 *       (trial_expiring, daysRemaining = 7 - daysSinceExpiry). Retain business tier.
 *   - daysSinceExpiry >= 7 (grace period over):
 *       Downgrade to freelancer, sync tenants.tier, notify (daysRemaining: 0).
 *
 * Notifications are fired best-effort via createNotification. The tenant OWNER
 * is looked up via SQL to find the membership row with roleId = OWNER role.
 */
import { Hono } from 'hono'
import { sql } from '@zync/db'
import { timingSafeEqual } from '@zync/auth'
import {
  createDb,
  getExpiredTrials,
  downgradeToFreelancerDb,
  setGracePeriodStarted,
  createNotification,
  getActiveTrials,
  stampTrialWarningSent,
} from '@zync/db/queries'
import { syncTierToTenant } from '@zync/payments'
import { sendEmail } from '@zync/notifications'
import { TenantTier } from '@zync/types'
import type { AppEnv } from '../../types'

export const subscriptionTrialCheckRoute = new Hono<AppEnv>()

subscriptionTrialCheckRoute.post('/', async (c) => {
  // Timing-safe secret check
  const secret = c.req.header('x-cron-secret') ?? ''
  const expected = c.env.CRON_SECRET
  if (!expected || expected.length < 16) {
    return c.json({ error: 'Server misconfigured' }, 500)
  }

  if (!timingSafeEqual(secret, expected)) {
    return c.json({ error: 'Unauthorized' }, 401)
  }

  const db = createDb(c.env)
  const now = new Date()
  const activeTrials = await getActiveTrials(db)
  const expiredTrials = await getExpiredTrials(db)

  let milestoneWarningsSent = 0
  let gracePeriodSet = 0
  let downgraded = 0

  for (const sub of activeTrials) {
    if (!sub.trialEndsAt) continue

    const daysUntilEnd = Math.ceil(
      (sub.trialEndsAt.getTime() - now.getTime()) / 86_400_000,
    )

    if (daysUntilEnd !== 3 && daysUntilEnd !== 1) continue
    if (wasSentToday(sub.trialWarningSentAt, now)) continue

    const subject =
      daysUntilEnd === 3
        ? 'Your Zync Business trial ends in 3 days'
        : 'Last day of your Business trial'
    const body =
      daysUntilEnd === 3
        ? 'Add a payment method to keep access to Lead forms & webhooks, Contracts & e-signing, Expense approval, Custom email domain, API keys, and Multi-currency invoicing. Upgrade at /settings/plan.'
        : 'Today is the last day of your Business trial. Add a payment method to keep access to Lead forms & webhooks, Contracts & e-signing, Expense approval, Custom email domain, API keys, and Multi-currency invoicing. Upgrade at /settings/plan.'

    const owner = await getTenantOwnerContact(db, sub.tenantId)
    if (!owner?.userId || !owner.email) continue

    await createNotification(db, {
      tenantId: sub.tenantId,
      userId: owner.userId,
      type: 'trial_expiring',
      titleKey: 'notifications.trial_expiring.title',
      bodyKey: 'notifications.trial_expiring.body',
      params: { days: String(daysUntilEnd) },
    })
    await sendEmail(
      {
        to: owner.email,
        templateKey: 'invoice-sent',
        locale: 'en-US',
        vars: {
          subject,
          title: subject,
          body,
        },
      },
      c.env,
    )
    await stampTrialWarningSent(db, sub.tenantId)
    milestoneWarningsSent++
  }

  for (const sub of expiredTrials) {
    if (!sub.trialEndsAt) continue

    const daysSinceExpiry = Math.floor(
      (now.getTime() - sub.trialEndsAt.getTime()) / 86_400_000,
    )

    if (daysSinceExpiry < 7) {
      // Grace period — tenant retains business tier access
      const updated = await setGracePeriodStarted(db, sub.tenantId)
      if (updated) {
        // First time entering grace: notify
        gracePeriodSet++
        const daysRemaining = 7 - daysSinceExpiry
        await notifyTenantOwner(db, sub.tenantId, 'trial_expiring', { daysRemaining })
      }
    } else {
      // Grace period over — downgrade to freelancer
      await downgradeToFreelancerDb(db, sub.tenantId)
      await syncTierToTenant(db, sub.tenantId, TenantTier.FREELANCER)
      downgraded++
      await notifyTenantOwner(db, sub.tenantId, 'trial_expiring', { daysRemaining: 0 })
    }
  }

  return c.json({
    ok: true,
    milestoneWarningsSent,
    activeTrialsProcessed: activeTrials.length,
    processed: expiredTrials.length,
    gracePeriodSet,
    downgraded,
  })
})

// ---------------------------------------------------------------------------
// Helper: find the tenant OWNER user and create a notification
// ---------------------------------------------------------------------------

async function notifyTenantOwner(
  db: ReturnType<typeof createDb>,
  tenantId: string,
  type: string,
  params: Record<string, unknown>,
): Promise<void> {
  try {
    // Find the OWNER membership for this tenant
    const result = await db.execute(
      sql`SELECT tm.user_id
          FROM tenant_memberships tm
          JOIN roles r ON r.id = tm.role_id
          WHERE tm.tenant_id = ${tenantId}
            AND r.name = 'OWNER'
            AND tm.status = 'active'
          LIMIT 1`,
    )
    const row = (result as unknown as { user_id?: string }[])[0]
    if (!row?.user_id) return

    await createNotification(db, {
      tenantId,
      userId: row.user_id,
      type,
      titleKey: `notifications.${type}.title`,
      bodyKey: `notifications.${type}.body`,
      params,
    })
  } catch (err) {
    // Best-effort — don't fail the cron if notification delivery fails
    console.error(`[subscriptionTrialCheck] notifyTenantOwner error: ${err}`)
  }
}

function wasSentToday(sentAt: Date | null | undefined, now: Date): boolean {
  if (!sentAt) return false
  return sentAt.toISOString().slice(0, 10) === now.toISOString().slice(0, 10)
}

async function getTenantOwnerContact(
  db: ReturnType<typeof createDb>,
  tenantId: string,
): Promise<{ userId: string; email: string | null } | null> {
  const result = await db.execute(
    sql`SELECT tm.user_id, u.email
        FROM tenant_memberships tm
        JOIN roles r ON r.id = tm.role_id
        JOIN users u ON u.id = tm.user_id
        WHERE tm.tenant_id = ${tenantId}
          AND r.name = 'OWNER'
          AND tm.status = 'active'
        LIMIT 1`,
  )
  const row = (result as unknown as Array<{ user_id?: string; email?: string | null }>)[0]
  if (!row?.user_id) return null
  return { userId: row.user_id, email: row.email ?? null }
}