# SOP: Production Ledger Imbalance Fix & Period Closing Deployment

This Standard Operating Procedure (SOP) outlines the step-by-step process to safely audit/repair ledger imbalances in the production database and deploy the new Period Closing (`tutup-buku`) feature.

---

## SOP Summary Workflow
```mermaid
graph TD
    A[Phase 1: Backup & Pre-Audit] --> B[Phase 2: Automatic & Manual Data Repair]
    B --> C[Phase 3: Code Deployment & Migrations]
    C --> D[Phase 4: Book Closing & Verification]
```

---

## Phase 1: Pre-Deployment Backup & Pre-Audit
Before changing any code or running repair scripts, establish a fallback state and measure the exact size of the mismatch.

### 1. Database Backup (CRITICAL)
Create a full SQL dump of the production database so that you can rollback if necessary.
```bash
mysqldump -u [username] -p [database_name] > backup_pre_repair_$(date +%F).sql
```

### 2. Check for Missing COA Metadata (INNER JOIN checks)
Before proceeding, check if any active journal entries point to deleted or missing COA accounts. Since the reports use an `INNER JOIN coas` query, any journal rows with missing COA IDs will be completely hidden from the Balance Sheet and Trial Balance:
```sql
SELECT COUNT(*) AS missing_coa_journals,
       SUM(debit) AS omitted_debit,
       SUM(credit) AS omitted_credit,
       SUM(debit) - SUM(credit) AS omitted_net_diff
FROM journals
WHERE coa_id NOT IN (SELECT id FROM coas) AND deleted_at IS NULL;
```
* **If result is 0:** Excellent. No missing COA metadata.
* **If result > 0:** You have deleted COA accounts! You must run the `restore_coa.sql` script to recreate the missing accounts in the `coas` table before the balance sheets will display balanced numbers.

### 3. Measure Current Ledger Imbalance
Run this query to find the total debit, credit, and net imbalance for the active school year:
```sql
SELECT SUM(debit) AS total_debit, 
       SUM(credit) AS total_credit, 
       SUM(debit) - SUM(credit) AS net_imbalance
FROM journals
WHERE deleted_at IS NULL AND tahun_id = [ACTIVE_YEAR_ID];
```
*Record this number. Our goal is to bring the `net_imbalance` to exactly `0`.*

---

## Phase 2: Ledger Repair (Automatic & Manual)
The general ledger **must** be perfectly balanced before the Period Closing feature can be executed, as the closing system enforces this constraint.

### 1. Run Automatic Repair Command (Dry Run First)
Run the Artisan command in dry-run mode to inspect proposed fixes without writing to the database:
```bash
php artisan audit:fix-ledger-imbalances --dry-run
```
*Verify the output matches your expectations. If correct, run the actual repair:*
```bash
php artisan audit:fix-ledger-imbalances
```

*(Note: If you do not have Artisan shell access, you can run the equivalent SQL script in [fixing script.sql](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/dumps/fixing%20script.sql#L353) inside a transactional block in your database GUI client).*

### 2. Audit & Repair Skipped Transactions (Manual)
The automatic repair script safely skips complex **Type 2 (Bill Payments / Bayaran)** transactions containing negative debits or complex multi-bill allocations (such as transaction `966288`).
To locate the remaining skipped imbalanced transactions:
```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;
```
For each skipped transaction:
1. Query its transaction details and journal entries (see guides in [FIX_IMBALANCES.md](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/docs/FIX_IMBALANCES.md#L57)).
2. Apply manual SQL adjustments (e.g. updating positive absolute value debit/credit columns and setting negative offsets to `0`) inside a `START TRANSACTION` / `COMMIT` block.

---

## Phase 3: Code Deployment & Migrations
Now that the database records are healthy, we can deploy the safety guards and period closing code.

### 1. Deploy Frontend & Backend Code
Pull the latest commits from the backend (`tutup-buku` branch) and frontend (`vite-staging` branch).

### 2. Run Database Migrations
Run the migrations to create the `period_closings` table and add `is_closed` columns to the school periods table:
```bash
php artisan migrate
```

### 3. Verify Active Integrity Guards
The newly deployed APIs contain the post-insert validator:
* Any future write action that attempts to write an imbalanced transaction will fail-loud and throw a `RuntimeException`, triggering a full database rollback.
* Concurrently triggered bank webhooks are now guarded by row-level database locks (`lockForUpdate()`), preventing duplicate payments.

---

## Phase 4: Book Closing & Verification
With the codebase updated and the ledger balanced, we can lock down the history.

### 1. Run Final Ledger Sanity Check
This query **must return 0 rows**:
```sql
SELECT transaction_id, 
       SUM(debit) AS total_debit, 
       SUM(credit) AS total_credit
FROM journals
WHERE deleted_at IS NULL
GROUP BY transaction_id
HAVING SUM(debit) <> SUM(credit);
```

### 2. Execute Period Closing (TUTUP BUKU)
1. Go to the school year configuration UI in the frontend dashboard.
2. Select the year you wish to close.
3. Click the **Close Period** button.
   * *The system will double-check that the ledger is balanced, insert a period closing ledger entry, and lock the period.*
   * *Once closed, the API will reject any backdated edits, updates, or deletes to journals/transactions in this period.*