# DATABASE SCHEMA & DATA MODELS (MongoDB)

[← Về Overview](../../README.md) | [Next: Data Models →](./02-data-models.md)

---

## 15.1. MongoDB Collections & Mongoose Schemas

### 15.1.1. Asset Management Collections

**Companies Collection** (Multi-tenant)
```javascript
const companySchema = new Schema({
  name: { type: String, required: true },
  code: { type: String, unique: true, required: true },
  taxCode: String,
  address: String,
  phone: String,
  email: String,
  status: { type: String, enum: ['active', 'inactive'], default: 'active' },
  createdAt: { type: Date, default: Date.now },
  updatedAt: { type: Date, default: Date.now }
});
```

**Projects Collection**
```javascript
const projectSchema = new Schema({
  companyId: { type: Schema.Types.ObjectId, ref: 'Company', required: true },
  name: { type: String, required: true },
  code: { type: String, required: true },
  location: {
    address: String,
    coordinates: { lat: Number, lng: Number }
  },
  capacityMWp: Number,
  commissioningDate: Date,
  status: { type: String, enum: ['planning', 'construction', 'operational', 'decommissioned'] },
  createdAt: { type: Date, default: Date.now }
});
```

**Assets Collection** (Hierarchical)
```javascript
const assetSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  parentAssetId: { type: Schema.Types.ObjectId, ref: 'Asset' },
  assetType: { type: String, required: true }, // Plant, Block, Inverter, String, Module
  name: { type: String, required: true },
  code: { type: String, required: true },
  serialNumber: String,
  manufacturer: String,
  model: String,
  capacityKW: Number,
  commissioningDate: Date,
  warrantyEndDate: Date,
  status: { type: String, enum: ['active', 'inactive', 'maintenance', 'decommissioned'] },
  locationGPS: {
    lat: Number,
    lng: Number
  },
  metadata: Schema.Types.Mixed,
  createdAt: { type: Date, default: Date.now },
  updatedAt: { type: Date, default: Date.now }
});

// Indexes for performance
assetSchema.index({ projectId: 1, code: 1 }, { unique: true });
assetSchema.index({ parentAssetId: 1 });
assetSchema.index({ assetType: 1 });
```

### 15.1.2. Contract Management Collections

**Contracts Collection**
```javascript
const contractSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  contractType: { 
    type: String, 
    enum: ['PPA', 'EPC', 'O&M', 'Insurance', 'Land'], 
    required: true 
  },
  contractNumber: { type: String, required: true },
  partyA: String,
  partyB: String,
  startDate: { type: Date, required: true },
  endDate: { type: Date, required: true },
  value: Number,
  currency: { type: String, default: 'VND' },
  status: { type: String, enum: ['draft', 'active', 'expired', 'terminated'] },
  documentUrl: String,
  milestones: [{
    milestoneName: String,
    dueDate: Date,
    completionDate: Date,
    status: String,
    notes: String
  }],
  slas: [{
    kpiName: String,
    targetValue: Number,
    measurementUnit: String,
    penaltyClause: String
  }],
  metadata: Schema.Types.Mixed,
  createdAt: { type: Date, default: Date.now }
});

contractSchema.index({ projectId: 1, contractNumber: 1 }, { unique: true });
contractSchema.index({ endDate: 1 }); // For expiring contracts query
```

### 15.1.3. O&M Collections

**Tickets Collection**
```javascript
const ticketSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset' },
  ticketType: { 
    type: String, 
    enum: ['CM', 'PM', 'Cleaning', 'Inspection'], 
    required: true 
  },
  title: { type: String, required: true },
  description: String,
  priority: { 
    type: String, 
    enum: ['Low', 'Medium', 'High', 'Critical'], 
    default: 'Medium' 
  },
  status: { 
    type: String, 
    enum: ['Open', 'Assigned', 'InProgress', 'Resolved', 'Closed'], 
    default: 'Open' 
  },
  reportedBy: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  assignedTo: { type: Schema.Types.ObjectId, ref: 'User' },
  createdAt: { type: Date, default: Date.now },
  resolvedAt: Date,
  closedAt: Date
});

ticketSchema.index({ projectId: 1, status: 1 });
ticketSchema.index({ assignedTo: 1, status: 1 });
```

