# QueryProcessingService

A Laravel service for processing SQL queries with dynamic date formatting and timestamp placeholders.

## Overview

The QueryProcessingService allows you to embed dynamic date formatting and arithmetic operations directly in SQL queries using template syntax. It supports three types of variables:
- `LASTRUNDATE` - The last execution time of a flow
- `TIMESTAMP` - Current timestamp in Unix milliseconds
- `TIMESTAMP_UTC` - Current timestamp in ISO8601 UTC format

This is particularly useful for data pipelines, ETL processes, automated reporting, and API integrations where you need dynamic timestamps.

## Supported Variables

| Variable | Type | Example Output | Description |
|----------|------|----------------|-------------|
| `LASTRUNDATE` | Flow-based | `2024-01-15 10:30:45` | Last execution time from flow |
| `TIMESTAMP` | Current time | `1757697636169` | Unix timestamp in milliseconds |
| `TIMESTAMP_UTC` | Current time | `2025-09-12T17:20:36+00:00` | ISO8601 UTC timestamp |

## Usage

### Template Syntax

```sql
{{formatDate 'format' VARIABLE}}
{{addDate 'format' VARIABLE 'interval'}}
{{VARIABLE}}
```

Where `VARIABLE` can be `LASTRUNDATE`, `TIMESTAMP`, or `TIMESTAMP_UTC`.

### Date Formats

#### Standard Formats
- `yyyy-MM-dd` → `2024-01-15`
- `dd/MM/yyyy` → `15/01/2024`
- `MM/dd/yyyy` → `01/15/2024`
- `HH:mm:ss` → `10:30:45`
- `yyyy-MM-dd HH:mm:ss` → `2024-01-15 10:30:45`

#### ISO8601 Formats
- `ISO8601` → `2024-01-15T10:30:45+00:00`
- `ISO8601Z` → `2024-01-15T10:30:45Z`
- `yyyy-MM-ddTHH:mm:ss` → `2024-01-15T10:30:45`
- `yyyy-MM-ddTHH:mm:ss.SSSZ` → `2024-01-15T10:30:45.000+00:00`

#### Individual Components
- `yyyy` → `2024` (4-digit year)
- `yy` → `24` (2-digit year)
- `MM` → `01` (zero-padded month)
- `M` → `1` (month without padding)
- `dd` → `15` (zero-padded day)
- `d` → `15` (day without padding)
- `HH` → `10` (24-hour format)
- `mm` → `30` (minutes)
- `ss` → `45` (seconds)

### Date Arithmetic with addDate

The `addDate` function allows you to add **or subtract** time intervals to/from any variable. It uses ISO 8601 duration format (P for period, T for time).

**✨ NEW: Negative Intervals** - You can now subtract time by adding a minus sign after P or PT (e.g., `P-30D`, `PT-2H`).

#### Basic Intervals (Addition)

| Interval | Description | Example Output |
|----------|-------------|----------------|
| `P1D` | Add 1 day | `2024-01-16` |
| `P1W` | Add 1 week | `2024-01-22` |
| `P1M` | Add 1 month | `2024-02-15` |
| `P1Y` | Add 1 year | `2025-01-15` |
| `P2D` | Add 2 days | `2024-01-17` |
| `P7D` | Add 7 days | `2024-01-22` |
| `P30D` | Add 30 days | `2024-02-14` |

#### Negative Intervals (Subtraction) ⭐

| Interval | Description | Example Output (from 2024-01-15) |
|----------|-------------|-----------------------------------|
| `P-1D` | Subtract 1 day | `2024-01-14` |
| `P-7D` | Subtract 7 days | `2024-01-08` |
| `P-30D` | Subtract 30 days | `2023-12-16` |
| `P-1M` | Subtract 1 month | `2023-12-15` |
| `P-1Y` | Subtract 1 year | `2023-01-15` |

#### Time-Only Intervals

| Interval | Description | Example Output |
|----------|-------------|----------------|
| `PT1H` | Add 1 hour | `2024-01-15 11:30:45` |
| `PT30M` | Add 30 minutes | `2024-01-15 11:00:45` |
| `PT2H30M` | Add 2 hours 30 minutes | `2024-01-15 13:00:45` |
| `PT-1H` | **Subtract 1 hour** ⭐ | `2024-01-15 09:30:45` |
| `PT-2H` | **Subtract 2 hours** ⭐ | `2024-01-15 08:30:45` |

