source: README.md

# Project Setup Guide

This guide will help you set up and run the project locally.

## Prerequisites

- Node.js (v16.x or v18.x)
- Yarn (recommended) or npm
- Access to project's database credentials
- Google Cloud credentials

## Installation Steps

1. **Install Dependencies**

   ```bash
   yarn install
   # or using npm
   npm install --legacy-peer-deps
   ```

2. **Database Connection**

   Open a terminal and run the following SSH command to establish a connection to the database:

   ```bash
   ssh -L 5432:localhost:5432 t_agency_os@194.149.135.130 -N
   ```

   Keep this terminal window open while working with the application.

3. **Environment Setup**

   Create a `.env` file in the root directory and set the path to your service-account.json file:

   ```env
   GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account.json"
   ```

4. **Generate Prisma Client**

   ```bash
   yarn prisma:generate
   ```

5. **Seed Database (First-time setup)**

   If you're setting up the project for the first time and the database is empty, run:

   ```bash
   yarn db:seed
   ```

   This will create an initial tenant and user in the database.

6. **Start Development Server**
   ```bash
   yarn dev
   ```
   The application should now be running on `http://localhost:3000`

## Authentication

The application uses Firebase Authentication. You can log in with the following default credentials:

- Email: naum@mvpmasters.com
- Password: BnP2025~

## Available Scripts

- `yarn dev` - Start development server
- `yarn prisma:generate` - Generate Prisma client
- `yarn build` - Build for production
- `yarn start` - Start production server
- `yarn db:seed` - Seed the database with initial data

## Database Features

### 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

### SQL Views

The application implements sophisticated SQL VIEWs for business intelligence and analytics:

#### `v_invoice_analytics`

- **Purpose**: Comprehensive invoice analytics with calculated business metrics
- **Features**:
  - Combines invoice, client, and tenant data
  - Calculates days overdue, payment status, value categories
  - Provides age categorization and risk indicators
  - Real-time business intelligence metrics

#### `v_client_financial_summary`

- **Purpose**: Complete financial overview for each client
- **Features**:
  - Total invoiced, paid, outstanding, and overdue amounts
  - Payment behavior analysis and risk assessment
  - Client performance metrics and historical data
  - Automated risk level calculation

#### Advanced Features

- **Complex business logic**: Multi-level conditional calculations
- **Performance optimization**: Strategic indexing for view performance
- **Data aggregation**: Multi-level aggregation for different time periods
- **Risk assessment**: Automated client risk evaluation algorithms

#### API Integration

The views are integrated into API endpoints for comprehensive data access:

- `src/app/api/clients/summary/route.ts`: Uses `v_client_financial_summary` for detailed client analytics
- `src/app/api/invoices/totals/route.ts`: Uses `v_invoice_analytics` for invoice analytics

#### Benefits

- **Business Intelligence**: Pre-computed complex business metrics
- **Performance**: Optimized queries with strategic indexing
- **Maintainability**: Centralized business logic in database layer
- **Scalability**: Supports complex reporting requirements

The views are implemented in `sql/03_business_views.sql` with comprehensive indexing for optimal performance.

## Troubleshooting

If you encounter any issues:

- Ensure the SSH connection to the database is active
- Verify your Google Cloud credentials path is correct
- Check if all environment variables are properly set
- Make sure the database is properly seeded if you're setting up for the first time

## Support

For additional help or questions, please contact the development team.