docs/api/reports/reports.md

# Reports API - Frontend Documentation

## Quick Start

```typescript
// 1. Generate a report
const response = await fetch('/api/v1/reports/generate', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    report_type: 'custom',
    date_range: {
      start_date: '2025-12-01',
      end_date: '2025-12-31'
    },
    custom_config: {
      entity: 'tickets',
      columns: ['status', 'priority'],
      aggregations: [{ field: 'id', type: 'count', alias: 'count' }],
      group_by: ['status', 'priority']
    },
    project_id: 'your-project-id'
  })
});

const data = await response.json();
// Use data.data for your charts/tables
```

---

## Overview

The Reports API provides business intelligence reports with pre-calculated metrics and aggregations. Use this for dashboard analytics, performance tracking, and compliance monitoring.

**Base URL:** `/api/v1/reports`

**Authorization:** 
- `view_reports` permission required for viewing
- `export_reports` permission required for CSV downloads
- Available to: PLATFORM_ADMIN, PROJECT_MANAGER, DISPATCHER, SALES_MANAGER

**Key Features:**
- ✅ Pre-defined reports (SLA, Performance, Financial, Inventory)
- ✅ Custom reports with flexible configuration
- ✅ Calculated fields (SLA violations, days to complete)
- ✅ Aggregations (count, sum, avg, min, max)
- ✅ CSV export for Excel
- ✅ Real-time data (no caching)

---

## TypeScript Interfaces

Copy these into your frontend project:

```typescript
// ============================================
// REQUEST TYPES
// ============================================

type ReportType = 
  | 'sla_compliance' 
  | 'user_performance' 
  | 'financial_summary' 
  | 'inventory_usage'
  | 'custom';

type AggregationType = 'count' | 'sum' | 'avg' | 'min' | 'max';

interface DateRange {
  start_date: string;  // ISO date: "2025-12-01"
  end_date: string;    // ISO date: "2025-12-31"
}

interface AggregationConfig {
  field: string;
  type: AggregationType;
  alias?: string;
}

interface CustomReportConfig {
  entity: 'tickets' | 'timesheets' | 'payroll' | 'expenses' | 'users';
  columns: string[];
  calculated_fields?: string[];
  aggregations?: AggregationConfig[];
  group_by?: string[];
  additional_filters?: Record<string, any>;
}

interface ReportRequest {
  report_type: ReportType;
  date_range: DateRange;
  project_id?: string;
  region_id?: string;
  user_id?: string;
  status?: string;
  limit?: number;
  skip?: number;
  custom_config?: CustomReportConfig;  // Required when report_type = 'custom'
}

// ============================================
// RESPONSE TYPES
// ============================================

interface ReportMeta {
  generated_at: string;
  record_count: number;
  filters_applied: Record<string, any>;
  report_type: string;
}

interface ReportResponse<T = any> {
  meta: ReportMeta;
  data: T[];
}

// Specific row types
interface SLAReportRow {
  ticket_id: string;
  ticket_number: string;
  project_name: string;
  ticket_type: string;
  priority: string;
  status: string;
  created_at: string;
  due_date: string | null;
  completed_at: string | null;
  is_violated: boolean;
  violation_margin_hours: number;
  assigned_agent: string;
  customer_name: string;
}

interface UserPerformanceRow {
  user_id: string;
  user_name: string;
  role: string;
  tickets_assigned: number;
  tickets_completed: number;
  completion_rate: number;
  total_hours_logged: number;
  avg_resolution_time_hours: number;
  sla_violations_count: number;
  on_time_percentage: number;
}

interface FinancialReportRow {
  category: string;
  description: string;
  date: string;
  project_name: string;
  amount: number;
  status: string;
  transaction_type: 'Credit' | 'Debit';
}

interface InventoryUsageRow {
  item_name: string;
  serial_number: string | null;
  category: string;
  used_at: string;
  ticket_id: string;
  project_name: string;
  used_by_user: string;
  unit_cost: number;
}

// Custom report rows are dynamic
type CustomReportRow = Record<string, any>;
```

---

## Endpoints

### 1. Generate Report (JSON)

Returns report data as JSON for rendering in tables, charts, or dashboards.

```
POST /api/v1/reports/generate
```

**Headers:**
```
Authorization: Bearer {access_token}
Content-Type: application/json
```

**Request Body:**
```typescript
{
  report_type: "sla_compliance" | "user_performance" | "financial_summary" | "inventory_usage";
  date_range: {
    start_date: string;  // ISO date: "2025-12-01"
    end_date: string;    // ISO date: "2025-12-31"
  };
  project_id?: string;   // UUID - optional filter
  region_id?: string;    // UUID - optional filter
  user_id?: string;      // UUID - optional filter
  status?: string;       // optional filter (e.g., "completed", "open")
  limit?: number;        // pagination limit (default: no limit)
  skip?: number;         // pagination offset (default: 0)
}
```

**Response (200 OK):**
```typescript
{
  meta: {
    generated_at: string;        // ISO timestamp
    record_count: number;        // total records returned
    filters_applied: object;     // echo of filters used
    report_type: string;         // report type
  };
  data: Array<ReportRow>;        // varies by report type (see below)
}
```

**Error Responses:**
- `401 Unauthorized` - Missing or invalid token
- `403 Forbidden` - User lacks `view_reports` permission
- `500 Internal Server Error` - Report generation failed

