/**
 * zync_subscriptions — zync-subscription spec.
 *
 * Authoritative subscription record for each tenant's commercial relationship
 * with Zync. One row per tenant (UNIQUE tenant_id). `tier` here is the source
 * of truth; `tenants.tier` is a denormalised copy kept in sync by
 * `syncTierToTenant` in packages/payments.
 *
 * Design decisions:
 * - tier/status enforced by CHECK constraints (text column + check, no pg enum)
 * - period DEFAULT NULL (AD #12): freelancer onboarding has no billing period
 * - grace_period_started_at folded in here (greenfield; no ALTER needed)
 * - Both indexes on tenant_id (for FK lookups) and status (for cron queries)
 */
import { pgTable, uuid, text, timestamp, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'

export const zyncSubscriptions = pgTable(
  'zync_subscriptions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .unique()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'freelancer' | 'business' | 'enterprise' | 'white_label'
    tier: text('tier').notNull().default('freelancer'),
    // 'active' | 'trialing' | 'past_due' | 'canceled'
    status: text('status').notNull().default('active'),
    // 'monthly' | 'annual' | NULL (NULL = freelancer, no billing period)
    period: text('period').default(sql`NULL`),
    // 'null' | 'stripe' | 'paypal' | 'manual' | ...
    adapter: text('adapter').notNull().default('null'),
    adapterSubscriptionId: text('adapter_subscription_id'),
    adapterCustomerId: text('adapter_customer_id'),
    currentPeriodStart: timestamp('current_period_start', { withTimezone: true }),
    currentPeriodEnd: timestamp('current_period_end', { withTimezone: true }),
    trialEndsAt: timestamp('trial_ends_at', { withTimezone: true }),
    canceledAt: timestamp('canceled_at', { withTimezone: true }),
    // NULL = not in grace period; set when trial expires; cleared on upgrade
    gracePeriodStartedAt: timestamp('grace_period_started_at', { withTimezone: true }),
    // Trial expiry UI columns (trial-expiry-conversion-ui spec):
    trialWarningSentAt: timestamp('trial_warning_sent_at', { withTimezone: true }),
    trialExpiryAckAt: timestamp('trial_expiry_ack_at', { withTimezone: true }),
    // subscription-cancellation-flow (spec 69):
    cancellationReason: text('cancellation_reason'),
    cancellationReasonFreetext: text('cancellation_reason_freetext'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_zync_subscriptions_tenant').on(t.tenantId),
    statusIdx: index('idx_zync_subscriptions_status').on(t.status),
    tierCheck: check(
      'zync_subscriptions_tier_check',
      sql`${t.tier} IN ('freelancer', 'business', 'enterprise', 'white_label')`,
    ),
    statusCheck: check(
      'zync_subscriptions_status_check',
      sql`${t.status} IN ('active', 'trialing', 'past_due', 'canceled')`,
    ),
    periodCheck: check(
      'zync_subscriptions_period_check',
      sql`${t.period} IS NULL OR ${t.period} IN ('monthly', 'annual')`,
    ),
  }),
)

export type ZyncSubscriptionRow = typeof zyncSubscriptions.$inferSelect
export type NewZyncSubscription = typeof zyncSubscriptions.$inferInsert