src/lib/db/schema.ts

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

export const syncStatusEnum = pgEnum('sync_status', ['idle', 'syncing', 'errored']);
export const videoScanStatusEnum = pgEnum('video_scan_status', [
  'pending_analysis',
  'in_progress',
  'awaiting_approval',
  'completed',
  'failed',
  'idle',
  'pending' // Keep for backwards compatibility during migration if needed
]);

export const channelTypeEnum = pgEnum('channel_type', ['verified', 'external']);
export const platformEnum = pgEnum('platform', ['youtube', 'instagram', 'tiktok', 'facebook']);

export const objectCategory = pgEnum('object_category', [
  'Tech',
  'Fashion',
  'Furniture',
  'Audio',
  'Other',
  'Person',
  'Apparel',
]);

export const detectionStatus = pgEnum('detection_status', [
  'pending_review',
  'approved',
  'rejected',
  'flagged',
]);

export const moderationStatus = pgEnum('moderation_status', [
  'PENDING',    // Awaiting creator review (default)
  'APPROVED',   // Creator approved for Vault
  'REJECTED',   // Creator rejected, hidden from Vault
]);

export const adminModerationType = pgEnum('admin_moderation_type', [
  'corrected',          // Admin corrected name/category/links → back to creator queue
  'marked_incorrect',   // Admin marked as bad detection → removed from pipeline
  'approved',           // Admin approved for vault visibility
  'rejected',           // Admin rejected/hidden from vault
]);

export const reasonCode = pgEnum('reason_code', [
  'wrong_object',
  'wrong_category',
  'false_positive',
  'unclear_image',
  'duplicate',
  'out_of_scope',
]);

// Reusable timestamp pattern (recommended by Drizzle community)
export const timestamps = {
  createdAt: timestamp('created_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),

  updatedAt: timestamp('updated_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  })
    .defaultNow()
    .notNull()
    .$onUpdateFn(() => new Date()),
};

// Users table - Extended for Better Auth
export const users = pgTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull(),
  image: text('image'),
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
});

// Sessions table
export const sessions = pgTable('session', {
  id: text('id').primaryKey(),
  expiresAt: timestamp('expires_at').notNull(),
  token: text('token').notNull().unique(),
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  userId: text('user_id')
    .notNull()
    .references(() => users.id),
});

// Accounts table
export const accounts = pgTable('account', {
  id: text('id').primaryKey(),
  accountId: text('account_id').notNull(),
  providerId: text('provider_id').notNull(),
  userId: text('user_id')
    .notNull()
    .references(() => users.id),
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  idToken: text('id_token'),
  accessTokenExpiresAt: timestamp('access_token_expires_at'),
  refreshTokenExpiresAt: timestamp('refresh_token_expires_at'),
  scope: text('scope'),
  password: text('password'),
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
});

// Verifications table
export const verifications = pgTable('verification', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at'),
  updatedAt: timestamp('updated_at'),
});

// YouTube Channels table
export const youtubeChannels = pgTable('youtube_channels', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  creatorId: text('creator_id')
    .notNull()
    .references(() => users.id),
  channelId: text('channel_id').notNull(),
  channelName: text('channel_name').notNull(),
  creatorSlug: text('creator_slug').notNull().unique(), // Story 4.1: URL-friendly slug for public vault
  subscriberCount: integer('subscriber_count'),
  thumbnailUrl: text('thumbnail_url'),
  connectedAt: timestamp('connected_at').defaultNow().notNull(),
  syncStatus: syncStatusEnum('sync_status').default('idle').notNull(),
  channelType: channelTypeEnum('channel_type').default('external').notNull(),
  platform: platformEnum('platform').default('youtube').notNull(),
}, (table) => ({
  creatorChannelIdx: uniqueIndex('creator_channel_idx').on(table.creatorId, table.channelId),
}));