---

### 2. Export Report (CSV Download)

Downloads report data as CSV file for Excel or external processing.

```
POST /api/v1/reports/export
```

**Headers:**
```
Authorization: Bearer {access_token}
Content-Type: application/json
```

**Request Body:**
```typescript
{
  report_type: "sla_compliance" | "user_performance" | "financial_summary" | "inventory_usage";
  date_range: {
    start_date: string;  // ISO date: "2025-12-01"
    end_date: string;    // ISO date: "2025-12-31"
  };
  format?: "csv";        // only CSV supported currently
  project_id?: string;   // UUID - optional filter
  region_id?: string;    // UUID - optional filter
  user_id?: string;      // UUID - optional filter
  status?: string;       // optional filter
  limit?: number;        // pagination limit
  skip?: number;         // pagination offset
}
```

**Response (200 OK):**
- **Content-Type:** `text/csv`
- **Content-Disposition:** `attachment; filename="{report_type}_{timestamp}.csv"`
- **Body:** CSV file with UTF-8 BOM encoding (Excel-compatible)

**Error Responses:**
- `400 Bad Request` - Invalid format (only CSV supported)
- `401 Unauthorized` - Missing or invalid token
- `403 Forbidden` - User lacks `export_reports` permission
- `500 Internal Server Error` - Export failed

---

## Report Types & Data Shapes

### 1. Custom Report (NEW - Fully Configurable)

**Purpose:** Generate any report with flexible column selection, calculated fields, and aggregations

**Report Type:** `"custom"`

**Use Cases:**
- Ad-hoc data analysis
- Custom dashboard widgets
- Flexible reporting for any entity
- Aggregated statistics by any dimension

**Configuration:**
```typescript
{
  report_type: "custom",
  date_range: {
    start_date: "2025-12-01",
    end_date: "2025-12-31"
  },
  custom_config: {
    entity: "tickets" | "timesheets" | "payroll" | "expenses" | "users",
    columns: string[],                    // Fields to include
    calculated_fields?: string[],         // "sla_violated", "days_to_complete", "is_overdue"
    aggregations?: Array<{
      field: string,
      type: "count" | "sum" | "avg" | "min" | "max",
      alias?: string
    }>,
    group_by?: string[],                  // Group results by these fields
    additional_filters?: Record<string, any>  // Entity-specific filters
  },
  project_id?: string,
  region_id?: string,
  user_id?: string
}
```

**Example 1: Tickets by Status with Counts**
```json
{
  "report_type": "custom",
  "date_range": {
    "start_date": "2025-12-01",
    "end_date": "2025-12-31"
  },
  "custom_config": {
    "entity": "tickets",
    "columns": ["status", "priority"],
    "aggregations": [
      {
        "field": "id",
        "type": "count",
        "alias": "ticket_count"
      }
    ],
    "group_by": ["status", "priority"]
  },
  "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a"
}
```

**Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T20:00:00Z",
    "record_count": 6,
    "filters_applied": {...},
    "report_type": "custom"
  },
  "data": [
    {
      "status": "completed",
      "priority": "high",
      "ticket_count": 15
    },
    {
      "status": "completed",
      "priority": "medium",
      "ticket_count": 23
    },
    {
      "status": "open",
      "priority": "high",
      "ticket_count": 8
    }
  ]
}
```

**Example 2: Detailed Ticket List with Calculated Fields**
```json
{
  "report_type": "custom",
  "date_range": {
    "start_date": "2025-12-01",
    "end_date": "2025-12-31"
  },
  "custom_config": {
    "entity": "tickets",
    "columns": [
      "id",
      "ticket_name",
      "status",
      "priority",
      "created_at",
      "completed_at"
    ],
    "calculated_fields": [
      "sla_violated",
      "days_to_complete",
      "is_overdue"
    ],
    "additional_filters": {
      "ticket_type": "installation"
    }
  },
  "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a",
  "limit": 50
}
```

**Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T20:00:00Z",
    "record_count": 50,
    "filters_applied": {...},
    "report_type": "custom"
  },
  "data": [
    {
      "id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
      "ticket_name": "Install fiber at ABC Corp",
      "status": "completed",
      "priority": "high",
      "created_at": "2025-12-01T10:00:00Z",
      "completed_at": "2025-12-04T15:30:00Z",
      "sla_violated": false,
      "days_to_complete": 3,
      "is_overdue": false
    }
  ]
}
```

**Example 3: Payroll Summary by User**
```json
{
  "report_type": "custom",
  "date_range": {
    "start_date": "2025-12-01",
    "end_date": "2025-12-31"
  },
  "custom_config": {
    "entity": "payroll",
    "columns": ["user_id"],
    "aggregations": [
      {
        "field": "total_amount",
        "type": "sum",
        "alias": "total_paid"
      },
      {
        "field": "days_worked",
        "type": "sum",
        "alias": "total_days"
      },
      {
        "field": "tickets_closed",
        "type": "sum",
        "alias": "total_tickets"
      }
    ],
    "group_by": ["user_id"]
  },
  "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a"
}
```

**Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T20:00:00Z",
    "record_count": 12,
    "filters_applied": {...},
    "report_type": "custom"
  },
  "data": [
    {
      "user_id": "c3d4e5f6-a7b8-9012-cdef-123456789012",
      "total_paid": 45000.0,
      "total_days": 20,
      "total_tickets": 35
    }
  ]
}
```

**Supported Entities:**
- `tickets` - Work orders/tickets
- `timesheets` - Daily attendance and work logs
- `payroll` - Worker compensation records
- `expenses` - Ticket expenses
- `users` - User records

**Supported Calculated Fields:**
- `sla_violated` - Whether SLA was breached (tickets)
- `days_to_complete` - Days from creation to completion (tickets)
- `is_overdue` - Whether ticket is overdue (tickets)

**Supported Aggregations:**
- `count` - Count records
- `sum` - Sum numeric field
- `avg` - Average of numeric field
- `min` - Minimum value
- `max` - Maximum value

**UI Implementation Tips:**
- Use for custom dashboard widgets
- Allow users to build their own reports
- Combine with date range picker for flexible analysis
- Use aggregations for summary statistics
- Use detailed mode (no aggregations) for drill-down

---

### 2. SLA Compliance Report

**Purpose:** Track ticket completion vs due dates, identify SLA violations

**Report Type:** `"sla_compliance"`

**Use Cases:**
- Dashboard SLA metrics
- Overdue ticket alerts
- Performance monitoring
- Customer satisfaction tracking

**Data Shape:**
```typescript
interface SLAReportRow {
  ticket_id: string;                    // UUID
  ticket_number: string;                // Display ID (e.g., "TKT-12345")
  project_name: string;                 // Project title
  ticket_type: string;                  // "installation" | "support" | "infrastructure"
  priority: string;                     // "low" | "medium" | "high" | "urgent"
  status: string;                       // "open" | "in_progress" | "completed" | etc.
  created_at: string;                   // ISO timestamp
  due_date: string | null;              // ISO timestamp
  completed_at: string | null;          // ISO timestamp
  is_violated: boolean;                 // true if SLA was breached
  violation_margin_hours: number;       // negative = early, positive = late
  assigned_agent: string;               // Agent name or "Unassigned"
  customer_name: string;                // Customer name or "Internal/Infrastructure"
}
```

**Example Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T18:30:00Z",
    "record_count": 3,
    "filters_applied": {
      "report_type": "sla_compliance",
      "date_range": {
        "start_date": "2025-12-01",
        "end_date": "2025-12-14"
      },
      "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a"
    },
    "report_type": "sla_compliance"
  },
  "data": [
    {
      "ticket_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
      "ticket_number": "TKT-12345",
      "project_name": "Atomio FTTX",
      "ticket_type": "installation",
      "priority": "high",
      "status": "completed",
      "created_at": "2025-12-01T10:00:00Z",
      "due_date": "2025-12-05T17:00:00Z",
      "completed_at": "2025-12-04T15:30:00Z",
      "is_violated": false,
      "violation_margin_hours": -25.5,
      "assigned_agent": "John Doe",
      "customer_name": "ABC Corporation"
    },
    {
      "ticket_id": "b2c3d4e5-f6a7-8901-bcde-f12345678901",
      "ticket_number": "TKT-12346",
      "project_name": "Atomio FTTX",
      "ticket_type": "support",
      "priority": "urgent",
      "status": "completed",
      "created_at": "2025-12-02T09:00:00Z",
      "due_date": "2025-12-03T17:00:00Z",
      "completed_at": "2025-12-04T10:00:00Z",
      "is_violated": true,
      "violation_margin_hours": 17.0,
      "assigned_agent": "Jane Smith",
      "customer_name": "XYZ Ltd"
    }
  ]
}
```

**UI Implementation Tips:**
- Show `is_violated: true` tickets in red/warning color
- Display `violation_margin_hours` as "X hours late" (positive) or "X hours early" (negative)
- Filter by `priority` for urgent SLA violations
- Sort by `violation_margin_hours` descending to show worst violations first

---

### 2. User Performance Report

**Purpose:** Aggregate worker performance metrics from timesheets

**Report Type:** `"user_performance"`

**Use Cases:**
- Team performance dashboards
- Individual worker reviews
- Productivity tracking
- Resource allocation planning

**Data Shape:**
```typescript
interface UserPerformanceRow {
  user_id: string;                      // UUID
  user_name: string;                    // Full name
  role: string;                         // "field_agent" | "project_manager" | etc.
  tickets_assigned: number;             // Total tickets assigned
  tickets_completed: number;            // Total tickets completed
  completion_rate: number;              // Percentage (0-100)
  total_hours_logged: number;           // Total hours from timesheets
  avg_resolution_time_hours: number;    // Average hours per ticket
  sla_violations_count: number;         // Number of SLA breaches
  on_time_percentage: number;           // Percentage completed on time (0-100)
}
```