**Work Orders Collection**
```javascript
const workOrderSchema = new Schema({
  ticketId: { type: Schema.Types.ObjectId, ref: 'Ticket' },
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset' },
  workOrderType: { 
    type: String, 
    enum: ['PM', 'CM', 'Cleaning', 'Inspection', 'Upgrade'] 
  },
  title: { type: String, required: true },
  description: String,
  status: { 
    type: String, 
    enum: ['Draft', 'Approved', 'InProgress', 'Completed', 'Cancelled'], 
    default: 'Draft' 
  },
  scheduledStart: Date,
  scheduledEnd: Date,
  actualStart: Date,
  actualEnd: Date,
  assignedTechnicianId: { type: Schema.Types.ObjectId, ref: 'User' },
  supervisorId: { type: Schema.Types.ObjectId, ref: 'User' },
  costLabor: { type: Number, default: 0 },
  costMaterial: { type: Number, default: 0 },
  costExternal: { type: Number, default: 0 },
  totalCost: { type: Number, default: 0 },
  checklist: [{
    item: String,
    status: { type: String, enum: ['pending', 'completed', 'skipped'] },
    notes: String,
    photoUrl: String,
    completedBy: { type: Schema.Types.ObjectId, ref: 'User' },
    completedAt: Date
  }],
  photos: [String],
  signatures: [{
    role: String,
    userId: { type: Schema.Types.ObjectId, ref: 'User' },
    signatureUrl: String,
    signedAt: Date
  }],
  createdAt: { type: Date, default: Date.now },
  completedAt: Date
});

workOrderSchema.index({ projectId: 1, status: 1 });
workOrderSchema.index({ assignedTechnicianId: 1, status: 1 });
workOrderSchema.index({ scheduledStart: 1 });
```

**PM Schedules Collection**
```javascript
const pmScheduleSchema = new Schema({
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset', required: true },
  pmType: String,
  frequencyMonths: { type: Number, required: true },
  lastPerformedDate: Date,
  nextDueDate: { type: Date, required: true },
  status: { type: String, enum: ['active', 'paused', 'completed'], default: 'active' },
  createdAt: { type: Date, default: Date.now }
});

pmScheduleSchema.index({ assetId: 1 });
pmScheduleSchema.index({ nextDueDate: 1 });
```

### 15.1.4. Inventory Management Collections

**Inventory Items Collection**
```javascript
const inventoryItemSchema = new Schema({
  itemCode: { type: String, unique: true, required: true },
  name: { type: String, required: true },
  category: { 
    type: String, 
    enum: ['CriticalSpare', 'Consumable', 'Tool', 'Equipment'] 
  },
  unit: String,
  minStockLevel: Number,
  maxStockLevel: Number,
  reorderPoint: Number,
  currentStock: { type: Number, default: 0 },
  unitCost: Number,
  supplierId: { type: Schema.Types.ObjectId, ref: 'Supplier' },
  location: String,
  createdAt: { type: Date, default: Date.now }
});

inventoryItemSchema.index({ itemCode: 1 }, { unique: true });
inventoryItemSchema.index({ category: 1 });
```

**Stock Transactions Collection**
```javascript
const stockTransactionSchema = new Schema({
  inventoryItemId: { 
    type: Schema.Types.ObjectId, 
    ref: 'InventoryItem', 
    required: true 
  },
  transactionType: { 
    type: String, 
    enum: ['In', 'Out', 'Adjustment'], 
    required: true 
  },
  quantity: { type: Number, required: true },
  unitCost: Number,
  totalCost: Number,
  workOrderId: { type: Schema.Types.ObjectId, ref: 'WorkOrder' },
  referenceNumber: String,
  notes: String,
  createdBy: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  createdAt: { type: Date, default: Date.now }
});

stockTransactionSchema.index({ inventoryItemId: 1, createdAt: -1 });
stockTransactionSchema.index({ workOrderId: 1 });
```

