timelapse-2/apps/api-server/src/db/schema.ts

import { pgTable, text, timestamp, boolean, integer, jsonb, serial, pgEnum, index, uniqueIndex } from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

// ---- Enums ----
export const orgStatusEnum = pgEnum('org_status', ['active', 'suspended', 'trial'])
export const projectStatusEnum = pgEnum('project_status', ['planning', 'active', 'paused', 'completed', 'archived'])
export const deviceStatusEnum = pgEnum('device_status', ['offline', 'online', 'capturing', 'uploading', 'degraded', 'updating', 'error'])
export const captureStatusEnum = pgEnum('capture_status', ['pending', 'uploaded', 'processing', 'ready', 'failed'])
export const videoStatusEnum = pgEnum('video_status', ['pending', 'processing', 'ready', 'failed'])
export const alertSeverityEnum = pgEnum('alert_severity', ['info', 'warning', 'error', 'critical'])
export const alertTypeEnum = pgEnum('alert_type', ['device_offline', 'device_error', 'storage_full', 'upload_failed', 'capture_missed', 'video_failed', 'firmware_update_available'])
export const alertStateEnum = pgEnum('alert_state', ['open', 'acknowledged', 'resolved'])
export const userRoleEnum = pgEnum('user_role', ['super_admin', 'org_admin', 'project_manager', 'viewer'])
export const commandResultEnum = pgEnum('command_result_status', ['pending', 'delivered', 'acknowledged', 'success', 'failed', 'timeout'])

// ---- Tenancy ----
export const organizations = pgTable('organizations', {
  id: text('id').primaryKey(), // nanoid
  name: text('name').notNull(),
  status: orgStatusEnum('status').default('trial').notNull(),
  planTier: text('plan_tier').default('free').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

export const projects = pgTable('projects', {
  id: text('id').primaryKey(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }).notNull(),
  name: text('name').notNull(),
  description: text('description'),
  timezone: text('timezone').default('Asia/Ho_Chi_Minh').notNull(),
  startDate: timestamp('start_date'),
  endDate: timestamp('end_date'),
  status: projectStatusEnum('status').default('planning').notNull(),
  captureInterval: integer('capture_interval').default(60).notNull(), // minutes
  resolution: text('resolution').default('1920x1080').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  orgStatusIdx: index('projects_org_status_idx').on(table.orgId, table.status),
}))

// ---- Identity ----
export const users = pgTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull(),
  name: text('name').notNull(),
  avatarUrl: text('avatar_url'),
  provider: text('provider').default('email').notNull(), // 'google' | 'email'
  emailVerified: boolean('email_verified').default(false).notNull(),
  passwordHash: text('password_hash'), // null for OAuth users
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}))

export const memberships = pgTable('memberships', {
  id: serial('id').primaryKey(),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }).notNull(),
  role: userRoleEnum('role').notNull(),
  invitedBy: text('invited_by').references(() => users.id),
  joinedAt: timestamp('joined_at').defaultNow().notNull(),
}, (table) => ({
  userOrgIdx: uniqueIndex('memberships_user_org_idx').on(table.userId, table.orgId),
}))

export const sessions = pgTable('sessions', {
  id: text('id').primaryKey(),
  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  refreshTokenHash: text('refresh_token_hash').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  userAgent: text('user_agent'),
  ipAddress: text('ip_address'),
})

export const magicLinks = pgTable('magic_links', {
  id: text('id').primaryKey(),
  email: text('email').notNull(),
  tokenHash: text('token_hash').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  usedAt: timestamp('used_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  emailExpiresIdx: index('magic_links_email_expires_idx').on(table.email, table.expiresAt),
}))

// ---- Devices ----
export const devices = pgTable('devices', {
  id: text('id').primaryKey(),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'cascade' }).notNull(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }).notNull(),
  serialNo: text('serial_no').notNull(),
  name: text('name').notNull(),
  apiKeyHash: text('api_key_hash').notNull(),
  firmwareVersion: text('firmware_version'),
  lastSeenAt: timestamp('last_seen_at'),
  status: deviceStatusEnum('status').default('offline').notNull(),
  config: jsonb('config').$type<{
    captureIntervalMinutes: number
    resolution: string
    quality: number
    uploadOnWifiOnly: boolean
    nightModeEnabled: boolean
    nightModeStart: string
    nightModeEnd: string
    maxStorageGb: number
    heartbeatIntervalSeconds: number
    timezone: string
  }>().default({
    captureIntervalMinutes: 60,
    resolution: '1920x1080',
    quality: 85,
    uploadOnWifiOnly: false,
    nightModeEnabled: false,
    nightModeStart: '20:00',
    nightModeEnd: '06:00',
    maxStorageGb: 64,
    heartbeatIntervalSeconds: 300,
    timezone: 'Asia/Ho_Chi_Minh',
  }).notNull(),
  claimCode: text('claim_code'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  projectStatusIdx: index('devices_project_status_idx').on(table.projectId, table.status),
  serialNoIdx: uniqueIndex('devices_serial_no_idx').on(table.serialNo),
}))

