# Comprehensive Booking.com Scraper & Market Intelligence System
## Project Report & Documentation

**Generated:** 2025-11-03  
**Project Path:** `/var/www/mssql.danmarcrm.com/dev1/bMon`  
**Database:** `booking_analysis`  
**Status:** Production Ready

---

## Table of Contents

1. [Project Overview](#project-overview)
2. [System Architecture](#system-architecture)
3. [Directory Structure & File Paths](#directory-structure--file-paths)
4. [Core Functionality](#core-functionality)
5. [Database Schema & Indexes](#database-schema--indexes)
6. [Scraping System](#scraping-system)
7. [Reporting System](#reporting-system)
8. [Monitoring & Management](#monitoring--management)
9. [Performance Optimizations](#performance-optimizations)
10. [Configuration Files](#configuration-files)
11. [API Endpoints](#api-endpoints)
12. [Deployment & Operations](#deployment--operations)

---

## 1. Project Overview

### Purpose
A comprehensive Booking.com scraping and market intelligence system that:
- Scrapes hotel availability, pricing, and room details from Booking.com
- Tracks competitor properties and their market position
- Generates market intelligence reports for dynamic pricing strategies
- Monitors scraper health and provides management dashboard
- Supports parallel scraping with proxy rotation for scalability

### Key Features
- **Automated Scraping**: Puppeteer-based scraping with auto-retry and error handling
- **Parallel Processing**: Multi-worker parallel scraping (up to 50 workers)
- **Proxy Support**: Webshare proxy integration with health checking
- **Real-time Monitoring**: Dashboard for scraper status and data coverage
- **Market Intelligence**: Comprehensive reports on pricing, availability, and competition
- **Database Optimization**: Optimized indexes for fast query performance

---

## 2. System Architecture

### Technology Stack
- **Backend**: PHP 7.x
- **Scraping Engine**: Node.js + Puppeteer
- **Database**: MySQL (booking_analysis)
- **Web Server**: Apache/Nginx
- **Proxy Service**: Webshare.io

### Data Flow
```
Booking.com → Puppeteer Scraper → PHP Processor → MySQL Database → Reports
```

### Components
1. **Scraper Workers**: PHP scripts that orchestrate Puppeteer instances
2. **Puppeteer Scripts**: Node.js scripts that perform actual web scraping
3. **Database Layer**: MySQL with optimized indexes
4. **Report Generator**: PHP-based report system
5. **Monitoring Dashboard**: Web-based status and control interface

---

## 3. Directory Structure & File Paths

### Root Directory
```
/var/www/mssql.danmarcrm.com/dev1/bMon/
```

### Main Directories

#### Core Scraping Files
```
├── getHotelAndRoomDetails.php          # Main scraper orchestrator
├── getHotelAndRoomDetails.js            # Puppeteer scraping script
├── getHotelsPosition.php                # Position tracking scraper
├── searchHotelsPosition.js              # Position search Puppeteer script
├── booking_details.php                  # Booking details extractor
├── booking_fetch_controller.php         # Fetch controller
├── parallel_worker.php                  # Parallel worker implementation
├── parallel_booking_scraper.php         # Parallel scraper coordinator
├── refetch_single_hotel.php             # Single hotel refetch utility
└── fetch_remote_single_puppeteer.php    # Remote Puppeteer fetcher
```

#### Configuration Files
```
├── configIndex.php                      # Main database configuration
├── config_proxies.php                   # Proxy configuration
├── ProxyManager.php                     # Proxy management class
└── reportsV3/config.php                 # Reports configuration
```

#### Shell Scripts
```
├── start_parallel_scrapers.sh           # Start parallel scrapers
├── start_aggressive_parallel.sh         # Aggressive parallel (50 workers)
├── scraper_watchdog.sh                  # Auto-restart stuck scrapers
├── scraper_supervisor.sh                # Supervisor for watchdog
├── scraper-wrapper.sh                   # Scraper wrapper script
├── __killAllFetches.sh                  # Kill all active scrapers
├── fetch_next_14_days.sh                # Fetch next 14 days
├── fetch_next_15_days.sh                # Fetch next 15 days
├── fetch_next_two_months.sh             # Fetch next 2 months
├── fetch_today_nextweeks.sh             # Fetch today + next weeks
├── fetch_positions_30days.sh            # Fetch positions for 30 days
├── fetch_both_types.sh                  # Fetch both types
├── daily_otopeni_fetcher.sh             # Daily Otopeni fetcher
├── backfill_missing_dates.sh            # Backfill missing dates
├── start_market_checks.sh               # Start market checks
├── monitor_resources.sh                 # Monitor system resources
├── check_system_health.sh               # System health check
└── __checkFetch.sh                      # Check fetch status
```

#### Reports Directory (`reportsV3/`)
```
reportsV3/
├── report_market_pricing_v1.php         # Main market pricing report
├── report_market_pricing_v2.php         # Market pricing v2
├── report_market_pricing.php            # Market pricing (base)
├── report_booking_evolution.php         # Booking evolution report
├── report_bookings.php                  # Bookings report
├── report_capacity.php                  # Capacity report
├── report_compression.php               # Compression report
├── report_pricing.php                   # Pricing report
├── competitive_pricing.php              # Competitive pricing data
├── pricing_analytics.php                # Pricing analytics
├── booking_evolution_analytics.php      # Booking evolution analytics
├── compression_analytics.php            # Compression analytics
├── analytics.php                        # General analytics
├── scraper_status.php                   # Scraper status dashboard
├── pricing_api.php                      # Pricing API endpoint
├── booking_evolution_api.php            # Booking evolution API
├── compression_api.php                  # Compression API
├── refetch_api.php                      # Refetch API
├── api.php                              # General API
├── add_indexes.php                      # Add database indexes
├── optimize_indexes.php                 # Optimize indexes (new)
├── config.php                           # Reports config
├── db.php                               # Database helper
└── test_booking_evolution.php           # Test booking evolution
```

#### Logs & Data Directories
```
├── logs/                                # General logs
├── logs2/                               # Scraper logs
│   ├── fetch_*.log                      # Individual scraper logs
│   ├── aggressive_worker_*.log          # Aggressive worker logs
│   ├── scraper_error.log                # Scraper errors
│   ├── proxy_statistics.json            # Proxy statistics
│   └── proxy_stats.log                  # Proxy stats log
└── responses/                           # Scraper response files
    └── [date]/                          # Date-organized responses
```

#### API Directory
```
api/
├── route.php                            # API router
├── puppeteer-scrape.php                 # Puppeteer scrape API
├── delete_project.php                   # Delete project API
└── delete_projecta.php                  # Delete project A API
```

#### Memory Service (if exists)
```
memory-service/
├── install.sh                           # Installation script
├── install-fix.sh                       # Installation fix
├── start.sh                             # Start service
├── start-simple.sh                      # Start simple
└── start-unified.sh                     # Start unified
```

#### Other Files
```
├── booking_analytics.php                 # Booking analytics
├── booking_dashboard.php                 # Booking dashboard
├── booking_monitor.php                   # Booking monitor
├── index.php                             # Main index
├── scraper-api.php                       # Scraper API
├── extract_room_units_test.php           # Room units extractor test
├── bulk-import-memory.php                # Bulk import memory
├── validate_two_weeks.php                # Validate two weeks data
└── tstnotes.php                          # Test notes
```

---

## 4. Core Functionality

### 4.1 Scraping System

#### Main Scraper (`getHotelAndRoomDetails.php`)
- **Purpose**: Orchestrates scraping of hotel and room details
- **Features**:
  - Date range processing
  - Hotel list management
  - Parallel worker support
  - Proxy integration
  - Progress tracking
  - Error handling and retry logic
- **Usage**:
  ```bash
  php getHotelAndRoomDetails.php 'from=2025-11-03&days=30&stay_nights=2&pers=2'
  ```

#### Puppeteer Scraper (`getHotelAndRoomDetails.js`)
- **Purpose**: Performs actual web scraping using Puppeteer
- **Features**:
  - Headless browser automation
  - Proxy support
  - Resource blocking for speed
  - Timeout handling
  - Base64 encoding of HTML/text content
- **Usage**:
  ```bash
  node getHotelAndRoomDetails.js "URL" waitTime extractText blockResources proxy
  ```

#### Parallel Processing
- **Workers**: Up to 50 parallel workers
- **Distribution**: Each worker handles different date ranges
- **Coordination**: File-based progress tracking
- **Scripts**:
  - `start_parallel_scrapers.sh`: Standard parallel (configurable workers)
  - `start_aggressive_parallel.sh`: Aggressive mode (50 workers)

### 4.2 Proxy Management

#### ProxyManager Class (`ProxyManager.php`)
- **Features**:
  - Proxy rotation
  - Health checking
  - Failure tracking
  - Statistics logging
  - Automatic disable after failures
- **Configuration**: `config_proxies.php`
- **Statistics**: `logs2/proxy_statistics.json`

#### Proxy Configuration (`config_proxies.php`)
```php
'enabled' => true,
'proxies' => [
    'ip:port:username:password',
    // ... 10 proxies
],
'max_failures_before_disable' => 5
```

### 4.3 Data Processing

#### Data Storage
- **Tables**:
  - `properties`: Main property data
  - `room_details`: Room-level details
  - `propsid`: Property IDs (if used)

#### Data Fields
- Property information (name, rating, location)
- Pricing (price_per_stay, minp, maxp)
- Availability (total_rooms, sold_rooms, is_sold_out)
- Dates (checkin, checkout, query_date)
- Metadata (last_fetch_time, last_fetch_date)

---

## 5. Database Schema & Indexes

### 5.1 Tables

#### `properties` Table
**Key Columns:**
- `id` (bigint): Property ID (primary key)
- `name` (varchar): Property name
- `checkin` (date): Check-in date
- `checkout` (date): Check-out date
- `query_date` (datetime): When data was scraped
- `price_per_stay` (decimal): Price for stay
- `total_rooms` (varchar): Total available rooms
- `is_sold_out` (tinyint): Sold out flag
- `persons` (varchar): Number of persons
- `last_fetch_time` (datetime): Last fetch timestamp
- `last_fetch_date` (date): Last fetch date

#### `room_details` Table
**Key Columns:**
- `id` (bigint): Primary key
- `hotel_id` (bigint): Hotel ID (foreign key to properties.id)
- `checkin` (date): Check-in date
- `checkout` (date): Check-out date
- `query_date` (datetime): Query date
- `has_inventory` (tinyint): Has inventory flag
- `apartment_id` (bigint): Apartment ID (if applicable)

### 5.2 Optimized Indexes

#### Properties Table Indexes
```sql
-- Date range queries
idx_properties_checkin_query_date (checkin, query_date, id, checkout)
idx_properties_id_checkin_checkout_query_date (id, checkin, checkout, query_date)
idx_properties_checkin_checkout_query_date_id (checkin, checkout, query_date, id)

-- Competitor filtering
idx_properties_id_checkin (id, checkin)

-- Sold out calculations
idx_properties_sold_out_dates (is_sold_out, checkin, checkout, query_date)

-- Room availability
idx_properties_total_rooms (total_rooms, checkin, query_date)

-- Price queries
idx_properties_price_checkin (price_per_stay, checkin, query_date)
idx_properties_minp_maxp (minp, maxp, checkin, query_date)

-- Fetch tracking
idx_properties_last_fetch (last_fetch_date, last_fetch_time, id)

-- Occupancy
idx_properties_persons_checkin (persons, checkin, checkout)
```

#### Room Details Table Indexes
```sql
-- Inventory queries
idx_room_details_query_date_inventory (query_date, has_inventory, hotel_id)
idx_room_details_hotel_checkin_checkout_query (hotel_id, checkin, checkout, query_date, has_inventory)
idx_room_details_hotel_query_inventory (hotel_id, query_date, has_inventory, checkin)

-- Date range
idx_room_details_dates_hotel (checkin, checkout, hotel_id, query_date)
```

### 5.3 Index Optimization Script
- **File**: `reportsV3/optimize_indexes.php`
- **Purpose**: Adds missing indexes for optimal query performance
- **Usage**: `php reportsV3/optimize_indexes.php`
- **Features**:
  - Checks existing indexes
  - Adds missing indexes
  - Updates table statistics
  - Reports summary

**Total Indexes**: 25 indexes across 3 tables
- **Properties**: 14 indexes
- **Room Details**: 9 indexes
- **Propsid**: 2 indexes

**Latest Optimization (2025-11-03)**:
- Added 8 new indexes to properties table
- Added 2 new indexes to room_details table
- Added 2 new indexes to propsid table
- All indexes verified and statistics updated

---

## 6. Reporting System

### 6.1 Market Pricing Report (`report_market_pricing_v1.php`)
**Purpose**: Main market intelligence report

**Features**:
- Market data by date
- Property breakdown
- Sold out counts
- Price analytics
- Heat map visualization
- Date range filtering

**Key Functions**:
- `getMarketIntelligence()`: Get market data for date range
- `getPropertyBreakdown()`: Get property-level breakdown
- `calculateSoldOutCount()`: Calculate sold out properties

### 6.2 Other Reports
- **Booking Evolution**: `report_booking_evolution.php`
- **Capacity**: `report_capacity.php`
- **Compression**: `report_compression.php`
- **Pricing**: `report_pricing.php`

### 6.3 API Endpoints
- **Pricing API**: `reportsV3/pricing_api.php`
- **Booking Evolution API**: `reportsV3/booking_evolution_api.php`
- **Compression API**: `reportsV3/compression_api.php`
- **Refetch API**: `reportsV3/refetch_api.php`

---

## 7. Monitoring & Management

### 7.1 Scraper Status Dashboard (`reportsV3/scraper_status.php`)
**Features**:
- Active scraper monitoring
- Date coverage visualization
- Missing date ranges detection
- Kill/restart scraper controls
- Watchdog trigger
- Log viewing

**Key Functions**:
- `getActiveScrapers()`: Get active scraper processes
- `getDateCoverage()`: Get data coverage by date
- `getMissingDateRanges()`: Identify missing dates
- `getLogStatus()`: Get scraper log status

### 7.2 Watchdog System
**Files**:
- `scraper_watchdog.sh`: Detects and restarts stuck scrapers
- `scraper_supervisor.sh`: Continuously runs watchdog

**Features**:
- Detects stuck PHP processes
- Kills associated Node.js processes
- Restarts scrapers with same parameters
- Logs restart events

### 7.3 Monitoring Scripts
- `monitor_resources.sh`: Monitor system resources
- `check_system_health.sh`: System health check
- `__checkFetch.sh`: Check fetch status

---

## 8. Performance Optimizations

### 8.1 Database Optimizations
- **Indexes**: 14+ optimized indexes on properties and room_details
- **Query Optimization**: Composite indexes for common query patterns
- **Statistics**: Regular ANALYZE TABLE updates

### 8.2 Scraping Optimizations
- **Parallel Workers**: Up to 50 concurrent workers
- **Proxy Rotation**: Distribute load across proxies
- **Resource Blocking**: Block unnecessary resources in Puppeteer
- **Timeout Management**: Configurable timeouts per request

### 8.3 Caching & Storage
- **Progress Files**: File-based progress tracking
- **Response Files**: Cached scraper responses
- **Log Files**: Organized log structure

---

## 9. Configuration Files

### 9.1 Database Configuration (`configIndex.php`)
```php
define('DB_HOST', 'localhost');
define('DB_NAME', 'booking_analysis');
define('DB_USER', 'root');
define('DB_PASS', 'danmar1234');
```

### 9.2 Proxy Configuration (`config_proxies.php`)
```php
return [
    'enabled' => true,
    'proxies' => [
        'ip:port:username:password',
        // ... 10 proxies
    ],
    'health_check_url' => 'https://www.booking.com/search.html',
    'health_check_timeout' => 5,
    'max_failures_before_disable' => 5,
    'statistics' => [
        'log_file' => 'logs2/proxy_statistics.json',
        'reset_interval' => 3600
    ]
];
```

### 9.3 Reports Configuration (`reportsV3/config.php`)
- Database connection settings
- Report-specific configurations

---

## 10. API Endpoints

### 10.1 Scraper APIs
- **POST** `/api/puppeteer-scrape.php`: Submit scraping job
- **GET** `/api/route.php`: API router

### 10.2 Report APIs
- **GET** `/reportsV3/pricing_api.php`: Get pricing data
- **GET** `/reportsV3/booking_evolution_api.php`: Get booking evolution
- **GET** `/reportsV3/compression_api.php`: Get compression data
- **POST** `/reportsV3/refetch_api.php`: Trigger refetch

---

## 11. Deployment & Operations

### 11.1 Starting Scrapers

#### Standard Parallel (Configurable)
```bash
./start_parallel_scrapers.sh
```

#### Aggressive Parallel (50 Workers)
```bash
./start_aggressive_parallel.sh
```

#### Single Date Range
```bash
php getHotelAndRoomDetails.php 'from=2025-11-03&days=30&stay_nights=2&pers=2'
```

### 11.2 Monitoring

#### View Active Scrapers
```bash
ps aux | grep "php.*getHotelAndRoomDetails"
```

#### View Logs
```bash
tail -f logs2/fetch_*.log
```

#### Dashboard
Access: `http://your-domain/reportsV3/scraper_status.php`

### 11.3 Maintenance

#### Kill All Scrapers
```bash
./__killAllFetches.sh
```

#### Restart Stuck Scrapers
```bash
bash scraper_watchdog.sh
```

#### Optimize Database
```bash
php reportsV3/optimize_indexes.php
```

#### Update Statistics
```sql
ANALYZE TABLE properties, room_details;
```

---

## 12. Key Features Summary

### ✅ Implemented Features
1. **Automated Scraping**: Full automation with Puppeteer
2. **Parallel Processing**: Up to 50 concurrent workers
3. **Proxy Support**: Webshare proxy integration
4. **Health Monitoring**: Watchdog and supervisor scripts
5. **Dashboard**: Web-based status and control
6. **Database Optimization**: 14+ optimized indexes
7. **Error Handling**: Comprehensive error handling and retry
8. **Progress Tracking**: File-based progress tracking
9. **Logging**: Comprehensive logging system
10. **Reports**: Multiple report types and APIs

### 📊 Performance Metrics
- **Single Worker**: ~7s per hotel
- **50 Workers**: ~1.2 min per day (30 days in ~36 minutes)
- **Database Queries**: Optimized with composite indexes
- **Proxy Rotation**: Automatic with health checking

### 🔧 Maintenance
- **Auto-Restart**: Watchdog for stuck scrapers
- **Health Checks**: System health monitoring
- **Statistics**: Proxy and scraper statistics
- **Logs**: Organized log structure

---

## 13. File Count Summary

- **PHP Files**: ~46 files
- **JavaScript Files**: ~3 files
- **Shell Scripts**: ~20 files
- **Total Code Files**: ~88 files

---

## 14. Important Paths Reference

### Absolute Paths
```
Project Root:     /var/www/mssql.danmarcrm.com/dev1/bMon
Reports:          /var/www/mssql.danmarcrm.com/dev1/bMon/reportsV3
Logs:             /var/www/mssql.danmarcrm.com/dev1/bMon/logs2
Responses:        /var/www/mssql.danmarcrm.com/dev1/bMon/responses
Config:           /var/www/mssql.danmarcrm.com/dev1/bMon/configIndex.php
```

### Web Paths (Relative)
```
Dashboard:        /reportsV3/scraper_status.php
Market Report:    /reportsV3/report_market_pricing_v1.php
Pricing API:      /reportsV3/pricing_api.php
```

---

## 15. Troubleshooting

### Common Issues

#### Scrapers Getting Stuck
- **Solution**: Run `scraper_watchdog.sh` or use dashboard "Restart Stuck Scrapers"

#### Missing Data for Dates
- **Solution**: Use dashboard to start scraper for missing date ranges

#### Slow Queries
- **Solution**: Run `php reportsV3/optimize_indexes.php` and `ANALYZE TABLE`

#### Proxy Failures
- **Solution**: Check `logs2/proxy_statistics.json` for proxy health

#### Database Connection Errors
- **Solution**: Verify `configIndex.php` credentials

---

## 16. Future Enhancements

### Potential Improvements
1. **Centralized Puppeteer Service**: REST API for reusable scraping
2. **Redis Queue**: Replace file-based queue with Redis
3. **Rate Limit Detection**: Automatic throttling based on error rates
4. **Residential Proxies**: Upgrade to residential proxies if needed
5. **Real-time Dashboard**: WebSocket-based real-time updates
6. **Automated Alerts**: Email/SMS alerts for failures
7. **Data Validation**: Automated data quality checks
8. **Backup System**: Automated database backups

---

## 17. Contact & Support

### Configuration
- **Database**: `booking_analysis` on `localhost`
- **PHP Version**: 7.x
- **Node.js**: Required for Puppeteer

### Logs Location
- Scraper Logs: `logs2/fetch_*.log`
- Error Logs: `logs2/scraper_error.log`
- Proxy Stats: `logs2/proxy_statistics.json`

---

**End of Report**

*Last Updated: 2025-11-03*  
*Version: 1.0*