#### Combined Intervals

| Interval | Description | Example Output |
|----------|-------------|----------------|
| `P1DT1H` | Add 1 day and 1 hour | `2024-01-16 11:30:45` |
| `P1Y2M3D` | Add 1 year, 2 months, 3 days | `2025-03-18` |
| `P1DT12H30M15S` | Add 1 day, 12 hours, 30 minutes, 15 seconds | `2024-01-16 23:01:00` |

### Variable Examples

#### LASTRUNDATE (Flow-Based)
```sql
{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}      -- 2024-01-15
{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P1D'}}   -- 2024-01-16
{{LASTRUNDATE}}                              -- 2024-01-15 10:30:45
```

#### TIMESTAMP (Current Unix Milliseconds)
```sql
{{formatDate 'yyyy-MM-dd' TIMESTAMP}}        -- 2025-09-12
{{formatDate 'ISO8601' TIMESTAMP}}           -- 2025-09-12T17:20:35+00:00
{{addDate 'yyyy-MM-dd' TIMESTAMP 'P1D'}}     -- 2025-09-13
{{TIMESTAMP}}                                -- 1757697635917
```

#### TIMESTAMP_UTC (Current ISO8601)
```sql
{{formatDate 'yyyy-MM-dd' TIMESTAMP_UTC}}    -- 2025-09-12
{{formatDate 'HH:mm:ss' TIMESTAMP_UTC}}      -- 17:20:35
{{addDate 'yyyy-MM-dd' TIMESTAMP_UTC 'P1M'}} -- 2025-10-12
{{TIMESTAMP_UTC}}                            -- 2025-09-12T17:20:36+00:00
```

### Mixed Variable Usage
```sql
SELECT * FROM table
WHERE created_date = '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
  AND updated_date = '{{formatDate 'yyyy-MM-dd' TIMESTAMP}}'
  AND current_timestamp = '{{TIMESTAMP}}'
  AND utc_timestamp = '{{TIMESTAMP_UTC}}'
```

## Real-World Examples

### Data Processing with Current Timestamps
```sql
-- Insert current processing timestamp
INSERT INTO processing_log (
    flow_id,
    start_time,
    execution_id
) VALUES (
    123,
    '{{TIMESTAMP_UTC}}',
    '{{TIMESTAMP}}'
);
```

### API Request Tracking
```sql
-- Track API requests with unique timestamps
INSERT INTO api_requests (
    request_id,
    timestamp_ms,
    iso_timestamp,
    date_only
) VALUES (
    '{{TIMESTAMP}}',
    {{TIMESTAMP}},
    '{{TIMESTAMP_UTC}}',
    '{{formatDate 'yyyy-MM-dd' TIMESTAMP}}'
);
```

### Daily Data Processing with Flow History
```sql
SELECT * FROM daily_transactions
WHERE transaction_date = '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
  AND created_at >= '{{LASTRUNDATE}}'
  AND processed_at <= '{{TIMESTAMP_UTC}}'
```

### Weekly Reports with Date Ranges
```sql
SELECT * FROM weekly_metrics
WHERE week_start = '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
  AND week_end = '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P7D'}}'
  AND report_generated = '{{formatDate 'yyyy-MM-dd' TIMESTAMP}}'
```

### Data Retention with Current Time
```sql
DELETE FROM old_logs
WHERE created_at < '{{addDate 'yyyy-MM-dd' TIMESTAMP 'P-30D'}}'
  AND archived_at < '{{addDate 'ISO8601' TIMESTAMP_UTC 'P-90D'}}'
```

### ⭐ Subtract Days from Last Run (Historical Data)
```sql
-- Get data from 7 days BEFORE the last run
SELECT * FROM historical_data
WHERE event_date BETWEEN
    '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P-7D'}}'   -- 7 days before
    AND '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'   -- last run date

-- Query data from the past 30 days relative to last run
SELECT * FROM customer_activity
WHERE activity_date >= '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P-30D'}}'
  AND activity_date <= '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
```

