# Step-by-Step Ledger Imbalance Fix & Audit Guide

This document explains how to audit and fix general ledger imbalances using our custom Artisan command and manual SQL procedures.

---

## 1. How the Command Works Under the Hood

The `audit:fix-ledger-imbalances` command operates in a transactional, set-based manner to heal ledger imbalances. Here is what it does when executed:

### A. Webhook Duplicate Cleanup (Race Conditions)
1. **Detection:** Scans the `transactions` table for multiple records sharing the same non-null `payment_id`.
2. **Identification:** For each duplicate group, it queries the `journals` table to calculate `SUM(debit)` and `SUM(credit)`. It identifies the **primary healthy transaction** (which has balanced journals, i.e., `debit = credit > 0`).
3. **Purge:** It keeps the primary balanced transaction, and automatically deletes the duplicate/corrupted transactions along with their orphaned journal entries from the database.

### B. Single-Legged Balance Transfers (Type 14) Repair
1. **Detection:** Finds transactions of type `14` (Balance Transfer) where the journal has exactly 1 entry (either only credit or only debit).
2. **Reconstruction:** 
   * **If missing Debit (sender leg):** It reads the `balance_id` from the transaction, finds the sender's user type (Student or Employee), loads their school ID, and resolves the correct `Tabungan` COA (e.g., `Tabungan siswa SDIT`, `Tabungan pegawai`). It then inserts the missing debit journal row.
   * **If missing Credit (receiver leg):** It reads the `to_balance_id` from the transaction, loads the recipient's school ID, resolves the correct `Tabungan` COA, and inserts the missing credit journal row.

### C. Single-Legged Topups (Type 3) Repair
1. **Detection:** Finds transactions of type `3` (Topup Jajan) with only 1 journal entry.
2. **Reconstruction:**
   * **If missing Credit (saving account):** Resolves the user's `Tabungan` COA and inserts the credit row.
   * **If missing Debit (bank/cash account):** Queries the `payment_bank_settlement` pivot table using the transaction's `payment_id` to find the bank account used. It resolves the matching bank account COA and inserts the debit row.

### D. Safe Transaction Control
* The entire batch run is wrapped in a `DB::beginTransaction()`. If any single query fails or throws an exception, all changes are rolled back automatically, ensuring zero partial state writes.

---

## 2. Command Execution Guide

### A. Local Development (Docker)
Always run a **dry-run** first to preview proposed repairs without writing to the database:
```bash
docker exec -it ziad-laravel-template-php php artisan audit:fix-ledger-imbalances --dry-run
```
To execute the repairs and commit them:
```bash
docker exec -it ziad-laravel-template-php php artisan audit:fix-ledger-imbalances
```

### B. Production Server
Dry-run:
```bash
php artisan audit:fix-ledger-imbalances --dry-run
```
Execute repairs:
```bash
php artisan audit:fix-ledger-imbalances
```

---

## 3. Pure SQL Command Equivalent (For Direct Database Access)

If you cannot run PHP Artisan commands on your production database shell (e.g., due to access restrictions or standard container limits), we have written a complete, equivalent SQL script.