**Suppliers Collection**
```javascript
const supplierSchema = new Schema({
  name: { type: String, required: true },
  code: { type: String, unique: true },
  contactPerson: String,
  phone: String,
  email: String,
  address: String,
  rating: { type: Number, min: 0, max: 5 },
  status: { type: String, enum: ['active', 'inactive'], default: 'active' },
  createdAt: { type: Date, default: Date.now }
});
```

### 15.1.5. Financial Management Collections

**Budgets Collection**
```javascript
const budgetSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  budgetYear: { type: Number, required: true },
  budgetType: { type: String, enum: ['OPEX', 'CAPEX'], required: true },
  category: String,
  plannedAmount: { type: Number, required: true },
  actualAmount: { type: Number, default: 0 },
  variance: Number,
  createdAt: { type: Date, default: Date.now },
  updatedAt: { type: Date, default: Date.now }
});

budgetSchema.index({ projectId: 1, budgetYear: 1, budgetType: 1 });
```

**Financial Transactions Collection**
```javascript
const financialTransactionSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  transactionType: { 
    type: String, 
    enum: ['Revenue', 'Expense'], 
    required: true 
  },
  category: String,
  amount: { type: Number, required: true },
  currency: { type: String, default: 'VND' },
  transactionDate: { type: Date, required: true },
  description: String,
  referenceNumber: String,
  workOrderId: { type: Schema.Types.ObjectId, ref: 'WorkOrder' },
  invoiceUrl: String,
  status: { type: String, enum: ['pending', 'approved', 'paid'], default: 'pending' },
  createdAt: { type: Date, default: Date.now }
});

financialTransactionSchema.index({ projectId: 1, transactionDate: -1 });
financialTransactionSchema.index({ workOrderId: 1 });
```

**Meter Readings Collection**
```javascript
const meterReadingSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project', required: true },
  meterType: { 
    type: String, 
    enum: ['Generation', 'Export', 'Import'], 
    required: true 
  },
  readingDate: { type: Date, required: true },
  readingValueKwh: { type: Number, required: true },
  previousReading: Number,
  differenceKwh: Number,
  tariffRate: Number,
  revenue: Number,
  createdAt: { type: Date, default: Date.now }
});

meterReadingSchema.index({ projectId: 1, readingDate: -1 });
meterReadingSchema.index({ meterType: 1 });
```

### 15.1.6. User & Permission Management Collections

**Users Collection**
```javascript
const userSchema = new Schema({
  email: { type: String, unique: true, required: true, lowercase: true },
  passwordHash: { type: String, required: true },
  firstName: { type: String, required: true },
  lastName: { type: String, required: true },
  phone: String,
  department: String,
  position: String,
  status: { 
    type: String, 
    enum: ['Active', 'Suspended', 'Disabled'], 
    default: 'Active' 
  },
  companyId: { type: Schema.Types.ObjectId, ref: 'Company' },
  roles: [{
    roleId: { type: Schema.Types.ObjectId, ref: 'Role' },
    projectId: { type: Schema.Types.ObjectId, ref: 'Project' },
    assignedAt: { type: Date, default: Date.now },
    assignedBy: { type: Schema.Types.ObjectId, ref: 'User' }
  }],
  createdAt: { type: Date, default: Date.now },
  lastLoginAt: Date
});

userSchema.index({ email: 1 }, { unique: true });
userSchema.index({ companyId: 1 });
```

**Roles Collection**
```javascript
const roleSchema = new Schema({
  name: { type: String, required: true },
  code: { type: String, unique: true, required: true },
  description: String,
  permissions: [{
    resource: String,
    action: String,
    description: String
  }],
  createdAt: { type: Date, default: Date.now }
});
```

### 15.1.7. Audit & Logging Collections

