# Market Availability Feature Implementation

## Overview
Added new scraper functionality to extract and display Booking.com market availability data:
- "X properties found" (e.g., "117 properties found")
- "Y% of places to stay are unavailable" (e.g., "94% unavailable")

## Database Changes

### New Table: `market_availability`
```sql
CREATE TABLE market_availability (
  id INT AUTO_INCREMENT PRIMARY KEY,
  checkin DATE NOT NULL,
  checkout DATE NOT NULL,
  query_date DATETIME NOT NULL,
  properties_found INT DEFAULT NULL,
  availability_pct DECIMAL(5,2) DEFAULT NULL,
  unavailable_pct DECIMAL(5,2) DEFAULT NULL,
  search_url TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_checkin_checkout (checkin, checkout, query_date),
  INDEX idx_checkin (checkin),
  INDEX idx_query_date (query_date),
  INDEX idx_checkin_checkout (checkin, checkout)
);
```

## Code Changes

### 1. JavaScript Scraper (`searchHotelsPosition.js`)
- Added navigation to search results page
- Extracts market availability data from HTML:
  - "X properties found" pattern
  - "Y% unavailable" pattern
- Returns data in JSON output under `marketAvailability` key

### 2. PHP Scraper (`searchHotelsPosition.php`)
- Added `saveMarketAvailability()` method
- Extracts market data from JSON output
- Saves to `market_availability` table
- Updates existing records for same checkin/checkout/date

### 3. API Endpoint (`pricing_api.php`)
- Added `market_availability` action
- Calls `CompetitivePricing::getMarketAvailability()`

### 4. Data Access (`competitive_pricing.php`)
- Added `getMarketAvailability()` method
- Filters by checkin date range and query date range
- Returns all market availability records

### 5. Report Display (`report_market_pricing.php`)
- Added new "Market Availability Overview" section
- Displays table with:
  - Check-in/Check-out dates
  - Properties Found count
  - Availability % (color-coded: green/yellow/red)
  - Unavailable % (color-coded: green/yellow/red)
  - Query Date
- Auto-loads when report refreshes

## Usage

The market availability data is automatically collected when:
- Position scraper runs (`getHotelsPosition.php`)
- Search results are fetched for any date range

Data appears in the Market Intelligence Report under "Market Availability Overview" section.

## Data Flow

1. `getHotelsPosition.php` → calls `searchHotelsPosition.php`
2. `searchHotelsPosition.php` → executes `searchHotelsPosition.js`
3. `searchHotelsPosition.js` → navigates to Booking.com search results
4. Extracts "X properties found" and "Y% unavailable" from HTML
5. Returns data in JSON
6. `searchHotelsPosition.php` → saves to `market_availability` table
7. Report → fetches via API and displays in table

## Testing

To test the feature:
1. Run position scraper: `php getHotelsPosition.php 2025-11-13 2025-11-15`
2. Check logs for: "✓ Saved market availability: X properties, Y% available, Z% unavailable"
3. Open report: `reportsV3/report_market_pricing.php`
4. Check "Market Availability Overview" section

## Notes

- Data is collected per checkin/checkout/query_date combination
- Updates existing records if same date combination exists
- Availability % = 100 - Unavailable %
- Color coding:
  - Green: Good availability (≥50% available or ≤50% unavailable)
  - Yellow: Moderate (30-50% available or 50-70% unavailable)
  - Red: Low availability (<30% available or >70% unavailable)