**Example Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T18:30:00Z",
    "record_count": 2,
    "filters_applied": {
      "report_type": "user_performance",
      "date_range": {
        "start_date": "2025-12-01",
        "end_date": "2025-12-14"
      }
    },
    "report_type": "user_performance"
  },
  "data": [
    {
      "user_id": "c3d4e5f6-a7b8-9012-cdef-123456789012",
      "user_name": "John Doe",
      "role": "field_agent",
      "tickets_assigned": 25,
      "tickets_completed": 23,
      "completion_rate": 92.0,
      "total_hours_logged": 184.5,
      "avg_resolution_time_hours": 8.02,
      "sla_violations_count": 2,
      "on_time_percentage": 91.3
    },
    {
      "user_id": "d4e5f6a7-b8c9-0123-def1-234567890123",
      "user_name": "Jane Smith",
      "role": "field_agent",
      "tickets_assigned": 30,
      "tickets_completed": 28,
      "completion_rate": 93.33,
      "total_hours_logged": 210.0,
      "avg_resolution_time_hours": 7.5,
      "sla_violations_count": 1,
      "on_time_percentage": 96.43
    }
  ]
}
```

**UI Implementation Tips:**
- Show `completion_rate` as progress bar or percentage badge
- Highlight users with `on_time_percentage < 90%` in warning color
- Display `avg_resolution_time_hours` as "X.X hours per ticket"
- Sort by `completion_rate` or `on_time_percentage` for leaderboards
- Use `sla_violations_count` for quality metrics

---

### 3. Financial Summary Report

**Purpose:** Unified ledger of revenue (ProjectFinance) and expenses (TicketExpenses)

**Report Type:** `"financial_summary"`

**Use Cases:**
- Project financial tracking
- Budget monitoring
- Expense approval workflows
- Revenue vs cost analysis

**Data Shape:**
```typescript
interface FinancialReportRow {
  category: string;                     // "General" | "Field Expense" | custom
  description: string;                  // Transaction description
  date: string;                         // ISO date (YYYY-MM-DD)
  project_name: string;                 // Project title
  amount: number;                       // Transaction amount (positive number)
  status: string;                       // "Approved" | "Pending" | "Paid" | etc.
  transaction_type: "Credit" | "Debit"; // Credit = revenue, Debit = expense
}
```

**Example Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T18:30:00Z",
    "record_count": 4,
    "filters_applied": {
      "report_type": "financial_summary",
      "date_range": {
        "start_date": "2025-12-01",
        "end_date": "2025-12-14"
      },
      "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a"
    },
    "report_type": "financial_summary"
  },
  "data": [
    {
      "category": "General",
      "description": "Client payment - Invoice #INV-001",
      "date": "2025-12-01",
      "project_name": "Atomio FTTX",
      "amount": 150000.0,
      "status": "Paid",
      "transaction_type": "Credit"
    },
    {
      "category": "Field Expense",
      "description": "transport: Fuel for site visit",
      "date": "2025-12-02",
      "project_name": "Atomio FTTX",
      "amount": 2500.0,
      "status": "Approved",
      "transaction_type": "Debit"
    },
    {
      "category": "Field Expense",
      "description": "materials: Fiber optic cable",
      "date": "2025-12-03",
      "project_name": "Atomio FTTX",
      "amount": 15000.0,
      "status": "Approved",
      "transaction_type": "Debit"
    }
  ]
}
```

**UI Implementation Tips:**
- Show `transaction_type: "Credit"` in green, `"Debit"` in red
- Calculate running balance: `balance += (Credit - Debit)`
- Group by `category` for expense breakdown charts
- Filter by `status` to show pending approvals
- Sort by `date` descending for recent transactions first
- Sum `amount` by `transaction_type` for totals

---

### 4. Inventory Usage Report

**Purpose:** Track inventory items installed/consumed across tickets

**Report Type:** `"inventory_usage"`

**Use Cases:**
- Inventory consumption tracking
- Cost analysis per project
- Equipment deployment monitoring
- Stock replenishment planning

**Data Shape:**
```typescript
interface InventoryUsageRow {
  item_name: string;                    // Inventory item name
  serial_number: string | null;         // Serial number if tracked
  category: string;                     // "General" | custom category
  used_at: string;                      // ISO timestamp
  ticket_id: string;                    // UUID of ticket where used
  project_name: string;                 // Project title
  used_by_user: string;                 // User name or "Unknown"
  unit_cost: number;                    // Cost per unit
}
```

**Example Response:**
```json
{
  "meta": {
    "generated_at": "2025-12-14T18:30:00Z",
    "record_count": 3,
    "filters_applied": {
      "report_type": "inventory_usage",
      "date_range": {
        "start_date": "2025-12-01",
        "end_date": "2025-12-14"
      },
      "project_id": "0ade6bd1-e492-4e25-b681-59f42058d29a"
    },
    "report_type": "inventory_usage"
  },
  "data": [
    {
      "item_name": "ONT Device - Model X200",
      "serial_number": "ONT-2025-001234",
      "category": "Customer Equipment",
      "used_at": "2025-12-02T14:30:00Z",
      "ticket_id": "a1b2c3d4-e5f6-7890-abcd-ef1234567890",
      "project_name": "Atomio FTTX",
      "used_by_user": "John Doe",
      "unit_cost": 5500.0
    },
    {
      "item_name": "Fiber Optic Cable (100m)",
      "serial_number": null,
      "category": "Installation Materials",
      "used_at": "2025-12-03T10:15:00Z",
      "ticket_id": "b2c3d4e5-f6a7-8901-bcde-f12345678901",
      "project_name": "Atomio FTTX",
      "used_by_user": "Jane Smith",
      "unit_cost": 12000.0
    }
  ]
}
```

**UI Implementation Tips:**
- Group by `category` for inventory breakdown
- Sum `unit_cost` for total inventory value used
- Link `ticket_id` to ticket detail pages
- Show `serial_number` for trackable equipment
- Filter by `used_by_user` for individual usage tracking
- Sort by `used_at` descending for recent usage