**System Logs Collection**
```javascript
const systemLogSchema = new Schema({
  userId: { type: Schema.Types.ObjectId, ref: 'User' },
  action: { 
    type: String, 
    enum: ['CREATE', 'UPDATE', 'DELETE', 'LOGIN', 'EXPORT', 'APPROVE'], 
    required: true 
  },
  resourceType: String,
  resourceId: Schema.Types.ObjectId,
  beforeValue: Schema.Types.Mixed,
  afterValue: Schema.Types.Mixed,
  ipAddress: String,
  userAgent: String,
  createdAt: { type: Date, default: Date.now, index: true }
});

systemLogSchema.index({ userId: 1, createdAt: -1 });
systemLogSchema.index({ resourceType: 1, resourceId: 1 });
systemLogSchema.index({ createdAt: 1 }, { expireAfterSeconds: 31536000 });
```

**User Activity Logs Collection**
```javascript
const userActivityLogSchema = new Schema({
  userId: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  activityType: String,
  description: String,
  projectId: { type: Schema.Types.ObjectId, ref: 'Project' },
  metadata: Schema.Types.Mixed,
  createdAt: { type: Date, default: Date.now, index: true }
});

userActivityLogSchema.index({ userId: 1, createdAt: -1 });
```

### 15.1.8. Documents Collection
```javascript
const documentSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project' },
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset' },
  documentType: String,
  title: { type: String, required: true },
  fileUrl: { type: String, required: true },
  fileSize: Number,
  mimeType: String,
  version: { type: Number, default: 1 },
  uploadedBy: { type: Schema.Types.ObjectId, ref: 'User', required: true },
  uploadedAt: { type: Date, default: Date.now },
  metadata: Schema.Types.Mixed
});

documentSchema.index({ projectId: 1, documentType: 1 });
documentSchema.index({ assetId: 1 });
```

---

## 15.2. Time-Series Data Collections (MongoDB)

**Measurements Collection** (SCADA/IoT Data)
```javascript
const measurementSchema = new Schema({
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset', required: true },
  metricName: { type: String, required: true },
  value: { type: Number, required: true },
  quality: { type: String, enum: ['Good', 'Bad', 'Uncertain'], default: 'Good' },
  tags: Schema.Types.Mixed,
  timestamp: { type: Date, required: true, index: true }
});

measurementSchema.index({ assetId: 1, metricName: 1, timestamp: -1 });
measurementSchema.index({ timestamp: 1 }, { expireAfterSeconds: 31536000 });
```

**Performance Metrics Collection** (Aggregated)
```javascript
const performanceMetricSchema = new Schema({
  projectId: { type: Schema.Types.ObjectId, ref: 'Project' },
  assetId: { type: Schema.Types.ObjectId, ref: 'Asset' },
  pr: Number,
  availability: Number,
  specificYield: Number,
  energyKwh: Number,
  powerKw: Number,
  timestamp: { type: Date, required: true, index: true },
  period: { type: String, enum: ['hourly', 'daily', 'monthly'] },
  createdAt: { type: Date, default: Date.now }
});

performanceMetricSchema.index({ projectId: 1, timestamp: -1 });
performanceMetricSchema.index({ assetId: 1, timestamp: -1 });
performanceMetricSchema.index({ period: 1, timestamp: -1 });
```

---

## 15.3. MongoDB Indexes Strategy

**Performance Optimization:**
- Compound indexes cho queries thường dùng
- TTL indexes cho auto-delete old data
- Text indexes cho full-text search (nếu cần)
- Unique indexes cho fields cần unique constraint

**Example Indexes:**
```javascript
// Assets
assetSchema.index({ projectId: 1, code: 1 }, { unique: true });
assetSchema.index({ parentAssetId: 1 });

// Tickets
ticketSchema.index({ projectId: 1, status: 1 });
ticketSchema.index({ assignedTo: 1, status: 1 });

// Work Orders
workOrderSchema.index({ scheduledStart: 1 });
workOrderSchema.index({ projectId: 1, status: 1 });

// Time-series data
measurementSchema.index({ assetId: 1, metricName: 1, timestamp: -1 });
performanceMetricSchema.index({ projectId: 1, timestamp: -1 });
```

---

**Next**: [Data Models](./02-data-models.md) | [Demo Data Structure](./03-demo-data-structure.md)