# Invoice Excel Template Mapping

This document records how each cell (or cell range) in
`storage/app/templates/invoice_template.xlsx` is populated from the current
database schema. The goal is to make the rendering logic deterministic before
implementing the actual export feature.

## Overview

- The template represents a single `invoices` record with up to 13 rendered
  detail rows.
- Detail rows are aggregated per `invoice_details.tax_classification` and
  `invoice_details.billing_id`. Within each group:
  - `description`, `quantity`, `remarks`, `payee_name`, and `payee_number`
    use the first non-null value by the lowest `invoice_details.id`.
  - `unit_price` and `total_amount` use the sum across the group.
- When there are more than 13 grouped rows, additional pages are created by
  repeating the template. Each page renders up to 13 rows.
- Totals beneath the table aggregate **all** `invoice_details` rows of the
  invoice, regardless of whether they were rendered in the visible rows. Totals
  are placed on the **last page only**.

## Cell Mapping

| Cell / Range | Source Table | Column(s) | Aggregation / Notes |
|--------------|--------------|-----------|---------------------|
| `BY1` | `invoices` | `billing_date` | Displayed as `YYYY/MM/DD`. |
| `A2` | `group_organizations` | `implementing_organization_name` | Look up by matching `invoices.billing_customer_id` to `group_organizations.implementing_organization_id`. |
| `A11:A23` | `invoice_details` | `description` | First record (by smallest `id`) for each `(tax_classification, billing_id)` group. |
| `Y11:Y23` | `invoice_details` | `unit_price` | Sum per group. |
| `AD11:AD23` | `invoice_details` | `quantity` | First record per group. |
| `AG11:AG23` | `invoice_details` | `tax_classification` | Group key (per-row rendering). |
| `AM11:AM23` | `invoice_details` | `total_amount` | Sum per group. |
| `AT11:AT23` | `invoice_details` | `remarks` | First record per group. |
| `BN11:BN23` | `invoice_details` | `payee_name` | First record per group. |
| `BY11:BY23` | `invoice_details` | `payee_number` | First record per group. |
| `AM24` | `invoice_details` | `total_amount` | Sum of **all** rows for the target invoice. |
| `N26` | `invoice_details` | `total_amount` | Sum where `tax_classification IN ('非', '非不・立')`. |
| `N27` | `invoice_details` | `total_amount` | Sum where `tax_classification IN ('込(10%)', '込・立10%')`. |
| `W27` | (derived) | | `N27 / 11` (to isolate the 10% consumption tax portion). |
| `BC27` | `invoice_details` | `total_amount` | Sum where `tax_classification = '非不・立'`. |
| `BC28` | `invoice_details` | `total_amount` | Sum where `tax_classification = '込・立10%'`. |
| `BC30` | `invoice_details` | `total_amount` | Sum where `tax_classification = '込・立8%'`. |
| `BC26` | (derived) | | `BC27 + BC28 + BC30`. |
| `BY26` | `invoice_details` | `total_amount` | Same as `AM24` (grand total). |
| `C37` | `invoices` | `billing_cutoff_date` | `YYYY` portion. |
| `I37` | `invoices` | `billing_cutoff_date` | `MM` portion. |
| `M37` | `invoices` | `billing_cutoff_date` | `DD` portion. |

## Detail Row Extraction Logic

```sql
WITH base_details AS (
    SELECT
        id,
        bill_id,
        tax_classification,
        billing_id,
        description,
        quantity,
        unit_price,
        total_amount,
        remarks,
        payee_name,
        payee_number,
        ROW_NUMBER() OVER (
            PARTITION BY tax_classification, billing_id
            ORDER BY id
        ) AS rn
    FROM invoice_details
    WHERE bill_id = :invoice_id
)
SELECT
    MIN(id) AS first_id,
    tax_classification,
    billing_id,
    MAX(description) FILTER (WHERE rn = 1) AS description,
    MAX(quantity)     FILTER (WHERE rn = 1) AS quantity,
    SUM(unit_price)                        AS unit_price_sum,
    SUM(total_amount)                      AS total_amount_sum,
    MAX(remarks)      FILTER (WHERE rn = 1) AS remarks,
    MAX(payee_name)   FILTER (WHERE rn = 1) AS payee_name,
    MAX(payee_number) FILTER (WHERE rn = 1) AS payee_number
FROM base_details
GROUP BY tax_classification, billing_id
ORDER BY first_id
LIMIT 13;
```

The query above communicates the grouping, ordering, and truncation rules
needed by the exporter service (ORM logic can express the same concept).

## Totals and Derived Values

All totals beneath the detail table aggregate directly from
`invoice_details` filtered by `bill_id = :invoice_id`. When a
`tax_classification` filter is specified, use the stated list of values.

- `N27` represents the tax-inclusive subtotal for the 10% bracket.
- `W27` extracts the 10% tax portion only and should be computed as
  `ROUND(N27 / 11)` (or an equivalent precise calculation).
- `BC26` is simply the sum of the three detailed subtotals in its box.
- The footer cells (`AM24`, `N26`, `N27`, `W27`, `BC26`, `BC27`, `BC28`,
  `BC30`, `BY26`, `C37`, `I37`, `M37`) are only filled on the final page.

## Storage Considerations

The current schema already contains every field referenced above and the
aggregations can be expressed through queries or Eloquent builders. No temporary
tables or schema migrations are required for this mapping.

## Environment Toggles

The exporter must read the following configuration knobs from `.env`:

```
# Directory where generated invoices are saved.
# Absolute path or path relative to the project root.
INVOICE_EXPORT_DIRECTORY=storage/app/invoices

# Output format for invoice exports.
#   excel -> keeps the filled template as .xlsx
#   pdf   -> converts the filled template to PDF before saving
INVOICE_EXPORT_FORMAT=excel
```

Rules:

- Files are written to `INVOICE_EXPORT_DIRECTORY`. No automatic download is
  triggered; users open the generated Excel manually and choose where/how to
  save it.
- `INVOICE_EXPORT_FORMAT` controls whether the exporter stops at the `.xlsx`
  (`excel`) or renders the sheet to PDF (`pdf`). Guard against invalid values by
  defaulting to `excel`.