schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema


// Prisma client generator
generator client {
  provider = "prisma-client-js"
}


// Database connection configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id            String    @id @default(cuid())
  email         String    @unique // Unique index for fast user lookup by email
  walletAddress String?   @unique @map("wallet_address") // Unique index for wallet address
  role          UserRole  @default(USER)
  roleId        String?   @map("role_id")
  password      String?
  isVerified    Boolean   @default(false) @map("is_verified")
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")

  properties           Property[]
  receivedTransactions Transaction[] @relation("UserTransactions")
  userRole             Role?     @relation(fields: [roleId], references: [id], onDelete: SetNull)
  roleChanges          RoleChangeLog[]
  documents            Document[]

  // Indexes for common query patterns
  @@index([email]) // For searching users by email
  @@index([walletAddress]) // For searching users by wallet address
  @@index([role]) // For filtering users by role
  @@index([createdAt]) // For sorting/filtering by creation date
  // Consider adding an index on isVerified if you often filter by verification status
  @@index([isVerified])
  @@map("users")
}

model Property {
  id                String         @id @default(cuid())
  title             String
  description       String?
  location          String
  price             Decimal
  status            PropertyStatus @default(DRAFT)
  ownerId           String         @map("owner_id")
  createdAt         DateTime       @default(now()) @map("created_at")
  updatedAt         DateTime       @updatedAt @map("updated_at")

  // Valuation fields
  estimatedValue    Decimal?       @map("estimated_value")
  valuationDate     DateTime?      @map("valuation_date")
  valuationConfidence Float?        @map("valuation_confidence")
  valuationSource   String?        @map("valuation_source")
  lastValuationId   String?        @map("last_valuation_id")
  
  // Property features for valuation
  bedrooms          Int?
  bathrooms         Int?
  squareFootage     Decimal?       @map("square_footage")
  yearBuilt         Int?           @map("year_built")
  propertyType      String?        @map("property_type")
  lotSize           Decimal?       @map("lot_size")
  
  owner             User            @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  transactions      Transaction[]
  valuations        PropertyValuation[]
  documents         Document[]

  latitude   Float?
  longitude  Float?

  // Indexes for geospatial queries and filtering
  @@index([latitude, longitude]) // For location-based queries
  @@index([ownerId]) // For filtering properties by owner
  @@index([status]) // For filtering by property status
  @@index([createdAt]) // For sorting/filtering by creation date
  @@index([location]) // For searching/filtering by location
  // Consider adding an index on price if you often filter/sort by price
  @@index([price])
  @@map("properties")
}

model PropertyValuation {
  id              String   @id @default(cuid())
  propertyId      String   @map("property_id")
  estimatedValue  Decimal  @map("estimated_value")
  confidenceScore Float    @map("confidence_score")
  valuationDate   DateTime @map("valuation_date")
  source          String
  marketTrend     String?  @map("market_trend")
  featuresUsed    Json?    @map("features_used")
  rawData         Json?    @map("raw_data")
  createdAt       DateTime @default(now())
  
  property        Property @relation(fields: [propertyId], references: [id], onDelete: Cascade)

  // Index for fast lookup by property and date
  @@index([propertyId])
  @@index([valuationDate])
  @@map("property_valuations")
}

model Transaction {
  id            String            @id @default(cuid())
  fromAddress   String            @map("from_address")
  toAddress     String            @map("to_address")
  amount        Decimal
  txHash        String?           @map("tx_hash")
  status        TransactionStatus  @default(PENDING)
  type          TransactionType
  propertyId    String?           @map("property_id")
  createdAt     DateTime          @default(now()) @map("created_at")
  updatedAt     DateTime          @updatedAt @map("updated_at")

  buyerId           String
  sellerId          String
  currency          String
  blockchainHash    String? 
  blockNumber       Int?
  confirmations     Int      @default(0)
  escrowWallet      String?
  gasFee            Decimal?
  platformFee       Decimal?
  disputeReason     String?

  property  Property? @relation(fields: [propertyId], references: [id], onDelete: SetNull)
  recipient User?     @relation("UserTransactions", fields: [toAddress], references: [walletAddress])
  documents Document[]

  // Indexes for common transaction queries
  @@index([buyerId]) // For filtering by buyer
  @@index([sellerId]) // For filtering by seller
  @@index([fromAddress]) // For filtering by sender
  @@index([toAddress]) // For filtering by recipient
  @@index([status]) // For filtering by transaction status
  @@index([createdAt]) // For sorting/filtering by creation date
  @@index([propertyId]) // For filtering by property
  // Consider adding an index on txHash if you often search by transaction hash
  @@index([txHash])
  @@map("transactions")
}

