docs/agent/implementation-notes/TICKET_REACTIVATION_STRATEGY.md

# Ticket Reactivation Strategy - Final Design

## Overview
The ticket system now supports **space-efficient reactivation** of cancelled/completed tickets, while still maintaining unique deduplication keys for audit trails.

---

## Business Problem Solved

### Scenario: Customer Service Reactivation
```
Customer orders WiFi → Cancels → Wants WiFi again
```

**Bad Design (Create New Ticket):**
- ❌ Duplicate rows in database
- ❌ Lose payment history if customer paid before cancelling
- ❌ Can't track chronic cancellers
- ❌ Wastes database space

**Good Design (Reactivate Ticket):**
- ✅ Reuse existing ticket row (space efficient)
- ✅ Preserve payment history
- ✅ Track customer behavior (chronic cancellers)
- ✅ Complete audit trail

---

## Implementation Logic

### Create Ticket Flow (With Reactivation)

```python
def create_ticket_from_sales_order(sales_order_id):
    """
    Creates new ticket OR reactivates cancelled/completed ticket.
    """
    
    # 1. Check for ACTIVE ticket (prevent duplicates)
    active_ticket = find_active_ticket(sales_order_id)
    if active_ticket:
        raise HTTPException(409, "Active ticket already exists")
    
    # 2. Check for CANCELLED/COMPLETED ticket (reactivation opportunity)
    cancelled_ticket = find_cancelled_ticket(sales_order_id)
    if cancelled_ticket:
        # REACTIVATE: Update existing ticket
        cancelled_ticket.status = "open"
        cancelled_ticket.scheduled_date = new_date
        cancelled_ticket.notes = f"[REACTIVATED {now}] {notes}\n{old_notes}"
        # dedup_key remains unchanged (permanent)
        return cancelled_ticket  # ✅ Reused row
    
    # 3. No existing ticket - Create new one
    dedup_key = MD5(source + source_id + ticket_type + created_at)
    new_ticket = Ticket(
        source_id=sales_order_id,
        status="open",
        dedup_key=dedup_key  # Permanent unique key
    )
    return new_ticket  # ✅ New row
```

### Key Points

1. **Active Ticket Check**: Prevents duplicate active tickets
2. **Cancelled Ticket Check**: Enables reactivation (space efficient)
3. **Dedup Key**: Permanent (tracks creation instance, not lifecycle)
4. **Reactivation**: Updates status, dates, adds note, reuses row

---

## Business Rules

### Rule 1: One Source = One Active Ticket
- Sales order can have ONE active ticket at a time
- Prevents duplicate work orders
- Check: `status NOT IN ('completed', 'cancelled')`

### Rule 2: Reactivation for Same Service
```
Customer cancels 10Mbps WiFi → Wants 10Mbps WiFi again
→ REACTIVATE cancelled ticket (reuse row)
```

**Implementation:**
- Find cancelled ticket for same sales order
- Update status: cancelled → open
- Update dates and schedule
- Add reactivation note with timestamp
- **Dedup key remains unchanged**

### Rule 3: New Ticket for Different Service
```
Customer had 10Mbps (cancelled) → Now wants 50Mbps
→ Create NEW sales order → NEW ticket (new row)
```

**Implementation:**
- Create new sales order (different package)
- Create new ticket with new dedup_key
- History shows multiple tickets for customer

---

## Database Space Efficiency

### Scenario: Chronic Canceller

**Without Reactivation (Wasteful):**
```sql
-- Customer orders/cancels 5 times
INSERT INTO tickets (...) -- Ticket 1
INSERT INTO tickets (...) -- Ticket 2
INSERT INTO tickets (...) -- Ticket 3
INSERT INTO tickets (...) -- Ticket 4
INSERT INTO tickets (...) -- Ticket 5
-- Result: 5 rows for same sales order
```

**With Reactivation (Efficient):**
```sql
-- Customer orders/cancels 5 times
INSERT INTO tickets (...) -- Ticket 1 created
UPDATE tickets SET status='cancelled' -- Cancelled
UPDATE tickets SET status='open' -- Reactivated
UPDATE tickets SET status='cancelled' -- Cancelled again
UPDATE tickets SET status='open' -- Reactivated again
-- Result: 1 row for sales order
-- Notes field tracks all state changes
```

**Savings:**
- 1 row instead of 5
- 80% space reduction
- Payment history preserved
- Chronic canceller easily identified

---

## Dedup Key Strategy

### Format
```
dedup_key = MD5(source + '::' + source_id + '::' + ticket_type + '::' + created_at)
```

