# Gadai Mulia Collection Broadcast Integration
## Complete Implementation Guide

---

## 📋 Overview

**Purpose:** Send collection invoices via WhatsApp (Sopiga Collar API) to Gadai Mulia customers.

**Architecture:** 
```
Gadai Collection Service 
  → INSERT broadcast_staging (JSON payload)
    → Go Worker (poll every 30s)
      → Query template_variable_mapping
        → Build message dynamically
          → POST Sopiga Collar API
            → WhatsApp delivery
              → Update status (delivered/failed)
```

**Key Innovation:** Flexible template system via `template_variable_mapping` table — NO code redeploy for new templates.

---

## 🗄️ Database Schema (Simplified Denormalized)

### Master Configuration (Read-only)

**Table: sopiga_template_config**
- Stores Sopiga template references (pre-created in Sopiga)
- Fields: `id`, `sopiga_template_id`, `template_name`, `channel` (waba), `template_type`, `description`, `active`
- Pre-populate: One row per template in Sopiga

**Table: template_variable_mapping**
- Maps Sopiga template variables → message_payload keys
- **CRITICAL:** `variable_order` determines message interpolation sequence
- Fields: `id`, `sopiga_template_id`, `variable_order`, `sopiga_variable_name`, `variable_type` (string/integer/date/decimal), `db_field_source`, `is_required`, `example_value`
- Example:
  ```
  sopiga_template_id=2, order=1, name='Nama', field='nasabah_nama'
  sopiga_template_id=2, order=2, name='TotalTagihan', field='nominal_tagihan'
  sopiga_template_id=2, order=3, name='TanggalJatuhTempo', field='tanggal_tempo'
  ```

**Table: sopiga_collar_config**
- Stores Sopiga collar references (pre-created in Sopiga)
- Fields: `id`, `sopiga_collar_id`, `broadcast_name`, `sopiga_template_id` (FK), `status` (open/closed), `description`
- Pre-populate: One row per collar in Sopiga

### Processing (Core)

**Table: broadcast_staging** ← Main table
- DENORMALIZED: All data in `message_payload` (JSONB)
- NO FK to nasabah or gadai_contract
- Fields:
  - `id` (PK)
  - `sopiga_collar_id` (FK)
  - `sopiga_template_id` (FK)
  - `message_payload` (JSONB) ← **Single source of truth**
  - `status` (pending → dispatched → delivered/failed)
  - `error_message`, `error_count`
  - `sopiga_recipient_detail_id` (response from Sopiga)
  - `created_at`, `updated_at`, `dispatched_at`, `delivered_at`, `failed_at`

**Example message_payload:**
```json
{
  "nasabah_nama": "Budi Santoso",
  "nasabah_phone": "6281234567890",
  "nominal_tagihan": 1500000,
  "tanggal_tempo": "2026-06-30",
  "invoice_pdf_url": "https://storage.gadai.com/invoices/inv-001.pdf",
  "contract_no": "GAD-2026-001"
}
```

### Audit & Tracking

**Table: broadcast_audit_log**
- Tracks status transitions
- Fields: `id`, `broadcast_id` (FK), `old_status`, `new_status`, `reason`, `sopiga_response` (JSONB), `changed_by`, `created_at`

**Table: broadcast_error_log**
- Detailed error tracking
- Fields: `id`, `broadcast_id` (FK), `error_type`, `error_code`, `error_message`, `error_details` (JSONB), `attempt_number`, `next_retry_at`

**Table: sopiga_sync_job**
- Tracks delivery status sync from Sopiga
- Fields: `id`, `broadcast_id` (FK), `sopiga_recipient_detail_id`, `last_synced_at`, `last_status_from_sopiga`, `sync_count`, `next_sync_at`

---

## 🔧 Setup Process

### Step 1: Setup in Sopiga (Manual)

1. **Create template** in Sopiga:
   ```
   POST https://omnix.promas.site/api/client/template
   {
     "template_name": "Collection Invoice",
     "channel": "waba",
     "variables": [
       {"name": "Nama", "type": "string"},
       {"name": "TotalTagihan", "type": "integer"},
       {"name": "TanggalJatuhTempo", "type": "date"}
     ]
   }
   → Response: template_id = 2
   ```

2. **Create collar** in Sopiga:
   ```
   POST https://omnix.promas.site/api/client/collar
   {
     "judul_broadcast": "Invoice May 2026",
     "template_id": 2
   }
   → Response: collar_id = 70
   ```

### Step 2: Register in Database (5 minutes)

**2.1 Insert template config:**
```sql
INSERT INTO collection_broadcasts.sopiga_template_config 
  (template_name, sopiga_template_id, channel, template_type, description)
VALUES 
  ('Collection Invoice May 2026', 2, 'waba', 'utility', 'Invoice bulanan');
```

