# Booking.com Fetch System - Complete Documentation

## Overview

This system fetches booking availability data from Booking.com for properties in Otopeni, Romania. It tracks hotel availability, pricing, and room details for competitive analysis and pricing optimization.

## System Architecture

### Components

1. **PHP Scraper** (`daily_booking_scraper_v14.php`)
   - Fetches data from Booking.com API
   - Parses and saves data to database
   - Handles errors and logging

2. **Shell Scripts** (`fetch_next_14_days.sh`, `fetch_next_15_days.sh`)
   - Orchestrates daily fetches
   - Validates data completeness
   - Manages batch processing

3. **Dashboard** (`booking_dashboard.php`)
   - View fetched data by date
   - Start new fetches
   - Monitor progress

4. **Analytics** (`booking_analytics.php`)
   - Visualize trends
   - Identify high-load periods
   - Price spike detection

## Code Path

### Starting a Fetch (Shell Script → PHP)

```
1. User runs shell script (e.g., fetch_next_15_days.sh)
   └─> Loops through dates (2025-10-30 to 2025-11-14)
       └─> For each date, calls PHP scraper
           └─> daily_booking_scraper_v14.php receives parameters
               └─> Creates BookingScraperV14 instance
                   └─> Calls fetchBookingForDate()
```

### Fetching Data (PHP Execution Flow)

```
fetchBookingForDate(checkin, checkout, persons)
│
├─> 1. Load hotels from propsid table (200 properties)
│   └─> SQL: SELECT * FROM propsid ORDER BY prio DESC LIMIT 200
│
├─> 2. For each hotel:
│   ├─> Build Booking.com URL with dates
│   ├─> Call scrapeBooking(url) → Booking.com API
│   │   └─> POST to https://sun.1crm.info/booking-api/v1/
│   │       └─> Returns JSON with room data
│   │
│   ├─> Parse API response
│   │   └─> Extract rooms, prices, availability
│   │
│   ├─> parseAndSaveFromAPIResponse()
│   │   ├─> Count available rooms
│   │   ├─> Determine if sold out
│   │   ├─> savePropertyFromAPI() → Insert into 'properties' table
│   │   └─> saveRoomsFromAPI() → Insert into 'room_details' table
│   │
│   └─> usleep(500000) // 0.5s delay between hotels
│
└─> 3. Validation Summary
    ├─> Total hotels: 200
    ├─> Successful: count
    ├─> Failed: count
    └─> Success rate: (successful / total) * 100
```

### Database Schema

```
properties table:
├─ id (hotel ID)
├─ name (hotel name)
├─ checkin (check-in date)
├─ checkout (check-out date)
├─ rating, review_count
├─ total_rooms (available rooms)
├─ no_availability (1 if sold out)
├─ query_date (when data was fetched)
└─ ... (other fields)

room_details table:
├─ id (unique room ID)
├─ hotel_id (links to properties)
├─ checkin, checkout
├─ room_name (e.g., "Deluxe Queen Room")
├─ price, raw_price, avg_price_per_night_eur
├─ has_inventory (1 if available)
└─ ... (other fields)
```

### API Response Format

```json
{
  "success": true,
  "data": {
    "hotels": [],
    "rooms": [
      {
        "name": "Deluxe Queen Room",
        "price": "€101",
        "availableRooms": 1,
        "features": ["Free WiFi", "Kitchen"]
      }
    ],
    "metadata": {
      "hotelName": "Hotel Name",
      "rating": "9.5",
      "hasRooms": true
    }
  }
}
```

## Key Features

### 1. Priority-Based Fetching

Properties with `prio` value are fetched first:
```sql
ORDER BY 
    CASE WHEN prio IS NOT NULL THEN 0 ELSE 1 END,
    prio DESC,
    id ASC
LIMIT 200
```

### 2. Sold-Out Tracking

Properties with 0 rooms are still saved to track availability:
- `no_availability = 1` (property has no rooms)
- `total_rooms = 0`
- Still appears in dashboard for market analysis

### 3. Validation

- Minimum 100 hotels per date
- Logs failed hotel IDs
- Success rate tracking
- Exit code 1 if insufficient data

### 4. Rate Limiting

- 0.5 second delay between hotels
- Prevents API overload
- Average: ~10 seconds per hotel

## Usage Examples

### Run 15-Day Fetch

```bash
cd /var/www/mssql.danmarcrm.com/openai/v14With1CRM
chmod +x fetch_next_15_days.sh
./fetch_next_15_days.sh
```

### Check Progress

```bash
# View logs
tail -f __daily_scraper.log

# Check database
mysql -u root -p booking_analysis -e \
  "SELECT checkin, COUNT(*) as hotels FROM properties 
   WHERE checkin >= '2025-10-30' GROUP BY checkin;"
```

### Stop Fetch

```bash
# Create stop file
touch __stop.dat

# Or kill process
pkill -f daily_booking_scraper
```

## Monitoring

### Dashboard

View at: `https://mssql.danmarcrm.com/openai/v14With1CRM/booking_dashboard.php`

Features:
- Select date to view data
- See hotel list with pricing
- View available dates
- Start new fetches

### Analytics

View at: `https://mssql.danmarcrm.com/openai/v14With1CRM/booking_analytics.php`

Features:
- Occupancy rate charts
- Price trend analysis
- High-load period detection
- Price spike alerts

## Troubleshooting

### Fetch Stuck or Hanging

```bash
# Check running processes
ps aux | grep daily_booking_scraper

# Kill if stuck
pkill -9 -f daily_booking_scraper
```

### Missing Hotels

Check if hotel exists in propsid:
```sql
SELECT * FROM propsid WHERE name LIKE '%hotel_name%';
```

If missing, add to propsid table with priority.

### API Errors

Check API response in responses folder:
```bash
ls -lh responses/hotel_*_checkin_2025-10-30*.json
```

## Performance

- **Speed**: ~10 seconds per hotel
- **200 hotels**: ~35 minutes per date
- **15 dates**: ~8-9 hours total
- **Storage**: ~1-2 KB per hotel response

## Next Steps

1. Monitor fetch completion
2. Review analytics dashboard
3. Identify high-demand periods
4. Adjust pricing strategy
5. Automate daily runs (cron job)






