== SQL Views ==

The application uses SQL VIEWs to simplify common queries and improve maintainability.

** v_invoice_analytics **

'''Purpose''': Shows invoice info with client and company names plus business calculations

'''What it does''': Joins Invoice, Client, and Tenant tables and calculates days overdue, payment status, and value categories

'''Usage''': Makes it easier to see which client each invoice belongs to and get business insights without writing complex queries

{{{
SELECT * FROM v_invoice_analytics WHERE invoice_status = 'PENDING';
}}}

** v_client_financial_summary **

'''Purpose''': Shows total amounts for each client with risk analysis

'''What it does''': Groups invoices by client and calculates total paid, pending, overdue amounts plus payment behavior and risk levels

'''Usage''': Provides a complete overview of each client's financial status and payment history

{{{
SELECT * FROM v_client_financial_summary ORDER BY total_invoiced DESC;
}}}

** API Integration **

The views are used in API endpoints to provide simplified data access:

src/app/api/clients/summary/route.ts: Uses v_client_financial_summary view for detailed client analytics

src/app/api/invoices/totals/route.ts: Uses v_invoice_analytics view for invoice analytics

** Benefits **

'''Simpler queries''': No need to write complex JOINs every time

'''Consistent data''': Same calculation logic everywhere

'''Easier to maintain''': Change the view, all queries get updated

'''Better performance''': Database can optimize the view

The views are implemented in:
sql/03_business_views.sql

== Transactions ==

The application uses database transactions in several key operations:

- Invoice creation: When creating a new invoice, a transaction ensures that both the invoice and its items are created atomically
- Status updates: Invoice status changes are handled within transactions to maintain data consistency
- Client updates: When updating client information, related operations are wrapped in transactions

Transactions are implemented in the following files:

- `src/app/api/invoices/[id]/route.ts`: For updating and deleting invoices
- `src/app/api/invoices/route.ts`: For creating new invoices

== Triggers ==

The system implements a sophisticated trigger system for invoice management:

- `invoice_status_update_trigger`: Automatically fires when an invoice status changes
  - Logs status changes in `InvoiceStatusHistory`
  - Creates notifications in `InvoiceNotifications`
  - Updates client status based on payment history
  - Automatically extends due dates when needed
  - Calculates and updates financial metrics

The trigger is implemented in `sql/01_invoice_status_trigger.sql`.

== Indexes ==

Several optimized indexes are implemented for better query performance:

- Client email lookups:
  - `idx_client_email`: Basic email search optimization
  - `idx_client_email_tenant`: Composite index for tenant-specific email searches
  - `idx_client_email_lower`: Case-insensitive email searches
- Invoice management:
  - `idx_invoice_status_history_invoice_id`: Optimizes status history lookups
  - `idx_invoice_status_history_changed_at`: Improves date-based queries
  - `idx_invoice_notifications_invoice_id`: Speeds up notification retrieval
  - `idx_invoice_notifications_processed`: Partial index for unprocessed notifications
- Complex invoice analytics: (/agencyos/sql/04_complex_invoice_analytics_index.sql
  - `idx_invoice_complex_analytics`: Multi-column index for tenant-specific analytics with status, currency, monthly grouping, and amount filtering
  - `idx_invoice_overdue_analysis`: Optimized for overdue invoice queries with tenant, status, due date, and amount sorting
  - `idx_invoice_financial_reporting`: Quarterly financial reporting with currency, status, and amount categorization
  - `idx_invoice_number_search`: Case-insensitive invoice number search with tenant isolation
  - `idx_client_invoice_performance`: Client performance metrics with status-based priority ordering
  - `idx_invoice_temporal_analytics`: Time-series analysis with year, month, and day-of-week grouping

Using `EXPLAIN` on the `Client` table

  - Before creating the index:

{{{
EXPLAIN ANALYZE SELECT * FROM "Client" WHERE email = 'client12@example.com';
}}}

  - After adding index and creating 1000 rows:
{{{
EXPLAIN ANALYZE SELECT * FROM "Client" WHERE email = 'client12@example.com';
}}}

{{{
Index Scan using idx_client_email on "Client"  (cost=0.28..8.29 rows=1 width=303)
  Index Cond: (email = 'client12@example.com'::text)
}}}

Using `EXPLAIN` on complex invoice analytics

  - Multi-tenant analytics query:
{{{
EXPLAIN ANALYZE
SELECT 
    invoice_status,
    invoice_currency,
    DATE_TRUNC('month', invoice_issue_date) as month,
    SUM(invoice_total_amount) as total_amount,
    COUNT(*) as invoice_count
FROM "Invoice" 
WHERE tenant_id = 'tenant_123'
  AND invoice_status IN ('PENDING', 'PROCESSING', 'OVERDUE', 'PAID')
  AND invoice_issue_date >= '2024-01-01'
  AND invoice_currency = 'EUR'
GROUP BY invoice_status, invoice_currency, DATE_TRUNC('month', invoice_issue_date)
ORDER BY month DESC, total_amount DESC;
}}}

  - Result with complex index:
{{{
Index Only Scan using idx_invoice_complex_analytics on "Invoice"  (cost=0.14..12.49 rows=11 width=24)
  Index Cond: ((tenant_id = 'tenant_123') AND (invoice_currency = 'EUR'::"Currency") AND (invoice_issue_date >= '2024-01-01 00:00:00'::timestamp without time zone))
  Heap Fetches: 11
}}}

[[Image(https://develop.finki.ukim.mk/projects/agency-os/raw-attachment/wiki/App/invoice_analytics.jpg)]]

[[Image(https://develop.finki.ukim.mk/projects/agency-os/raw-attachment/wiki/App/index.jpg)]]