schema.prisma

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

datasource db {
  provider = "postgresql"
}


model PriceHistory {
  id          Int       @id @default(autoincrement())
  currency    String    @db.VarChar(10)
  rate        Decimal   @db.Decimal(20, 10)
  bid         Decimal?  @db.Decimal(20, 10) // Buy price (optional)
  ask         Decimal?  @db.Decimal(20, 10) // Sell price (optional)
  source      String    @db.VarChar(100)
  timestamp   DateTime  @db.Timestamp(3)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  currencyRef Currency? @relation(fields: [currency], references: [code])

  @@index([currency, timestamp])
  @@index([timestamp])
  @@index([updatedAt])
  @@unique([currency, source, timestamp])
}

model Currency {
  code        String    @id @db.VarChar(10)  // e.g., "GHS", "KES"
  name        String    @db.VarChar(100)      // Full name e.g., "Ghanaian Cedi"
  symbol      String    @db.VarChar(5)        // Currency symbol e.g., "₵", "KES"
  decimals    Int       @default(2)           // Decimal places for display
  isActive    Boolean   @default(true)        // Whether this currency is currently supported
  
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  priceHistory PriceHistory[]

  @@index([symbol])
}

// On-Chain Confirmed Price Model
// Stores prices confirmed on Stellar via manageData or Soroban contract events
model OnChainPrice {
  id            Int       @id @default(autoincrement())
  currency      String    @db.VarChar(10)    // e.g., "NGN", "KES", "GHS"
  rate          Decimal   @db.Decimal(20, 10) // The confirmed price
  txHash        String    @db.VarChar(64)    // Stellar transaction hash
  memoId        String?   @db.VarChar(28)    // Memo ID from transaction (e.g., SF-NGN-1234567890-001)
  ledgerSeq     Int                          // Ledger sequence number
  confirmedAt   DateTime  @db.Timestamp(3)   // When the transaction was confirmed on-chain

  @@index([currency, confirmedAt])
  @@index([txHash])
  @@index([ledgerSeq])
  @@unique([txHash, currency])

  createdAt     DateTime  @default(now())
}

model ProviderReputation {
  id              Int       @id @default(autoincrement())
  providerName    String    @db.VarChar(100)  // e.g., "CoinGecko", "ExchangeRateAPI"
  endpoint        String?   @db.VarChar(255)  // Specific endpoint URL or identifier
  status          String    @db.VarChar(20)   // "online", "offline", "degraded"
  totalRequests   Int       @default(0)       // Total API calls made
  successfulRequests Int    @default(0)       // Successful responses
  failedRequests  Int       @default(0)       // Failed responses
  incorrectResponses Int   @default(0)        // Responses with invalid/incorrect data
  averageLatency  Float?    @db.DoublePrecision // Average response time in ms
  lastSuccess     DateTime?                    // Last successful response timestamp
  lastFailure     DateTime?                    // Last failed response timestamp
  lastIncorrect   DateTime?                    // Last incorrect response timestamp
  consecutiveFailures Int  @default(0)        // Current streak of consecutive failures
  consecutiveIncorrect Int @default(0)        // Current streak of consecutive incorrect responses
  
  // Reliability metrics
  reliabilityScore Float?   @db.DoublePrecision // Calculated score (0-100)
  lastUpdated     DateTime  @updatedAt
  
  createdAt       DateTime  @default(now())
  
  // Indexes for quick lookups
  @@index([providerName])
  @@index([status])
  @@index([reliabilityScore])
  @@unique([providerName, endpoint])
}

// Multi-Sig Price Approval Model
// Tracks price updates that require signatures from multiple oracle servers
model MultiSigPrice {
  id                Int       @id @default(autoincrement())
  priceReviewId     Int       // Reference to the price_review_records with contract_status tracking
  currency          String    @db.VarChar(10)   // e.g., "NGN", "KES", "GHS"
  rate              Decimal   @db.Decimal(20, 10) // The price to be updated
  source            String    @db.VarChar(100)  // Price source (e.g., "CoinGecko")
  
  // Multi-sig status
  status            String    @db.VarChar(20)   // "PENDING", "APPROVED", "REJECTED", "EXPIRED"
  requiredSignatures Int      @default(2)       // Number of signatures needed (typically 2)
  collectedSignatures Int     @default(0)       // Number of signatures collected so far
  
  // Stellar transaction details (set when fully signed)
  memoId            String?   @db.VarChar(28)   // Memo ID from transaction
  stellarTxHash     String?   @db.VarChar(64)   // Submitted transaction hash
  submittedAt       DateTime?                    // When transaction was submitted to Stellar
  
  // Timestamps
  requestedAt       DateTime  @default(now())   // When the multi-sig request was created
  expiresAt         DateTime                     // Expiration time for the signature request
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  
  @@index([currency, status, requestedAt])
  @@index([status, expiresAt])
  @@index([priceReviewId])

  multiSigSignatures MultiSigSignature[]
}

// Multi-Sig Signature Model
// Records individual signatures from oracle servers
model MultiSigSignature {
  id                Int       @id @default(autoincrement())
  multiSigPriceId   Int       // Reference to MultiSigPrice
  multiSigPrice     MultiSigPrice @relation(fields: [multiSigPriceId], references: [id], onDelete: Cascade)
  
  // Server/Signer information
  signerPublicKey   String    @db.VarChar(56)   // Stellar public key of the signer
  signerName        String    @db.VarChar(100)  // Human-readable name (e.g., "oracle-server-1")
  
  // Signature details
  signature         String    @db.Text          // The actual XDR signature (can be long)
  signedAt          DateTime  @default(now())   // When the signature was created
  
  createdAt         DateTime  @default(now())
  
  @@unique([multiSigPriceId, signerPublicKey])  // Prevent duplicate signatures from same signer
  @@index([multiSigPriceId])
  @@index([signerPublicKey])
}

// Error log table for tracking provider/fetcher failures
model ErrorLog {
  id           Int      @id @default(autoincrement())
  providerName String   @db.VarChar(200) // e.g., "CoinGecko", "KESRateFetcher"
  errorMessage String?  @db.Text
  occurredAt   DateTime @db.Timestamp(3)
  createdAt    DateTime @default(now())

  @@index([providerName])
  @@index([occurredAt])
}

model Relayer {
  id            Int      @id @default(autoincrement())
  name          String   @db.VarChar(100)
  apiKey        String   @unique @db.VarChar(255)
  isActive      Boolean  @default(true)
  allowedAssets String[]
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
}

// Hourly Statistics for smoothing out price spikes
model HourlyStats {
  id           Int      @id @default(autoincrement())
  currency     String   @db.VarChar(10)
  averageRate  Decimal  @db.Decimal(20, 10)
  hour         DateTime // The start of the hour this average represents
  createdAt    DateTime @default(now())

  @@unique([currency, hour])
  @@index([hour])
}