# Accounting API Implementation Plan

## Objective
The Accounting Dashboard UI has been upgraded to support advanced time-range filtering, fixing the data mismatch between Sales and Expenses tabs, and allowing Excel and PDF exports. 

**Backend Developer Task**: Update the following endpoints to properly filter data based on the `timeRange` query parameter and implement the export functionality.

## Global `timeRange` Filter
All updated APIs must accept a `timeRange` query parameter. 
The supported values are:
- `today`
- `yesterday`
- `this_week`
- `this_month`
- `last_6_months`
- `all_time`

You should use your existing date utility (e.g., `getDates(timeRange)`) to calculate the exact `startDate` and `endDate` in IST for database queries.

---

## 1. Updated Endpoints

### 1.1 Dashboard Stats
**Endpoint**: `GET /api/accounting/dashboard`

**Current Behavior**: Uses `?date=YYYY-MM-DD`
**Required Change**: Update to use `?timeRange=...`.
**Query**:
```sql
SELECT ... WHERE date >= ? AND date <= ?
```
**Response**:
Must return `total_revenue`, `total_expenses`, `net_profit`, and `recent_transactions` exactly bounded by the `timeRange`.

### 1.2 Expenses List
**Endpoint**: `GET /api/expenses`

**Current Behavior**: Returns paginated or all-time expenses.
**Required Change**: 
- Must accept `?timeRange=...`.
- Must only return the `Expense` records that fall within the specified date range.
- **Critical Fix**: Ensure this query queries the `expenses` table, completely isolated from billing/sales records.

### 1.3 Sales Ledger
**Endpoint**: `GET /api/billing`

**Current Behavior**: Returns paginated billing records.
**Required Change**:
- Must accept `?timeRange=...`.
- If `timeRange` is provided, ignore pagination (or set high limit) and return all `Sale` records that fall within the specified date range.
- **Critical Fix**: Ensure this query only queries `billing_records`, completely isolated from expenses.

---

## 2. New Export Endpoint

**Endpoint**: `GET /api/accounting/export`

**Query Parameters**:
- `timeRange` (string): e.g. `today`, `this_month`.
- `format` (string): exactly `excel` or `pdf`.

**Requirements**:
1. Calculate the start and end dates based on `timeRange`.
2. Fetch the matched `DashboardStats` (Total Revenue, Total Expenses, Net Profit).
3. Fetch the matched `Sales` records.
4. Fetch the matched `Expenses` records.
5. Generate the file:
   - **Excel**: Use a library like `exceljs` or `xlsx`. Create sheets for "Overview", "Sales", and "Expenses". Add borders and bold headers to make it look professional. Set Content-Type to `application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`.
   - **PDF**: Use a library like `pdfkit` or `puppeteer`. Create a cleanly formatted table with borders for transactions and summaries. Set Content-Type to `application/pdf`.
6. Stream the generated file back to the client as an attachment so the browser downloads it automatically.

### Example Response Headers (Excel):
```http
Content-Disposition: attachment; filename="accounting_report_this_month.xlsx"
Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
```