# Query untuk Cek SALES_RECEIPT di SQLite

## Query 1: Cek Apakah Semua DFT (Paling Penting)

```sql
SELECT 
    id,
    type,
    status,
    -- Extract salesReceiptNo dari JSON payload
    json_extract(payload, '$.data[0].salesReceiptNo') AS receipt_no,
    datetime(created_at) as created
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
ORDER BY id DESC
LIMIT 50;
```

**Jika SQLite versi lama tidak support `json_extract`, pakai query ini:**

```sql
SELECT 
    id,
    type,
    status,
    -- Manual extract dengan substr (lebih robust)
    CASE 
        WHEN instr(payload, '"salesReceiptNo"') > 0 THEN
            substr(
                payload,
                instr(payload, '"salesReceiptNo"') + 17,
                CASE 
                    WHEN instr(substr(payload, instr(payload, '"salesReceiptNo"') + 17), '"') > 0 
                    THEN instr(substr(payload, instr(payload, '"salesReceiptNo"') + 17), '"') - 1
                    ELSE 30
                END
            )
        ELSE 'NOT_FOUND'
    END AS receipt_no,
    datetime(created_at) as created
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
ORDER BY id DESC
LIMIT 50;
```

---

## Query 2: Lihat Full Payload (Untuk Debug)

```sql
SELECT 
    id,
    type,
    status,
    payload,  -- Full payload untuk lihat struktur
    datetime(created_at) as created
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
ORDER BY id DESC
LIMIT 10;
```

**Copy salah satu payload, lalu parse manual atau pakai `jq`:**

```bash
# Di terminal, setelah dapat payload
echo 'PASTE_PAYLOAD_DISINI' | jq '.data[0].salesReceiptNo'
```

---

## Query 3: Hitung DFT vs BKB

```sql
SELECT 
    CASE 
        WHEN json_extract(payload, '$.data[0].salesReceiptNo') LIKE 'DFT%' THEN 'DFT'
        WHEN json_extract(payload, '$.data[0].salesReceiptNo') LIKE 'BK%' THEN 'BKB'
        ELSE 'OTHER'
    END AS receipt_type,
    COUNT(*) as count
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
GROUP BY receipt_type;
```

**Jika tidak support `json_extract`, pakai:**

```sql
SELECT 
    CASE 
        WHEN payload LIKE '%"salesReceiptNo":"DFT%' THEN 'DFT'
        WHEN payload LIKE '%"salesReceiptNo":"BK%' THEN 'BKB'
        ELSE 'OTHER'
    END AS receipt_type,
    COUNT(*) as count
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
GROUP BY receipt_type;
```

---

## Query 4: Bandingkan SALES_RECEIPT vs SALES_INVOICE

```sql
SELECT 
    type,
    status,
    COUNT(*) as count,
    MIN(datetime(created_at)) as first,
    MAX(datetime(created_at)) as last
FROM webhook_queue 
WHERE datetime(created_at) >= '2026-02-02 00:00:00'
GROUP BY type, status
ORDER BY type, status;
```

---

## Cara Pakai di Terminal

**Langsung dari shell (tanpa masuk sqlite3):**

```bash
# Query 1: Cek receipt_no
sqlite3 /opt/webhook-service/data/webhook.db "
SELECT 
    id,
    CASE 
        WHEN payload LIKE '%\"salesReceiptNo\":\"DFT%' THEN 'DFT'
        WHEN payload LIKE '%\"salesReceiptNo\":\"BK%' THEN 'BKB'
        ELSE 'OTHER'
    END AS receipt_type,
    datetime(created_at) as created
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
ORDER BY id DESC
LIMIT 50;
"

# Query 2: Hitung DFT vs BKB
sqlite3 /opt/webhook-service/data/webhook.db "
SELECT 
    CASE 
        WHEN payload LIKE '%\"salesReceiptNo\":\"DFT%' THEN 'DFT'
        WHEN payload LIKE '%\"salesReceiptNo\":\"BK%' THEN 'BKB'
        ELSE 'OTHER'
    END AS receipt_type,
    COUNT(*) as count
FROM webhook_queue 
WHERE type = 'SALES_RECEIPT'
    AND datetime(created_at) >= '2026-02-02 10:00:00'
GROUP BY receipt_type;
"
```

---

## Troubleshooting

**Jika query tidak mengembalikan hasil:**

1. **Cek apakah ada data SALES_RECEIPT setelah jam 10:**
   ```sql
   SELECT COUNT(*) FROM webhook_queue 
   WHERE type = 'SALES_RECEIPT' 
       AND datetime(created_at) >= '2026-02-02 10:00:00';
   ```

2. **Cek format datetime:**
   ```sql
   SELECT created_at, datetime(created_at) 
   FROM webhook_queue 
   WHERE type = 'SALES_RECEIPT' 
   LIMIT 5;
   ```

3. **Cek struktur payload:**
   ```sql
   SELECT substr(payload, 1, 200) FROM webhook_queue 
   WHERE type = 'SALES_RECEIPT' 
   LIMIT 1;
   ```

---

**Mulai dari Query 3 (Hitung DFT vs BKB)** - itu yang paling penting untuk tahu apakah semua DFT!