### Properties
- **Permanent**: Never cleared (even on completion/cancellation)
- **Unique**: Timestamp ensures uniqueness
- **Tracks Creation**: Identifies when ticket was first created
- **Not Lifecycle**: Doesn't change with status updates

### Why Timestamp?
1. **Prevents Collisions**: If same source tries multiple times
2. **Audit Trail**: Know when ticket was created
3. **Allows Multiple Tickets**: New creation = new timestamp = new dedup_key
4. **Reactivation Support**: Same dedup_key when reactivating

---

## API Behavior

### POST /api/v1/tickets/from-sales-order

**Request:**
```json
{
  "sales_order_id": "uuid-123",
  "priority": "normal",
  "scheduled_date": "2024-03-20",
  "notes": "Customer wants morning installation"
}
```

**Response (New Ticket):**
```json
{
  "id": "ticket-uuid",
  "source": "sales_order",
  "source_id": "uuid-123",
  "status": "open",
  "dedup_key": "abc123...",
  "created_at": "2024-03-15T10:00:00Z"
}
```

**Response (Reactivated Ticket):**
```json
{
  "id": "ticket-uuid",
  "source": "sales_order",
  "source_id": "uuid-123",
  "status": "open",  // Changed from 'cancelled'
  "dedup_key": "abc123...",  // Same as before
  "created_at": "2024-03-10T08:00:00Z",  // Original creation date
  "updated_at": "2024-03-15T10:00:00Z",  // Now
  "notes": "[REACTIVATED 2024-03-15T10:00:00Z] Customer wants morning installation\n[CANCELLED 2024-03-12] Customer changed mind\n..."
}
```

---

## Customer Behavior Tracking

### Identifying Chronic Cancellers

**Query:**
```sql
-- Find tickets that were reactivated multiple times
SELECT 
    ticket_name,
    status,
    notes,
    (LENGTH(notes) - LENGTH(REPLACE(notes, '[REACTIVATED', ''))) / LENGTH('[REACTIVATED') as reactivation_count
FROM tickets
WHERE notes LIKE '%[REACTIVATED%'
ORDER BY reactivation_count DESC;
```

**Business Value:**
- Identify customers who frequently cancel
- Adjust payment terms (require upfront payment)
- Risk assessment for resource allocation
- Customer support insights

---

## Payment History Preservation

### Scenario: Customer Paid Before Cancelling

**Without Reactivation:**
```
Ticket 1 (cancelled) → Has payment record
Ticket 2 (new) → No link to previous payment
→ Lost payment history! ❌
```

**With Reactivation:**
```
Ticket 1 (cancelled) → Has payment record
Ticket 1 (reactivated) → Still has payment record
→ Payment history preserved! ✅
```

**Additional Metadata Example:**
```json
{
  "payments": [
    {
      "amount": 5000,
      "date": "2024-03-01",
      "status": "refunded",
      "reason": "Customer cancelled before installation"
    }
  ],
  "cancellations": [
    {
      "date": "2024-03-05",
      "reason": "Customer changed mind"
    }
  ],
  "reactivations": [
    {
      "date": "2024-03-15",
      "reason": "Customer wants service again"
    }
  ]
}
```

---

## Summary

### Key Features
1. ✅ **Space Efficient**: Reactivates existing tickets instead of creating duplicates
2. ✅ **Payment History**: Preserves financial records when reactivating
3. ✅ **Customer Behavior**: Tracks chronic cancellers via reactivation notes
4. ✅ **Audit Trail**: Dedup key is permanent (tracks creation instance)
5. ✅ **Flexibility**: New service = new ticket, same service = reactivate

### Database Impact
- **Reduces rows**: 1 ticket per sales order (not 1 per order attempt)
- **Preserves data**: Payment history, notes, metadata
- **Clear history**: Notes field shows all state changes with timestamps

### Business Value
- **Cost Savings**: Less database storage
- **Better Analytics**: True customer behavior tracking
- **Financial Accuracy**: Payment history never lost
- **Operational Efficiency**: One ticket to manage per order

---

## Migration Notes

**No database migration needed!** 

The existing schema already supports this:
- `dedup_key` is already unique and permanent
- `status` column supports state transitions
- `notes` field stores reactivation history
- Logic change only (service layer, not schema)

**Code Changes:**
- ✅ Service: Check for cancelled tickets before creating new
- ✅ Service: Reactivate logic (update status, dates, notes)
- ✅ API: Document reactivation behavior
- ✅ Docs: Update business rules