---

## Choosing Between Report Types

### Use Pre-defined Reports When:
- ✅ You need standard business metrics (SLA compliance, user performance)
- ✅ You want consistent reporting across the organization
- ✅ You need optimized queries for common use cases
- ✅ You want pre-calculated fields and complex joins

**Pre-defined Reports:**
- `sla_compliance` - Ticket SLA tracking
- `user_performance` - Worker productivity metrics
- `financial_summary` - Revenue and expenses ledger
- `inventory_usage` - Equipment deployment tracking

### Use Custom Reports When:
- ✅ You need flexible column selection
- ✅ You want to analyze data by custom dimensions
- ✅ You need aggregations (count, sum, avg) by any field
- ✅ You want to build ad-hoc reports or custom dashboards
- ✅ Pre-defined reports don't meet your specific needs

**Custom Report Advantages:**
- Fully configurable columns
- Any entity (tickets, timesheets, payroll, expenses, users)
- Flexible aggregations and grouping
- Calculated fields on demand
- Entity-specific filtering

---

## Common Filters

All report types support these optional filters:

```typescript
interface ReportFilters {
  project_id?: string;      // Filter by specific project (UUID)
  region_id?: string;       // Filter by project region (UUID)
  user_id?: string;         // Filter by specific user (UUID)
  status?: string;          // Filter by status (varies by report type)
  limit?: number;           // Pagination: max records to return
  skip?: number;            // Pagination: records to skip
}
```

**Filter Behavior:**
- All filters are optional (omit for all data)
- Multiple filters are combined with AND logic
- `limit` and `skip` enable pagination for large datasets
- Managers automatically see only their project data (authorization-filtered)

---

## Error Handling

**Common Error Response Format:**
```typescript
{
  detail: string;  // Human-readable error message
}
```

**Error Scenarios:**

**401 Unauthorized:**
```json
{
  "detail": "Not authenticated"
}
```

**403 Forbidden:**
```json
{
  "detail": "User does not have permission to view reports"
}
```

**500 Internal Server Error:**
```json
{
  "detail": "Report generation failed: [error details]"
}
```

---

## Frontend Implementation Guide

### React Hook Example

```typescript
// hooks/useReports.ts
import { useState } from 'react';
import { ReportRequest, ReportResponse } from '@/types/reports';

export const useReports = () => {
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState<string | null>(null);

  const generateReport = async <T = any>(
    request: ReportRequest
  ): Promise<ReportResponse<T> | null> => {
    setLoading(true);
    setError(null);

    try {
      const response = await fetch('/api/v1/reports/generate', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${localStorage.getItem('token')}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify(request)
      });

      if (!response.ok) {
        const errorData = await response.json();
        throw new Error(errorData.detail || 'Failed to generate report');
      }

      const data = await response.json();
      return data;
    } catch (err) {
      setError(err instanceof Error ? err.message : 'Unknown error');
      return null;
    } finally {
      setLoading(false);
    }
  };

  const exportReport = async (request: ReportRequest) => {
    setLoading(true);
    setError(null);

    try {
      const response = await fetch('/api/v1/reports/export', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${localStorage.getItem('token')}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify(request)
      });

      if (!response.ok) {
        throw new Error('Failed to export report');
      }

      // Trigger download
      const blob = await response.blob();
      const url = window.URL.createObjectURL(blob);
      const a = document.createElement('a');
      a.href = url;
      a.download = `report_${Date.now()}.csv`;
      document.body.appendChild(a);
      a.click();
      window.URL.revokeObjectURL(url);
      document.body.removeChild(a);
    } catch (err) {
      setError(err instanceof Error ? err.message : 'Unknown error');
    } finally {
      setLoading(false);
    }
  };

  return { generateReport, exportReport, loading, error };
};
```

### React Component Example

```typescript
// components/TicketStatusChart.tsx
import { useEffect, useState } from 'react';
import { useReports } from '@/hooks/useReports';
import { CustomReportRow } from '@/types/reports';

interface Props {
  projectId: string;
  startDate: string;
  endDate: string;
}

export const TicketStatusChart: React.FC<Props> = ({ 
  projectId, 
  startDate, 
  endDate 
}) => {
  const { generateReport, loading, error } = useReports();
  const [data, setData] = useState<CustomReportRow[]>([]);

  useEffect(() => {
    const fetchData = async () => {
      const result = await generateReport<CustomReportRow>({
        report_type: 'custom',
        date_range: { start_date: startDate, end_date: endDate },
        project_id: projectId,
        custom_config: {
          entity: 'tickets',
          columns: ['status', 'priority'],
          aggregations: [
            { field: 'id', type: 'count', alias: 'count' }
          ],
          group_by: ['status', 'priority']
        }
      });

      if (result) {
        setData(result.data);
      }
    };

    fetchData();
  }, [projectId, startDate, endDate]);

  if (loading) return <div>Loading...</div>;
  if (error) return <div>Error: {error}</div>;

  return (
    <div>
      <h3>Tickets by Status</h3>
      {data.map((row, idx) => (
        <div key={idx}>
          {row.status} ({row.priority}): {row.count} tickets
        </div>
      ))}
    </div>
  );
};
```

### Vue Composable Example

