/**
 * Trial-expiry query helpers — trial-expiry-conversion-ui spec.
 *
 * Additive helpers for the two timing-flag columns added to zync_subscriptions:
 *   - trial_warning_sent_at  (milestone email guard)
 *   - trial_expiry_ack_at    (interstitial one-time acknowledgement)
 *
 * Uses the typed Drizzle builder throughout — no raw sql column references.
 */
import { and, eq, isNull, gt } from 'drizzle-orm'
import type { Db } from '../client'
import { zyncSubscriptions } from '../schema/zync-subscriptions'

// ---------------------------------------------------------------------------
// Ack-trial-expiry (interstitial acknowledgement)
// ---------------------------------------------------------------------------

/**
 * Stamp trial_expiry_ack_at = now() for the given tenant's subscription.
 * Idempotent: only updates when trial_expiry_ack_at IS NULL so the first
 * acknowledgement timestamp is preserved.
 *
 * Returns the updated row, or undefined if already acknowledged.
 */
export async function ackTrialExpiry(
  db: Db,
  tenantId: string,
): Promise<{ trialExpiryAckAt: Date | null } | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({ trialExpiryAckAt: new Date() })
    .where(
      and(
        eq(zyncSubscriptions.tenantId, tenantId),
        isNull(zyncSubscriptions.trialExpiryAckAt),
      ),
    )
    .returning({ trialExpiryAckAt: zyncSubscriptions.trialExpiryAckAt })
  return row
}

// ---------------------------------------------------------------------------
// Trial-warning milestone guard
// ---------------------------------------------------------------------------

/**
 * Stamp trial_warning_sent_at = now() for a trialing tenant.
 * Used by the daily cron after sending a T-3 or T-1 milestone notification.
 */
export async function stampTrialWarningSent(
  db: Db,
  tenantId: string,
): Promise<void> {
  await db
    .update(zyncSubscriptions)
    .set({ trialWarningSentAt: new Date() })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
}

// ---------------------------------------------------------------------------
// Query: trialing subscriptions not yet expired (for cron milestone check)
// ---------------------------------------------------------------------------

/**
 * Return subscriptions where status='trialing' AND trial_ends_at > now.
 * The cron uses this to find tenants approaching their trial end to send
 * T-3 / T-1 milestone emails.
 */
export async function getActiveTrials(db: Db) {
  const { sql } = await import('drizzle-orm')
  return db
    .select()
    .from(zyncSubscriptions)
    .where(
      and(
        eq(zyncSubscriptions.status, 'trialing'),
        gt(zyncSubscriptions.trialEndsAt, sql`now()`),
      ),
    )
}