**2.2 Insert variable mappings (ORDER IS CRITICAL!):**
```sql
INSERT INTO collection_broadcasts.template_variable_mapping 
  (sopiga_template_id, variable_order, sopiga_variable_name, variable_type, db_field_source, is_required, example_value)
VALUES
  (2, 1, 'Nama', 'string', 'nasabah_nama', TRUE, 'Budi Santoso'),
  (2, 2, 'TotalTagihan', 'integer', 'nominal_tagihan', TRUE, '1500000'),
  (2, 3, 'TanggalJatuhTempo', 'date', 'tanggal_tempo', TRUE, '2026-06-30');
```

**2.3 Insert collar config:**
```sql
INSERT INTO collection_broadcasts.sopiga_collar_config 
  (broadcast_name, sopiga_collar_id, sopiga_template_id, status)
VALUES 
  ('Collection Invoices May 2026', 70, 2, 'open');
```

### Step 3: Deploy Application

Deploy Go worker with:
- Database connection string
- Sopiga base URL: `https://omnix.promas.site`
- Sopiga API token

Worker runs continuously:
- **Broadcast dispatch:** Poll every 30s, process pending records
- **Status sync:** Poll every 5 min, sync delivery status from Sopiga

---

## 🔄 How It Works

### Runtime Flow

**Gadai Collection Service (sends data):**
```go
// When collection reminder needed:
payload := map[string]interface{}{
  "nasabah_nama": "Budi Santoso",
  "nasabah_phone": "6281234567890",
  "nominal_tagihan": 1500000.0,
  "tanggal_tempo": "2026-06-30",
  "invoice_pdf_url": "https://storage.gadai.com/invoices/inv-001.pdf",
  "contract_no": "GAD-2026-001",
}

// Insert to broadcast_staging
db.Exec(`
  INSERT INTO collection_broadcasts.broadcast_staging 
    (sopiga_collar_id, sopiga_template_id, message_payload, status)
  VALUES ($1, $2, $3, 'pending')
`, 70, 2, payload)
```

**Go Worker (processes data - automatic):**
```
1. Poll broadcast_staging WHERE status='pending' LIMIT 100
2. For each record:
   a. Get message_payload from database (already has all fields)
   b. Query template_variable_mapping WHERE sopiga_template_id=2 ORDER BY variable_order
   c. Build message:
      - For each variable in order (1,2,3...):
        - Get value from payload[db_field_source]
        - Format by type (string/integer/date)
        - Append to parts[]
      - message = parts.join("#")
      - Result: "Budi Santoso#1500000#2026-06-30"
   
   d. Call Sopiga API:
      POST /api/client/collar/add-recipient
      {
        "broadcast_id": 70,
        "template_id": 2,
        "details": {
          "recipient": "6281234567890",
          "message": "Budi Santoso#1500000#2026-06-30"
        },
        "attachment": {
          "type": "document",
          "file": "https://storage.gadai.com/invoices/inv-001.pdf"
        }
      }
   
   e. Response (HTTP 202 Accepted):
      {
        "success": true,
        "data": {
          "recipient_detail_id": 512,
          "status": "pending"
        }
      }
   
   f. Update broadcast_staging:
      UPDATE status='dispatched', sopiga_recipient_detail_id=512

3. Status sync worker (every 5 min):
   - For dispatched records:
   - GET /api/client/collar/add-recipient/{recipient_detail_id}/detail
   - Update status: delivered OR failed
```

---

## 📊 Adding New Template (Zero Code Change)

### Scenario: Template now has 4 variables instead of 3

**Old:**
```
1. Nama
2. TotalTagihan
3. TanggalJatuhTempo
```

**New:**
```
1. Nama
2. NoKontrak ← NEW!
3. TotalTagihan
4. TanggalJatuhTempo
```

### Setup (5 minutes, Database only)

1. Create template in Sopiga → template_id = 3
2. Create collar in Sopiga → collar_id = 71

3. Register in database:
```sql
-- Template
INSERT INTO sopiga_template_config (template_name, sopiga_template_id, ...)
VALUES ('Collection Invoice Extended', 3, ...);

-- Variables (URUTAN PENTING!)
INSERT INTO template_variable_mapping 
  (sopiga_template_id, variable_order, sopiga_variable_name, variable_type, db_field_source, ...)
VALUES
  (3, 1, 'Nama', 'string', 'nasabah_nama', ...),
  (3, 2, 'NoKontrak', 'string', 'contract_no', ...),        ← NEW!
  (3, 3, 'TotalTagihan', 'integer', 'nominal_tagihan', ...),
  (3, 4, 'TanggalJatuhTempo', 'date', 'tanggal_tempo', ...);

-- Collar
INSERT INTO sopiga_collar_config (broadcast_name, sopiga_collar_id, sopiga_template_id, ...)
VALUES ('Collection Invoices June 2026', 71, 3, ...);
```

**Go code?** ✅ ZERO changes — `buildDynamicMessage()` queries mapping every time.

---