export const deviceHeartbeats = pgTable('device_heartbeats', {
  id: serial('id').primaryKey(),
  deviceId: text('device_id').references(() => devices.id, { onDelete: 'cascade' }).notNull(),
  heartbeatAt: timestamp('heartbeat_at').defaultNow().notNull(),
  tempC: integer('temp_c'),
  batteryPct: integer('battery_pct'),
  storageFreeGb: integer('storage_free_gb'),
  capturesToday: integer('captures_today').default(0),
  lastCaptureAt: timestamp('last_capture_at'),
  networkStatus: text('network_status').default('online'),
}, (table) => ({
  deviceHeartbeatIdx: index('device_heartbeats_device_idx').on(table.deviceId, table.heartbeatAt),
}))

// ---- Captures ----
export const captures = pgTable('captures', {
  id: text('id').primaryKey(),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'cascade' }).notNull(),
  deviceId: text('device_id').references(() => devices.id, { onDelete: 'cascade' }).notNull(),
  capturedAt: timestamp('captured_at').notNull(),
  uploadedAt: timestamp('uploaded_at'),
  fileKey: text('file_key'),
  thumbnailKey: text('thumbnail_key'),
  checksum: text('checksum'),
  resolution: text('resolution'),
  fileSizeBytes: integer('file_size_bytes'),
  exposureMs: integer('exposure_ms'),
  iso: integer('iso'),
  aperture: text('aperture'),
  gpsLat: text('gps_lat'),
  gpsLng: text('gps_lng'),
  status: captureStatusEnum('status').default('pending').notNull(),
  metadata: jsonb('metadata').default({}).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  deviceCapturedIdx: index('captures_device_captured_idx').on(table.deviceId, table.capturedAt),
  projectCapturedIdx: index('captures_project_captured_idx').on(table.projectId, table.capturedAt),
  statusUploadedIdx: index('captures_status_uploaded_idx').on(table.status, table.uploadedAt),
}))

// ---- Videos ----
export const videos = pgTable('videos', {
  id: text('id').primaryKey(),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'cascade' }).notNull(),
  periodStart: timestamp('period_start').notNull(),
  periodEnd: timestamp('period_end').notNull(),
  fps: integer('fps').default(24).notNull(),
  resolution: text('resolution').default('1920x1080').notNull(),
  fileKey: text('file_key'),
  thumbnailKey: text('thumbnail_key'),
  durationSec: integer('duration_sec'),
  status: videoStatusEnum('status').default('pending').notNull(),
  generatedAt: timestamp('generated_at'),
  fileSizeBytes: integer('file_size_bytes'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  projectGeneratedIdx: index('videos_project_generated_idx').on(table.projectId, table.generatedAt),
}))

