f31d71ff5a
- Added Docker support (Dockerfiles, docker-compose.yml) - Fixed authentication and authorization (token storage, CORS, permissions) - Fixed API response transformations for all modules - Added production deployment scripts and guides - Fixed frontend permission checks and module access - Added database seeding script for production - Complete documentation for deployment and configuration Co-authored-by: Cursor <cursoragent@cursor.com>
1343 lines
36 KiB
Plaintext
1343 lines
36 KiB
Plaintext
// Z.CRM - Complete Database Schema
|
|
// مجموعة أتمتة - نظام إدارة شامل
|
|
|
|
generator client {
|
|
provider = "prisma-client-js"
|
|
binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
|
|
}
|
|
|
|
datasource db {
|
|
provider = "postgresql"
|
|
url = env("DATABASE_URL")
|
|
}
|
|
|
|
// ============================================
|
|
// CORE MODELS - الموديولات الأساسية
|
|
// ============================================
|
|
|
|
// Audit Log - سجل التدقيق
|
|
model AuditLog {
|
|
id String @id @default(uuid())
|
|
entityType String // Contact, Deal, Invoice, etc.
|
|
entityId String
|
|
action String // CREATE, UPDATE, DELETE, MERGE, APPROVE, etc.
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id])
|
|
changes Json? // Before/After data
|
|
ipAddress String?
|
|
userAgent String?
|
|
reason String? // Required for sensitive operations
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([entityType, entityId])
|
|
@@index([userId])
|
|
@@index([createdAt])
|
|
@@map("audit_logs")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 5: HR MANAGEMENT (BASE MODULE)
|
|
// Must be defined first as it controls all access
|
|
// ============================================
|
|
|
|
model User {
|
|
id String @id @default(uuid())
|
|
email String @unique
|
|
username String @unique
|
|
password String
|
|
isActive Boolean @default(true)
|
|
lastLogin DateTime?
|
|
failedLoginAttempts Int @default(0)
|
|
lockedUntil DateTime?
|
|
|
|
// Link to Employee
|
|
employeeId String? @unique
|
|
employee Employee? @relation(fields: [employeeId], references: [id])
|
|
|
|
// Security
|
|
refreshToken String?
|
|
passwordResetToken String?
|
|
passwordResetExpires DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
auditLogs AuditLog[]
|
|
createdContacts Contact[] @relation("CreatedByUser")
|
|
ownedDeals Deal[]
|
|
assignedTasks Task[]
|
|
projectMembers ProjectMember[]
|
|
campaigns Campaign[]
|
|
|
|
@@map("users")
|
|
}
|
|
|
|
model Employee {
|
|
id String @id @default(uuid())
|
|
uniqueEmployeeId String @unique // رقم الموظف الموحد
|
|
|
|
// Personal Info
|
|
firstName String
|
|
lastName String
|
|
firstNameAr String?
|
|
lastNameAr String?
|
|
email String @unique
|
|
phone String?
|
|
mobile String
|
|
dateOfBirth DateTime?
|
|
gender String?
|
|
nationality String?
|
|
nationalId String? @unique
|
|
passportNumber String?
|
|
|
|
// Employment Info
|
|
employmentType String // Full-time, Part-time, Contract
|
|
contractType String? // Fixed, Unlimited
|
|
hireDate DateTime
|
|
endDate DateTime?
|
|
probationEndDate DateTime?
|
|
|
|
// Position & Structure
|
|
departmentId String
|
|
department Department @relation(fields: [departmentId], references: [id])
|
|
positionId String
|
|
position Position @relation(fields: [positionId], references: [id])
|
|
reportingToId String?
|
|
reportingTo Employee? @relation("ReportsTo", fields: [reportingToId], references: [id])
|
|
directReports Employee[] @relation("ReportsTo")
|
|
|
|
// Compensation
|
|
basicSalary Decimal @db.Decimal(12, 2)
|
|
currency String @default("SAR")
|
|
|
|
// Status
|
|
status String @default("ACTIVE") // ACTIVE, ON_LEAVE, SUSPENDED, TERMINATED
|
|
terminationDate DateTime?
|
|
terminationReason String?
|
|
|
|
// Emergency Contact
|
|
emergencyContactName String?
|
|
emergencyContactPhone String?
|
|
emergencyContactRelation String?
|
|
|
|
// Address
|
|
address String?
|
|
city String?
|
|
country String?
|
|
|
|
// Documents
|
|
documents Json? // Array of document references
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
user User?
|
|
attendances Attendance[]
|
|
leaves Leave[]
|
|
salaries Salary[]
|
|
evaluations PerformanceEvaluation[]
|
|
trainings EmployeeTraining[]
|
|
disciplinaryActions DisciplinaryAction[]
|
|
allowances Allowance[]
|
|
commissions Commission[]
|
|
|
|
@@index([departmentId])
|
|
@@index([positionId])
|
|
@@index([status])
|
|
@@map("employees")
|
|
}
|
|
|
|
model Department {
|
|
id String @id @default(uuid())
|
|
name String
|
|
nameAr String?
|
|
code String @unique
|
|
parentId String?
|
|
parent Department? @relation("DepartmentHierarchy", fields: [parentId], references: [id])
|
|
children Department[] @relation("DepartmentHierarchy")
|
|
description String?
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
employees Employee[]
|
|
positions Position[]
|
|
|
|
@@map("departments")
|
|
}
|
|
|
|
model Position {
|
|
id String @id @default(uuid())
|
|
title String
|
|
titleAr String?
|
|
code String @unique
|
|
departmentId String
|
|
department Department @relation(fields: [departmentId], references: [id])
|
|
level Int // Management level
|
|
description String?
|
|
responsibilities Json?
|
|
requirements Json?
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
employees Employee[]
|
|
permissions PositionPermission[]
|
|
|
|
@@index([departmentId])
|
|
@@map("positions")
|
|
}
|
|
|
|
model PositionPermission {
|
|
id String @id @default(uuid())
|
|
positionId String
|
|
position Position @relation(fields: [positionId], references: [id])
|
|
module String // contacts, crm, inventory, etc.
|
|
resource String // contacts, deals, quotes, etc.
|
|
actions Json // [create, read, update, delete, approve, etc.]
|
|
conditions Json? // Additional conditions
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([positionId, module, resource])
|
|
@@map("position_permissions")
|
|
}
|
|
|
|
model Attendance {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
date DateTime @db.Date
|
|
checkIn DateTime?
|
|
checkOut DateTime?
|
|
workHours Decimal? @db.Decimal(5, 2)
|
|
overtimeHours Decimal? @db.Decimal(5, 2)
|
|
status String // PRESENT, ABSENT, LATE, HALF_DAY, etc.
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([employeeId, date])
|
|
@@index([employeeId])
|
|
@@index([date])
|
|
@@map("attendances")
|
|
}
|
|
|
|
model Leave {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
leaveType String // ANNUAL, SICK, UNPAID, EMERGENCY, etc.
|
|
startDate DateTime @db.Date
|
|
endDate DateTime @db.Date
|
|
days Int
|
|
reason String?
|
|
status String @default("PENDING") // PENDING, APPROVED, REJECTED
|
|
approvedBy String?
|
|
approvedAt DateTime?
|
|
rejectedReason String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@index([status])
|
|
@@map("leaves")
|
|
}
|
|
|
|
model Salary {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
month Int
|
|
year Int
|
|
basicSalary Decimal @db.Decimal(12, 2)
|
|
allowances Decimal @db.Decimal(12, 2)
|
|
deductions Decimal @db.Decimal(12, 2)
|
|
commissions Decimal @db.Decimal(12, 2)
|
|
overtimePay Decimal @db.Decimal(12, 2)
|
|
netSalary Decimal @db.Decimal(12, 2)
|
|
status String @default("PENDING") // PENDING, APPROVED, PAID
|
|
paidDate DateTime?
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([employeeId, month, year])
|
|
@@index([employeeId])
|
|
@@map("salaries")
|
|
}
|
|
|
|
model Allowance {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
type String // HOUSING, TRANSPORT, FOOD, etc.
|
|
amount Decimal @db.Decimal(12, 2)
|
|
isRecurring Boolean @default(false)
|
|
startDate DateTime @db.Date
|
|
endDate DateTime? @db.Date
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@map("allowances")
|
|
}
|
|
|
|
model Commission {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
dealId String?
|
|
deal Deal? @relation(fields: [dealId], references: [id])
|
|
amount Decimal @db.Decimal(12, 2)
|
|
percentage Decimal? @db.Decimal(5, 2)
|
|
month Int
|
|
year Int
|
|
status String @default("PENDING") // PENDING, APPROVED, PAID
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@index([dealId])
|
|
@@map("commissions")
|
|
}
|
|
|
|
model PerformanceEvaluation {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
evaluationPeriod String // Q1-2024, 2024, etc.
|
|
kpis Json // Array of KPI scores
|
|
overallScore Decimal @db.Decimal(5, 2)
|
|
comments String?
|
|
evaluatedBy String
|
|
evaluatedAt DateTime
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@map("performance_evaluations")
|
|
}
|
|
|
|
model EmployeeTraining {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
trainingName String
|
|
trainingType String // INTERNAL, EXTERNAL, ONLINE
|
|
provider String?
|
|
startDate DateTime @db.Date
|
|
endDate DateTime @db.Date
|
|
cost Decimal? @db.Decimal(12, 2)
|
|
status String @default("PLANNED") // PLANNED, COMPLETED, CANCELLED
|
|
certificate String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@map("employee_trainings")
|
|
}
|
|
|
|
model DisciplinaryAction {
|
|
id String @id @default(uuid())
|
|
employeeId String
|
|
employee Employee @relation(fields: [employeeId], references: [id])
|
|
type String // WARNING, SUSPENSION, TERMINATION
|
|
reason String
|
|
description String
|
|
actionDate DateTime @db.Date
|
|
issuedBy String
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([employeeId])
|
|
@@map("disciplinary_actions")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 1: CONTACT MANAGEMENT
|
|
// ============================================
|
|
|
|
model Contact {
|
|
id String @id @default(uuid())
|
|
uniqueContactId String @unique // رقم تعريفي موحد
|
|
|
|
// Basic Info
|
|
type String // INDIVIDUAL, COMPANY, HOLDING, GOVERNMENT
|
|
name String
|
|
nameAr String?
|
|
email String?
|
|
phone String?
|
|
mobile String?
|
|
website String?
|
|
|
|
// Company/Entity Info
|
|
companyName String?
|
|
companyNameAr String?
|
|
taxNumber String? @unique
|
|
commercialRegister String? @unique
|
|
|
|
// Address
|
|
address String?
|
|
city String?
|
|
country String?
|
|
postalCode String?
|
|
|
|
// Classification
|
|
categories ContactCategory[]
|
|
tags String[]
|
|
|
|
// Hierarchy - for companies/entities
|
|
parentId String?
|
|
parent Contact? @relation("ContactHierarchy", fields: [parentId], references: [id])
|
|
children Contact[] @relation("ContactHierarchy")
|
|
|
|
// Relationship
|
|
primaryContactId String? // For individuals in companies
|
|
|
|
// Source & Status
|
|
source String // EXHIBITION, VISIT, CALL, WEBSITE, REFERRAL, etc.
|
|
status String @default("ACTIVE") // ACTIVE, INACTIVE, ARCHIVED, BLOCKED
|
|
|
|
// Rating & Scoring
|
|
rating Int? @db.SmallInt // Manual rating 1-5
|
|
score Decimal? @db.Decimal(5, 2) // Auto scoring
|
|
|
|
// Custom Fields
|
|
customFields Json?
|
|
|
|
// Metadata
|
|
createdById String
|
|
createdBy User @relation("CreatedByUser", fields: [createdById], references: [id])
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
archivedAt DateTime?
|
|
|
|
// Relations
|
|
relationships ContactRelationship[] @relation("FromContact")
|
|
relatedTo ContactRelationship[] @relation("ToContact")
|
|
activities Activity[]
|
|
deals Deal[]
|
|
attachments Attachment[]
|
|
notes Note[]
|
|
|
|
@@index([type])
|
|
@@index([status])
|
|
@@index([email])
|
|
@@index([phone])
|
|
@@index([mobile])
|
|
@@index([taxNumber])
|
|
@@index([commercialRegister])
|
|
@@map("contacts")
|
|
}
|
|
|
|
model ContactCategory {
|
|
id String @id @default(uuid())
|
|
name String
|
|
nameAr String?
|
|
parentId String?
|
|
parent ContactCategory? @relation("CategoryHierarchy", fields: [parentId], references: [id])
|
|
children ContactCategory[] @relation("CategoryHierarchy")
|
|
description String?
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
contacts Contact[]
|
|
|
|
@@map("contact_categories")
|
|
}
|
|
|
|
model ContactRelationship {
|
|
id String @id @default(uuid())
|
|
fromContactId String
|
|
fromContact Contact @relation("FromContact", fields: [fromContactId], references: [id])
|
|
toContactId String
|
|
toContact Contact @relation("ToContact", fields: [toContactId], references: [id])
|
|
type String // REPRESENTATIVE, PARTNER, SUPPLIER, EMPLOYEE, etc.
|
|
startDate DateTime @db.Date
|
|
endDate DateTime? @db.Date
|
|
isActive Boolean @default(true)
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([fromContactId, toContactId, type])
|
|
@@index([fromContactId])
|
|
@@index([toContactId])
|
|
@@map("contact_relationships")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 2: CRM
|
|
// ============================================
|
|
|
|
model Deal {
|
|
id String @id @default(uuid())
|
|
dealNumber String @unique
|
|
name String
|
|
|
|
// Contact & Type
|
|
contactId String
|
|
contact Contact @relation(fields: [contactId], references: [id])
|
|
structure String // B2B, B2C, B2G, PARTNERSHIP
|
|
|
|
// Pipeline & Stage
|
|
pipelineId String
|
|
pipeline Pipeline @relation(fields: [pipelineId], references: [id])
|
|
stage String // OPEN, NEGOTIATION, PENDING_INTERNAL, PENDING_CLIENT, WON, LOST, ON_HOLD
|
|
|
|
// Values
|
|
estimatedValue Decimal @db.Decimal(15, 2)
|
|
actualValue Decimal? @db.Decimal(15, 2)
|
|
currency String @default("SAR")
|
|
probability Int? @db.SmallInt // 0-100
|
|
|
|
// Dates
|
|
expectedCloseDate DateTime? @db.Date
|
|
actualCloseDate DateTime? @db.Date
|
|
|
|
// Assignment
|
|
ownerId String
|
|
owner User @relation(fields: [ownerId], references: [id])
|
|
|
|
// Win/Loss
|
|
wonReason String?
|
|
lostReason String?
|
|
|
|
// Fiscal Year
|
|
fiscalYear Int
|
|
|
|
// Status
|
|
status String @default("ACTIVE") // ACTIVE, WON, LOST, CANCELLED, ARCHIVED
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
quotes Quote[]
|
|
costSheets CostSheet[]
|
|
activities Activity[]
|
|
attachments Attachment[]
|
|
notes Note[]
|
|
contracts Contract[]
|
|
invoices Invoice[]
|
|
commissions Commission[]
|
|
|
|
@@index([contactId])
|
|
@@index([ownerId])
|
|
@@index([pipelineId])
|
|
@@index([stage])
|
|
@@index([status])
|
|
@@index([fiscalYear])
|
|
@@map("deals")
|
|
}
|
|
|
|
model Pipeline {
|
|
id String @id @default(uuid())
|
|
name String
|
|
nameAr String?
|
|
structure String // B2B, B2C, B2G, PARTNERSHIP
|
|
stages Json // Array of stage definitions
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
deals Deal[]
|
|
|
|
@@map("pipelines")
|
|
}
|
|
|
|
model Quote {
|
|
id String @id @default(uuid())
|
|
quoteNumber String @unique
|
|
dealId String
|
|
deal Deal @relation(fields: [dealId], references: [id])
|
|
version Int @default(1)
|
|
|
|
// Items
|
|
items Json // Array of quote items
|
|
|
|
// Pricing
|
|
subtotal Decimal @db.Decimal(15, 2)
|
|
discountType String? // PERCENTAGE, FIXED
|
|
discountValue Decimal? @db.Decimal(15, 2)
|
|
taxRate Decimal @db.Decimal(5, 2)
|
|
taxAmount Decimal @db.Decimal(15, 2)
|
|
total Decimal @db.Decimal(15, 2)
|
|
|
|
// Terms
|
|
validUntil DateTime @db.Date
|
|
paymentTerms String?
|
|
deliveryTerms String?
|
|
notes String?
|
|
|
|
// Status & Approval
|
|
status String @default("DRAFT") // DRAFT, SENT, VIEWED, APPROVED, REJECTED
|
|
sentAt DateTime?
|
|
viewedAt DateTime?
|
|
approvedBy String?
|
|
approvedAt DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([dealId])
|
|
@@map("quotes")
|
|
}
|
|
|
|
model CostSheet {
|
|
id String @id @default(uuid())
|
|
costSheetNumber String @unique
|
|
dealId String
|
|
deal Deal @relation(fields: [dealId], references: [id])
|
|
version Int @default(1)
|
|
|
|
// Cost Items
|
|
items Json // Array of cost items with sources
|
|
|
|
// Totals
|
|
totalCost Decimal @db.Decimal(15, 2)
|
|
suggestedPrice Decimal @db.Decimal(15, 2)
|
|
profitMargin Decimal @db.Decimal(5, 2)
|
|
|
|
// Approval
|
|
status String @default("DRAFT") // DRAFT, APPROVED, REJECTED
|
|
approvedBy String?
|
|
approvedAt DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([dealId])
|
|
@@map("cost_sheets")
|
|
}
|
|
|
|
model Contract {
|
|
id String @id @default(uuid())
|
|
contractNumber String @unique
|
|
dealId String
|
|
deal Deal @relation(fields: [dealId], references: [id])
|
|
version Int @default(1)
|
|
|
|
title String
|
|
type String // SALES, SERVICE, MAINTENANCE, etc.
|
|
|
|
// Parties
|
|
clientInfo Json
|
|
companyInfo Json
|
|
|
|
// Terms
|
|
startDate DateTime @db.Date
|
|
endDate DateTime? @db.Date
|
|
value Decimal @db.Decimal(15, 2)
|
|
paymentTerms Json
|
|
deliveryTerms Json
|
|
terms String
|
|
|
|
// Status
|
|
status String @default("DRAFT") // DRAFT, PENDING_SIGNATURE, ACTIVE, EXPIRED, TERMINATED
|
|
signedAt DateTime?
|
|
|
|
// Documents
|
|
documentUrl String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([dealId])
|
|
@@map("contracts")
|
|
}
|
|
|
|
model Invoice {
|
|
id String @id @default(uuid())
|
|
invoiceNumber String @unique
|
|
dealId String?
|
|
deal Deal? @relation(fields: [dealId], references: [id])
|
|
|
|
// Items
|
|
items Json
|
|
|
|
// Pricing
|
|
subtotal Decimal @db.Decimal(15, 2)
|
|
taxAmount Decimal @db.Decimal(15, 2)
|
|
total Decimal @db.Decimal(15, 2)
|
|
|
|
// Payment
|
|
status String @default("DRAFT") // DRAFT, SENT, PAID, OVERDUE, CANCELLED
|
|
dueDate DateTime @db.Date
|
|
paidDate DateTime?
|
|
paidAmount Decimal? @db.Decimal(15, 2)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([dealId])
|
|
@@index([status])
|
|
@@map("invoices")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 3: INVENTORY & ASSETS
|
|
// ============================================
|
|
|
|
model Warehouse {
|
|
id String @id @default(uuid())
|
|
code String @unique
|
|
name String
|
|
nameAr String?
|
|
type String // MAIN, BRANCH, PROJECT, VIRTUAL
|
|
|
|
// Location
|
|
address String?
|
|
city String?
|
|
country String?
|
|
|
|
// Parent for hierarchy
|
|
parentId String?
|
|
parent Warehouse? @relation("WarehouseHierarchy", fields: [parentId], references: [id])
|
|
children Warehouse[] @relation("WarehouseHierarchy")
|
|
|
|
// Manager
|
|
managerId String?
|
|
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
items InventoryItem[]
|
|
movements InventoryMovement[]
|
|
transfers WarehouseTransfer[] @relation("FromWarehouse")
|
|
receivedTransfers WarehouseTransfer[] @relation("ToWarehouse")
|
|
|
|
@@map("warehouses")
|
|
}
|
|
|
|
model Product {
|
|
id String @id @default(uuid())
|
|
sku String @unique
|
|
name String
|
|
nameAr String?
|
|
description String?
|
|
|
|
// Category
|
|
categoryId String
|
|
category ProductCategory @relation(fields: [categoryId], references: [id])
|
|
|
|
// Properties
|
|
brand String?
|
|
model String?
|
|
specifications Json?
|
|
|
|
// Tracking
|
|
trackBy String @default("QUANTITY") // QUANTITY, SERIAL, BATCH
|
|
|
|
// Pricing
|
|
costPrice Decimal @db.Decimal(12, 2)
|
|
sellingPrice Decimal @db.Decimal(12, 2)
|
|
|
|
// Stock
|
|
minStock Int @default(0)
|
|
maxStock Int?
|
|
reorderPoint Int?
|
|
|
|
// Unit
|
|
unit String // PCS, KG, LITER, etc.
|
|
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
inventoryItems InventoryItem[]
|
|
movements InventoryMovement[]
|
|
|
|
@@index([categoryId])
|
|
@@index([sku])
|
|
@@map("products")
|
|
}
|
|
|
|
model ProductCategory {
|
|
id String @id @default(uuid())
|
|
name String
|
|
nameAr String?
|
|
code String @unique
|
|
parentId String?
|
|
parent ProductCategory? @relation("CategoryHierarchy", fields: [parentId], references: [id])
|
|
children ProductCategory[] @relation("CategoryHierarchy")
|
|
description String?
|
|
isActive Boolean @default(true)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
products Product[]
|
|
|
|
@@map("product_categories")
|
|
}
|
|
|
|
model InventoryItem {
|
|
id String @id @default(uuid())
|
|
warehouseId String
|
|
warehouse Warehouse @relation(fields: [warehouseId], references: [id])
|
|
productId String
|
|
product Product @relation(fields: [productId], references: [id])
|
|
|
|
quantity Int
|
|
reservedQty Int @default(0)
|
|
availableQty Int // quantity - reservedQty
|
|
|
|
// Location in warehouse
|
|
location String?
|
|
|
|
// Value
|
|
averageCost Decimal @db.Decimal(12, 2)
|
|
totalValue Decimal @db.Decimal(15, 2)
|
|
|
|
lastUpdated DateTime @default(now())
|
|
|
|
@@unique([warehouseId, productId])
|
|
@@index([warehouseId])
|
|
@@index([productId])
|
|
@@map("inventory_items")
|
|
}
|
|
|
|
model InventoryMovement {
|
|
id String @id @default(uuid())
|
|
warehouseId String
|
|
warehouse Warehouse @relation(fields: [warehouseId], references: [id])
|
|
productId String
|
|
product Product @relation(fields: [productId], references: [id])
|
|
|
|
type String // IN, OUT, ADJUST, TRANSFER
|
|
quantity Int
|
|
|
|
// Reference
|
|
referenceType String? // PURCHASE, SALE, TRANSFER, PROJECT, etc.
|
|
referenceId String?
|
|
|
|
// Pricing
|
|
unitCost Decimal? @db.Decimal(12, 2)
|
|
totalCost Decimal? @db.Decimal(15, 2)
|
|
|
|
// Serial/Batch
|
|
serialNumber String?
|
|
batchNumber String?
|
|
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([warehouseId])
|
|
@@index([productId])
|
|
@@index([type])
|
|
@@index([createdAt])
|
|
@@map("inventory_movements")
|
|
}
|
|
|
|
model WarehouseTransfer {
|
|
id String @id @default(uuid())
|
|
transferNumber String @unique
|
|
fromWarehouseId String
|
|
fromWarehouse Warehouse @relation("FromWarehouse", fields: [fromWarehouseId], references: [id])
|
|
toWarehouseId String
|
|
toWarehouse Warehouse @relation("ToWarehouse", fields: [toWarehouseId], references: [id])
|
|
|
|
items Json // Array of items with quantities
|
|
|
|
status String @default("PENDING") // PENDING, APPROVED, IN_TRANSIT, RECEIVED, CANCELLED
|
|
requestedBy String
|
|
approvedBy String?
|
|
approvedAt DateTime?
|
|
shippedAt DateTime?
|
|
receivedAt DateTime?
|
|
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([fromWarehouseId])
|
|
@@index([toWarehouseId])
|
|
@@index([status])
|
|
@@map("warehouse_transfers")
|
|
}
|
|
|
|
model Asset {
|
|
id String @id @default(uuid())
|
|
assetNumber String @unique
|
|
name String
|
|
type String // EQUIPMENT, VEHICLE, FURNITURE, IT, etc.
|
|
|
|
// Purchase Info
|
|
purchaseDate DateTime @db.Date
|
|
purchasePrice Decimal @db.Decimal(15, 2)
|
|
supplier String?
|
|
invoiceNumber String?
|
|
|
|
// Depreciation
|
|
depreciationMethod String? // STRAIGHT_LINE, DECLINING_BALANCE
|
|
usefulLife Int? // in years
|
|
residualValue Decimal? @db.Decimal(15, 2)
|
|
currentValue Decimal @db.Decimal(15, 2)
|
|
|
|
// Assignment
|
|
assignedTo String? // Employee ID
|
|
assignedDate DateTime? @db.Date
|
|
department String?
|
|
location String?
|
|
|
|
// Maintenance
|
|
lastMaintenanceDate DateTime? @db.Date
|
|
nextMaintenanceDate DateTime? @db.Date
|
|
|
|
// Status
|
|
status String @default("ACTIVE") // ACTIVE, IN_USE, MAINTENANCE, RETIRED, DISPOSED
|
|
|
|
// Additional Info
|
|
serialNumber String?
|
|
model String?
|
|
manufacturer String?
|
|
warranty String?
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
maintenances AssetMaintenance[]
|
|
|
|
@@map("assets")
|
|
}
|
|
|
|
model AssetMaintenance {
|
|
id String @id @default(uuid())
|
|
assetId String
|
|
asset Asset @relation(fields: [assetId], references: [id])
|
|
|
|
type String // PREVENTIVE, CORRECTIVE, EMERGENCY
|
|
description String
|
|
cost Decimal? @db.Decimal(12, 2)
|
|
performedBy String?
|
|
performedDate DateTime @db.Date
|
|
|
|
nextScheduledDate DateTime? @db.Date
|
|
|
|
notes String?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([assetId])
|
|
@@map("asset_maintenances")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 4: TASKS & PROJECTS
|
|
// ============================================
|
|
|
|
model Project {
|
|
id String @id @default(uuid())
|
|
projectNumber String @unique
|
|
name String
|
|
description String?
|
|
|
|
// Type & Source
|
|
type String // INTERNAL, CLIENT, IMPLEMENTATION, MAINTENANCE, GOVERNMENT
|
|
dealId String?
|
|
|
|
// Client
|
|
clientId String?
|
|
|
|
// Dates
|
|
startDate DateTime @db.Date
|
|
endDate DateTime? @db.Date
|
|
actualEndDate DateTime? @db.Date
|
|
|
|
// Budget
|
|
estimatedCost Decimal? @db.Decimal(15, 2)
|
|
actualCost Decimal? @db.Decimal(15, 2)
|
|
|
|
// Progress
|
|
progress Int @default(0) // 0-100
|
|
|
|
// Status
|
|
status String @default("PLANNING") // PLANNING, ACTIVE, ON_HOLD, COMPLETED, CANCELLED
|
|
priority String @default("MEDIUM") // LOW, MEDIUM, HIGH, URGENT
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
phases ProjectPhase[]
|
|
tasks Task[]
|
|
members ProjectMember[]
|
|
expenses ProjectExpense[]
|
|
attachments Attachment[]
|
|
notes Note[]
|
|
|
|
@@map("projects")
|
|
}
|
|
|
|
model ProjectPhase {
|
|
id String @id @default(uuid())
|
|
projectId String
|
|
project Project @relation(fields: [projectId], references: [id])
|
|
name String
|
|
description String?
|
|
order Int
|
|
|
|
startDate DateTime @db.Date
|
|
endDate DateTime @db.Date
|
|
|
|
status String @default("PENDING") // PENDING, IN_PROGRESS, COMPLETED
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
tasks Task[]
|
|
|
|
@@index([projectId])
|
|
@@map("project_phases")
|
|
}
|
|
|
|
model Task {
|
|
id String @id @default(uuid())
|
|
taskNumber String @unique
|
|
projectId String?
|
|
project Project? @relation(fields: [projectId], references: [id])
|
|
phaseId String?
|
|
phase ProjectPhase? @relation(fields: [phaseId], references: [id])
|
|
|
|
// Hierarchy
|
|
parentId String?
|
|
parent Task? @relation("TaskHierarchy", fields: [parentId], references: [id])
|
|
children Task[] @relation("TaskHierarchy")
|
|
|
|
title String
|
|
description String?
|
|
|
|
// Assignment
|
|
assignedToId String?
|
|
assignedTo User? @relation(fields: [assignedToId], references: [id])
|
|
|
|
// Dates & Time
|
|
startDate DateTime? @db.Date
|
|
dueDate DateTime? @db.Date
|
|
completedDate DateTime? @db.Date
|
|
estimatedHours Decimal? @db.Decimal(8, 2)
|
|
actualHours Decimal? @db.Decimal(8, 2)
|
|
|
|
// Status & Priority
|
|
status String @default("TODO") // TODO, IN_PROGRESS, REVIEW, COMPLETED, CANCELLED
|
|
priority String @default("MEDIUM")
|
|
progress Int @default(0)
|
|
|
|
// Dependencies
|
|
dependencies Json? // Array of task IDs this depends on
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
activities Activity[]
|
|
attachments Attachment[]
|
|
|
|
@@index([projectId])
|
|
@@index([phaseId])
|
|
@@index([assignedToId])
|
|
@@index([status])
|
|
@@map("tasks")
|
|
}
|
|
|
|
model ProjectMember {
|
|
id String @id @default(uuid())
|
|
projectId String
|
|
project Project @relation(fields: [projectId], references: [id])
|
|
userId String
|
|
user User @relation(fields: [userId], references: [id])
|
|
role String // MANAGER, MEMBER, VIEWER
|
|
|
|
joinedAt DateTime @default(now())
|
|
leftAt DateTime?
|
|
|
|
@@unique([projectId, userId])
|
|
@@index([projectId])
|
|
@@index([userId])
|
|
@@map("project_members")
|
|
}
|
|
|
|
model ProjectExpense {
|
|
id String @id @default(uuid())
|
|
projectId String
|
|
project Project @relation(fields: [projectId], references: [id])
|
|
|
|
category String
|
|
description String
|
|
amount Decimal @db.Decimal(12, 2)
|
|
date DateTime @db.Date
|
|
|
|
receipt String?
|
|
status String @default("PENDING") // PENDING, APPROVED, REJECTED
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([projectId])
|
|
@@map("project_expenses")
|
|
}
|
|
|
|
// ============================================
|
|
// MODULE 6: MARKETING
|
|
// ============================================
|
|
|
|
model Campaign {
|
|
id String @id @default(uuid())
|
|
campaignNumber String @unique
|
|
name String
|
|
nameAr String?
|
|
type String // EMAIL, WHATSAPP, SOCIAL, EXHIBITION, MULTI_CHANNEL
|
|
|
|
// Content
|
|
description String?
|
|
content Json? // Templates, messages, etc.
|
|
|
|
// Targeting
|
|
targetAudience Json?
|
|
|
|
// Budget & ROI
|
|
budget Decimal? @db.Decimal(15, 2)
|
|
actualCost Decimal? @db.Decimal(15, 2)
|
|
expectedROI Decimal? @db.Decimal(15, 2)
|
|
actualROI Decimal? @db.Decimal(15, 2)
|
|
|
|
// Dates
|
|
startDate DateTime @db.Date
|
|
endDate DateTime? @db.Date
|
|
|
|
// Status & Approval
|
|
status String @default("DRAFT") // DRAFT, PENDING_APPROVAL, APPROVED, SCHEDULED, RUNNING, COMPLETED, CANCELLED
|
|
approvedBy String?
|
|
approvedAt DateTime?
|
|
|
|
// Owner
|
|
ownerId String
|
|
owner User @relation(fields: [ownerId], references: [id])
|
|
|
|
// Metrics
|
|
sentCount Int @default(0)
|
|
openRate Decimal? @db.Decimal(5, 2)
|
|
clickRate Decimal? @db.Decimal(5, 2)
|
|
responseRate Decimal? @db.Decimal(5, 2)
|
|
leadsGenerated Int @default(0)
|
|
conversions Int @default(0)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
// Relations
|
|
activities Activity[]
|
|
|
|
@@index([ownerId])
|
|
@@index([type])
|
|
@@index([status])
|
|
@@map("campaigns")
|
|
}
|
|
|
|
// ============================================
|
|
// SHARED MODELS - نماذج مشتركة
|
|
// ============================================
|
|
|
|
model Activity {
|
|
id String @id @default(uuid())
|
|
type String // CALL, MEETING, EMAIL, WHATSAPP, NOTE, etc.
|
|
|
|
// Related Entity
|
|
entityType String // CONTACT, DEAL, TASK, PROJECT, CAMPAIGN
|
|
entityId String
|
|
|
|
// Relations (optional, for better querying)
|
|
contactId String?
|
|
contact Contact? @relation(fields: [contactId], references: [id])
|
|
dealId String?
|
|
deal Deal? @relation(fields: [dealId], references: [id])
|
|
taskId String?
|
|
task Task? @relation(fields: [taskId], references: [id])
|
|
campaignId String?
|
|
campaign Campaign? @relation(fields: [campaignId], references: [id])
|
|
|
|
// Content
|
|
title String
|
|
description String?
|
|
|
|
// Date & Time
|
|
scheduledAt DateTime?
|
|
completedAt DateTime?
|
|
duration Int? // in minutes
|
|
|
|
// Status
|
|
status String @default("PLANNED") // PLANNED, COMPLETED, CANCELLED
|
|
|
|
// Participants
|
|
participants Json?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([entityType, entityId])
|
|
@@index([contactId])
|
|
@@index([dealId])
|
|
@@index([taskId])
|
|
@@index([type])
|
|
@@map("activities")
|
|
}
|
|
|
|
model Note {
|
|
id String @id @default(uuid())
|
|
|
|
// Related Entity
|
|
entityType String
|
|
entityId String
|
|
|
|
// Relations
|
|
contactId String?
|
|
contact Contact? @relation(fields: [contactId], references: [id])
|
|
dealId String?
|
|
deal Deal? @relation(fields: [dealId], references: [id])
|
|
projectId String?
|
|
project Project? @relation(fields: [projectId], references: [id])
|
|
|
|
content String
|
|
isPinned Boolean @default(false)
|
|
|
|
createdBy String
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([entityType, entityId])
|
|
@@index([contactId])
|
|
@@index([dealId])
|
|
@@index([projectId])
|
|
@@map("notes")
|
|
}
|
|
|
|
model Attachment {
|
|
id String @id @default(uuid())
|
|
|
|
// Related Entity
|
|
entityType String
|
|
entityId String
|
|
|
|
// Relations
|
|
contactId String?
|
|
contact Contact? @relation(fields: [contactId], references: [id])
|
|
dealId String?
|
|
deal Deal? @relation(fields: [dealId], references: [id])
|
|
projectId String?
|
|
project Project? @relation(fields: [projectId], references: [id])
|
|
taskId String?
|
|
task Task? @relation(fields: [taskId], references: [id])
|
|
|
|
// File Info
|
|
fileName String
|
|
originalName String
|
|
mimeType String
|
|
size Int
|
|
path String
|
|
url String?
|
|
|
|
// Metadata
|
|
description String?
|
|
category String?
|
|
|
|
uploadedBy String
|
|
uploadedAt DateTime @default(now())
|
|
|
|
@@index([entityType, entityId])
|
|
@@index([contactId])
|
|
@@index([dealId])
|
|
@@index([projectId])
|
|
@@index([taskId])
|
|
@@map("attachments")
|
|
}
|
|
|
|
model CustomField {
|
|
id String @id @default(uuid())
|
|
module String // contacts, crm, inventory, etc.
|
|
entityType String // Contact, Deal, Product, etc.
|
|
name String
|
|
nameAr String?
|
|
fieldType String // TEXT, NUMBER, DATE, SELECT, MULTISELECT, BOOLEAN
|
|
options Json? // For SELECT/MULTISELECT
|
|
isRequired Boolean @default(false)
|
|
isActive Boolean @default(true)
|
|
order Int @default(0)
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@unique([module, entityType, name])
|
|
@@map("custom_fields")
|
|
}
|
|
|
|
model Notification {
|
|
id String @id @default(uuid())
|
|
userId String
|
|
type String // TASK_ASSIGNED, DEAL_STAGE_CHANGED, APPROVAL_PENDING, etc.
|
|
title String
|
|
message String
|
|
entityType String?
|
|
entityId String?
|
|
isRead Boolean @default(false)
|
|
readAt DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
|
|
@@index([userId, isRead])
|
|
@@index([createdAt])
|
|
@@map("notifications")
|
|
}
|
|
|
|
model Approval {
|
|
id String @id @default(uuid())
|
|
entityType String // QUOTE, DEAL, CONTRACT, TRANSFER, etc.
|
|
entityId String
|
|
type String // PRICE_APPROVAL, DISCOUNT_APPROVAL, etc.
|
|
|
|
requestedBy String
|
|
requestedAt DateTime @default(now())
|
|
|
|
approverLevel Int // For multi-level approvals
|
|
approverId String
|
|
|
|
status String @default("PENDING") // PENDING, APPROVED, REJECTED
|
|
comments String?
|
|
respondedAt DateTime?
|
|
|
|
createdAt DateTime @default(now())
|
|
updatedAt DateTime @updatedAt
|
|
|
|
@@index([entityType, entityId])
|
|
@@index([approverId, status])
|
|
@@map("approvals")
|
|
}
|
|
|