# Move Draft to Current - Differential Sync Implementation

## Overview

This document describes the implementation of the improved `move-draft-to-current` function in `OrganizationController.ts`. The function synchronizes organization structure and position data from the **Draft Revision** to the **Current Revision** using a differential sync approach (instead of the previous "delete all and insert all" method).

**API Endpoint:** `POST /api/v1/org/move-draft-to-current/{rootDnaId}`

---

## Architecture

### Data Models

The organization structure consists of 5 hierarchical levels:

```
OrgRoot (Level 0)
  └── OrgChild1 (Level 1)
       └── OrgChild2 (Level 2)
            └── OrgChild3 (Level 3)
                 └── OrgChild4 (Level 4)
```

Each level has:
- Organization nodes with `ancestorDNA` for hierarchical tracking
- Foreign key relationships to parent levels
- Associated position records (`PosMaster`)

### Type Definitions

Located in `src/interfaces/OrgMapping.ts`:

```typescript
interface OrgIdMapping {
  byAncestorDNA: Map<string, string>;  // ancestorDNA → current ID
  byDraftId: Map<string, string>;      // draft ID → current ID
}

interface AllOrgMappings {
  orgRoot: OrgIdMapping;
  orgChild1: OrgIdMapping;
  orgChild2: OrgIdMapping;
  orgChild3: OrgIdMapping;
  orgChild4: OrgIdMapping;
}
```

---

## Implementation Workflow

### Phase 0: Preparation

1. **Get Revision IDs**
   - Fetch Draft Revision (`orgRevisionIsDraft: true`)
   - Fetch Current Revision (`orgRevisionIsCurrent: true`)

2. **Validate rootDnaId**
   - Check if rootDnaId exists in Draft Revision
   - Return error if not found

### Phase 1: Sync Organization Structure (Bottom-Up)

**Processing Order:** `OrgChild4 → OrgChild3 → OrgChild2 → OrgChild1 → OrgRoot`

**Why Bottom-Up?** Child nodes have no dependent children (only parent references), allowing safe deletion without FK violations.

#### For Each Organization Level

The `syncOrgLevel()` helper performs:

1. **FETCH** - Get all draft and current nodes under `rootDnaId`
   ```typescript
   where: { ancestorDNA: Like(`${rootDnaId}%`) }  // All descendants
   ```

2. **DELETE** - Remove current nodes not in draft
   - Cascade delete positions first (via `cascadeDeletePositions()`)
   - Delete the organization node

3. **UPDATE** - Update nodes that exist in both (matched by `ancestorDNA`)
   - Map parent IDs using `parentMappings`
   - Preserve original node ID

4. **INSERT** - Add draft nodes not in current
   - Create new node with mapped parent IDs
   - Return new ID for tracking

5. **RETURN** - Return `OrgIdMapping` for next level

**Result:** `allMappings` contains draft ID → current ID mappings for all org levels

### Phase 2: Sync Position Data

#### Step 2.1: Clear current_holderId

```typescript
// Clear holders for positions that will have new holders
await queryRunner.manager.update(PosMaster,
  { current_holderId: In(nextHolderIds) },
  { current_holderId: null, isSit: false }
)
```

#### Step 2.2: Fetch Draft and Current Positions

- Get draft positions using `draftOrgIds` from `allMappings`
- Get current positions using `currentOrgIds` from `allMappings`

#### Step 2.3: Batch DELETE

```typescript
// Delete current positions not in draft (cascade delete positions first)
await queryRunner.manager.delete(Position, { posMasterId: In(toDeleteIds) })
await queryRunner.manager.delete(PosMaster, toDeleteIds)
```

#### Step 2.4: Process UPDATE or INSERT

For each draft position:
1. Map organization IDs using `resolveOrgId()`
2. If exists in current → **UPDATE**
3. If not exists → **INSERT**
4. Track `draftPosMasterId → currentPosMasterId` mapping

#### Step 2.5: Sync Position Table

For each mapped PosMaster:
```typescript
await syncPositionsForPosMaster(
  queryRunner,
  draftPosMasterId,
  currentPosMasterId,
  draftRevisionId,
  currentRevisionId
)
```

---

## Helper Functions

### `resolveOrgId(draftId, mapping)`

Maps a draft organization ID to its current ID.

```typescript
private resolveOrgId(
  draftId: string | null,
  mapping: OrgIdMapping
): string | null {
  if (!draftId) return null;
  return mapping.byDraftId.get(draftId) ?? null;
}
```

### `cascadeDeletePositions(queryRunner, node, entityClass)`

Deletes positions associated with an organization node before deleting the node itself.

```typescript
private async cascadeDeletePositions(
  queryRunner: any,
  node: any,
  entityClass: any
): Promise<void> {
  const whereClause = { orgRevisionId: node.orgRevisionId };

  // Set FK field based on entity type
  if (entityClass === OrgRoot) whereClause.orgRootId = node.id;
  else if (entityClass === OrgChild1) whereClause.orgChild1Id = node.id;
  // ... etc

  await queryRunner.manager.delete(PosMaster, whereClause);
}
```

### `syncOrgLevel(...)`

Generic differential sync for each organization level.