export const videoJobs = pgTable('video_jobs', {
  id: text('id').primaryKey(),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'cascade' }).notNull(),
  videoId: text('video_id').references(() => videos.id, { onDelete: 'cascade' }),
  jobType: text('job_type').notNull(), // 'daily', 'weekly', 'custom'
  params: jsonb('params').default({}).notNull(),
  status: text('status').default('pending').notNull(),
  attempts: integer('attempts').default(0),
  startedAt: timestamp('started_at'),
  completedAt: timestamp('completed_at'),
  errorMsg: text('error_msg'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

// ---- Commands ----
export const commands = pgTable('commands', {
  id: text('id').primaryKey(),
  deviceId: text('device_id').references(() => devices.id, { onDelete: 'cascade' }).notNull(),
  commandType: text('command_type').notNull(), // DeviceCommandType
  payload: jsonb('payload'),
  scheduledAt: timestamp('scheduled_at'),
  queuedAt: timestamp('queued_at').defaultNow().notNull(),
  deliveredAt: timestamp('delivered_at'),
  acknowledgedAt: timestamp('acknowledged_at'),
  resultStatus: commandResultEnum('result_status').default('pending').notNull(),
  resultData: jsonb('result_data'),
}, (table) => ({
  deviceQueuedIdx: index('commands_device_queued_idx').on(table.deviceId, table.queuedAt),
  pendingIdx: index('commands_pending_idx').on(table.resultStatus, table.scheduledAt),
}))

// ---- Alerts ----
export const alertRules = pgTable('alert_rules', {
  id: text('id').primaryKey(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }).notNull(),
  name: text('name').notNull(),
  type: alertTypeEnum('type').notNull(),
  condition: jsonb('condition').notNull(),
  cooldownMinutes: integer('cooldown_minutes').default(15),
  enabled: boolean('enabled').default(true).notNull(),
  notifyEmail: boolean('notify_email').default(false).notNull(),
  notifySms: boolean('notify_sms').default(false).notNull(),
  webhookUrl: text('webhook_url'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

export const alerts = pgTable('alerts', {
  id: text('id').primaryKey(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }).notNull(),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'set null' }),
  deviceId: text('device_id').references(() => devices.id, { onDelete: 'set null' }),
  type: alertTypeEnum('type').notNull(),
  severity: alertSeverityEnum('severity').notNull(),
  message: text('message').notNull(),
  data: jsonb('data').default({}).notNull(),
  state: alertStateEnum('state').default('open').notNull(),
  openedAt: timestamp('opened_at').defaultNow().notNull(),
  acknowledgedAt: timestamp('acknowledged_at'),
  acknowledgedBy: text('acknowledged_by').references(() => users.id),
  resolvedAt: timestamp('resolved_at'),
}, (table) => ({
  orgStateIdx: index('alerts_org_state_idx').on(table.orgId, table.state),
  projectIdx: index('alerts_project_idx').on(table.projectId),
}))

// ---- Audit ----
export const auditLogs = pgTable('audit_logs', {
  id: serial('id').primaryKey(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }),
  userId: text('user_id').references(() => users.id, { onDelete: 'set null' }),
  action: text('action').notNull(),
  resourceType: text('resource_type').notNull(),
  resourceId: text('resource_id'),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  metadata: jsonb('metadata').default({}),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  orgCreatedIdx: index('audit_logs_org_created_idx').on(table.orgId, table.createdAt),
}))

export const activityLogs = pgTable('activity_logs', {
  id: serial('id').primaryKey(),
  orgId: text('org_id').references(() => organizations.id, { onDelete: 'cascade' }),
  projectId: text('project_id').references(() => projects.id, { onDelete: 'set null' }),
  userId: text('user_id').references(() => users.id, { onDelete: 'set null' }),
  eventType: text('event_type').notNull(),
  metadata: jsonb('metadata').default({}),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  projectCreatedIdx: index('activity_logs_project_created_idx').on(table.projectId, table.createdAt),
}))

// ---- Relations ----
export const organizationsRelations = relations(organizations, ({ many }) => ({
  projects: many(projects),
  memberships: many(memberships),
}))

export const projectsRelations = relations(projects, ({ one, many }) => ({
  organization: one(organizations, { fields: [projects.orgId], references: [organizations.id] }),
  devices: many(devices),
  captures: many(captures),
  videos: many(videos),
  alerts: many(alerts),
}))

export const usersRelations = relations(users, ({ many }) => ({
  memberships: many(memberships),
  sessions: many(sessions),
}))

export const membershipsRelations = relations(memberships, ({ one }) => ({
  user: one(users, { fields: [memberships.userId], references: [users.id] }),
  organization: one(organizations, { fields: [memberships.orgId], references: [organizations.id] }),
}))

export const devicesRelations = relations(devices, ({ one, many }) => ({
  project: one(projects, { fields: [devices.projectId], references: [projects.id] }),
  organization: one(organizations, { fields: [devices.orgId], references: [organizations.id] }),
  captures: many(captures),
  heartbeats: many(deviceHeartbeats),
  commands: many(commands),
  alerts: many(alerts),
}))

export const capturesRelations = relations(captures, ({ one }) => ({
  project: one(projects, { fields: [captures.projectId], references: [projects.id] }),
  device: one(devices, { fields: [captures.deviceId], references: [devices.id] }),
}))

export const videosRelations = relations(videos, ({ one }) => ({
  project: one(projects, { fields: [videos.projectId], references: [projects.id] }),
}))