packages/server/src/db/schema.ts

import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  googleId: text('google_id').unique(),
  email: text('email').unique().notNull(),
  name: text('name'),
  avatarUrl: text('avatar_url'),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  tokenExpires: text('token_expires'),
  role: text('role', { enum: ['admin', 'editor', 'viewer'] }).default('viewer'),
  createdAt: text('created_at').$defaultFn(() => new Date().toISOString()),
  updatedAt: text('updated_at').$defaultFn(() => new Date().toISOString()),
});

export const transactions = sqliteTable('transactions', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  emailId: text('email_id').unique().notNull(),
  userId: text('user_id').references(() => users.id),
  date: text('date').notNull(),
  sender: text('sender').notNull(),
  amount: real('amount').notNull(),
  currency: text('currency').default('CAD'),
  reference: text('reference'),
  message: text('message'),
  recipientEmail: text('recipient_email'),
  branch: text('branch'),
  status: text('status', { enum: ['deposited', 'pending', 'expired', 'cancelled'] }),
  rawEmail: text('raw_email'),
  rawEmailHtml: text('raw_email_html'),
  envelopeNumber: text('envelope_number'),
  screenshotOriginal: text('screenshot_original'),
  screenshotPreview: text('screenshot_preview'),
  parsedAt: text('parsed_at').$defaultFn(() => new Date().toISOString()),
  reviewed: integer('reviewed', { mode: 'boolean' }).default(false),
  reviewedBy: text('reviewed_by').references(() => users.id),
  createdAt: text('created_at').$defaultFn(() => new Date().toISOString()),
});

export const branchConfig = sqliteTable('branch_config', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').unique().notNull(),
  branch: text('branch').notNull(),
  active: integer('active', { mode: 'boolean' }).default(true),
  updatedAt: text('updated_at').$defaultFn(() => new Date().toISOString()),
});

export const scanLogs = sqliteTable('scan_logs', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  userId: text('user_id').references(() => users.id),
  scanPreset: text('scan_preset', { enum: ['today', 'last7days', 'custom'] }),
  scanStartDate: text('scan_start_date').notNull(),
  scanEndDate: text('scan_end_date').notNull(),
  forceRescan: integer('force_rescan', { mode: 'boolean' }).default(false),
  startedAt: text('started_at').notNull(),
  finishedAt: text('finished_at'),
  emailsFound: integer('emails_found').default(0),
  emailsParsed: integer('emails_parsed').default(0),
  emailsSkipped: integer('emails_skipped').default(0),
  errors: integer('errors').default(0),
  errorDetails: text('error_details'),
  aiProvider: text('ai_provider'),
  aiModel: text('ai_model'),
  createdAt: text('created_at').$defaultFn(() => new Date().toISOString()),
});

export const envelopes = sqliteTable('envelopes', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  envelopeNumber: text('envelope_number').notNull(),
  businessName: text('business_name'),
  name: text('name').notNull(),
  email: text('email'),
});

export const aiSettings = sqliteTable('ai_settings', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  userId: text('user_id').references(() => users.id),
  provider: text('provider').notNull(),
  model: text('model').notNull(),
  apiKey: text('api_key'),
  baseUrl: text('base_url'),
  isActive: integer('is_active', { mode: 'boolean' }).default(true),
  createdAt: text('created_at').$defaultFn(() => new Date().toISOString()),
});