```typescript
// composables/useReports.ts
import { ref } from 'vue';
import type { ReportRequest, ReportResponse } from '@/types/reports';

export const useReports = () => {
  const loading = ref(false);
  const error = ref<string | null>(null);

  const generateReport = async <T = any>(
    request: ReportRequest
  ): Promise<ReportResponse<T> | null> => {
    loading.value = true;
    error.value = null;

    try {
      const response = await fetch('/api/v1/reports/generate', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${localStorage.getItem('token')}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify(request)
      });

      if (!response.ok) {
        const errorData = await response.json();
        throw new Error(errorData.detail || 'Failed to generate report');
      }

      return await response.json();
    } catch (err) {
      error.value = err instanceof Error ? err.message : 'Unknown error';
      return null;
    } finally {
      loading.value = false;
    }
  };

  const exportReport = async (request: ReportRequest) => {
    loading.value = true;
    error.value = null;

    try {
      const response = await fetch('/api/v1/reports/export', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${localStorage.getItem('token')}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify(request)
      });

      if (!response.ok) {
        throw new Error('Failed to export report');
      }

      const blob = await response.blob();
      const url = window.URL.createObjectURL(blob);
      const a = document.createElement('a');
      a.href = url;
      a.download = `report_${Date.now()}.csv`;
      document.body.appendChild(a);
      a.click();
      window.URL.revokeObjectURL(url);
      document.body.removeChild(a);
    } catch (err) {
      error.value = err instanceof Error ? err.message : 'Unknown error';
    } finally {
      loading.value = false;
    }
  };

  return { generateReport, exportReport, loading, error };
};
```

### API Service Class (Framework Agnostic)

```typescript
// services/reportsService.ts
import { ReportRequest, ReportResponse } from '@/types/reports';

class ReportsService {
  private baseUrl = '/api/v1/reports';
  
  private getHeaders() {
    return {
      'Authorization': `Bearer ${localStorage.getItem('token')}`,
      'Content-Type': 'application/json'
    };
  }

  async generate<T = any>(request: ReportRequest): Promise<ReportResponse<T>> {
    const response = await fetch(`${this.baseUrl}/generate`, {
      method: 'POST',
      headers: this.getHeaders(),
      body: JSON.stringify(request)
    });

    if (!response.ok) {
      const error = await response.json();
      throw new Error(error.detail || 'Failed to generate report');
    }

    return response.json();
  }

  async export(request: ReportRequest): Promise<void> {
    const response = await fetch(`${this.baseUrl}/export`, {
      method: 'POST',
      headers: this.getHeaders(),
      body: JSON.stringify(request)
    });

    if (!response.ok) {
      throw new Error('Failed to export report');
    }

    const blob = await response.blob();
    const url = window.URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = `report_${Date.now()}.csv`;
    document.body.appendChild(a);
    a.click();
    window.URL.revokeObjectURL(url);
    document.body.removeChild(a);
  }

  // Helper: Generate SLA report
  async getSLAReport(projectId: string, startDate: string, endDate: string) {
    return this.generate({
      report_type: 'sla_compliance',
      date_range: { start_date: startDate, end_date: endDate },
      project_id: projectId
    });
  }

  // Helper: Generate custom aggregation report
  async getAggregationReport(
    entity: string,
    columns: string[],
    aggregations: any[],
    groupBy: string[],
    filters: any
  ) {
    return this.generate({
      report_type: 'custom',
      date_range: filters.date_range,
      project_id: filters.project_id,
      custom_config: {
        entity,
        columns,
        aggregations,
        group_by: groupBy
      }
    });
  }
}

export const reportsService = new ReportsService();
```

### 1. Report Dashboard Page

```typescript
// Example: Fetch SLA Compliance Report
const fetchSLAReport = async (projectId: string, startDate: string, endDate: string) => {
  const response = await fetch('/api/v1/reports/generate', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      report_type: 'sla_compliance',
      date_range: {
        start_date: startDate,
        end_date: endDate
      },
      project_id: projectId
    })
  });

  if (!response.ok) {
    throw new Error('Failed to fetch report');
  }

  return await response.json();
};
```

### 2. CSV Export Button

```typescript
// Example: Download CSV Export
const downloadReport = async (reportType: string, filters: any) => {
  const response = await fetch('/api/v1/reports/export', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      report_type: reportType,
      ...filters
    })
  });

  if (!response.ok) {
    throw new Error('Failed to export report');
  }

  // Trigger download
  const blob = await response.blob();
  const url = window.URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = url;
  a.download = `${reportType}_${new Date().toISOString()}.csv`;
  document.body.appendChild(a);
  a.click();
  window.URL.revokeObjectURL(url);
  document.body.removeChild(a);
};
```

### 3. Date Range Picker

```typescript
// Example: Default to current week
const getDefaultDateRange = () => {
  const today = new Date();
  const monday = new Date(today);
  monday.setDate(today.getDate() - today.getDay() + 1);
  
  const sunday = new Date(monday);
  sunday.setDate(monday.getDate() + 6);
  
  return {
    start_date: monday.toISOString().split('T')[0],
    end_date: sunday.toISOString().split('T')[0]
  };
};
```

### 4. Report Type Selector

