schema.prisma

// =============================================================================
// Prisma Schema
//
// Defines the database connection, client generator, and data models.
// =============================================================================

datasource db {
  provider = "postgresql"
  // Connection URLs were moved to prisma.config.ts per Prisma v7+:
  // - Migrate connection URL and shadow DB URL should be configured in prisma.config.ts
  //schemas = ["bsc", "public"]
}

generator client {
  provider = "prisma-client-js"
  // previewFeatures = ["multiSchema"]
}

generator dbml {
  provider = "prisma-dbml-generator"
}

generator nestjsDto {
  provider                        = "prisma-generator-nestjs-dto"
  output                          = "../src/generated/nestjs-dto"
  outputToNestJsResourceStructure = "false"
  exportRelationModifierClasses   = "true"
  reExport                        = "false"
  createDtoPrefix                 = "Create"
  updateDtoPrefix                 = "Update"
  dtoSuffix                       = "Dto"
  entityPrefix                    = ""
  entitySuffix                    = ""
  fileNamingStyle                 = "camel"
}

// =============================================================================
// Models
// =============================================================================

/// Stores job listings from all sources (Reddit, Web3Career, etc).
/// Contains only the main, normalized/shared job fields.
model Job {
  id          Int       @id @default(autoincrement()) @map("id")
  title       String    @map("title") // Job title
  companyId   Int?      @map("company_id") // FK to company, nullable
  company     Company?  @relation(fields: [companyId], references: [id])
  author      String?   @map("author") // Author of the job post (if available)
  location    String?   @map("location") // Location string (if available)
  url         String    @unique @map("url") // Unique canonical job URL (from source)
  postedAt    DateTime? @map("posted_at") // Datetime when job was originally posted
  description String?   @map("description") // Main job description
  isRemote    Boolean?  @map("is_remote") // Is this a remote job?
  createdAt   DateTime  @default(now()) @map("created_at") // Record creation timestamp
  updatedAt   DateTime  @updatedAt @map("updated_at") // Auto-updated timestamp

  // Moved from JobSource
  source     String? @map("source")
  externalId String? @map("external_id")
  data       Json?   @map("data")

  tags     JobTag[] // Many-to-many relation to Tag via JobTag
  metadata JobMetadata[] // Arbitrary extra fields (key/value) for this job

  @@index([source, externalId], name: "jobs_source_external_id_idx")
  @@map("jobs")
}

/// Stores company information, normalized so jobs with the same company point here.
model Company {
  id        Int      @id @default(autoincrement()) @map("id")
  name      String   @unique @map("name") // Company name (unique)
  jobs      Job[] // All jobs for this company
  createdAt DateTime @default(now()) @map("created_at") // Record creation timestamp

  @@map("companies")
}

/// Stores unique tags (skills, role types, etc).
/// Used for jobs and supports future polymorphic tagging.
model Tag {
  id        Int        @id @default(autoincrement()) @map("id")
  name      String     @unique @map("name") // Tag name (unique)
  jobTags   JobTag[] // Jobs with this tag (many-to-many)
  taggables Taggable[] // For polymorphic tagging (future use)

  @@map("tags")
}

/// Join table for many-to-many Job <-> Tag relationships.
/// Each row links a job to a tag.
model JobTag {
  id    Int @id @default(autoincrement()) @map("id")
  jobId Int @map("job_id") // FK to Job
  tagId Int @map("tag_id") // FK to Tag
  job   Job @relation(fields: [jobId], references: [id])
  tag   Tag @relation(fields: [tagId], references: [id])

  @@unique([jobId, tagId])
  @@map("job_tags")
}

/// Stores arbitrary name/value metadata for jobs.
/// Allows storage of extra source-specific or custom fields.
model JobMetadata {
  id        Int      @id @default(autoincrement())
  jobId     Int
  name      String
  value     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  job Job @relation(fields: [jobId], references: [id])

  @@unique([jobId, name])
  @@map("job_metadata")
}

/// Enables polymorphic tagging (linking tags to any model, not just jobs).
/// For future extensibility; currently jobs use JobTag directly.
model Taggable {
  id         Int      @id @default(autoincrement()) @map("id")
  tagId      Int      @map("tag_id") // FK to Tag
  tagType    String   @map("tag_type") // E.g. "job", "company", etc.
  taggableId Int      @map("taggable_id") // ID of the tagged record (in its own table)
  tag        Tag      @relation(fields: [tagId], references: [id])
  createdAt  DateTime @default(now()) @map("created_at")

  @@index([tagType, taggableId])
  @@map("taggables")
}

/// Stores posts scraped from Reddit.
model RedditPost {
  // Fields
  id        Int     @id @default(autoincrement())
  title     String  @db.VarChar(350)
  author    String
  subreddit String
  url       String  @unique @db.VarChar(350)
  type      String  @default("reddit") @db.VarChar(50)
  body      String? @db.Text
  bodyHtml  String? @map("body_html") @db.Text
  upvotes   Int     @default(0)
  downvotes Int     @default(0)

  // Timestamps
  createdAt DateTime @default(now()) @map("created_at")
  postedAt  DateTime @map("posted_at") /// The original creation time on Reddit.

  @@index([subreddit]) // For filtering by subreddit
  @@index([author]) // For finding posts by author
  @@index([postedAt]) // For sorting by original post date
  // Indexes & Mapping
  @@map("reddit_posts")
}

/// Defines the types of push notification subscriptions.
enum SubscriptionType {
  web // Web Push Protocol (browsers)
  fcm // Firebase Cloud Messaging (native apps)
}