You can find the full script at the bottom of [fixing script.sql](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/dumps/fixing%20script.sql#L353).

### How to use the SQL script:
1. **Preview Changes:** Run queries under `A. PREVIEW CHANGES (DRY RUN)` to check which duplicate transactions will be purged and which missing journal legs will be generated.
2. **Execute Repairs:** Run the block under `B. EXECUTION BLOCK` inside a `START TRANSACTION` block.
3. **Verify:** Check that the verification query returns `0` rows of mismatches before executing `COMMIT;`. Otherwise, run `ROLLBACK;`.

---

## 4. Manual Audit Guide (For Unrepairable Cases)

If the script outputs:
`Fix failed: Transaction pattern not automatically repairable. Review manually.`
This usually happens for **Type 2 (Bill Payments / Bayaran)**. These transactions are complex because they involve multi-bill allocations, partial payments, or manual accounting adjustments.

Here is how to audit and repair them manually.

### Step 1: Gather Transaction and Journal Evidence
For the failed Transaction ID (e.g., `955957`), query its metadata and journal logs:

```sql
-- Query 1: Get parent transaction details
SELECT id, number, amount, transactionDate, transaction_type_value, description 
FROM transactions 
WHERE id = 955957;

-- Query 2: Get all associated journal entries
SELECT id, coa_id, coa_name, debit, credit 
FROM journals 
WHERE transaction_id = 955957 AND deleted_at IS NULL;
```

### Step 2: Compare Mismatch and Determine the Error
Calculate the sum of debits and credits from Query 2:
* **Balanced Ledger Rule:** `SUM(debit)` must equal `SUM(credit)`, and both must equal the parent transaction's `amount`.
* **Mismatched Leg:** If `debit = 3,140,000` but `credit = 1,540,000`, the transaction is unbalanced by `1,600,000` (missing credit).
* **Negative Values:** Check if any debit or credit column contains negative values (e.g., `-35,000`), which is a database corruption symptom from old adjustment bugs.

### Step 3: Write and Run the Repair Script in a Transaction Block

#### Scenario A: The Credit or Debit leg is missing/mismatched by an amount
Suppose the transaction `955957` (amount `3,140,000`) has a debit entry for `3,140,000` but the credit entry was only written as `1,540,000`. We need to increase the credit entry by the difference (`1,600,000`) or insert a compensating entry.

```sql
START TRANSACTION;

-- Option 1: Update the mismatched journal row (if one exists with the wrong amount)
UPDATE journals 
SET credit = 3140000 
WHERE transaction_id = 955957 AND coa_id = 2101; -- Replace with the affected COA ID

-- Option 2: Insert a compensating entry to balance the transaction
-- (Used if the missing amount needs to go to a separate account or a second leg was omitted)
INSERT INTO journals (transaction_id, transactionDate, coa_id, coa_name, tahun_id, debit, credit, created_at, updated_at)
SELECT 
    t.id, 
    t.transactionDate, 
    336,                    -- Replace with correct COA ID
    'Tabungan siswa SDIT',  -- Replace with correct COA Name
    t.tahun_id, 
    0, 
    1600000,                -- Missing credit amount
    NOW(), 
    NOW()
FROM transactions t 
WHERE t.id = 955957;

-- Verification: Sum check
SELECT SUM(debit) AS total_debit, SUM(credit) AS total_credit 
FROM journals 
WHERE transaction_id = 955957 AND deleted_at IS NULL;

-- If total_debit == total_credit, commit the transaction:
COMMIT;
-- If they do not match, rollback:
-- ROLLBACK;
```

#### Scenario B: Negative Debit/Credit values are present
If you find journal entries with negative amounts, update them to `0` and adjust the main leg to represent the positive absolute value:

```sql
START TRANSACTION;

-- Correct negative debit entries by shifting the offset back to the proper positive columns
UPDATE journals 
SET debit = 0, credit = credit + 35000 
WHERE id = [journal_row_id] AND debit = -35000;

COMMIT;
```

---

## 4. Post-Repair Verification

After running the command or applying manual fixes, run this final audit check. It must return **0 rows**:

```sql
SELECT transaction_id, 
       SUM(debit) AS total_debit, 
       SUM(credit) AS total_credit, 
       SUM(debit) - SUM(credit) AS difference
FROM journals
WHERE deleted_at IS NULL
GROUP BY transaction_id
HAVING total_debit <> total_credit;
```

---

## 5. Reconstructing Deleted COA Records (UI Neraca Imbalance)

If a COA record is deleted/truncated from the database, but historical journals referencing its `coa_id` remain, accounting reports query joins (e.g. `INNER JOIN coas`) will silently filter out these rows, causing the Balance Sheet UI (Neraca) to show an imbalance even though the ledger itself is balanced.

To audit for missing COA references:
```sql
SELECT DISTINCT j.coa_id, j.coa_name 
FROM journals j 
LEFT JOIN coas c ON c.id = j.coa_id 
WHERE c.id IS NULL AND j.deleted_at IS NULL;
```

If any missing COAs are found, execute the following reconstruction query block on your database shell to restore them:

```sql
START TRANSACTION;

INSERT INTO coas (id, name, code, `group`, description, school_id)
SELECT DISTINCT 
  j.coa_id AS id,
  j.coa_name AS name,
  CONCAT('99', j.coa_id) AS code,
  CASE 
    WHEN j.coa_name LIKE 'Piutang%' THEN 'PIUTANG'
    WHEN j.coa_name LIKE 'Kas%' OR j.coa_name LIKE 'Bank%' OR j.coa_name LIKE 'BJB%' THEN 'HARTA'
    WHEN j.coa_name LIKE 'Tabungan%' OR j.coa_name LIKE 'Utang%' OR j.coa_name LIKE 'Kewajiban%' THEN 'UTANG'
    WHEN j.coa_name LIKE 'Modal%' OR j.coa_name LIKE 'Laba%' THEN 'MODAL'
    WHEN j.coa_name LIKE 'Pendapatan%' THEN 'PENDAPATAN'
    WHEN j.coa_name LIKE 'Biaya%' OR j.coa_name LIKE 'Beban%' THEN 'BIAYA'
    ELSE 'HARTA'
  END AS `group`,
  'Reconstructed historical COA' AS description,
  CASE 
    WHEN j.coa_name LIKE '%TK%' THEN 2
    WHEN j.coa_name LIKE '%SD%' THEN 3
    WHEN j.coa_name LIKE '%SMP%' THEN 4
    WHEN j.coa_name LIKE '%SMK%' THEN 5
    ELSE NULL
  END AS school_id
FROM journals j
LEFT JOIN coas c ON c.id = j.coa_id
WHERE c.id IS NULL;

COMMIT;
```

---

## 6. Resolving School Year-Level Imbalances (Tutup Buku Errors)

If a student pays a bill in advance or pays arrears across school years, the debit leg (e.g. BJB Syariah cash receipt) is recorded in the active year, while the credit leg (Piutang reduction) is recorded in the bill's school year. This splits the transaction's journals across different `tahun_id` values. Although the transaction is internally balanced, each school year's ledger on its own becomes unbalanced, preventing period closing.

### A. Audit Year-Level Imbalances
To check the balance of debits and credits grouped by school year:
```sql
SELECT tahun_id, 
       SUM(debit) AS total_debit, 
       SUM(credit) AS total_credit, 
       SUM(debit) - SUM(credit) AS difference
FROM journals
WHERE deleted_at IS NULL
GROUP BY tahun_id;
```

### B. Repair Year-Level Imbalances
To resolve this, synchronize the `tahun_id` of all journal entries to match their parent transactions' `tahun_id` (this groups both legs of the payment within the active year they occurred, preserving ledger balance):

```sql
START TRANSACTION;

-- Synchronize journal tahun_id to match parent transaction tahun_id
UPDATE journals j
INNER JOIN transactions t ON t.id = j.transaction_id
SET j.tahun_id = t.tahun_id
WHERE j.deleted_at IS NULL 
  AND t.deleted_at IS NULL
  AND j.tahun_id <> t.tahun_id;

-- Verify all differences are now 0
SELECT tahun_id, 
       SUM(debit) AS total_debit, 
       SUM(credit) AS total_credit, 
       SUM(debit) - SUM(credit) AS difference
FROM journals
WHERE deleted_at IS NULL
GROUP BY tahun_id;

-- If all differences are 0, commit:
COMMIT;
-- Otherwise:
-- ROLLBACK;
```