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

/**
 * Member-account tables (auth flows, security, personal tooling). Everything a member creates is
 * private by default (§176); public sharing is an explicit opt-in on the parent record.
 */

/** One-time codes: e-mail verification, MFA e-mail fallback, password reset, e-mail change, new device. */
export const authCodes = pgTable(
  'auth_codes',
  {
    id: text('id').primaryKey(),
    userId: text('user_id'),
    email: text('email').notNull(),
    purpose: text('purpose').notNull(), // verify_email | mfa_email | password_reset | change_email | new_device
    codeHash: text('code_hash').notNull(),
    expiresAt: ts('expires_at').notNull(),
    attempts: integer('attempts').notNull().default(0),
    consumedAt: ts('consumed_at'),
    /** arbitrary payload, e.g. { newEmail } for change_email */
    payload: jsonObject<Record<string, unknown>>('payload'),
    createdAt: createdAt(),
  },
  (t) => [index('auth_codes_lookup_idx').on(t.email, t.purpose, t.createdAt)],
);

/** MFA recovery codes (hashed, single use). */
export const recoveryCodes = pgTable(
  'recovery_codes',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    codeHash: text('code_hash').notNull(),
    usedAt: ts('used_at'),
    createdAt: createdAt(),
  },
  (t) => [index('recovery_codes_user_idx').on(t.userId)],
);

/** Devices that may skip the second factor for 30 days. */
export const trustedDevices = pgTable(
  'trusted_devices',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    tokenHash: text('token_hash').notNull(),
    label: text('label'),
    userAgent: text('user_agent'),
    ip: text('ip'),
    createdAt: createdAt(),
    lastUsedAt: ts('last_used_at'),
    expiresAt: ts('expires_at').notNull(),
    revokedAt: ts('revoked_at'),
  },
  (t) => [index('trusted_devices_user_idx').on(t.userId), uniqueIndex('trusted_devices_token_uq').on(t.tokenHash)],
);

/** Login history shown in security settings. */
export const loginEvents = pgTable(
  'login_events',
  {
    id: text('id').primaryKey(),
    userId: text('user_id'),
    email: text('email'),
    ip: text('ip'),
    userAgent: text('user_agent'),
    outcome: text('outcome').notNull(), // success | bad_password | mfa_required | mfa_failed | locked | unknown_user
    method: text('method'), // password | totp | email_code | recovery_code | trusted_device
    createdAt: createdAt(),
  },
  (t) => [index('login_events_user_idx').on(t.userId, t.createdAt)],
);

/** Fixed-window rate limit counters (works across PM2 instances). */
export const rateLimits = pgTable('rate_limits', {
  key: text('key').primaryKey(),
  count: integer('count').notNull().default(0),
  resetAt: ts('reset_at').notNull(),
});

/** In-app notification inbox (alerts, security, digests, system). */
export const notifications = pgTable(
  'notifications',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    kind: text('kind').notNull(), // alert | security | system | digest | target_hit
    title: text('title').notNull(),
    body: text('body'),
    href: text('href'),
    payload: jsonObject<Record<string, unknown>>('payload'),
    readAt: ts('read_at'),
    emailedAt: ts('emailed_at'),
    createdAt: createdAt(),
  },
  (t) => [index('notifications_user_idx').on(t.userId, t.createdAt), index('notifications_unread_idx').on(t.userId, t.readAt)],
);

/** Saved searches (Explore / Search filter URLs) — powers Deal Radar and digests. */
export const savedSearches = pgTable(
  'saved_searches',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    name: text('name').notNull(),
    url: text('url').notNull(),
    params: jsonObject<Record<string, unknown>>('params'),
    notify: boolean('notify').notNull().default(false),
    lastRunAt: ts('last_run_at'),
    lastCount: integer('last_count'),
    createdAt: createdAt(),
  },
  (t) => [index('saved_searches_user_idx').on(t.userId)],
);

/** Personal price targets with progress tracking. */
export const priceTargets = pgTable(
  'price_targets',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    direction: text('direction').notNull().default('above'), // above | below
    targetUsd: money('target_usd').notNull(),
    /** RIV in USD when the target was set — used for progress */
    baselineUsd: money('baseline_usd'),
    note: text('note'),
    hitAt: ts('hit_at'),
    notifiedAt: ts('notified_at'),
    createdAt: createdAt(),
  },
  (t) => [index('price_targets_user_idx').on(t.userId), index('price_targets_asset_idx').on(t.assetId, t.hitAt)],
);

/** Files uploaded by members (collection photos, avatars). Stored under RI_DATA_DIR/uploads. */
export const uploads = pgTable(
  'uploads',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').notNull(),
    kind: text('kind').notNull(), // item_photo | avatar
    path: text('path').notNull(),
    mime: text('mime').notNull(),
    bytes: integer('bytes').notNull(),
    width: integer('width'),
    height: integer('height'),
    createdAt: createdAt(),
  },
  (t) => [index('uploads_user_idx').on(t.userId)],
);

/** Badges are computed from data, never hand-assigned (no fake badges). Cached here for profile pages. */
export const userBadges = pgTable(
  'user_badges',
  {
    userId: text('user_id').notNull(),
    badge: text('badge').notNull(),
    evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),
    awardedAt: createdAt(),
  },
  (t) => [uniqueIndex('user_badges_uq').on(t.userId, t.badge)],
);

export const collectionTags = textArray;