/// Stores user subscriptions for push notifications.
model Subscription {
  // Fields
  id        Int              @id @default(autoincrement())
  ipAddress String           @map("ip_address") @db.VarChar(150)
  type      SubscriptionType @default(web)
  endpoint  String           @unique @db.VarChar(350)
  keys      Json? /// Auth keys required for sending push notifications.

  // Timestamps
  createdAt DateTime @default(now()) @map("created_at")

  // Relations
  locations Location[]
  // Note: Querying the `keys` JSON field can be slow at scale on MySQL.

  @@unique([endpoint, type, keys], name: "endpoint_type_keys") // Prevents duplicate subscriptions.
  // Indexes & Mapping
  @@map("subscriptions")
}

enum Role {
  ADMIN
  USER

  //@@schema("bsc")
}

// [ USERS ]
// Users are individual Web3 wallets that have logged in by signing a Message in order to get a valid nonce authenticated login session. 
// Note: We may want to have a seperate users table later & allow users to sign in with or without a wallet, and also allow them to have multiple Web3 wallets associated to them.
// Relationships:
//   - One-to-Many: User->hasMany(Positions):  - A User may have zero or more Positions that belong to it . [Optional]
//   - One-to-Many: User->hasMany(Swaps):  - A User may have zero or more Swaps that belong to it . [Optional]
//   - One-to-Many: User->hasMany(Trades):  - A User may have zero or more Trades that belong to it . [Optional]
//   - One-to-Many: User->hasMany(CopyWallet): - A User may have zero or more CopyWallets that belong to it . [Optional]
model User {
  id             Int      @id @default(autoincrement()) @map("id")
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  email          String   @unique //depracated
  password       String //depracated
  firstname      String? //depracated
  lastname       String? //depracated
  username       String? // Google display name (full name)
  profilePicture String?  @map("profile_picture") // Google profile picture URL
  googleId       String?  @unique @map("google_id") // Google's unique identifier

  role      Role     @default(USER) @map("role")
  wallet    String?  @unique @db.VarChar(42)
  is_active Boolean? @default(true)
  // @@schema("public")

  @@index([wallet, is_active], map: "active_user")
  @@index([googleId], map: "google_user")
  @@map("users")
}

/// Stores location data points for subscriptions.
model Location {
  // Fields
  id               Int     @id @default(autoincrement())
  ipAddress        String  @map("ip_address") @db.VarChar(150)
  accuracy         Float?
  altitude         Float?
  altitudeAccuracy Float?  @map("altitude_accuracy")
  heading          Float?
  latitude         Float?
  longitude        Float?
  speed            Float?
  mocked           Boolean @map("mocked") /// True if location was from a mock provider.
  timestamp        BigInt? @map("timestamp") /// Original Unix timestamp from the client.

  // Geocoded Address Fields
  city             String? @db.VarChar(150)
  country          String? @db.VarChar(150)
  district         String? @db.VarChar(150)
  formattedAddress String? @map("formatted_address") @db.Text
  isoCountryCode   String? @map("iso_country_code") @db.VarChar(10)
  name             String? @db.VarChar(150)
  postalCode       String? @map("postal_code") @db.VarChar(20)
  region           String? @db.VarChar(150)
  street           String? @db.VarChar(150)
  streetNumber     String? @map("street_number") @db.VarChar(50)
  subregion        String? @db.VarChar(150)
  timezone         String? @db.VarChar(100)

  // Timestamps
  createdAt DateTime @default(now()) @map("created_at")

  // Relations
  subscriptionId Int
  subscription   Subscription @relation(fields: [subscriptionId], references: [id], onDelete: Cascade)

  @@index([subscriptionId]) // To quickly find all locations for a subscription.
  @@index([timestamp]) // To filter or sort by time.
  @@index([latitude, longitude]) // For basic geographic queries.
  // Indexes & Mapping
  @@map("locations")
}

/// Stores messages received from Reddit (comments, DMs, etc.).
model RedditMessage {
  // Fields
  id                 Int     @id @default(autoincrement())
  redditId           String  @unique @map("reddit_id") /// The unique ID from the Reddit API.
  type               String /// e.g., "comment", "post", "dm".
  author             String
  content            String  @db.Text
  bodyHtml           String? @map("body_html") @db.Text
  subreddit          String?
  contextUrl         String? @map("context_url")
  parentId           String? @map("parent_id") /// For message threading.
  messageType        String? @map("message_type") /// e.g., "inbox", "sent", "modmail".
  isRead             Boolean @default(false) @map("is_read")
  isSubredditModMail Boolean @default(false) @map("is_subreddit_mod_mail")
  isInternal         Boolean @default(false) @map("is_internal") /// For mod-to-mod discussions.
  rawData            Json?   @map("raw_data") /// Stores the original API response.

  // Timestamps
  createdAt  DateTime @map("created_at") /// The original creation time on Reddit.
  receivedAt DateTime @default(now()) @map("received_at")

  @@index([author])
  @@index([createdAt])
  @@index([isRead])
  @@index([subreddit])
  @@index([parentId]) // Essential for building threaded views efficiently.
  // Indexes & Mapping
  @@map("reddit_messages")
}

/// Stores error reports sent from client applications.
model ErrorReport {
  // Fields
  id        String   @id @default(cuid())
  message   String   @db.Text
  stack     String?  @db.Text
  platform  String? /// 'android', 'ios', 'web', etc.
  isFatal   Boolean? /// True if the error crashed the client app.
  errorInfo Json? /// React Error Boundary info, like componentStack.
  payload   Json /// The complete, raw JSON payload from the client.

  // Timestamps
  createdAt DateTime @default(now())

  @@index([createdAt])
  @@index([platform])
  @@index([isFatal])
  // THE FIX: Removed the MySQL-specific '(length: 191)' from these two lines.
  @@index([message])
  @@index([stack])
  // Indexes & Mapping
  @@map("error_reports")
}