/**
 * billing_plans — billing-plans-management-ui (P083, wave-9 leaf 1).
 *
 * System-wide plan catalogue. One row per purchasable plan that tenants can
 * subscribe to. `tier` mirrors the tier enum used by `tenants.tier` and
 * `zync_subscriptions.tier`. Plans are soft-deleted via `archived_at`; route
 * handlers treat non-null `archived_at` as inactive.
 *
 * Tenant count per plan is derived at query time via a join on
 * `zync_subscriptions.tier` — no denormalised counter needed.
 *
 * Design decisions:
 * - price_monthly / price_annual stored as NUMERIC(10,2) in ILS cents (integer
 *   representation kept simple; UI formats via formatCurrencyILS).
 * - features TEXT[] — ordered list of marketing bullets.
 * - max_users / max_projects nullable = unlimited.
 * - No CHECK on tier: plans may introduce new tiers in future; existing tier
 *   values are enforced by the admin form's Select options.
 */
import { pgTable, uuid, text, integer, timestamp, index } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'

export const billingPlans = pgTable(
  'billing_plans',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    // 'freelancer' | 'business' | 'enterprise' | 'white_label'
    tier: text('tier').notNull(),
    // Monthly price in ILS (whole shekels, e.g. 99 = ₪99/mo)
    priceMonthly: integer('price_monthly').notNull().default(0),
    // Annual price in ILS (whole shekels, billed annually)
    priceAnnual: integer('price_annual').notNull().default(0),
    // Marketing feature bullets
    features: text('features')
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    // NULL = unlimited
    maxUsers: integer('max_users'),
    maxProjects: integer('max_projects'),
    // NULL = active; non-null = soft-archived
    archivedAt: timestamp('archived_at', { withTimezone: true }),
    createdBy: uuid('created_by').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tierIdx: index('billing_plans_tier_idx').on(t.tier),
    activeIdx: index('billing_plans_active_idx').on(t.archivedAt),
  }),
)

export type BillingPlanRow = typeof billingPlans.$inferSelect
export type NewBillingPlan = typeof billingPlans.$inferInsert