generator client { provider = "prisma-client-js" binaryTargets = ["native", "linux-musl-arm64-openssl-3.0.x"] output = "/home/ubuntu/greco_workflow_system/nextjs_space/node_modules/.prisma/client" } datasource db { provider = "postgresql" url = env("DATABASE_URL") } enum Role { ADMIN MANAGER AGENT } enum FileStatus { NEW IN_PROGRESS PAID DEFAULTED } enum PaymentMethod { CASH BANK_TRANSFER M_PESA CHEQUE } enum ActivityType { CALL NOTE EMAIL VISIT SMS OTHER } enum PromiseStatus { PENDING KEPT BROKEN RESCHEDULED } enum TaskStatus { PENDING COMPLETED CANCELLED } enum TaskPriority { LOW MEDIUM HIGH } enum SmsStatus { PENDING SENT DELIVERED FAILED } model User { id String @id @default(cuid()) email String @unique password String name String role Role @default(AGENT) isSuperAdmin Boolean @default(false) // Admin permissions (only applicable when role is ADMIN) canManageUsers Boolean @default(false) canManageSettings Boolean @default(false) canViewReports Boolean @default(true) canManageFiles Boolean @default(true) canManagePayments Boolean @default(true) canManageSms Boolean @default(true) canManageWorkflows Boolean @default(false) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt collectionFiles CollectionFile[] activities Activity[] promisesToPay PromiseToPay[] tasks Task[] @relation("TaskAssignee") createdTasks Task[] @relation("TaskCreator") smsLogs SmsLog[] accounts Account[] sessions Session[] agentTargets AgentTarget[] @relation("AgentTargets") createdTargets AgentTarget[] @relation("TargetsCreated") assignedBatches Batch[] @relation("BatchAssignee") } model Account { id String @id @default(cuid()) userId String type String provider String providerAccountId String refresh_token String? @db.Text access_token String? @db.Text expires_at Int? token_type String? scope String? id_token String? @db.Text session_state String? user User @relation(fields: [userId], references: [id], onDelete: Cascade) @@unique([provider, providerAccountId]) } model Session { id String @id @default(cuid()) sessionToken String @unique userId String expires DateTime user User @relation(fields: [userId], references: [id], onDelete: Cascade) } model VerificationToken { identifier String token String @unique expires DateTime @@unique([identifier, token]) } model CollectionFile { id String @id @default(cuid()) customerName String phoneNumber String idNumber String? alias String? appliedAmount Float @default(0) allocatedAmount Float @default(0) commission Float @default(0) vat Float @default(0) totalToCollect Float @default(0) outstandingBalance Float @default(0) assignedAgentId String? assignedAgent User? @relation(fields: [assignedAgentId], references: [id], onDelete: SetNull) batchId String? batch Batch? @relation(fields: [batchId], references: [id], onDelete: SetNull) dateOutsourced DateTime? status FileStatus @default(NEW) payments Payment[] activities Activity[] promisesToPay PromiseToPay[] smsLogs SmsLog[] tasks Task[] aiPredictions AIPrediction[] aiFeedback AIFeedback[] communications Communication[] inboundSms InboundSms[] mpesaTransactions MpesaTransaction[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([assignedAgentId]) @@index([batchId]) @@index([status]) @@index([customerName]) } model Payment { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) amount Float paymentDate DateTime paymentMethod PaymentMethod referenceNumber String? notes String? @db.Text mpesaTransaction MpesaTransaction? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([paymentDate]) } model Activity { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) type ActivityType description String @db.Text contactNumber String? outcome String? activityDate DateTime @default(now()) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([userId]) @@index([activityDate]) } model PromiseToPay { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) promisedAmount Float promisedDate DateTime status PromiseStatus @default(PENDING) notes String? @db.Text followUpDate DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([userId]) @@index([promisedDate]) @@index([status]) } model Batch { id String @id @default(cuid()) name String description String? @db.Text clientName String? clientId String? client Client? @relation(fields: [clientId], references: [id], onDelete: SetNull) assignedToId String? assignedTo User? @relation("BatchAssignee", fields: [assignedToId], references: [id], onDelete: SetNull) targetAmount Float @default(0) collectedAmount Float @default(0) startDate DateTime? endDate DateTime? isActive Boolean @default(true) collectionFiles CollectionFile[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([name]) @@index([isActive]) @@index([clientId]) @@index([assignedToId]) } model Task { id String @id @default(cuid()) title String description String? @db.Text collectionFileId String? collectionFile CollectionFile? @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) assigneeId String assignee User @relation("TaskAssignee", fields: [assigneeId], references: [id], onDelete: Cascade) createdById String createdBy User @relation("TaskCreator", fields: [createdById], references: [id], onDelete: Cascade) dueDate DateTime reminderDate DateTime? priority TaskPriority @default(MEDIUM) status TaskStatus @default(PENDING) completedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([assigneeId]) @@index([collectionFileId]) @@index([dueDate]) @@index([status]) } model SmsLog { id String @id @default(cuid()) collectionFileId String? collectionFile CollectionFile? @relation(fields: [collectionFileId], references: [id], onDelete: SetNull) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) phoneNumber String message String @db.Text provider String? messageId String? status SmsStatus @default(PENDING) errorMessage String? cost Float? sentAt DateTime @default(now()) deliveredAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([userId]) @@index([status]) @@index([sentAt]) } // Communication Hub Models enum CommunicationType { CALL_OUTBOUND CALL_INBOUND SMS_OUTBOUND SMS_INBOUND EMAIL_OUTBOUND EMAIL_INBOUND WHATSAPP } enum CommunicationStatus { PENDING COMPLETED FAILED NO_ANSWER BUSY VOICEMAIL } enum WorkflowTriggerType { PTP_REMINDER // Promise to Pay reminder OVERDUE_PAYMENT // Payment overdue NO_CONTACT // No contact in X days WELCOME // New file welcome message PAYMENT_RECEIVED // Thank you after payment CUSTOM // Custom trigger } enum WorkflowStatus { ACTIVE PAUSED DISABLED } model Communication { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) type CommunicationType direction String // INBOUND or OUTBOUND channel String // CALL, SMS, EMAIL, WHATSAPP phoneNumber String? emailAddress String? subject String? content String @db.Text status CommunicationStatus @default(COMPLETED) duration Int? // Call duration in seconds outcome String? // Connected, Voicemail, Busy, etc. callNotes String? @db.Text scheduledFollowUp DateTime? isAutomated Boolean @default(false) workflowId String? externalId String? // External message/call ID metadata String? @db.Text // JSON for extra data createdById String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([type]) @@index([channel]) @@index([createdAt]) @@index([isAutomated]) } model InboundSms { id String @id @default(cuid()) collectionFileId String? collectionFile CollectionFile? @relation(fields: [collectionFileId], references: [id], onDelete: SetNull) from String // Sender phone number to String // Our shortcode/number message String @db.Text messageId String? @unique isRead Boolean @default(false) isReplied Boolean @default(false) repliedAt DateTime? replyMessage String? @db.Text receivedAt DateTime @default(now()) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([from]) @@index([isRead]) @@index([receivedAt]) } model WorkflowTrigger { id String @id @default(cuid()) name String description String? @db.Text triggerType WorkflowTriggerType channel String // SMS, EMAIL, CALL conditions String @db.Text // JSON: { daysBefore: 1, daysAfter: 0, etc. } messageTemplate String @db.Text isActive Boolean @default(true) status WorkflowStatus @default(ACTIVE) runTime String? // HH:MM format for daily execution lastRunAt DateTime? executionCount Int @default(0) successCount Int @default(0) failureCount Int @default(0) createdById String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt executions WorkflowExecution[] @@index([triggerType]) @@index([isActive]) @@index([status]) } model WorkflowExecution { id String @id @default(cuid()) workflowId String workflow WorkflowTrigger @relation(fields: [workflowId], references: [id], onDelete: Cascade) collectionFileId String? targetPhone String? targetEmail String? channel String messageContent String @db.Text status String // PENDING, SENT, DELIVERED, FAILED errorMessage String? externalId String? // SMS/Email provider ID executedAt DateTime @default(now()) completedAt DateTime? createdAt DateTime @default(now()) @@index([workflowId]) @@index([collectionFileId]) @@index([status]) @@index([executedAt]) } // Payment Gateway Models (M-Pesa STK Push) enum MpesaTransactionStatus { PENDING // STK Push initiated, waiting for user PROCESSING // User entered PIN, processing COMPLETED // Payment successful FAILED // Payment failed CANCELLED // User cancelled/declined TIMEOUT // Request timed out } model MpesaTransaction { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) paymentId String? @unique payment Payment? @relation(fields: [paymentId], references: [id], onDelete: SetNull) // Request details phoneNumber String amount Float accountReference String // Reference shown on customer's phone transactionDesc String? // M-Pesa response fields merchantRequestId String? @unique checkoutRequestId String? @unique responseCode String? responseDescription String? customerMessage String? // Callback result fields mpesaReceiptNumber String? @unique transactionDate DateTime? resultCode String? resultDesc String? status MpesaTransactionStatus @default(PENDING) initiatedById String? retryCount Int @default(0) lastRetryAt DateTime? completedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([phoneNumber]) @@index([status]) @@index([merchantRequestId]) @@index([checkoutRequestId]) @@index([mpesaReceiptNumber]) @@index([createdAt]) } model MpesaCallback { id String @id @default(cuid()) merchantRequestId String checkoutRequestId String resultCode String resultDesc String amount Float? mpesaReceiptNumber String? balance String? transactionDate DateTime? phoneNumber String? rawPayload String @db.Text // Full callback JSON for debugging processedAt DateTime? createdAt DateTime @default(now()) @@index([merchantRequestId]) @@index([checkoutRequestId]) @@index([createdAt]) } // AI Prediction Models model AIPrediction { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) paymentProbability Float // 0-100 score riskLevel String // LOW, MEDIUM, HIGH optimalChannel String? // CALL, SMS, EMAIL optimalTimeSlot String? // MORNING, AFTERNOON, EVENING optimalDayOfWeek String? // MONDAY, TUESDAY, etc. nextBestAction String @db.Text keyInsights String @db.Text // JSON array of insights patterns String? @db.Text // JSON array of detected patterns confidence Float @default(0.7) modelVersion String @default("v1") expiresAt DateTime createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([collectionFileId]) @@index([paymentProbability]) @@index([riskLevel]) @@index([expiresAt]) } model AIFeedback { id String @id @default(cuid()) collectionFileId String collectionFile CollectionFile @relation(fields: [collectionFileId], references: [id], onDelete: Cascade) actionTaken String // CALL, SMS, VISIT, etc. actionOutcome String // SUCCESS, PARTIAL, FAILED, NO_RESPONSE predictedProbability Float? actualOutcome Boolean? // Did they pay within expected timeframe? amountCollected Float? responseTime Int? // Hours to response notes String? @db.Text createdAt DateTime @default(now()) @@index([collectionFileId]) @@index([actionTaken]) @@index([actionOutcome]) @@index([createdAt]) } model SystemSettings { id String @id @default(cuid()) category String // CALLS, SMS, EMAIL key String value String @db.Text description String? updatedBy String? updatedAt DateTime @updatedAt createdAt DateTime @default(now()) @@unique([category, key]) @@index([category]) } // Client Management (businesses who hired GRECO) model Client { id String @id @default(cuid()) name String email String @unique phone String? contactPerson String? address String? @db.Text isActive Boolean @default(true) // Daily streaming preferences dailyReportEnabled Boolean @default(true) dailyReportTime String @default("08:00") // HH:MM format dailyReportEmail Boolean @default(true) dailyReportSms Boolean @default(false) // Monthly report preferences monthlyReportEnabled Boolean @default(true) batches Batch[] reports Report[] dailyDigests DailyDigest[] reportSchedules ReportSchedule[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([name]) @@index([isActive]) } // Report Types Enum enum ReportType { MONTHLY_SUMMARY MONTHLY_ACCOUNTS MONTHLY_AGENTS MONTHLY_MANAGERS MONTHLY_ADMINS DAILY_DIGEST CUSTOM } enum ReportStatus { PENDING GENERATING COMPLETED FAILED SENT } // Generated Reports model Report { id String @id @default(cuid()) clientId String? client Client? @relation(fields: [clientId], references: [id], onDelete: SetNull) type ReportType title String description String? @db.Text // Report period periodStart DateTime periodEnd DateTime // Report data (JSON) data String @db.Text // File storage (if exported) fileUrl String? fileName String? status ReportStatus @default(PENDING) generatedAt DateTime? sentAt DateTime? sentTo String? @db.Text // JSON array of recipients createdById String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([clientId]) @@index([type]) @@index([status]) @@index([periodStart, periodEnd]) @@index([createdAt]) } // Report Schedules model ReportSchedule { id String @id @default(cuid()) clientId String? client Client? @relation(fields: [clientId], references: [id], onDelete: SetNull) reportType ReportType name String description String? @db.Text // Schedule settings isActive Boolean @default(true) frequency String // DAILY, WEEKLY, MONTHLY, LAST_DAY_OF_MONTH dayOfMonth Int? // 1-31 or null for last day dayOfWeek Int? // 0-6 (Sunday-Saturday) timeOfDay String @default("09:00") // HH:MM // Notification settings sendEmail Boolean @default(true) sendSms Boolean @default(false) emailRecipients String? @db.Text // JSON array smsRecipients String? @db.Text // JSON array lastRunAt DateTime? nextRunAt DateTime? runCount Int @default(0) createdById String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@index([clientId]) @@index([isActive]) @@index([frequency]) @@index([nextRunAt]) } // Daily Digest for Clients model DailyDigest { id String @id @default(cuid()) clientId String client Client @relation(fields: [clientId], references: [id], onDelete: Cascade) digestDate DateTime @db.Date // Summary metrics totalAccounts Int @default(0) activeAccounts Int @default(0) totalOutstanding Float @default(0) todayCollected Float @default(0) newPayments Int @default(0) newPromises Int @default(0) callsMade Int @default(0) smsCount Int @default(0) // Detailed data (JSON) accountsSummary String? @db.Text paymentsSummary String? @db.Text activitiesSummary String? @db.Text agentsSummary String? @db.Text // Delivery status emailDelivered Boolean @default(false) emailDeliveredAt DateTime? smsDelivered Boolean @default(false) smsDeliveredAt DateTime? exportedFile String? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@unique([clientId, digestDate]) @@index([clientId]) @@index([digestDate]) } // Agent Targets - Monthly targets set by Admin/Manager for each agent model AgentTarget { id String @id @default(cuid()) agentId String agent User @relation("AgentTargets", fields: [agentId], references: [id], onDelete: Cascade) // Target period month Int // 1-12 year Int // Collection targets targetAmount Float @default(0) // Target amount to collect targetFiles Int @default(0) // Number of files to resolve targetCalls Int @default(0) // Number of calls to make targetSms Int @default(0) // Number of SMS to send targetPaidFiles Int @default(0) // Number of files to get paid // Current progress (updated periodically) currentAmount Float @default(0) // Amount collected currentFiles Int @default(0) // Files resolved currentCalls Int @default(0) // Calls made currentSms Int @default(0) // SMS sent currentPaidFiles Int @default(0) // Files paid notes String? @db.Text createdById String? createdBy User? @relation("TargetsCreated", fields: [createdById], references: [id], onDelete: SetNull) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt @@unique([agentId, month, year]) @@index([agentId]) @@index([month, year]) }