import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, date, primaryKey } from 'drizzle-orm/pg-core';
import { createdAt, updatedAt, ts, money, jsonObject } from './_common.js';

export const users = pgTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  emailVerifiedAt: ts('email_verified_at'),
  passwordHash: text('password_hash'),
  name: text('name'),
  role: text('role').notNull().default('user'), // user | pro | admin
  displayCurrency: text('display_currency').notNull().default('USD'),
  /** oauth/passkey providers linked (§175) */
  providers: jsonb('providers').$type<Array<{ provider: string; subject: string }>>().notNull().default([]),
  preferences: jsonObject<Record<string, unknown>>('preferences'),
  /** public collector handle (lowercase, unique) */
  handle: text('handle').unique(),
  avatarUrl: text('avatar_url'),
  bio: text('bio'),
  mfaEnabled: boolean('mfa_enabled').notNull().default(false),
  /** TOTP secret, encrypted at rest with SESSION_SECRET-derived key */
  totpSecretEnc: text('totp_secret_enc'),
  /** ask an e-mail code on every new device even without TOTP */
  alwaysAskCode: boolean('always_ask_code').notNull().default(true),
  passwordChangedAt: ts('password_changed_at'),
  pendingEmail: text('pending_email'),
  deletedAt: ts('deleted_at'),
  purgeAfter: ts('purge_after'),
  createdAt: createdAt(),
  lastLoginAt: ts('last_login_at'),
});

export const sessions = pgTable(
  'sessions',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    expiresAt: ts('expires_at').notNull(),
    userAgent: text('user_agent'),
    ip: text('ip'),
    lastSeenAt: ts('last_seen_at'),
    revokedAt: ts('revoked_at'),
    createdAt: createdAt(),
  },
  (t) => [index('sessions_user_idx').on(t.userId)],
);

export const collections = pgTable(
  'collections',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    name: text('name').notNull(),
    description: text('description'),
    isPublic: boolean('is_public').notNull().default(false),
    publicSlug: text('public_slug'),
    /** default | wishlist | vault … free-form */
    kind: text('kind').notNull().default('collection'),
    budgetUsd: money('budget_usd'),
    color: text('color'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('collections_user_idx').on(t.userId), uniqueIndex('collections_public_slug_uq').on(t.publicSlug)],
);