// YouTube Videos table
export const youtubeVideos = pgTable('youtube_videos', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  channelId: text('channel_id')
    .notNull()
    .references(() => youtubeChannels.id, { onDelete: 'cascade' }),
  videoId: text('video_id').notNull(),
  title: text('title').notNull(),
  description: text('description'),
  thumbnailUrl: text('thumbnail_url'),
  duration: text('duration'), // ISO 8601 format (PT15M33S)
  viewCount: integer('view_count'),
  availabilityStatus: text('availability_status').notNull().default('unknown'), // 'available' (public/unlisted) | 'private' | 'unknown'
  scanStatus: videoScanStatusEnum('scan_status').default('pending').notNull(),
  publishedAt: timestamp('published_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }),
  platform: platformEnum('platform').default('youtube').notNull(),
  url: text('url'), // Canonical URL for TikTok/Insta
  width: integer('width'),
  height: integer('height'),
  isInWorkbench: boolean('is_in_workbench').default(true).notNull(),
  ...timestamps,
}, (table) => ({
  channelVideoIdx: uniqueIndex('channel_video_idx').on(table.channelId, table.videoId),
}));

// Video Scan Jobs table for progress tracking
export const videoScanJobs = pgTable('video_scan_jobs', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  channelId: text('channel_id')
    .notNull()
    .references(() => youtubeChannels.id, { onDelete: 'cascade' }),
  userId: text('user_id')
    .notNull()
    .references(() => users.id),
  status: videoScanStatusEnum('status').default('pending').notNull(),
  progress: integer('progress').default(0).notNull(), // 0-100
  totalVideos: integer('total_videos'),
  scannedVideos: integer('scanned_videos').default(0).notNull(),
  errorMessage: text('error_message'),
  inngestRunId: text('inngest_run_id'),
  ...timestamps,
});

// Type inference
export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;
export type Session = typeof sessions.$inferSelect;
export type Account = typeof accounts.$inferSelect;
export type Verification = typeof verifications.$inferSelect;
export type YoutubeChannel = typeof youtubeChannels.$inferSelect;
export type InsertYoutubeChannel = typeof youtubeChannels.$inferInsert;
export type YoutubeVideo = typeof youtubeVideos.$inferSelect;
export type InsertYoutubeVideo = typeof youtubeVideos.$inferInsert;
export type VideoScanJob = typeof videoScanJobs.$inferSelect;
export type InsertVideoScanJob = typeof videoScanJobs.$inferInsert;

// Detected Objects table
export const detectedObjects = pgTable('detected_objects', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  videoId: text('video_id')
    .notNull()
    .references(() => youtubeVideos.id, { onDelete: 'cascade' }),
  objectName: text('object_name').notNull(),
  category: objectCategory('category').notNull(),
  confidenceScore: real('confidence_score').notNull(), // 0.0 - 1.0
  frameTimestamp: integer('frame_timestamp').notNull(), // Seconds into video
  detectionMetadata: jsonb('detection_metadata'), // Bounding box, model info
  thumbnailUrl: text('thumbnail_url'),
  status: detectionStatus('status').notNull().default('pending_review'),

  // NEW COLUMNS FOR STORY 3.5
  moderationStatus: moderationStatus('moderation_status').notNull().default('PENDING'),
  moderatedAt: timestamp('moderated_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }),
  moderatedBy: text('moderated_by').references(() => users.id),
  moderationMetadata: jsonb('moderation_metadata'), // Edit history, notes

  ...timestamps,
});

export type DetectedObject = typeof detectedObjects.$inferSelect;
export type InsertDetectedObject = typeof detectedObjects.$inferInsert;

export const marketplaceType = pgEnum('marketplace_type', [
  'amazon',
  'ebay',
  'etsy',
]);

export const availabilityStatus = pgEnum('availability_status', [
  'IN_STOCK',
  'SOLD_OUT',
  'DISCONTINUED',
]);

export const interestPledgeStatus = pgEnum('interest_pledge_status', [
  'ACTIVE',      // Waiting for notification
  'NOTIFIED',    // User has been notified
  'EXPIRED',     // Pledge expired (optional future use)
]);

export const linkStatusEnum = pgEnum('link_status', [
  'ACTIVE',      // Link is working (default)
  'CHECKING',    // Currently being checked by monitoring job
  'BROKEN',      // Link is broken (404, deleted product)
]);

export const bountyPledgeStatusEnum = pgEnum('bounty_pledge_status', [
  'ACTIVE',      // Pledge is active and waiting for item to be sourced
  'WITHDRAWN',   // User withdrew their pledge
  'FULFILLED',   // Item was sourced and user was notified
]);

