# Hybrid Tender System Design

## Overview

Enhance the tender management system to support both centralized category-based tendering (bulk procurement) and project-specific tendering (for unique/variable items). The system must also support dynamic item creation with auto-generated codes for new project-specific items.

## Requirements Summary

1. **Category-based tendering** - aggregate standard/common items across multiple projects into a single bulk tender (existing functionality)
2. **Project-specific tendering** - allow tendering for items unique to certain projects that don't exist in the master item list
3. **Dynamic item creation** - auto-generate item codes for new project-specific entries
4. **Unified tender flow** - single flow with tender type selection supporting both approaches

---

## 1. Data Model

### 1.1 New Models

#### ProjectSpecificItem

```
id: String (cuid, PK)
code: String (unique, format: PROJ-{projectCode}-{sequence})
name: String (required)
description: String?
unit: String (required)
projectId: String (FK to Project)
createdAt: DateTime
updatedAt: DateTime
```

**Rationale:** Separate table for project-specific items maintains clean separation from master items. Auto-generated codes follow pattern `PROJ-P001-0001`.

### 1.2 Modified Models

#### TenderBundleItem

Add field:

```
projectSpecificItemId: String? (FK to ProjectSpecificItem, optional)
```

**Rationale:** Allow tender bundle items to reference either master Item or ProjectSpecificItem.

#### TenderNotice

Add field for ad-hoc project tenders:

```
projectId: String? (FK to Project, optional)
```

**Rationale:** Allow creating a PROJECT tender without requiring a pre-existing TenderBundle - enables direct ad-hoc tendering.

### 1.3 Enums

Add new enum `TenderItemSource`:

```
MASTER_ITEM
PROJECT_SPECIFIC_ITEM
```

---

## 2. Core Logic

### 2.1 Item Code Generation

**Pattern:** `PROJ-{projectCode}-{sequence}`

**Examples:**

- PROJ-P001-0001
- PROJ-P001-0002
- PROJ-P002-0001

**Algorithm:**

1. Get project code from Project table
2. Query for existing codes matching `PROJ-{projectCode}-%`
3. Extract sequence from last code, increment by 1
4. Format with zero-padding (4 digits)

**Service Function:**

```typescript
async function generateProjectItemCode(projectId: string): Promise<string>;
```

### 2.2 Project-Specific Item Creation

**Flow:**

1. User enters name, unit (description optional)
2. System generates code using algorithm above
3. Record created in ProjectSpecificItem table

**Validation:**

- Name: required, max 255 chars
- Unit: required
- ProjectId: required, must be valid project

### 2.3 Tender Item Aggregation

When fetching items for a tender, support both sources:

```typescript
interface TenderItem {
  id: string;
  name: string;
  unit: string;
  code: string | null;
  quantity: Decimal;
  estimatedRate: Decimal | null;
  source: "MASTER_ITEM" | "PROJECT_SPECIFIC_ITEM";
}
```

**Service Function:**

```typescript
async function getTenderItems(tenderId: string): Promise<TenderItem[]>;
```

---

## 3. API/Service Layer

### 3.1 New Service Functions (project-specific-item.service.ts)

```typescript
// Create project-specific item with auto-generated code
createProjectSpecificItem(data: {
  projectId: string;
  name: string;
  unit: string;
  description?: string;
}): Promise<ProjectSpecificItem>

// Get items for a specific project
getProjectItems(projectId: string): Promise<ProjectSpecificItem[]>

// Generate item code
generateItemCode(projectId: string): Promise<string>
```

### 3.2 Modified Routers

#### tender.router.ts

**create mutation - add fields:**

```typescript
input: {
  // existing fields
  tenderType: 'PROJECT' | 'CATEGORY'
  projectId?: string  // for ad-hoc PROJECT tenders
  projectSpecificItemIds?: string[]  // new items to include
}
```

#### boq.router.ts

**create mutation - handle both item types:**

```typescript
input: {
  items: Array<{
    itemId?: string; // for master items
    projectSpecificItemId?: string; // for project-specific items
    quantity: number;
    estimatedRate?: number;
  }>;
}
```

---

## 4. UI/UX Considerations

### 4.1 Tender Creation Flow

```
Step 1: Select Tender Type
  [ ] PROJECT
  [ ] CATEGORY

Step 2a: For PROJECT type
  - Option A: Select existing TenderBundle
  - Option B: Ad-hoc (select project, add items directly)

  Item Selection:
  - Tab 1: Master Items (search from Item table)
  - Tab 2: Project-Specific (create new inline)
    [Name] [Unit] [+ Add] → auto-generates code

Step 2b: For CATEGORY type
  - Select CategoryBundle
```

### 4.2 Item Display

- Master items: show category and code
- Project-specific items: show "Project-Specific" badge, display generated code

---

## 5. Bid Submission Considerations

### 5.1 Bid Line Items

When vendors submit bids, they reference items:

```typescript
// BidLineItem - extend to support both sources
model BidLineItem {
  // existing fields
  projectSpecificItemId: String?  // NEW: FK to ProjectSpecificItem
}
```

**Rationale:** Vendors need to quote rates for project-specific items too.

### 5.2 Bid Comparison

When comparing bids, aggregate from both sources:

- Query BidLineItems with OR (itemId IS NOT NULL OR projectSpecificItemId IS NOT NULL)
- Display source type in comparison table

---

## 6. Constraints & Validations

1. **Mutual exclusivity**: A TenderBundleItem cannot reference both Item and ProjectSpecificItem
2. **Project scope**: ProjectSpecificItems can only be used in tenders for their originating project (or ad-hoc tenders for that project)
3. **Category exclusion**: ProjectSpecificItems cannot be included in CategoryBundle aggregation
4. **Tender type integrity**: Category tenders must reference CategoryBundle, not project-specific items

---

## 7. Migration Strategy

1. Create new tables via Prisma migration
2. Add nullable fields to existing tables
3. Add new service functions
4. Update router inputs
5. Update UI components

---

## 8. Open Questions

1. Should ProjectSpecificItems be exportable/promotable to master Item table? (Not in initial scope)
2. Should historical tenders show items with their source type? (Yes, store source reference)