```typescript
const reportTypes = [
  { value: 'sla_compliance', label: 'SLA Compliance', icon: '⏱️' },
  { value: 'user_performance', label: 'User Performance', icon: '📊' },
  { value: 'financial_summary', label: 'Financial Summary', icon: '💰' },
  { value: 'inventory_usage', label: 'Inventory Usage', icon: '📦' }
];
```

---

## Performance Notes

- Reports are designed for **sub-2 second** response times
- Use `limit` and `skip` for pagination on large datasets
- User Performance report uses aggregated timesheet data (fast)
- SLA Compliance may be slower for large date ranges (complex joins)
- Consider caching report results for frequently-accessed data

---

## Custom Report Examples

### Example 1: Ticket Status Dashboard
**Goal:** Show ticket counts by status and priority for current month

```typescript
const response = await fetch('/api/v1/reports/generate', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    report_type: 'custom',
    date_range: {
      start_date: '2025-12-01',
      end_date: '2025-12-31'
    },
    custom_config: {
      entity: 'tickets',
      columns: ['status', 'priority'],
      aggregations: [
        { field: 'id', type: 'count', alias: 'count' }
      ],
      group_by: ['status', 'priority']
    },
    project_id: projectId
  })
});

// Use for stacked bar chart or heatmap
```

### Example 2: Worker Performance Leaderboard
**Goal:** Rank workers by tickets completed this week

```typescript
const response = await fetch('/api/v1/reports/generate', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    report_type: 'custom',
    date_range: {
      start_date: '2025-12-08',
      end_date: '2025-12-14'
    },
    custom_config: {
      entity: 'timesheets',
      columns: ['user_id'],
      aggregations: [
        { field: 'tickets_completed', type: 'sum', alias: 'total_completed' },
        { field: 'hours_worked', type: 'sum', alias: 'total_hours' }
      ],
      group_by: ['user_id']
    },
    project_id: projectId
  })
});

// Sort by total_completed descending for leaderboard
```

### Example 3: Expense Analysis by Category
**Goal:** Breakdown expenses by type for budget tracking

```typescript
const response = await fetch('/api/v1/reports/generate', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    report_type: 'custom',
    date_range: {
      start_date: '2025-12-01',
      end_date: '2025-12-31'
    },
    custom_config: {
      entity: 'expenses',
      columns: ['expense_type'],
      aggregations: [
        { field: 'total_cost', type: 'sum', alias: 'total_amount' },
        { field: 'id', type: 'count', alias: 'expense_count' }
      ],
      group_by: ['expense_type'],
      additional_filters: {
        is_approved: true
      }
    },
    project_id: projectId
  })
});

// Use for pie chart or expense breakdown table
```

### Example 4: Overdue Tickets Report
**Goal:** List all overdue tickets with details

```typescript
const response = await fetch('/api/v1/reports/generate', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    report_type: 'custom',
    date_range: {
      start_date: '2025-01-01',
      end_date: '2025-12-31'
    },
    custom_config: {
      entity: 'tickets',
      columns: [
        'id',
        'ticket_name',
        'priority',
        'status',
        'due_date',
        'created_at'
      ],
      calculated_fields: ['is_overdue', 'days_to_complete'],
      additional_filters: {
        status: 'open'
      }
    },
    project_id: projectId
  })
});

// Filter client-side for is_overdue === true
// Sort by due_date ascending
```

---

## Testing

**Test with cURL:**

```bash
# Generate SLA Report
curl -X POST "http://localhost:7860/api/v1/reports/generate" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "report_type": "sla_compliance",
    "date_range": {
      "start_date": "2025-12-01",
      "end_date": "2025-12-14"
    }
  }'

# Generate Custom Report
curl -X POST "http://localhost:7860/api/v1/reports/generate" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "report_type": "custom",
    "date_range": {
      "start_date": "2025-12-01",
      "end_date": "2025-12-14"
    },
    "custom_config": {
      "entity": "tickets",
      "columns": ["status", "priority"],
      "aggregations": [
        {"field": "id", "type": "count", "alias": "count"}
      ],
      "group_by": ["status", "priority"]
    }
  }'

# Export to CSV
curl -X POST "http://localhost:7860/api/v1/reports/export" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "report_type": "user_performance",
    "date_range": {
      "start_date": "2025-12-01",
      "end_date": "2025-12-14"
    }
  }' \
  --output report.csv
```

---

## Questions?

Contact the backend team or check:
- API Swagger docs: `/docs`
- Source code: `src/app/api/v1/reports.py`
- Service logic: `src/app/services/report_service.py`


---

## Common Integration Patterns

### Pattern 1: Dashboard Widget with Auto-Refresh

```typescript
// Auto-refresh every 5 minutes
useEffect(() => {
  const fetchData = async () => {
    const result = await reportsService.generate({
      report_type: 'custom',
      date_range: { start_date: startDate, end_date: endDate },
      project_id: projectId,
      custom_config: {
        entity: 'tickets',
        columns: ['status'],
        aggregations: [{ field: 'id', type: 'count', alias: 'count' }],
        group_by: ['status']
      }
    });
    setData(result.data);
  };

  fetchData();
  const interval = setInterval(fetchData, 5 * 60 * 1000);
  return () => clearInterval(interval);
}, [projectId, startDate, endDate]);
```

### Pattern 2: Report Builder UI