## 🚀 Gadai Service Integration

**How Gadai Collection Service calls this:**

```go
package gadai

type CollectionBroadcastService struct {
  db *sql.DB
}

func (s *CollectionBroadcastService) SendInvoiceReminder(
  ctx context.Context,
  nasabahID, contractNo string,
  nominalTagihan float64,
  dueDate time.Time,
) error {
  // Get invoice URL (from Gadai storage or API)
  invoiceURL := fmt.Sprintf("https://storage.gadai.com/invoices/%s.pdf", contractNo)
  
  // Build payload (flexible - can have extra fields)
  payload := map[string]interface{}{
    "nasabah_nama": "Budi Santoso",
    "nasabah_phone": "6281234567890",
    "nominal_tagihan": nominalTagihan,
    "tanggal_tempo": dueDate.Format("2006-01-02"),
    "invoice_pdf_url": invoiceURL,
    "contract_no": contractNo,
    "cif": "12345",  // Extra field - ignored by worker if not in mapping
  }
  
  // Marshal to JSONB
  payloadJSON, _ := json.Marshal(payload)
  
  // Insert to broadcast_staging
  // Worker will automatically pick it up (every 30s poll)
  query := `
    INSERT INTO collection_broadcasts.broadcast_staging 
      (sopiga_collar_id, sopiga_template_id, message_payload, status)
    VALUES ($1, $2, $3, 'pending')
  `
  
  _, err := s.db.ExecContext(ctx, query,
    70,  // sopiga_collar_id (hardcoded or from config)
    2,   // sopiga_template_id (hardcoded or from config)
    payloadJSON,
  )
  
  return err
}
```

---

## 📈 Monitoring & Reporting

### Views Available

**v_template_variables_ordered**
```sql
SELECT * FROM collection_broadcasts.v_template_variables_ordered;
-- Shows all template variables with proper ordering
```

**v_collar_summary**
```sql
SELECT * FROM collection_broadcasts.v_collar_summary;
-- Shows delivery rate per collar
```

**v_delivery_rate_24h**
```sql
SELECT * FROM collection_broadcasts.v_delivery_rate_24h;
-- Shows delivery rate for last 24 hours
```

**v_failed_records_24h**
```sql
SELECT * FROM collection_broadcasts.v_failed_records_24h;
-- Shows failed records with error details
```

---

## 🛠️ Troubleshooting

### Issue: Message format wrong

**Cause:** `variable_order` in mapping doesn't match Sopiga template order

**Fix:** 
```sql
-- Check current mapping
SELECT variable_order, sopiga_variable_name FROM template_variable_mapping 
WHERE sopiga_template_id=2 ORDER BY variable_order;

-- If order wrong, delete and re-insert correctly
DELETE FROM template_variable_mapping WHERE sopiga_template_id=2;
-- Re-insert with correct order
```

### Issue: Missing field in message_payload

**Cause:** Gadai service didn't include field in payload, but it's marked required in mapping

**Fix:**
```sql
-- Either make it optional
UPDATE template_variable_mapping SET is_required=FALSE 
WHERE sopiga_variable_name='NoKontrak';

-- Or ensure Gadai service includes it
```

### Issue: Delivery status stuck at 'dispatched'

**Cause:** Status sync worker not running or Sopiga API unreachable

**Fix:**
```sql
-- Check sync jobs
SELECT * FROM sopiga_sync_job WHERE next_sync_at < NOW();

-- Manually trigger status check
SELECT collection_broadcasts.get_template_variables(2);
```

---

## 🔐 Security Considerations

1. **Sopiga API Token:** Store in environment variable, never hardcode
2. **Database:** Restrict access to `collection_broadcasts` schema
3. **Phone Numbers:** Already PII, treat as sensitive
4. **PDFs:** Ensure URLs are time-limited or access-controlled

---

## 📋 Checklist: New Template

- [ ] Create template in Sopiga, note `template_id`
- [ ] Create collar in Sopiga, note `collar_id`
- [ ] Insert `sopiga_template_config` row
- [ ] Insert `template_variable_mapping` rows (check order!)
- [ ] Insert `sopiga_collar_config` row
- [ ] Verify mapping: `SELECT * FROM v_template_variables_ordered WHERE sopiga_template_id=X`
- [ ] Test with sample record in `broadcast_staging`
- [ ] Monitor first 10 deliveries in logs
- [ ] Confirm in `v_collar_summary` delivery rate

---

## 📞 Support

**Schema files:**
- `gadai_collection_broadcast_simplified_schema.sql` — Complete DDL

**Application files:**
- `collection_broadcast_worker_simplified.go` — Go worker implementation

**Deployment:**
- Requires: PostgreSQL 11+, Go 1.16+
- Connects to: Sopiga API at `https://omnix.promas.site`

---

**Version:** 1.0  
**Last Updated:** 2026-08-03  
**Status:** Ready for deployment ✅