### ⭐ Time-Based Lookback Windows
```sql
-- Get recent activity from 2 hours ago (relative to now)
SELECT * FROM real_time_events
WHERE event_timestamp >= '{{addDate 'yyyy-MM-dd HH:mm:ss' TIMESTAMP 'PT-2H'}}'
  AND event_timestamp <= '{{formatDate 'yyyy-MM-dd HH:mm:ss' TIMESTAMP}}'

-- Cleanup events older than 1 hour
DELETE FROM temporary_events
WHERE created_at < '{{addDate 'ISO8601' TIMESTAMP_UTC 'PT-1H'}}'
```

### ⭐ Date Range Queries (Past and Future)
```sql
-- Get data from 7 days before to 7 days after the last run
SELECT * FROM scheduled_tasks
WHERE task_date BETWEEN
    '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P-7D'}}'   -- 7 days before
    AND '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P7D'}}'  -- 7 days after
ORDER BY task_date
```

### ETL Pipeline with Multiple Timestamps
```sql
SELECT
    customer_id,
    order_date,
    total_amount,
    '{{TIMESTAMP}}' as batch_id,
    '{{TIMESTAMP_UTC}}' as processed_at
FROM orders
WHERE order_date BETWEEN
    '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
    AND '{{formatDate 'yyyy-MM-dd' TIMESTAMP}}'
  AND created_at >= '{{LASTRUNDATE}}'
```

### API Integration with ISO8601
```sql
SELECT * FROM api_events
WHERE timestamp >= '{{formatDate 'ISO8601Z' LASTRUNDATE}}'
  AND timestamp < '{{addDate 'yyyy-MM-ddTHH:mm:ssZ' TIMESTAMP 'P1D'}}'
  AND sync_time = '{{TIMESTAMP_UTC}}'
```

## PHP Usage

### Basic Usage
```php
use App\Services\QueryProcessingService;

$service = new QueryProcessingService();
$flowId = 123; // Only needed for LASTRUNDATE

// Using LASTRUNDATE (requires flowId)
$query = "SELECT * FROM table WHERE date = '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'";
$processedQuery = $service->processQuery($query, $flowId);

// Using TIMESTAMP (no flowId needed)
$query = "SELECT * FROM table WHERE created = '{{TIMESTAMP_UTC}}'";
$processedQuery = $service->processQuery($query);

// Using mixed variables
$query = "SELECT * FROM events WHERE last_run = '{{LASTRUNDATE}}' AND current_time = '{{TIMESTAMP}}'";
$processedQuery = $service->processQuery($query, $flowId);
```

### In a Controller or Service
```php
class DataProcessingController extends Controller
{
    private QueryProcessingService $queryProcessor;

    public function __construct(QueryProcessingService $queryProcessor)
    {
        $this->queryProcessor = $queryProcessor;
    }

    public function processData(Request $request)
    {
        $flowId = $request->input('flow_id');

        // Query with multiple timestamp types
        $rawQuery = "
            INSERT INTO processing_batches (
                flow_id,
                start_time,
                batch_id,
                last_run_date
            ) VALUES (
                {$flowId},
                '{{TIMESTAMP_UTC}}',
                {{TIMESTAMP}},
                '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
            )
        ";

        $processedQuery = $this->queryProcessor->processQuery($rawQuery, $flowId);

        // Execute the processed query
        DB::statement($processedQuery);

        return response()->json(['status' => 'success']);
    }

    public function getCurrentTimestamp()
    {
        // Get current timestamp without flow dependency
        $query = "SELECT '{{TIMESTAMP_UTC}}' as current_time, {{TIMESTAMP}} as timestamp_ms";
        $processedQuery = $this->queryProcessor->processQuery($query);

        $result = DB::select($processedQuery);
        return response()->json($result[0]);
    }
}
```

### Integration with API Nodes
```php
// In an API node configuration
$apiBody = [
    'requestId' => '{{TIMESTAMP}}',
    'timestamp' => '{{TIMESTAMP_UTC}}',
    'lastSync' => '{{formatDate 'ISO8601' LASTRUNDATE}}',
    'nextSync' => '{{addDate 'ISO8601' TIMESTAMP 'PT1H'}}'
];

$processedBody = $this->queryProcessor->processQuery(
    json_encode($apiBody),
    $flowId
);
```