**Parameters:**
- `queryRunner` - Database query runner
- `entityClass` - Organization entity class (OrgRoot, OrgChild1, etc.)
- `repository` - Repository for the entity
- `draftRevisionId` - Draft revision ID
- `currentRevisionId` - Current revision ID
- `rootDnaId` - Root DNA ID to sync under
- `parentMappings` - Mappings from child levels (for FK resolution)

**Returns:** `OrgIdMapping` for this level

### `syncPositionsForPosMaster(...)`

Syncs positions for a PosMaster record.

**Parameters:**
- `queryRunner` - Database query runner
- `draftPosMasterId` - Draft PosMaster ID
- `currentPosMasterId` - Current PosMaster ID
- `draftRevisionId` - Draft revision ID
- `currentRevisionId` - Current revision ID

**Process:**
1. Fetch draft and current positions
2. Delete current positions not in draft (by `orderNo`)
3. Update existing positions
4. Insert new positions

---

## Transaction Management

All operations are wrapped in a transaction:

```typescript
const queryRunner = AppDataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  // ... all sync operations
  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw new HttpError(HttpStatusCode.INTERNAL_SERVER_ERROR, "...");
}
```

---

## Benefits of Differential Sync

| Aspect | Old Approach (Delete All + Insert) | New Approach (Differential Sync) |
|--------|-----------------------------------|----------------------------------|
| **ID Preservation** | All IDs changed | Unchanged nodes keep original IDs |
| **Performance** | N deletes + N inserts | Only changed data processed |
| **Tracking** | Cannot track what changed | Can track additions/updates/deletes |
| **Data Integrity** | Higher risk of data loss | Better integrity with cascade deletes |
| **Scalability** | Poor for large datasets | Efficient with batch operations |

---

## Database Schema Relationships

```
orgRevision
  ├── orgRoot (FK: orgRevisionId)
  │     ├── orgChild1 (FK: orgRootId, orgRevisionId)
  │     │     ├── orgChild2 (FK: orgChild1Id, orgRootId, orgRevisionId)
  │     │     │     ├── orgChild3 (FK: orgChild2Id, orgChild1Id, orgRootId, orgRevisionId)
  │     │     │     │     └── orgChild4 (FK: orgChild3Id, orgChild2Id, orgChild1Id, orgRootId, orgRevisionId)
  │     │     │     │
  │     │     │     └── posMaster (FK: orgRootId, orgChild1Id, orgChild2Id, orgChild3Id, orgChild4Id)
  │     │     │           └── position (FK: posMasterId)
  │     │     │
  │     │     └── posMaster
  │     │
  │     └── posMaster
  │
  └── (current revision similar structure)
```

---

## Error Handling

| Error Condition | Response |
|----------------|----------|
| Draft/Current revision not found | 404 NOT_FOUND |
| rootDnaId not found in draft | 404 NOT_FOUND |
| No positions in draft structure | 404 NOT_FOUND |
| Database error during sync | 500 INTERNAL_SERVER_ERROR (rollback) |

---

## Files Modified/Created

```
src/
├── interfaces/
│   └── OrgMapping.ts          [NEW] Type definitions
├── controllers/
│   └── OrganizationController.ts  [MODIFIED] moveDraftToCurrent function + helpers
docs/
└── move-draft-to-current.md   [NEW] This documentation
```

---

## Testing Considerations

### Unit Tests

- Test `syncOrgLevel()` for each org level with various scenarios
- Test `resolveOrgId()` mapping function
- Test `cascadeDeletePositions()` function
- Test `syncPositionsForPosMaster()` function

### Integration Tests

1. **Empty Draft** - Sync with no draft data
2. **Full Replacement** - All nodes changed
3. **Partial Update** - Some nodes added, some updated, some deleted
4. **Position Sync** - Verify position table syncs correctly
5. **Foreign Key Constraints** - Verify all FK relationships maintained

### Manual Testing Flow

1. Create draft structure with various changes:
   - Add new department
   - Modify existing department
   - Remove existing department
   - Add/modify/remove positions
2. Call `move-draft-to-current` API
3. Verify:
   - New departments appear in current
   - Modified departments are updated (not recreated)
   - Removed departments are gone (with positions cascade deleted)
   - Positions have correct new org IDs
   - Position table records sync correctly
   - All foreign key constraints satisfied

---

## Performance Optimization

- **Batch Operations** - Use `In()` clause for multiple IDs
- **Map Lookups** - Use `Map` for O(1) lookups instead of array searches
- **Bottom-Up Processing** - Minimize FK constraint checks
- **Parallel Queries** - Use `Promise.all()` for independent queries

---

## Future Improvements

1. **Parallel Processing** - Process independent org branches in parallel
2. **Incremental Sync** - Only sync changed subtrees
3. **Caching** - Cache org mappings for repeated operations
4. **Audit Log** - Track all changes for audit purposes
5. **Validation** - Add pre-sync validation to catch errors early

---

## References

- TypeORM Documentation: https://typeorm.io/
- TSOA Documentation: https://tsoa-community.github.io/
- Project Repository: [Internal Git]

---

**Last Updated:** 2025-02-09
**Author:** Claude Code
**Version:** 1.0.0