export const marketplaceMatches = pgTable('marketplace_matches', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  objectId: text('object_id')
    .notNull()
    .references(() => detectedObjects.id, { onDelete: 'cascade' }),
  marketplace: marketplaceType('marketplace').notNull(),
  productId: text('product_id').notNull(), // ASIN, eBay Item ID, Etsy Listing ID
  productName: text('product_name').notNull(),
  price: real('price').notNull(), // USD
  availabilityStatus: availabilityStatus('availability_status').notNull(),
  affiliateUrl: text('affiliate_url').notNull(),
  imageUrl: text('image_url'),

  // Link Health Tracking (Story 5.3)
  linkStatus: linkStatusEnum('link_status').notNull().default('ACTIVE'),
  lastCheckedAt: timestamp('last_checked_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }),
  checkAttempts: integer('check_attempts').notNull().default(0),
  checkMetadata: jsonb('check_metadata'), // { httpStatus, errorMessage, checkedAt, headers }

  matchedAt: timestamp('matched_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }).notNull().defaultNow(),
  ...timestamps,
}, (table) => ({
  linkStatusIdx: index('marketplace_matches_link_status_idx').on(table.linkStatus),
  lastCheckedAtIdx: index('marketplace_matches_last_checked_at_idx').on(table.lastCheckedAt),
}));

export const productClicks = pgTable(
  'product_clicks',
  {
    id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
    marketplaceMatchId: text('marketplace_match_id')
      .notNull()
      .references(() => marketplaceMatches.id, { onDelete: 'cascade' }),
    clickedAt: timestamp('clicked_at', {
      mode: 'date',
      precision: 3,
      withTimezone: true,
    }).notNull().defaultNow(),
    viewerIp: text('viewer_ip'), // Anonymized for privacy (e.g., "192.168.1.0")
    userAgent: text('user_agent'),
    referrer: text('referrer'),
    ...timestamps,
  },
  (table) => ({
    marketplaceMatchIdIdx: index('product_clicks_marketplace_match_id_idx').on(
      table.marketplaceMatchId
    ),
    clickedAtIdx: index('product_clicks_clicked_at_idx').on(table.clickedAt),
  })
);

export const adminModeration = pgTable('admin_moderation', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  detectionId: text('detection_id')
    .notNull()
    .references(() => detectedObjects.id, { onDelete: 'cascade' }),
  adminId: text('admin_id')
    .notNull()
    .references(() => users.id),
  action: adminModerationType('action').notNull(),
  reasonCode: reasonCode('reason_code'),  // nullable — only set for 'marked_incorrect'
  originalValues: jsonb('original_values').notNull(),  // Snapshot before mutation
  correctedValues: jsonb('corrected_values'),          // What was changed to (null for mark_incorrect)
  trainAiFlag: boolean('train_ai_flag').notNull().default(true),  // Future: feed back to model
  ...timestamps,
});

export const interestPledges = pgTable('interest_pledges', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  marketplaceMatchId: text('marketplace_match_id')
    .references(() => marketplaceMatches.id, { onDelete: 'cascade' }),
  detectedObjectId: text('detected_object_id')
    .references(() => detectedObjects.id, { onDelete: 'cascade' }),
  emailHash: text('email_hash').notNull(), // SHA-256 hash for duplicate detection
  emailEncrypted: text('email_encrypted').notNull(), // AES-256 encrypted email
  status: interestPledgeStatus('status').notNull().default('ACTIVE'),

  // GDPR/CCPA Compliance Fields (Story 5.2)
  consentTimestamp: timestamp('consent_timestamp', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }).notNull().defaultNow(), // When user agreed to receive emails
  consentIp: text('consent_ip'), // Anonymized IP (e.g., "192.168.1.0")
  unsubscribeToken: text('unsubscribe_token').notNull().$defaultFn(() => crypto.randomUUID()), // For one-click unsubscribe
  consentMetadata: jsonb('consent_metadata'), // {userAgent, referrer, etc.}

  notifiedAt: timestamp('notified_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }),
  ...timestamps,
}, (table) => ({
  marketplaceMatchIdIdx: index('interest_pledges_marketplace_match_id_idx').on(
    table.marketplaceMatchId
  ),
  detectedObjectIdIdx: index('interest_pledges_detected_object_id_idx').on(
    table.detectedObjectId
  ),
  unsubscribeTokenIdx: uniqueIndex('interest_pledges_unsubscribe_token_idx').on(
    table.unsubscribeToken
  ),
}));

