# Billing Repositories - Phase 2

This directory contains the data access layer (repositories) for the Billing Tracking System (Spec-036).

## Overview

Three repositories provide all database operations for billing data:
- **SmsUsageRepository** - SMS usage tracking and aggregation
- **InvoiceRepository** - Invoice and line item CRUD operations
- **BillingConfigRepository** - Billing configuration management

## Design Patterns

All repositories follow the PremiumRepository pattern:
- PDO injection via constructor
- Whitelist validation for dynamic column names
- Unique named parameters to avoid PDO HY093 errors
- `PDO::FETCH_ASSOC` for all fetches
- Explicit return types

## SmsUsageRepository

**Purpose:** Track SMS usage and generate billing summaries

**Key Methods:**
- `insertUsage()` - Record an SMS send event
- `getUsageSummary()` - Aggregate usage by category for a billing period
- `getUsageTrends()` - Multi-month usage trends for analytics

**Tables:** `billingSmsUsage`

## InvoiceRepository

**Purpose:** Manage invoices and line items

**Key Methods:**
- `createInvoice()` - Create new invoice record
- `findByStoreAndPeriod()` - Find active invoice for store+period
- `addLineItems()` - Bulk insert line items (uses unique param names)
- `getInvoiceWithLineItems()` - Fetch invoice with all line items
- `listInvoices()` - Paginated invoice list for a store
- `getAllStoresSummary()` - Cross-store summary for admin dashboard
- `voidInvoice()` - Mark invoice as voided
- `updateTotal()` - Update invoice total amount

**Tables:** `billingInvoices`, `billingLineItems`

**Security:** Sort column whitelist prevents SQL injection

## BillingConfigRepository

**Purpose:** Manage billing configuration

**Key Methods:**
- `getStoreConfig()` - Get full store billing configuration
- `getBaseRateOverride()` - Get store's base rate override
- `getCategoryOverride()` - Get per-category billing config
- `getAllCategoryConfigs()` - Get all category configs for a store
- `saveCategoryConfigs()` - Upsert category configs (bulk)
- `updateStoreConfig()` - Update store billing columns (whitelisted)
- `getStoreType()` - Get store type for concept-based defaults
- `getActiveStores()` - Get all stores with billing enabled

**Tables:** `stores`, `billingSmsCategoryConfig`

**Security:** Column whitelist prevents SQL injection

## Testing

All repositories have comprehensive unit tests:
- 43 tests, 278 assertions
- 100% pass rate
- Located in `tests/Unit/Billing/Repositories/`

## Static Analysis

PHPStan Level 9 compliant:
```bash
./vendor/bin/phpstan analyse src/BuyerKiosk/Billing/Repositories/
```

## Usage Example

```php
use BuyerKiosk\Billing\Repositories\InvoiceRepository;

// Get database connection
$db = new PDO('mysql:host=localhost;dbname=kiosk_buykiosk', 'user', 'pass');

// Create repository
$invoiceRepo = new InvoiceRepository($db);

// Create invoice
$invoiceId = $invoiceRepo->createInvoice(
    invoiceNumber: 'INV-2025-01-OU00',
    typeNum: 'ou00',
    billingPeriod: '2025-01',
    periodStart: '2025-01-01',
    periodEnd: '2025-01-31',
    issueDate: '2025-02-01',
    generatedByJobId: 123
);

// Add line items
$invoiceRepo->addLineItems((int)$invoiceId, [
    [
        'lineItemType' => 'base_rate',
        'description' => 'Base subscription',
        'smsCategory' => null,
        'quantity' => 1,
        'unitRate' => '50.00',
        'totalAmount' => '50.00',
        'sortOrder' => 1,
        'metadata' => null,
    ],
]);

// Update total
$invoiceRepo->updateTotal((int)$invoiceId, '50.00');
```

## PDO Parameter Best Practice

**CRITICAL:** Never reuse named parameters in a single query. PDO's native prepared statements will throw HY093 error.

```php
// ❌ WRONG - Reuses :typeNum
$sql = "WHERE typeNum = :typeNum AND otherTypeNum = :typeNum";

// ✅ CORRECT - Unique names
$sql = "WHERE typeNum = :typeNum1 AND otherTypeNum = :typeNum2";
$params = [':typeNum1' => $value, ':typeNum2' => $value];
```

## Database

All tables are in the central `kiosk_buykiosk` database:
- `billingSmsUsage` - SMS tracking records
- `billingInvoices` - Invoice headers
- `billingLineItems` - Invoice line items
- `billingSmsCategoryConfig` - Per-store category configs
- `stores` - Store billing columns

## Next Steps

Phase 3 will implement:
- `BillingService` - Business logic layer
- `BillingJobHandler` - Invoice generation job
- API controllers for billing operations

## References

- **Spec:** `docs/specs/036-billing-tracking-system/solution-design.md`
- **Tests:** `tests/Unit/Billing/Repositories/`
- **Pattern Reference:** `src/BuyerKiosk/Premium/PremiumRepository.php`