model Role {
  id          String   @id @default(cuid())
  name        String   @unique
  description String?
  level       Int      @default(0)
  isSystem    Boolean  @default(false) @map("is_system")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  users           User[]
  permissions     RolePermission[]
  roleChangeLogs  RoleChangeLog[]

  @@map("roles")
}

model Permission {
  id          String   @id @default(cuid())
  resource    String
  action      String
  description String?
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  roles RolePermission[]

  @@unique([resource, action])
  @@map("permissions")
}

model RolePermission {
  id           String   @id @default(cuid())
  roleId       String   @map("role_id")
  permissionId String   @map("permission_id")
  createdAt    DateTime @default(now()) @map("created_at")

  role       Role       @relation(fields: [roleId], references: [id], onDelete: Cascade)
  permission Permission @relation(fields: [permissionId], references: [id], onDelete: Cascade)

  @@unique([roleId, permissionId])
  @@map("role_permissions")
}

model RoleChangeLog {
  id        String   @id @default(cuid())
  userId    String   @map("user_id")
  roleId    String   @map("role_id")
  oldRoleId String?  @map("old_role_id")
  changedBy String?  @map("changed_by")
  reason    String?
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  role Role @relation(fields: [roleId], references: [id], onDelete: Cascade)

  @@map("role_change_logs")
}

model AuditLog {
  id        String   @id @default(cuid())
  tableName String   @map("table_name")
  operation String
  oldData   Json?    @map("old_data")
  newData   Json?    @map("new_data")
  userId    String?  @map("user_id")
  timestamp DateTime @default(now())

  @@map("audit_logs")
}


model SystemLog {
  id        String   @id @default(cuid())
  logLevel  String   @map("log_level")
  message    String
  context    String?
  timestamp DateTime @default(now())

  @@map("system_logs")
}




enum UserRole {
  ADMIN
  AGENT
  SELLER
  BUYER
  VIEWER
  USER
  VERIFIED_USER
}

enum PropertyStatus {
  DRAFT
  PENDING
  APPROVED
  LISTED
  SOLD
  REMOVED
  PUBLISHED
}

enum TransactionStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  CANCELLED

  ESCROW_FUNDED
  BLOCKCHAIN_SUBMITTED
  CONFIRMING
  CONFIRMED
  DISPUTED
  REFUNDED
}

enum TransactionType {
  PURCHASE
  TRANSFER
  ESCROW
  REFUND
}

enum DocumentType {
  TITLE_DEED
  OWNERSHIP_CERTIFICATE
  INSPECTION_REPORT
  APPRAISAL
  INSURANCE
  TAX_DOCUMENT
  CONTRACT
  IDENTITY
  OTHER
}

enum DocumentStatus {
  PENDING
  VERIFIED
  REJECTED
  EXPIRED
}

model Document {
  id            String         @id @default(cuid())
  name          String
  type          DocumentType
  status        DocumentStatus @default(PENDING)
  fileUrl       String         @map("file_url")
  fileHash      String?        @map("file_hash")
  mimeType      String?        @map("mime_type")
  fileSize      Int?           @map("file_size")
  description   String?
  propertyId    String?        @map("property_id")
  transactionId String?        @map("transaction_id")
  uploadedById  String         @map("uploaded_by_id")
  verifiedAt    DateTime?      @map("verified_at")
  expiresAt     DateTime?      @map("expires_at")
  createdAt     DateTime       @default(now()) @map("created_at")
  updatedAt     DateTime       @updatedAt @map("updated_at")

  property    Property?    @relation(fields: [propertyId], references: [id], onDelete: SetNull)
  transaction Transaction? @relation(fields: [transactionId], references: [id], onDelete: SetNull)
  uploadedBy  User         @relation(fields: [uploadedById], references: [id], onDelete: Cascade)

  @@index([propertyId])
  @@index([transactionId])
  @@index([uploadedById])
  @@index([type])
  @@index([status])
  @@index([createdAt])
  @@map("documents")
}

model ApiKey {
  id           String   @id @default(cuid())
  name         String
  key          String   @unique
  keyPrefix    String   @map("key_prefix")
  scopes       String[]
  requestCount BigInt   @default(0) @map("request_count")
  lastUsedAt   DateTime? @map("last_used_at")
  isActive     Boolean  @default(true) @map("is_active")
  rateLimit    Int?     @map("rate_limit")
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @updatedAt @map("updated_at")

  @@index([keyPrefix])
  @@index([isActive])
  @@index([createdAt])
  @@map("api_keys")
}