export const bountyPledges = pgTable('bounty_pledges', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),

  // Product reference (denormalized for easier querying)
  productId: text('product_id').notNull(),
  marketplaceMatchId: text('marketplace_match_id')
    .notNull()
    .references(() => marketplaceMatches.id, { onDelete: 'cascade' }),

  // Email and encryption (following Story 5.2 pattern)
  emailHash: text('email_hash').notNull(), // SHA-256 for deduplication
  encryptedEmail: text('encrypted_email').notNull(), // AES-256 encrypted

  // Pledge details
  pledgeAmount: real('pledge_amount').notNull(), // In USD
  currency: text('currency').notNull().default('USD'),
  status: bountyPledgeStatusEnum('status').notNull().default('ACTIVE'),

  // GDPR/CCPA compliance (NFR-4)
  consentTimestamp: timestamp('consent_timestamp', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }).notNull().defaultNow(),
  consentIp: text('consent_ip'), // Optional, for legal compliance
  unsubscribeToken: text('unsubscribe_token').notNull().$defaultFn(() => crypto.randomUUID()),

  // Notifications
  notifiedAt: timestamp('notified_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }),

  // Timestamps
  ...timestamps,
}, (table) => ({
  productIdIdx: index('bounty_pledges_product_id_idx').on(table.productId),
  statusIdx: index('bounty_pledges_status_idx').on(table.status),
  emailHashIdx: index('bounty_pledges_email_hash_idx').on(table.emailHash),
}));

// Revenue tracking for affiliate commissions (Story 6.1)
export const affiliateRevenue = pgTable('affiliate_revenue', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  marketplaceMatchId: text('marketplace_match_id')
    .notNull()
    .references(() => marketplaceMatches.id, { onDelete: 'cascade' }),
  amount: real('amount').notNull(), // Commission earned in USD
  recordedAt: timestamp('recorded_at', {
    mode: 'date',
    precision: 3,
    withTimezone: true,
  }).notNull().defaultNow(),
  orderId: text('order_id'), // Optional marketplace order ID
  ...timestamps,
}, (table) => ({
  marketplaceMatchIdIdx: index('affiliate_revenue_marketplace_match_id_idx').on(
    table.marketplaceMatchId
  ),
  recordedAtIdx: index('affiliate_revenue_recorded_at_idx').on(table.recordedAt),
}));

export type MarketplaceMatch = typeof marketplaceMatches.$inferSelect;
export type InsertMarketplaceMatch = typeof marketplaceMatches.$inferInsert;
export type ProductClick = typeof productClicks.$inferSelect;
export type InsertProductClick = typeof productClicks.$inferInsert;
export type AdminModerationRecord = typeof adminModeration.$inferSelect;
export type InsertAdminModerationRecord = typeof adminModeration.$inferInsert;
export type InterestPledge = typeof interestPledges.$inferSelect;
export type InsertInterestPledge = typeof interestPledges.$inferInsert;
export type BountyPledge = typeof bountyPledges.$inferSelect;
export type InsertBountyPledge = typeof bountyPledges.$inferInsert;
export type AffiliateRevenue = typeof affiliateRevenue.$inferSelect;
export type InsertAffiliateRevenue = typeof affiliateRevenue.$inferInsert;

export const requestStatus = pgEnum('request_status', [
  'PENDING',
  'FULFILLED', // Creator added the product
  'DISMISSED',
]);

export const affiliateSubmissionStatus = pgEnum('affiliate_submission_status', [
  'PENDING',    // Awaiting creator review
  'APPROVED',   // Creator accepted and added to Vault
  'REJECTED',   // Creator rejected
]);