## Error Handling

The service gracefully handles various error conditions:

- **No Flow ID**: LASTRUNDATE placeholders return empty strings, TIMESTAMP variables work normally
- **Invalid Flow**: LASTRUNDATE placeholders return empty strings, TIMESTAMP variables work normally
- **Invalid Intervals**: Returns empty string for the specific placeholder
- **Exceptions**: Logs errors and returns the original query

### Error Examples
```sql
-- If flowId is null or invalid (LASTRUNDATE only):
{{formatDate 'yyyy-MM-dd' LASTRUNDATE}} → ''

-- TIMESTAMP variables work regardless of flowId:
{{formatDate 'yyyy-MM-dd' TIMESTAMP}} → '2025-09-12'

-- If interval is invalid:
{{addDate 'yyyy-MM-dd' TIMESTAMP 'INVALID'}} → ''

-- Original query preserved on exceptions
```

## Testing

Run the comprehensive test suite:

```bash
# Run all QueryProcessingService tests
php artisan test tests/Unit/Services/QueryProcessingServiceTest.php
php artisan test tests/Unit/Services/QueryProcessingServiceTimestampTest.php

# Using Pest
./vendor/bin/pest tests/Unit/Services/QueryProcessingServiceTest.php
./vendor/bin/pest tests/Unit/Services/QueryProcessingServiceTimestampTest.php

# Run with verbose output
./vendor/bin/pest tests/Unit/Services/ --verbose
```

The test suite covers:
- ✅ LASTRUNDATE functionality (original)
- ✅ TIMESTAMP formatDate operations
- ✅ TIMESTAMP addDate operations
- ✅ TIMESTAMP standalone variables
- ✅ TIMESTAMP_UTC formatDate operations
- ✅ TIMESTAMP_UTC addDate operations
- ✅ TIMESTAMP_UTC standalone variables
- ✅ Mixed variable queries
- ✅ All date formats (basic, ISO8601, components)
- ✅ Date arithmetic operations
- ✅ **Negative intervals (subtraction)** ⭐ NEW
- ✅ **Mixed positive/negative intervals** ⭐ NEW
- ✅ Edge cases and error handling
- ✅ Content preservation

**Total Coverage**: 26 tests, 200+ assertions

## Performance Considerations

- **LASTRUNDATE**: Queries the Flow model for each reference - consider caching for high-frequency usage
- **TIMESTAMP/TIMESTAMP_UTC**: Generated in real-time using PHP's native functions - minimal overhead
- **Query Complexity**: Regex patterns for placeholder replacement - monitor with large query volumes
- **Memory Usage**: Minimal overhead beyond standard DateTime objects

## Best Practices

1. **Choose the Right Variable**:
   - Use `LASTRUNDATE` for incremental data processing based on flow execution
   - Use `TIMESTAMP` for unique identifiers and batch tracking
   - Use `TIMESTAMP_UTC` for API integrations and standardized timestamps

2. **Use Specific Formats**: Choose the most appropriate format for your use case
3. **Handle Errors**: Always check for empty results when using date placeholders
4. **Test Intervals**: Verify complex intervals work as expected
5. **Document Queries**: Keep track of which queries use dynamic date formatting
6. **Monitor Performance**: Watch for performance impact with large query volumes

## Static Analysis

The service passes PHPStan Level 5 analysis:

```bash
./vendor/bin/phpstan analyse src/App/Services/QueryProcessingService.php --level=5
```

## Troubleshooting

### Common Issues

**Q: Why is my LASTRUNDATE placeholder returning empty?**
A: Check that the flowId is valid and the flow has a `last_run` value.

**Q: Why aren't my TIMESTAMP variables working?**
A: TIMESTAMP and TIMESTAMP_UTC work independently of flowId. Ensure you're using correct template syntax.

**Q: Why is my interval not working?**
A: Ensure you're using valid ISO 8601 duration format (P1D, PT1H, etc.). For subtraction, use P-1D or PT-1H.

