/**
 * Subscription query helpers — zync-subscription spec.
 *
 * All Drizzle interactions with `zync_subscriptions` are isolated here.
 * Route files and adapter implementations import from this module.
 */
import { and, eq, lte, isNull, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { zyncSubscriptions } from '../schema/zync-subscriptions'
import type { ZyncSubscriptionRow, NewZyncSubscription } from '../schema/zync-subscriptions'
import { auditLog } from './_audit-forward'

export type { ZyncSubscriptionRow, NewZyncSubscription }

// ---------------------------------------------------------------------------
// Read helpers
// ---------------------------------------------------------------------------

/**
 * Fetch the subscription record for a tenant. Returns undefined if none exists
 * (tenants created before zync-subscription migration will have no row).
 */
export async function getSubscriptionByTenantId(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .select()
    .from(zyncSubscriptions)
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .limit(1)
  return row
}

/**
 * Return all subscriptions where status='trialing' AND trial_ends_at <= now.
 * Used by the daily cron to find expired/grace-period trials.
 */
export async function getExpiredTrials(db: Db): Promise<ZyncSubscriptionRow[]> {
  return db
    .select()
    .from(zyncSubscriptions)
    .where(
      and(
        eq(zyncSubscriptions.status, 'trialing'),
        lte(zyncSubscriptions.trialEndsAt, sql`now()`),
      ),
    )
}

// ---------------------------------------------------------------------------
// Write helpers
// ---------------------------------------------------------------------------

/**
 * Insert the initial freelancer/active subscription row created at tenant signup.
 */
export async function createFreelancerSubscription(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow> {
  const rows = await db
    .insert(zyncSubscriptions)
    .values({
      tenantId,
      tier: 'freelancer',
      status: 'active',
      period: null,
      adapter: 'null',
    })
    .returning()
  const row = rows[0]
  if (!row) throw new Error('createFreelancerSubscription: insert returned no row')
  return row
}

/**
 * Mark a subscription canceled: sets status='canceled' and canceled_at=now.
 */
export async function cancelSubscription(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({
      status: 'canceled',
      canceledAt: new Date(),
    })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

export async function markSubscriptionCanceled(
  db: Db,
  tenantId: string,
  input: {
    canceledAt?: Date
    cancellationReason?: string | null
    cancellationReasonFreetext?: string | null
  } = {},
): Promise<ZyncSubscriptionRow | undefined> {
  const canceledAt = input.canceledAt ?? new Date()
  const [row] = await db
    .update(zyncSubscriptions)
    .set({
      status: 'canceled',
      canceledAt,
      cancellationReason: input.cancellationReason ?? null,
      cancellationReasonFreetext: input.cancellationReasonFreetext ?? null,
    })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

export async function reactivateCanceledSubscription(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({
      status: 'active',
      canceledAt: null,
    })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

/**
 * Apply a webhook event that activates a subscription: sets status='active',
 * clears grace_period_started_at, and optionally fills adapter IDs + period.
 */
export async function activateSubscription(
  db: Db,
  tenantId: string,
  updates: {
    adapterSubscriptionId?: string
    adapterCustomerId?: string
    currentPeriodStart?: Date
    currentPeriodEnd?: Date
    tier?: string
  } = {},
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({
      status: 'active',
      gracePeriodStartedAt: null,
      ...updates,
    })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

/**
 * Admin upsert: creates or updates a subscription with adapter='manual'.
 * Used by the admin override endpoint (Enterprise / White Label provisioning).
 */
export async function upsertAdminSubscription(
  db: Db,
  tenantId: string,
  data: {
    tier: string
    status: string
  },
): Promise<ZyncSubscriptionRow> {
  const rows = await db
    .insert(zyncSubscriptions)
    .values({
      tenantId,
      tier: data.tier,
      status: data.status,
      adapter: 'manual',
      adapterSubscriptionId: null,
    })
    .onConflictDoUpdate({
      target: zyncSubscriptions.tenantId,
      set: {
        tier: data.tier,
        status: data.status,
        adapter: 'manual',
        adapterSubscriptionId: null,
      },
    })
    .returning()
  const row = rows[0]
  if (!row) throw new Error('upsertAdminSubscription: upsert returned no row')
  return row
}

/**
 * Downgrade a tenant to freelancer after grace period expires.
 * Sets tier='freelancer', status='active'; preserves trial_ends_at as a
 * historical record.
 */
export async function downgradeToFreelancerDb(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({
      tier: 'freelancer',
      status: 'active',
      gracePeriodStartedAt: null,
    })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

/**
 * Set grace_period_started_at to now for a tenant whose trial has expired but
 * is within the 7-day grace window. Only sets it once (when currently null).
 */
export async function setGracePeriodStarted(
  db: Db,
  tenantId: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({ gracePeriodStartedAt: new Date() })
    .where(
      and(
        eq(zyncSubscriptions.tenantId, tenantId),
        isNull(zyncSubscriptions.gracePeriodStartedAt),
      ),
    )
    .returning()
  return row
}

/**
 * Update the NullAdapter's direct DB path for updateSubscription:
 * sets the new tier on the subscription row.
 */
export async function updateSubscriptionTier(
  db: Db,
  tenantId: string,
  newTier: string,
): Promise<ZyncSubscriptionRow | undefined> {
  const [row] = await db
    .update(zyncSubscriptions)
    .set({ tier: newTier })
    .where(eq(zyncSubscriptions.tenantId, tenantId))
    .returning()
  return row
}

/**
 * Resolve a subscription by tenant slug (for admin routes).
 * Joins tenants table to find by slug → tenantId → subscription.
 */
export async function getSubscriptionByTenantSlug(
  db: Db,
  slug: string,
): Promise<(ZyncSubscriptionRow & { tenantId: string }) | undefined> {
  // We use a raw join via drizzle relational query approach
  const result = await db.execute(
    sql`SELECT zs.* FROM zync_subscriptions zs
        JOIN tenants t ON t.id = zs.tenant_id
        WHERE t.slug = ${slug}
        LIMIT 1`,
  )
  const row = result?.[0] as Record<string, unknown> | undefined
  if (!row) return undefined
  // Map snake_case to camelCase for ZyncSubscriptionRow
  return {
    id: row.id as string,
    tenantId: row.tenant_id as string,
    tier: row.tier as string,
    status: row.status as string,
    period: (row.period as string | null) ?? null,
    adapter: row.adapter as string,
    adapterSubscriptionId: (row.adapter_subscription_id as string | null) ?? null,
    adapterCustomerId: (row.adapter_customer_id as string | null) ?? null,
    currentPeriodStart: row.current_period_start ? new Date(row.current_period_start as string) : null,
    currentPeriodEnd: row.current_period_end ? new Date(row.current_period_end as string) : null,
    trialEndsAt: row.trial_ends_at ? new Date(row.trial_ends_at as string) : null,
    canceledAt: row.canceled_at ? new Date(row.canceled_at as string) : null,
    gracePeriodStartedAt: row.grace_period_started_at
      ? new Date(row.grace_period_started_at as string)
      : null,
    createdAt: new Date(row.created_at as string),
  } as ZyncSubscriptionRow & { tenantId: string }
}

/**
 * Resolve a tenant's id from its slug. Used by admin routes.
 */
export async function getTenantIdBySlug(
  db: Db,
  slug: string,
): Promise<string | undefined> {
  const result = await db.execute(
    sql`SELECT id FROM tenants WHERE slug = ${slug} LIMIT 1`,
  )
  const row = result?.[0] as { id?: string } | undefined
  return row?.id
}

// ---------------------------------------------------------------------------
// Audit log helper for admin subscription overrides
// ---------------------------------------------------------------------------

export interface AdminSubscriptionAuditInput {
  tenantId: string
  adminId: string
  subscriptionId: string
  tier: string
  status: string
  note?: string | null
}

/**
 * Write an audit_log entry for an admin subscription override.
 * Uses the _audit-forward table definition (owned by audit-compliance spec).
 */
export async function logAdminSubscriptionOverride(
  db: Db,
  input: AdminSubscriptionAuditInput,
): Promise<void> {
  await db.insert(auditLog).values({
    tenantId: input.tenantId,
    actorId: input.adminId,
    actorType: 'admin',
    entityType: 'zync_subscription',
    entityId: input.subscriptionId,
    action: 'admin_subscription_override',
    changes: {
      tier: [null, input.tier],
      status: [null, input.status],
      note: [null, input.note ?? null],
    },
  })
}