/**
 * AI Infrastructure Layer schema — system-ai.
 * Six tables: ai_tenant_settings, ai_tier_quotas, ai_model_pricing,
 * ai_global_config, ai_usage_log, ai_credit_purchases.
 * Neon Postgres / Drizzle pgTable; UUID PKs, TIMESTAMPTZ, JSONB, BOOLEAN, NUMERIC.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  integer,
  bigint,
  numeric,
  date,
  index,
  uniqueIndex,
  check,
  primaryKey,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { adminUsers } from './admin'

// ── Per-tenant personality, preferences, extra-usage toggle ──────────────────

export const aiTenantSettings = pgTable('ai_tenant_settings', {
  tenantId: uuid('tenant_id')
    .primaryKey()
    .references(() => tenants.id, { onDelete: 'cascade' }),
  personalityPrompt: text('personality_prompt'),
  useCaseOverrides: jsonb('use_case_overrides')
    .notNull()
    .default(sql`'{}'::jsonb`),
  extraUsageEnabled: boolean('extra_usage_enabled').notNull().default(false),
  extraSpendLimitUsd: numeric('extra_spend_limit_usd', { precision: 10, scale: 2 }),
  autoReloadEnabled: boolean('auto_reload_enabled').notNull().default(false),
  autoReloadAmountUsd: numeric('auto_reload_amount_usd', { precision: 10, scale: 2 }),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

// ── Per-tier monthly AI quota configuration ───────────────────────────────────

export const aiTierQuotas = pgTable(
  'ai_tier_quotas',
  {
    tier: text('tier').notNull(),
    modelId: text('model_id').notNull(),
    monthlyTokens: bigint('monthly_tokens', { mode: 'number' }).notNull(),
    extraAllowed: boolean('extra_allowed').notNull().default(false),
    extraMaxUsd: numeric('extra_max_usd', { precision: 10, scale: 2 }),
    effectiveFrom: date('effective_from').notNull(),
    effectiveTo: date('effective_to'),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.tier, t.effectiveFrom] }),
    tierCheck: check(
      'ai_tier_quotas_tier_check',
      sql`${t.tier} IN ('freelancer', 'business', 'enterprise', 'white_label')`,
    ),
  }),
)

// ── Token pricing per model ───────────────────────────────────────────────────

export const aiModelPricing = pgTable(
  'ai_model_pricing',
  {
    modelId: text('model_id').primaryKey(),
    provider: text('provider').notNull(),
    label: text('label').notNull(),
    inputCostPer1m: numeric('input_cost_per_1m', { precision: 12, scale: 6 }).notNull(),
    outputCostPer1m: numeric('output_cost_per_1m', { precision: 12, scale: 6 }).notNull(),
    isVisionCapable: boolean('is_vision_capable').notNull().default(false),
    active: boolean('active').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    providerCheck: check(
      'ai_model_pricing_provider_check',
      sql`${t.provider} IN ('anthropic', 'openai', 'google')`,
    ),
  }),
)

// ── Global AI configuration (single row enforced) ─────────────────────────────

export const aiGlobalConfig = pgTable(
  'ai_global_config',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    mainModelId: text('main_model_id')
      .notNull()
      .references(() => aiModelPricing.modelId),
    backupModelIds: jsonb('backup_model_ids')
      .notNull()
      .default(sql`'[]'::jsonb`),
    useCasePrompts: jsonb('use_case_prompts')
      .notNull()
      .default(sql`'{}'::jsonb`),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    updatedBy: uuid('updated_by').references(() => adminUsers.id),
  },
  () => ({
    // Singleton: unique index on a constant expression enforces a single row
    // (spec's `CHECK (id = 1)` can't apply to a UUID PK). `true` is IMMUTABLE.
    singleton: uniqueIndex('ai_global_config_singleton').on(sql`(true)`),
  }),
)

// ── Per-AI-call usage and cost log ────────────────────────────────────────────

export const aiUsageLog = pgTable(
  'ai_usage_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id').references(() => users.id),
    useCase: text('use_case').notNull(),
    modelId: text('model_id').notNull(),
    provider: text('provider').notNull(),
    inputTokens: integer('input_tokens').notNull(),
    outputTokens: integer('output_tokens').notNull(),
    totalTokens: integer('total_tokens').notNull(),
    costUsd: numeric('cost_usd', { precision: 12, scale: 6 }).notNull(),
    billedFrom: text('billed_from').notNull(),
    durationMs: integer('duration_ms').notNull(),
    entityType: text('entity_type'),
    entityId: uuid('entity_id'),
    error: text('error'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantPeriodIdx: index('ai_usage_log_tenant_period').on(t.tenantId, t.createdAt),
    useCaseIdx: index('ai_usage_log_use_case').on(t.tenantId, t.useCase, t.createdAt),
    providerCheck: check(
      'ai_usage_log_provider_check',
      sql`${t.provider} IN ('anthropic', 'openai', 'google')`,
    ),
    billedFromCheck: check(
      'ai_usage_log_billed_from_check',
      sql`${t.billedFrom} IN ('quota', 'extra', 'purchased')`,
    ),
  }),
)

// ── Purchased extra credits ───────────────────────────────────────────────────

export const aiCreditPurchases = pgTable('ai_credit_purchases', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id')
    .notNull()
    .references(() => tenants.id, { onDelete: 'cascade' }),
  amountUsd: numeric('amount_usd', { precision: 10, scale: 2 }).notNull(),
  tokensGranted: bigint('tokens_granted', { mode: 'number' }).notNull(),
  tokensRemaining: bigint('tokens_remaining', { mode: 'number' }).notNull(),
  paymentReference: text('payment_reference'),
  expiresAt: timestamp('expires_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// ── Types ─────────────────────────────────────────────────────────────────────

export type AITenantSettingsRow = typeof aiTenantSettings.$inferSelect
export type NewAITenantSettings = typeof aiTenantSettings.$inferInsert

export type AITierQuotaRow = typeof aiTierQuotas.$inferSelect
export type NewAITierQuota = typeof aiTierQuotas.$inferInsert

export type AIModelPricingRow = typeof aiModelPricing.$inferSelect
export type NewAIModelPricing = typeof aiModelPricing.$inferInsert

export type AIGlobalConfigRow = typeof aiGlobalConfig.$inferSelect
export type NewAIGlobalConfig = typeof aiGlobalConfig.$inferInsert

export type AIUsageLogRow = typeof aiUsageLog.$inferSelect
export type NewAIUsageLog = typeof aiUsageLog.$inferInsert

export type AICreditPurchaseRow = typeof aiCreditPurchases.$inferSelect
export type NewAICreditPurchase = typeof aiCreditPurchases.$inferInsert