/**
 * Communications tables — system-communications-notifications.
 *
 * push_subscriptions: browser Web Push endpoint registrations per user/tenant.
 * notifications:      in-app notification inbox (i18n keys + params, never pre-rendered).
 * adapter_credentials: per-tenant AES-256-GCM encrypted bot tokens / SMTP credentials.
 *
 * FK targets: users(id) and tenants(id) from foundation-auth-rbac.
 * notifications.type has NO CHECK — the NotificationType union is owned by
 * spec 97 (notification-preferences); any value is valid here.
 */
import {
  pgTable,
  uuid,
  text,
  jsonb,
  timestamp,
  index,
  check,
  unique,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { users } from './users'
import { tenants } from './tenants'

// ── Push Subscriptions ──────────────────────────────────────────────────────

export const pushSubscriptions = pgTable(
  'push_subscriptions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // Browser push endpoint URL — globally unique per subscription
    endpoint: text('endpoint').notNull().unique(),
    // Client public key (base64url)
    p256dh: text('p256dh').notNull(),
    // Client auth secret (base64url)
    auth: text('auth').notNull(),
    // For display in preferences UI
    userAgent: text('user_agent'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
  },
  (t) => ({
    userTenantIdx: index('idx_push_subs_user').on(t.userId, t.tenantId),
  }),
)

export type PushSubscriptionRow = typeof pushSubscriptions.$inferSelect
export type NewPushSubscription = typeof pushSubscriptions.$inferInsert

// ── Notifications ───────────────────────────────────────────────────────────

export const notifications = pgTable(
  'notifications',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    // NotificationType union owned by spec 97 (notification-preferences); NO CHECK here.
    type: text('type').notNull(),
    // i18n key, rendered client-side at read time
    titleKey: text('title_key').notNull(),
    // i18n key, nullable (title-only notifications)
    bodyKey: text('body_key'),
    // Interpolation params for the i18n keys
    params: jsonb('params').notNull().default(sql`'{}'::jsonb`),
    entityType: text('entity_type'),
    entityId: uuid('entity_id'),
    readAt: timestamp('read_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    inboxIdx: index('notifications_inbox').on(
      t.tenantId,
      t.userId,
      t.readAt,
      t.createdAt.desc(),
    ),
  }),
)

export type NotificationRow = typeof notifications.$inferSelect
export type NewNotification = typeof notifications.$inferInsert

// ── Adapter Credentials ─────────────────────────────────────────────────────

export const adapterCredentials = pgTable(
  'adapter_credentials',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // AES-256-GCM encrypted credential blob, keyed by INTEGRATION_ENCRYPTION_KEY.
    adapterId: text('adapter_id').notNull(),
    // base64-encoded AES-256-GCM ciphertext (without IV or auth tag)
    ciphertext: text('ciphertext').notNull(),
    // base64-encoded 12-byte GCM nonce / IV
    iv: text('iv').notNull(),
    // base64-encoded 16-byte GCM auth tag
    authTag: text('auth_tag').notNull(),
    // Non-secret display fields (bot username, from-address, account email)
    metadata: jsonb('metadata').notNull().default(sql`'{}'::jsonb`),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantAdapterUniq: unique('adapter_credentials_tenant_adapter_unique').on(
      t.tenantId,
      t.adapterId,
    ),
    tenantIdx: index('idx_adapter_credentials_tenant').on(t.tenantId),
    adapterCheck: check(
      'adapter_credentials_adapter_id_check',
      sql`${t.adapterId} IN ('gmail','outlook','telegram','slack','whatsapp','smtp','trello','asana','jira','monday','clickup') OR ${t.adapterId} LIKE 'invoice:%'`,
    ),
  }),
)

export type AdapterCredentialRow = typeof adapterCredentials.$inferSelect
export type NewAdapterCredential = typeof adapterCredentials.$inferInsert