**Q: What's the difference between TIMESTAMP and TIMESTAMP_UTC?**
A: TIMESTAMP returns Unix milliseconds (integer), TIMESTAMP_UTC returns ISO8601 string format.

**Q: How do I get a specific timezone format?**
A: Use `ISO8601` for timezone offset (+00:00) or `ISO8601Z` for Z timezone.

**Q: Can I mix different variables in one query?**
A: Yes! You can use LASTRUNDATE, TIMESTAMP, and TIMESTAMP_UTC in the same query.

### Variable Comparison

| Use Case | Recommended Variable | Example |
|----------|---------------------|---------|
| Incremental data sync | `LASTRUNDATE` | `WHERE updated_at > '{{LASTRUNDATE}}'` |
| Unique batch IDs | `TIMESTAMP` | `batch_id = {{TIMESTAMP}}` |
| API timestamps | `TIMESTAMP_UTC` | `"timestamp": "{{TIMESTAMP_UTC}}"` |
| Current date filters | `TIMESTAMP` | `WHERE date = '{{formatDate 'yyyy-MM-dd' TIMESTAMP}}'` |

## Related Files

- `src/App/Services/QueryProcessingService.php` - Main service implementation
- `tests/Unit/Services/QueryProcessingServiceTest.php` - LASTRUNDATE test suite
- `tests/Unit/Services/QueryProcessingServiceTimestampTest.php` - TIMESTAMP test suite
- `src/Domain/Ipaas/Flows/Models/Flow.php` - Flow model for last run dates
- `src/App/Helpers/IpaasHelper.php` - Helper with TIMESTAMP generation functions

## Common Use Cases

### 1. **Data Cleanup (Remove Old Records)**
```sql
-- Delete logs older than 30 days
DELETE FROM application_logs
WHERE created_at < '{{addDate 'yyyy-MM-dd' TIMESTAMP 'P-30D'}}'

-- Archive records older than 90 days
INSERT INTO archive_table
SELECT * FROM active_table
WHERE last_modified < '{{addDate 'yyyy-MM-dd HH:mm:ss' TIMESTAMP 'P-90D'}}'
```

### 2. **Incremental Data Synchronization**
```sql
-- Sync only new/updated records since last run
SELECT * FROM source_table
WHERE updated_at >= '{{LASTRUNDATE}}'
  AND updated_at < '{{TIMESTAMP_UTC}}'
ORDER BY updated_at
```

### 3. **Historical Analysis (Look Back)**
```sql
-- Analyze trends from the past week relative to last run
SELECT 
    DATE(event_date) as day,
    COUNT(*) as events_count
FROM user_events
WHERE event_date BETWEEN
    '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P-7D'}}'
    AND '{{formatDate 'yyyy-MM-dd' LASTRUNDATE}}'
GROUP BY DATE(event_date)
```

### 4. **Real-Time Monitoring (Recent Activity)**
```sql
-- Get events from the last 2 hours
SELECT * FROM monitoring_events
WHERE event_timestamp >= '{{addDate 'ISO8601' TIMESTAMP_UTC 'PT-2H'}}'
  AND severity IN ('ERROR', 'CRITICAL')
ORDER BY event_timestamp DESC
```

### 5. **Scheduled Reports (Past to Future)**
```sql
-- Generate report covering 30 days before to 7 days after last run
SELECT * FROM sales_data
WHERE sale_date BETWEEN
    '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P-30D'}}'
    AND '{{addDate 'yyyy-MM-dd' LASTRUNDATE 'P7D'}}'
```

## Changelog

### Version 2.0 (October 2025) ⭐
- ✅ **Negative intervals support** (P-30D, PT-2H for subtraction)
- ✅ Comprehensive test coverage for negative intervals
- ✅ Updated documentation with subtraction examples
- ✅ 26 tests, 200+ assertions

### Version 1.0
- ✅ Added full TIMESTAMP variable support
- ✅ Added full TIMESTAMP_UTC variable support
- ✅ All formatDate patterns work with new variables
- ✅ All addDate patterns work with new variables
- ✅ Standalone variable replacement
- ✅ Mixed variable queries supported
- ✅ PHPStan Level 5 compliance
- ✅ Backward compatibility maintained