# Re-fetch Feature - Market Pricing Report

## Overview

Added Booking.com links and re-fetch functionality to the Market Pricing Report property breakdown grid.

## Features Added

### 1. ✅ Booking.com Direct Links

Each property in the breakdown grid now has a **"View"** button that opens the property page on Booking.com with the exact check-in/checkout dates.

**Example URL Format:**
```
https://www.booking.com/searchresults.html?ss=12345&checkin=2025-11-04&checkout=2025-11-06&group_adults=2&group_children=0&dest_type=hotel
```

### 2. ✅ Re-fetch Single Property

Each property has a **"🔄" button** to re-fetch its data from Booking.com.

**How it works:**
1. Click the 🔄 button next to any property
2. Confirm the re-fetch action
3. The system queues a background job to scrape that property
4. Button changes to ✓ when queued
5. Data will be updated in the database within 1-2 minutes

**What gets re-fetched:**
- Current prices and availability
- Room details
- Rating and reviews
- All property metadata

### 3. ✅ Re-fetch All Properties

A **"Re-fetch All Properties"** button at the top of the grid allows bulk re-fetching of all visible properties.

**Limitations:**
- Maximum 100 properties per bulk request (for safety)
- 0.1 second delay between each property spawn
- All fetches run in parallel background processes

## Technical Implementation

### Files Created

1. **`refetch_api.php`** - API endpoint for re-fetch requests
   - Handles single property re-fetch
   - Handles bulk property re-fetch
   - Returns JSON responses

2. **`refetch_single_hotel.php`** - CLI script to re-fetch one hotel
   - Standalone script using the main scraper
   - Can be run manually: `php refetch_single_hotel.php HOTEL_ID CHECKIN CHECKOUT`
   - Logs output to `/tmp/refetch_HOTELID_TIMESTAMP.log`

### Files Modified

1. **`report_market_pricing.php`**
   - Added "Actions" column to property grid
   - Added Booking.com link button
   - Added re-fetch button for each property
   - Added "Re-fetch All" button
   - Added JavaScript functions:
     - `buildBookingUrl()` - Constructs Booking.com URLs
     - `refetchProperty()` - Triggers single property re-fetch
     - `refetchAllProperties()` - Triggers bulk re-fetch
   - Added global variable `currentDrilldownProperties` to track visible properties

## Usage Examples

### 1. View Property on Booking.com

1. Open the Market Pricing Report
2. Click on any date cell to open property breakdown
3. Click the **🔗 View** button next to any property
4. Browser opens Booking.com with that property and dates pre-filled

### 2. Re-fetch Single Property

1. Open property breakdown grid
2. Click the **🔄** button next to a property
3. Confirm the action in the popup
4. Wait 1-2 minutes for data to refresh
5. Refresh the report to see updated data

### 3. Re-fetch All Visible Properties

1. Open property breakdown grid (shows multiple properties)
2. Click **🔄 Re-fetch All Properties** button at the top
3. Confirm bulk re-fetch (shows count)
4. All properties are queued for background processing
5. Wait 2-5 minutes depending on property count
6. Refresh the report to see updated data

### 4. Manual Re-fetch via CLI

If needed, you can trigger re-fetch manually:

```bash
cd /var/www/mssql.danmarcrm.com/openai/v14With1CRM

# Re-fetch specific hotel
php refetch_single_hotel.php 12345 2025-11-10 2025-11-12 2 2

# Check logs
tail -f /tmp/refetch_*.log
```

## API Endpoints

### POST /reportsV3/refetch_api.php

**Single Property Re-fetch:**
```json
{
  "action": "refetch_single",
  "property_id": 12345,
  "checkin": "2025-11-10",
  "checkout": "2025-11-12"
}
```

**Response:**
```json
{
  "ok": true,
  "message": "Re-fetch queued for property 12345",
  "property_id": 12345,
  "checkin": "2025-11-10",
  "checkout": "2025-11-12"
}
```