export const productRequests = pgTable('product_requests', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  videoId: text('video_id')
    .notNull()
    .references(() => youtubeVideos.id, { onDelete: 'cascade' }),
  creatorId: text('creator_id')
    .notNull()
    .references(() => users.id),
  viewerName: text('viewer_name'),
  viewerEmail: text('viewer_email'),
  note: text('note').notNull(),
  imageUrl: text('image_url'), // Snapshot or upload
  frameTimestamp: integer('frame_timestamp'), // If request came from a specific point in video
  status: requestStatus('status').notNull().default('PENDING'),
  ...timestamps,
});

export const affiliateProposals = pgTable('affiliate_proposals', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  objectId: text('object_id').references(() => detectedObjects.id, { onDelete: 'set null' }),
  videoId: text('video_id').notNull().references(() => youtubeVideos.id, { onDelete: 'cascade' }),
  creatorId: text('creator_id').notNull().references(() => users.id),
  submitterName: text('submitter_name'),
  submitterEmail: text('submitter_email'),
  productUrl: text('product_url').notNull(),
  affiliateUrl: text('affiliate_url').notNull(),
  productName: text('product_name').notNull(),
  price: real('price'),
  imageUrl: text('image_url'),
  note: text('note'),
  status: affiliateSubmissionStatus('status').notNull().default('PENDING'),
  ...timestamps,
});

export type ProductRequest = typeof productRequests.$inferSelect;
export type InsertProductRequest = typeof productRequests.$inferInsert;

export const analysisNotifications = pgTable('analysis_notifications', {
  id: text('id').notNull().primaryKey().$defaultFn(() => crypto.randomUUID()),
  videoId: text('video_id')
    .notNull()
    .references(() => youtubeVideos.id, { onDelete: 'cascade' }),
  emailHash: text('email_hash').notNull(),
  emailEncrypted: text('email_encrypted').notNull(),
  status: text('status', { enum: ['PENDING', 'SENT'] }).notNull().default('PENDING'),
  unsubscribeToken: text('unsubscribe_token').notNull().$defaultFn(() => crypto.randomUUID()),
  ...timestamps,
});

export type AnalysisNotification = typeof analysisNotifications.$inferSelect;
export type InsertAnalysisNotification = typeof analysisNotifications.$inferInsert;

// Relations
import { relations } from 'drizzle-orm';

export const youtubeChannelsRelations = relations(youtubeChannels, ({ one, many }) => ({
  user: one(users, {
    fields: [youtubeChannels.creatorId],
    references: [users.id],
  }),
  videos: many(youtubeVideos),
}));

export const youtubeVideosRelations = relations(youtubeVideos, ({ one, many }) => ({
  channel: one(youtubeChannels, {
    fields: [youtubeVideos.channelId],
    references: [youtubeChannels.id],
  }),
  detections: many(detectedObjects),
  requests: many(productRequests),
  proposals: many(affiliateProposals),
  analysisNotifications: many(analysisNotifications),
}));

export const analysisNotificationsRelations = relations(analysisNotifications, ({ one }) => ({
  video: one(youtubeVideos, {
    fields: [analysisNotifications.videoId],
    references: [youtubeVideos.id],
  }),
}));

export const detectedObjectsRelations = relations(detectedObjects, ({ one, many }) => ({
  video: one(youtubeVideos, {
    fields: [detectedObjects.videoId],
    references: [youtubeVideos.id],
  }),
  marketplaceMatches: many(marketplaceMatches),
}));

export const marketplaceMatchesRelations = relations(marketplaceMatches, ({ one }) => ({
  object: one(detectedObjects, {
    fields: [marketplaceMatches.objectId],
    references: [detectedObjects.id],
  }),
}));

export const productRequestsRelations = relations(productRequests, ({ one }) => ({
  video: one(youtubeVideos, {
    fields: [productRequests.videoId],
    references: [youtubeVideos.id],
  }),
  creator: one(users, {
    fields: [productRequests.creatorId],
    references: [users.id],
  }),
}));

export const affiliateProposalsRelations = relations(affiliateProposals, ({ one }) => ({
  video: one(youtubeVideos, {
    fields: [affiliateProposals.videoId],
    references: [youtubeVideos.id],
  }),
  creator: one(users, {
    fields: [affiliateProposals.creatorId],
    references: [users.id],
  }),
  object: one(detectedObjects, {
    fields: [affiliateProposals.objectId],
    references: [detectedObjects.id],
  }),
}));