export const collectionItems = pgTable(
  'collection_items',
  {
    id: text('id').primaryKey(),
    collectionId: text('collection_id').notNull(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    quantity: integer('quantity').notNull().default(1),
    acquiredAt: date('acquired_at'),
    purchasePrice: money('purchase_price'),
    purchaseCurrency: text('purchase_currency'),
    purchasePriceUsd: money('purchase_price_usd'),
    source: text('source'),
    grader: text('grader'),
    grade: text('grade'),
    certificationNumber: text('certification_number'),
    serial: text('serial'),
    photos: jsonb('photos').$type<string[]>().notNull().default([]),
    notes: text('notes'),
    tags: jsonb('tags').$type<string[]>().notNull().default([]),
    condition: text('condition'),
    /** member-entered fallback value when RareIndex has no valuation yet (never shown as RIV) */
    manualValueUsd: money('manual_value_usd'),
    soldAt: date('sold_at'),
    soldPriceUsd: money('sold_price_usd'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('collection_items_collection_idx').on(t.collectionId), index('collection_items_asset_idx').on(t.assetId)],
);

/** Daily portfolio value history (§130). */
export const collectionSnapshots = pgTable(
  'collection_snapshots',
  {
    collectionId: text('collection_id').notNull(),
    date: date('date').notNull(),
    valueUsd: money('value_usd').notNull(),
    costBasisUsd: money('cost_basis_usd').notNull(),
    items: integer('items').notNull(),
  },
  (t) => [primaryKey({ columns: [t.collectionId, t.date] })],
);

export const watchlists = pgTable(
  'watchlists',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    name: text('name').notNull().default('Watchlist'),
    createdAt: createdAt(),
  },
  (t) => [index('watchlists_user_idx').on(t.userId)],
);

export const watchlistItems = pgTable(
  'watchlist_items',
  {
    id: text('id').primaryKey(),
    watchlistId: text('watchlist_id').notNull(),
    targetType: text('target_type').notNull(), // asset | category | brand | set | source | auction
    targetId: text('target_id').notNull(),
    label: text('label'),
    note: text('note'),
    targetPriceUsd: money('target_price_usd'),
    /** RIV when added, to show change since watching */
    baselineUsd: money('baseline_usd'),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('watchlist_items_uq').on(t.watchlistId, t.targetType, t.targetId)],
);

export const alerts = pgTable(
  'alerts',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    alertType: text('alert_type').notNull(), // price_below | price_above | new_listing | new_auction | auction_ending | auction_below_riv | record_sale | unusual_volume | market_move | rare_item | population_update
    targetType: text('target_type').notNull(),
    targetId: text('target_id').notNull(),
    threshold: money('threshold'),
    currency: text('currency'),
    params: jsonObject<Record<string, unknown>>('params'),
    channel: text('channel').notNull().default('inapp'), // inapp | email | both
    active: boolean('active').notNull().default(true),
    name: text('name'),
    cooldownMinutes: integer('cooldown_minutes').notNull().default(1440),
    lastTriggeredAt: ts('last_triggered_at'),
    triggerCount: integer('trigger_count').notNull().default(0),
    createdAt: createdAt(),
  },
  (t) => [index('alerts_user_idx').on(t.userId), index('alerts_target_idx').on(t.targetType, t.targetId, t.active)],
);

export const alertEvents = pgTable(
  'alert_events',
  {
    id: text('id').primaryKey(),
    alertId: text('alert_id').notNull(),
    userId: text('user_id').notNull(),
    message: text('message').notNull(),
    payload: jsonObject<Record<string, unknown>>('payload'),
    readAt: ts('read_at'),
    createdAt: createdAt(),
  },
  (t) => [index('alert_events_user_idx').on(t.userId, t.createdAt)],
);

export const apiKeys = pgTable(
  'api_keys',
  {
    id: text('id').primaryKey(),
    userId: text('user_id'),
    name: text('name').notNull(),
    prefix: text('prefix').notNull(),
    keyHash: text('key_hash').notNull().unique(),
    tier: text('tier').notNull().default('free'), // free | hobby | professional | research | enterprise
    rateLimitPerMinute: integer('rate_limit_per_minute').notNull().default(60),
    dailyQuota: integer('daily_quota').notNull().default(1000),
    createdAt: createdAt(),
    lastUsedAt: ts('last_used_at'),
    revokedAt: ts('revoked_at'),
  },
  (t) => [index('api_keys_user_idx').on(t.userId)],
);

export const apiUsage = pgTable(
  'api_usage',
  {
    keyId: text('key_id').notNull(),
    date: date('date').notNull(),
    endpoint: text('endpoint').notNull(),
    count: integer('count').notNull().default(0),
    latencyMsAvg: real('latency_ms_avg'),
  },
  (t) => [primaryKey({ columns: [t.keyId, t.date, t.endpoint] })],
);

export const assetViews = pgTable(
  'asset_views',
  {
    assetId: text('asset_id').notNull(),
    date: date('date').notNull(),
    views: integer('views').notNull().default(0),
  },
  (t) => [primaryKey({ columns: [t.assetId, t.date] })],
);

export const searchLog = pgTable(
  'search_log',
  {
    id: text('id').primaryKey(),
    query: text('query').notNull(),
    normalized: text('normalized').notNull(),
    results: integer('results').notNull(),
    userId: text('user_id'),
    createdAt: createdAt(),
  },
  (t) => [index('search_log_created_idx').on(t.createdAt), index('search_log_normalized_idx').on(t.normalized)],
);