**Bulk Re-fetch:**
```json
{
  "action": "refetch_bulk",
  "properties": [
    {"property_id": 12345, "checkin": "2025-11-10", "checkout": "2025-11-12"},
    {"property_id": 67890, "checkin": "2025-11-10", "checkout": "2025-11-12"}
  ]
}
```

**Response:**
```json
{
  "ok": true,
  "message": "Re-fetch queued for 2 properties",
  "queued_count": 2,
  "total_requested": 2
}
```

## System Requirements

- **PHP 7.x+** with exec() function enabled
- **Background process execution** (nohup)
- **Puppeteer/Node.js** installed for scraping
- **Write access** to `/tmp/` for log files

## Monitoring

### Check Active Re-fetches

```bash
# View active re-fetch processes
ps aux | grep "refetch_single_hotel" | grep -v grep

# View recent logs
ls -lt /tmp/refetch_*.log | head -10

# Monitor a specific re-fetch
tail -f /tmp/refetch_12345_*.log
```

### Database Verification

```sql
-- Check recently updated properties
SELECT id, name, checkin, checkout, query_date, total_rooms
FROM properties
WHERE DATE(query_date) = CURDATE()
ORDER BY query_date DESC
LIMIT 10;

-- Check for specific property
SELECT id, name, checkin, query_date, total_rooms, is_sold_out
FROM properties
WHERE id = 12345 AND checkin = '2025-11-10'
ORDER BY query_date DESC
LIMIT 5;
```

## Safety Features

1. **Confirmation Dialogs** - Prevents accidental re-fetches
2. **Button State Management** - Disables button during processing
3. **Error Handling** - Shows user-friendly error messages
4. **Rate Limiting** - 0.1s delay between bulk spawns
5. **Max Limit** - 100 properties per bulk request
6. **Background Execution** - Doesn't block UI
7. **Logging** - All re-fetches logged to /tmp/

## Troubleshooting

### Re-fetch Not Working?

1. **Check logs:**
   ```bash
   tail -f /tmp/refetch_*.log
   ```

2. **Verify exec() is enabled:**
   ```bash
   php -r "echo (function_exists('exec') ? 'exec enabled' : 'exec disabled');"
   ```

3. **Check process spawning:**
   ```bash
   ps aux | grep refetch_single_hotel
   ```

4. **Test manual re-fetch:**
   ```bash
   php refetch_single_hotel.php 12345 2025-11-10 2025-11-12 2 2
   ```

### Button Shows Error?

- Check browser console (F12) for JavaScript errors
- Verify `refetch_api.php` is accessible
- Check file permissions on refetch scripts

### Data Not Updating?

- Re-fetches take 1-2 minutes to complete
- Check if scraper is already running (might be busy)
- Verify database connection in scraper
- Check for errors in `/tmp/refetch_*.log`

## Security Considerations

1. **No Authentication** - Anyone with access to the report can trigger re-fetches
   - Consider adding authentication if needed
   - Monitor for abuse via log files

2. **Command Injection** - All parameters are escaped using `escapeshellarg()`
   - Property IDs, dates are validated
   - Safe to use with untrusted input

3. **Resource Usage** - Bulk re-fetches spawn multiple processes
   - Limited to 100 properties max
   - 0.1s delay prevents DOS
   - Monitor system load during bulk operations

## Future Improvements

1. **Queue System** - Implement proper job queue (Redis/database)
2. **Progress Tracking** - Real-time progress updates in UI
3. **Authentication** - Add user authentication for re-fetch actions
4. **Rate Limiting** - Per-user rate limits
5. **Scheduling** - Schedule re-fetches for specific times
6. **Webhooks** - Notify when re-fetch completes
7. **History** - Track re-fetch history per property

---

**Status:** ✅ COMPLETE - Ready for production use

**Date:** November 4, 2025

**Access Report:**
```
https://mssql.danmarcrm.com/openai/v14With1CRM/reportsV3/report_market_pricing.php
```