```typescript
interface ReportBuilderState {
  entity: string;
  selectedColumns: string[];
  aggregations: AggregationConfig[];
  groupBy: string[];
  filters: Record<string, any>;
}

const ReportBuilder = () => {
  const [config, setConfig] = useState<ReportBuilderState>({
    entity: 'tickets',
    selectedColumns: [],
    aggregations: [],
    groupBy: [],
    filters: {}
  });

  const handleGenerate = async () => {
    const result = await reportsService.generate({
      report_type: 'custom',
      date_range: config.filters.date_range,
      project_id: config.filters.project_id,
      custom_config: {
        entity: config.entity,
        columns: config.selectedColumns,
        aggregations: config.aggregations,
        group_by: config.groupBy
      }
    });
    // Display result
  };

  return (
    <div>
      <EntitySelector value={config.entity} onChange={...} />
      <ColumnSelector columns={config.selectedColumns} onChange={...} />
      <AggregationBuilder aggregations={config.aggregations} onChange={...} />
      <button onClick={handleGenerate}>Generate Report</button>
    </div>
  );
};
```

### Pattern 3: Cached Reports with React Query

```typescript
import { useQuery } from '@tanstack/react-query';

const useTicketStatusReport = (projectId: string, dateRange: DateRange) => {
  return useQuery({
    queryKey: ['report', 'ticket-status', projectId, dateRange],
    queryFn: () => reportsService.generate({
      report_type: 'custom',
      date_range: dateRange,
      project_id: projectId,
      custom_config: {
        entity: 'tickets',
        columns: ['status', 'priority'],
        aggregations: [{ field: 'id', type: 'count', alias: 'count' }],
        group_by: ['status', 'priority']
      }
    }),
    staleTime: 5 * 60 * 1000, // 5 minutes
    cacheTime: 10 * 60 * 1000 // 10 minutes
  });
};

// Usage
const { data, isLoading, error } = useTicketStatusReport(projectId, dateRange);
```

### Pattern 4: Export with Loading State

```typescript
const ExportButton = ({ reportConfig }: { reportConfig: ReportRequest }) => {
  const [exporting, setExporting] = useState(false);

  const handleExport = async () => {
    setExporting(true);
    try {
      await reportsService.export(reportConfig);
      toast.success('Report exported successfully');
    } catch (error) {
      toast.error('Failed to export report');
    } finally {
      setExporting(false);
    }
  };

  return (
    <button onClick={handleExport} disabled={exporting}>
      {exporting ? 'Exporting...' : 'Export to CSV'}
    </button>
  );
};
```

---

## Troubleshooting

### Issue: 403 Forbidden
**Cause:** User lacks `view_reports` or `export_reports` permission  
**Solution:** Check user role - only PLATFORM_ADMIN, PROJECT_MANAGER, DISPATCHER, SALES_MANAGER have access

### Issue: 422 Validation Error
**Cause:** Invalid request body (missing required fields, wrong date format)  
**Solution:** 
- Ensure `date_range` has both `start_date` and `end_date`
- Use ISO date format: "YYYY-MM-DD"
- For custom reports, ensure `custom_config` is provided

### Issue: 500 Internal Server Error
**Cause:** Report generation failed (database error, invalid entity, etc.)  
**Solution:**
- Check entity name is valid (tickets, timesheets, payroll, expenses, users)
- Verify column names exist for the entity
- Check aggregation field names are valid
- Review browser console for detailed error message

### Issue: Empty Data Array
**Cause:** No records match the filters  
**Solution:**
- Expand date range
- Remove or adjust filters
- Check if project has data for the selected period

### Issue: Slow Report Generation
**Cause:** Large dataset or complex aggregations  
**Solution:**
- Use pagination (`limit` and `skip`)
- Narrow date range
- Add more specific filters (project_id, region_id)
- Consider caching results

---

## Best Practices

### 1. Always Handle Errors
```typescript
try {
  const result = await reportsService.generate(request);
  setData(result.data);
} catch (error) {
  console.error('Report generation failed:', error);
  showErrorToast(error.message);
}
```

### 2. Use Pagination for Large Datasets
```typescript
const request: ReportRequest = {
  report_type: 'custom',
  date_range: { start_date, end_date },
  limit: 100,  // Fetch 100 records at a time
  skip: page * 100,  // Offset for pagination
  custom_config: { ... }
};
```

### 3. Cache Report Results
```typescript
// Use React Query, SWR, or simple state management
const cacheKey = `report_${reportType}_${projectId}_${startDate}_${endDate}`;
const cached = localStorage.getItem(cacheKey);
if (cached && Date.now() - cached.timestamp < 5 * 60 * 1000) {
  return JSON.parse(cached.data);
}
```

### 4. Show Loading States
```typescript
{loading && <Spinner />}
{error && <ErrorMessage message={error} />}
{data && <ReportTable data={data} />}
```

### 5. Validate Dates Before Sending
```typescript
const validateDateRange = (start: string, end: string) => {
  const startDate = new Date(start);
  const endDate = new Date(end);
  
  if (startDate > endDate) {
    throw new Error('Start date must be before end date');
  }
  
  if (endDate > new Date()) {
    throw new Error('End date cannot be in the future');
  }
};
```

---

## Questions?

Contact the backend team or check:
- API Swagger docs: `/docs`
- Source code: `src/app/api/v1/reports.py`
- Service logic: `src/app/services/report_service.py`
- Feature docs: `docs/api/reports/CUSTOM_REPORTS_FEATURE.md`