# LLM APIs: End-to-End Business Logic & Specifications

This document defines the internal business logic, calculations, and rules for the APIs in the [llm](file:///c:/laragon/www/tjs/app_api/llm) directory.

---

## 1. Global Authentication Failure (HTTP 401)

All endpoints require authentication by including the [auth.php](file:///c:/laragon/www/tjs/app_api/llm/auth.php) gateway.

If the query parameter `token` is missing or does not match the static key `84aux1j1yuliqy8i95oaprjy`, execution terminates immediately:

*   **HTTP Status**: `401 Unauthorized`
*   **Response Body**:
    ```json
    {
      "status": "Failed",
      "message": "Unauthorized"
    }
    ```

### Edge Cases & Vulnerabilities
*   **Missing Token**: If the request does not specify `?token=...`, the parameter resolves as an empty string (`$_GET['token'] ?? ''`), triggering a 401 error.
*   **Security Vulnerability (GET Parameters)**: Passing tokens in the URL is insecure because they can be logged in web server logs, proxy logs, and browser histories.

---

## 2. API Business Logic Specification

### 2.1. Location-to-Branch Lookup: [cek_cabang.php](file:///c:/laragon/www/tjs/app_api/llm/cek_cabang.php)

Resolves geographic location keywords to their corresponding branch code (`kode_cabang`) and branch name.

#### Core Business Rules
*   **Tiered Geographic Resolution (Search Hierarchy)**: 
    Instead of searching all geographic levels simultaneously, lookup follows a strict priority order:
    1.  **Primary Search (Kabupaten)**: Checks if the search term matches any regency (`A.kabupaten LIKE '%$search%'`). If matches are found, it returns them immediately.
    2.  **Secondary Search (Propinsi/Kecamatan)**: If and only if no kabupaten matches are found, it falls back to querying matches against province (`A.propinsi`) or district (`A.kecamatan`).
*   **Active Branch Association**: Joins the resolved location with `tblmastercabang` to retrieve the registered branch name where `status = 1`.
*   **Branch Name Fallback**: If the associated branch name does not exist or is inactive, the API falls back to using the raw `kode_cabang` as the branch name.
*   **Search Input Filtering**: The `search` parameter is trimmed and sanitized. If it evaluates to empty or the string `"0"`, the request is rejected with `400 Bad Request`.
*   **Response Limit**: Constrained to return a maximum of 50 unique geographic matches sorted alphabetically by regency.

---

### 2.2. Customer Profile & Address Lookup: [datadiri.php](file:///c:/laragon/www/tjs/app_api/llm/datadiri.php)

Retrieves a customer's profile details and associated shipping address book.

#### Core Business Rules
*   **Indonesian Phone Number Normalization**: Converts various input phone number formats to a standardized E.164-style format starting with `+62`:
    1.  Strips spaces (` `), parentheses (`(`, `)`), and dots (`.`).
    2.  If the number starts with `+62`, it is kept.
    3.  If the number starts with `0` (local Indonesian format), `0` is replaced with `+62` (e.g. `0812...` $\rightarrow$ `+62812...`).
    4.  If it starts with any other characters or formats, it prepends `+62`.
*   **Active Registration Check (`aktif = 1`)**: Only retrieves customer profiles where `aktif = 1` from `tblregister`. (Note: `tblregister.status` is not used yet; filtering relies entirely on the `aktif` column for active accounts).
*   **Primary Address Priority**: Retrieves all active addresses (`status = 1`) associated with the customer, sorting primary addresses (`keterangan = 'primary'`) to the top of the list.
*   **Geographic Enrichment**: Joins each address with the master regional JNE table (`tbljne`) on `idkp = jnecode` to map the specific province, regency, and district associated with the shipping address.

---

### 2.3. Product Detail & Price Lookup: [detail_barang.php](file:///c:/laragon/www/tjs/app_api/llm/detail_barang.php)

Retrieves active catalog product details, images, and branch-specific pricing tiers for a specific product code (`kode_barang`) and branch (`kode_cabang`).

#### How It Works (Step-by-Step E2E Business Flow)
1.  **Branch Check & Filtering**:
    *   Verifies if the requested `kode_cabang` is registered as an active branch in `tblmastercabang` where `status = 1`. If not found or inactive, terminates with a `404 Not Found` response.
2.  **Product Fetch & Inner Join**:
    *   Queries `tblmasterbarang` for the active product (`status = 1`) matching the exact `kode_barang`.
    *   Performs an `INNER JOIN` with `tblmasterharga` matching the product code and the requested `kode_cabang`. If the product is not registered or not active in the requested branch, terminates with a `404 Not Found` response.
    *   Performs a `LEFT JOIN` on `tblmasterbarang` as parent to fetch parent variation names (`Parent.nama_barang AS nama_varian`).
3.  **Product Images Fetch**:
    *   Retrieves all associated image paths from `tblmasterbarang_img` sorted by `urutan` ascending, mapped to absolute URLs: `https://trigunajayasentosaplastik.com/{imgpath}`.
4.  **Shopee Links Fetch**:
    *   Retrieves associated Shopee store links (`shopee_pack`, `shopee_gojek`, `shopee_jaskir`, `shopee_kurirtoko`) from `tblmasterbarang_link`.
5.  **Stock Location Resolution**:
    *   Resolves the transaction stock storage location:
        *   **Office Stock (`kode_kantor`)**: Resolved if the product acts as a parent bundle (has no conversion parent `konv_code` but child items reference it).
        *   **Warehouse Stock (`kode_gudang`)**: Resolved for standard, single-pack, and child items.
    *   Aggregates transaction quantities (`tbltransaksibarang` where `status = 1`) for the product in the resolved location to determine final branch stock quantity.
6.  **Markup Price Calculation**:
    *   Applies the branch-specific markup percentages (`persen_pack`, `persen_cargo_triguna`, `persen_pack_ol`, `persen_ol_tp`, `persen_ol_sp`, `persen_ol_lzd`) to the base price (`harga` from `tblmasterharga`) to compute final rounded pricing tiers.
    *   *Conversion quantity fallback*: If `konv_qty` is `<= 0`, it defaults to `1` to prevent division-by-zero errors.
7.  **Flat Output Format Assembly**:
    *   Constructs a flat product object including fields such as `detail_referral`, `uom` (defaulting to 'pcs'), `sku_gudang`, and `images` (array of strings). The object is wrapped in a single-element array under the `data` key.

---

### 2.4. Checkout & Free Shipping Simulation: [freeongkir.php](file:///c:/laragon/www/tjs/app_api/llm/freeongkir.php)

Simulates shipping costs, checks free shipping eligibility for Cargo Triguna, calculates the required add-on purchase if not eligible, and provides JNE Trucking (JTR) fallbacks.

#### Core Business Rules & Execution Steps

1.  **Coordinate & Input Resolution**:
    *   Attempts to parse and resolve coordinates from `kord` (which can be a Google Maps URL, shortlink, or raw coordinates).
    *   If `kord` is missing, but customer phone number (`nohp`) is provided, retrieves the primary address coordinates from `tblregister_alamat` where `status = 1` and `reg_id = customer_id`.
2.  **Optimized Geographical Branch Mapping**:
    To prevent high server CPU/DB load when scanning all 82,806 rows in `tbljne`:
    *   Query `tbljne` within a bounding box starting at `±0.1` degrees (approx. 11 km) of the resolved destination coordinates.
    *   If no matching rows are found, expand the search bounding box by `0.2` degrees progressively up to a maximum of `1.5` degrees.
    *   Compute the exact Haversine distance in PHP on only those candidate rows to find the geographically closest JNE area.
    *   Extract the resolved `jnecode`, regional details, coordinates, and associated serving branch `kode_cabang`.
    *   **Lookup Fallback**: If coordinates cannot be resolved, fall back to the explicit `jnecode` parameter or customer profile registered `jnecode`. If a branch is still not resolved or inactive, default to the `TGR` (Tangerang) branch.
3.  **Cart Calculations (Wholesale & Reseller Tiering)**:
    *   For each item in the cart, queries `tblmasterharga` for the serving branch `kode_cabang`.
    *   Calculates the wholesale quantity discount price based on quantity tiers (`mingrs1` to `mingrs5` and `hrggrs1` to `hrggrs5`).
    *   Based on the normal grand total, resolves the customer's reseller level and looks up reseller tier pricing limits (`limrsl` to `limrsl7` in the resolved branch, mapping to `harga_rsl` to `harga_rsl7`).
    *   Chooses the minimum of the wholesale quantity discount price and the reseller tier price as the final item price.
    *   Computes the totals: `subtotal`, `subtotal_cargo_triguna` (using `harga_cargo_triguna` from `tblmasterharga`), `subtotal_cargo_jne` (using `harga_cargo_jne`), `total_berat_bts`, and `total_berat_jne`.
4.  **Biteship Rate Resolution (Cargo Triguna)**:
    *   Queries Biteship pricing for the courier `grab` (Instant) from the branch coordinates to the resolved destination.
    *   If Grab Instant is unavailable or if the cost multiplied by `kali_grab_u_mobil_box` exceeds the branch limit `mobil_box_triguna_max`, queries Biteship for `deliveree` (Economy).
    *   Applies branch markups (`kali_grab_u_cargo` or `kali_deliveree_u_cargo`) to determine the raw Cargo Triguna shipping cost.
    *   If the cost exceeds the branch maximum `cargo_triguna_max`, the route is considered unreachable (`cargo_triguna_reachable = 0`).
5.  **JNE JTR Fallback Rate Resolution**:
    *   Queries JNE pricedev API for `JTR` (JNE Trucking) using the resolved `jnecode` and computed JNE weight.
    *   Applies province-specific JTR discounts and branch-subsidized Triguna cargo discounts (`kali_cargo_jne` and `cargo_jne_disc_max`).
6.  **Free Shipping Threshold Evaluation**:
    *   If Cargo Triguna is reachable, sets the threshold: `cargo_triguna_threshold = round(ongkir_cargo_triguna / kali_cargo_triguna)`.
    *   If `subtotal_cargo_triguna >= threshold`, sets `cargo_triguna_freeongkir_achieved = 1`.
### 2.5. Track Order: [lacak_pesanan.php](file:///c:/laragon/www/tjs/app_api/llm/lacak_pesanan.php)

Queries order status and compiles corresponding tracking history from JNE, Biteship, or Kurir Triguna.

#### Core Business Rules & Execution Steps

1.  **Input Parameters**:
    *   Accepts optional `nohp` (WhatsApp phone number) and `no_inv` (Invoice number).
    *   If both are missing, rejects request with `400 Bad Request`.
2.  **Order Selection & Status Filtering**:
    *   If `no_inv` is provided, queries matching order from `tblorder_master` directly with no status filtering.
    *   If `nohp` is provided, normalizes the phone number and queries the active registered customer profile (`tblregister` where `aktif = 1`). Finds the customer's orders in `tblorder_master` matching `status IN (1, 3, 4)` (active/running or arrived orders, excluding completed `status = 5` and cancelled/rejected `status = 0 or 2` orders).
3.  **Status Classification Mapping**:
    *   `1` $\rightarrow$ `order_baru` (Masih order baru belum diproses)
    *   `3` $\rightarrow$ `diproses` (Masih diproses belum dikirim)
    *   `4` and `tgl_terima = '1900-01-01 00:00:00'` $\rightarrow$ `dikirim` (Orderan sedang dikirim)
    *   `4` and `tgl_terima != '1900-01-01 00:00:00'` $\rightarrow$ `tiba` (Orderan telah tiba)
    *   `5` $\rightarrow$ `selesai` (Pesanan selesai)
    *   `0` or `2` $\rightarrow$ `batal` (Pesanan dibatalkan / ditolak)
4.  **Carrier-Specific Data Tracking Construction**:
    *   **JNE Trucking (JTR) / JNE Reguler**: Fetches `jne_user` and `jne_api` credentials for the order's branch. Queries corporate resis in `tblorder_resi_corporate` (falls back to the order's main resi if none exist). For each resi, queries `jne_trace_tracking()` and formats the timeline, appending proof photos or signature links if present.
    *   **Kurir Triguna / Cargo Triguna / Instant Triguna**: Fetches the driver (sopir) name and phone number from `tblmastersopir` and estimated arrival time (`eta_kurir`).
    *   **Biteship Couriers (Grab, Deliveree, Gojek)**: Queries driver details and tracking links from the latest `tblorder_biteship` entry.
    *   **Ambil Sendiri (Sendiri)**: If arrived or completed, shows the receiver name and signature photo links (absolute URLs resolved dynamically using the request's protocol and host).

---

### 2.6. Gold Points Lookup: [poin_emas.php](file:///c:/laragon/www/tjs/app_api/llm/poin_emas.php)

Queries active customer gold points by phone number.

#### Core Business Rules & Execution Steps

1. **Input Parameters**:
   * Accepts `nohp` (WhatsApp phone number).
   * If missing, rejects request with `400 Bad Request`.
2. **Customer Validation**:
   * Normalizes the phone number using `normalizePhoneNum` helper.
   * Checks if an active customer profile exists in `tblregister` where `aktif = 1`.
   * If not registered or found, returns `{"status": "Success", "data": []}`.
3. **Gold Points Summation**:
   * Queries `tblorder_poin_emas` for `IFNULL(SUM(poin), 0)` where `status <> '0'` and `reg_id` is matched.
   * Returns `{"status": "Success", "data": [{"poin_emas": X}]}`.
### 2.7. Self-Pickup OTP Retrieval: [get_order_otp.php](file:///c:/laragon/www/tjs/app_api/llm/get_order_otp.php)

Queries the self-pickup (Ambil Sendiri) OTP code for an order.

#### Core Business Rules & Execution Steps

1. **Input Parameters**:
   * Accepts `no_inv` (or `no_invoice`) and `no_wa` (or `nohp`).
   * If either is missing, rejects request with `400 Bad Request`.
2. **Customer & Phone Validation**:
   * Normalizes the input phone number using `normalizePhoneNum` helper.
   * Queries `tblorder_master` joined with `tblregister` where `no_inv` is matched.
   * If the order is not found, or the associated customer's normalized phone number does not match the input phone number, returns a standard customer warning message with `422 Unprocessable Entity`:
     `Invoice untuk {$no_wa} tidak ditemukan. Perhatian: untuk meminta kode ulang harus pakai no_wa yang sama dengan saat memesan.`
3. **Shipping Method Validation**:
   * Inspects the order's shipping method using `order_get_jakir_name`.
   * If the shipping carrier is not "Sendiri" (Ambil Sendiri), rejects the request with a `422 Unprocessable Entity` and message: `Pesanan ini tidak menggunakan metode Ambil Sendiri.`
4.  **OTP Existence Check**:
    *   If the OTP code in the database is empty, rejects the request with a `422 Unprocessable Entity` and message: `Kode OTP belum dibuat untuk pesanan ini. Silakan hubungi admin.`
    *   Otherwise, returns the minimal payload: `{"status": "Success", "data": {"no_inv": "...", "otp": "..."}}`.

### 2.8. Parent Variation Lookup: [varian_barang.php](file:///c:/laragon/www/tjs/app_api/llm/varian_barang.php)

Searches for variation parent products by name or code. Space-separated words are treated as distinct keywords that must all match in the parent product name.

#### Core Business Rules & Execution Steps

1. **Input Parameters**:
   * Accepts `varian` (required): Keyword or product code to search parent variations.
2. **Parent Resolution**:
   * Checks for products where `status = 1`, `var_kode = ''`, and the `kode_barang` is referenced by at least one child product (`var_kode <> ''`). Matches all space-separated keywords in the name.
3. **Response Payload**:
   * Returns a flat array containing `kode_barang`, `nama_barang`, and the absolute product `url`.

### 2.9. Product Catalog Search: [cari_barang.php](file:///c:/laragon/www/tjs/app_api/llm/cari_barang.php)

Performs a search on active catalog products using a single literal search keyword, and groups active branches.

#### Core Business Rules & Execution Steps

1. **Input Parameters**:
   * Accepts `nama_barang` (required): Search query treated as a single literal search keyword.
   * Accepts `kode_cabang` (optional): Filter to restrict matches to a specific branch.
2. **Keyword Filtering**:
   * Queries products matching the single literal search keyword within `nama_barang`.
3. **Branch Visibility Grouping**:
   * Collects unique matching products. For each product, aggregates all branch codes (`kode_cabang`) where the product is visible (`tampil = 1` in `tblmasterharga`).

---

### 2.10. JNE Destination Code Search: [jnecode.php](file:///c:/laragon/www/tjs/app_api/llm/jnecode.php)

Resolves customer locations to JNE destination codes (`jnecode`) from the `tbljne` master table (kelurahan granularity, ~82,000 rows). Registration stores this code as `idkp` on `tblregister` and `tblregister_alamat`.

#### Core Business Rules & Execution Steps

1. **Input Parameters** (all optional, trimmed and sanitized):
   * `search`: Global keyword — matched with `OR` across `propinsi`, `kabupaten`, `kecamatan`, `kelurahan`, `kodepos`, and `jnecode` (all `LIKE '%term%'`).
   * `propinsi`, `kabupaten`, `kecamatan`, `kelurahan`, `kodepos`: Field-specific partial-match filters.
2. **Filter Composition**:
   * All provided parameters (including `search`) are combined with `AND`. Empty-string parameters are treated as absent.
3. **Two Execution Modes**:
   * **Filtered mode** (≥1 parameter present): Returns at most **5** `DISTINCT` rows (`LIMIT 5`), sorted by `propinsi`, `kabupaten`, `kecamatan`, `kelurahan` ascending. No match → `data: []` (soft "not found", still HTTP 200).
   * **Dump mode** (no parameters): Returns **all** rows with no limit. The response (~13 MB) is streamed row-by-row (`MYSQLI_USE_RESULT`, incremental `json_encode` per row) to avoid buffering the full result set in PHP memory; `set_time_limit(300)` guards the long-running response.
4. **Row Shape**:
   * `jnecode`, `propinsi`, `kabupaten`, `kecamatan`, `kelurahan`, `kodepos`, `kode_cabang`. Note that one `jnecode` typically covers several kelurahan rows.

---

### 2.11. Customer Registration: [register.php](file:///c:/laragon/www/tjs/app_api/llm/register.php)

API twin of the web registration flow ([func/register.php](file:///c:/laragon/www/tjs/func/register.php)): inserts `tblregister` plus a primary `tblregister_alamat` row, without the web session/cookie side effects.

#### Core Business Rules & Execution Steps

1. **Input Parameters** (read from `$_REQUEST`, so GET and POST both work; aliases in parentheses):
   * Required: `nama` (`nmuser`), `nohp` (`hp`), `alamat` (`address`), `jnecode` (`idkp`).
   * Optional: `kelurahan` (`kel`), `kord`, `pwd`, `kode_referral`.
2. **Phone Normalization**: Same rules as the other endpoints (strip ` ( ) . -`, map `0`/`62`/bare prefix to `+62`), then validated against `/^\+62\d{7,15}$/` → `422` if invalid. Note: unlike the web form, dashes (`-`) are also stripped.
3. **Referral Validation** (only when provided): normalized like a phone number; must differ from the registrant's number and must exist in `tblregister` with `status = 1` → otherwise `422`.
4. **Duplicate Guard**: An active account (`status = 1`) with the same normalized `nohp` → `422`.
5. **JNE Code Validation**: `jnecode` must exist in `tbljne` → otherwise `422` (message points the AI to `jnecode.php`). The matching row(s) supply `propinsi`/`kabupaten`/`kecamatan` for the response.
   * **Kelurahan resolution**: explicit `kelurahan` parameter wins; if omitted and the code covers exactly **one** kelurahan, it is auto-filled; if ambiguous, stored as empty string.
6. **Coordinate Resolution (`kord`)**: Uses the shared [kord_helper.php](file:///c:/laragon/www/tjs/app_api/llm/kord_helper.php) (also used by freeongkir.php): accepts raw `lat,lng` or Google Maps links/shortlinks (resolved via cURL redirects, Google-domain-restricted). Resolved → stored as `https://www.google.com/maps/place/lat,lng` with `is_temp_kord = 0`. Unresolvable input → `422`. Absent → stored `''` with `is_temp_kord = 1` (matching the web form default).
7. **Password**: `pwd` used as-is when provided; otherwise an 8-char random alphanumeric (ambiguous chars `0/O/1/l/I` excluded) is generated. Stored as `md5(pwd)` (legacy scheme shared with the web login). Generated passwords are returned in the response (`password`, `password_generated: true`); caller-provided passwords are never echoed.
8. **Inserts** (identical columns to the web flow):
   * `tblregister (nohp, sandi, nama, kode_referral, alamat, idkp)`;
   * `tblregister_alamat (reg_id, pengingat='Alamat Utama', nama_penerima, hp_penerima, alamat, kelurahan, idkp, kord, is_temp_kord, keterangan='primary')`.
9. **Response**: `data` array with one object: `reg_id`, normalized `nohp`, geographic enrichment from `tbljne`, `kode_cabang` via `cabang_find_cbg()`, `kord`, `is_temp_kord`, and the generated password when applicable.

---


## 3. Vocabulary Guide for LLM Prompts

If you want to ask an AI to map these details out in the future, you can use terms like:
*   **"Response Matrix / Response Payload Catalog"** (to list all status codes and exact success/failure JSON schemas).
*   **"Execution Branches / Decision Trees / Logical Paths"** (to map out every `if/else` statement and conditional check).
*   **"Input Pre-processing & Normalization Rules"** (to trace raw parameters to their final queried state).
*   **"Query Constraints & Side Effects"** (to identify silent failures, joins, and truncation).