# Canada Buys Tender Data Scraper

## Summary

Successfully created a database and scraper to collect tender data from canadabuys.canada.ca

### Database Details
- **Host:** localhost
- **User:** root
- **Database:** canadabuys
- **Table:** tenders

### Table Schema

| Field | Type | Description |
|-------|------|-------------|
| id | INT (PK) | Auto-increment primary key |
| tender_id | VARCHAR(255) | Unique tender identifier from URL |
| title | VARCHAR(500) | Tender title |
| url | VARCHAR(500) | Tender detail page URL |
| category | VARCHAR(50) | Goods or Services |
| open_date | DATE | Publication/amendment date |
| is_amended | TINYINT(1) | Whether tender was amended |
| closing_date | DATE | Tender closing date |
| organization | VARCHAR(300) | Issuing organization |
| created_at | TIMESTAMP | Record creation time |
| updated_at | TIMESTAMP | Record update time |

### Data Available

From ajax.json analysis:
- ✓ Title (full text)
- ✓ URL (tender notice link)
- ✓ Category (Services/Goods)
- ✓ Open/Amendment Date
- ✓ Amended Status
- ✓ Closing Date
- ✓ Organization Name

**Total records available:** ~233,591 tenders

### Files Created

1. **create_table_and_insert.py**
   - Creates database and table
   - Parses ajax.json and inserts initial data
   - Run once to set up database

2. **scrape_tenders.py**
   - Main scraper script
   - Fetches data page by page
   - Inserts into database
   - Handles duplicates automatically

### Usage

#### 1. Initial Setup (Already Done)
```bash
python create_table_and_insert.py
```
Result: Database created with 50 records from ajax.json

#### 2. Test Scraper (Already Done)
```bash
python scrape_tenders.py --start-page 1 --max-pages 3
```
Result: Successfully tested with 3 pages (170 total records)

#### 3. Run Full Scraper (To collect all ~240,000 records)

**Option A: Run until target reached (240,000 records)**
```bash
python scrape_tenders.py
```

**Option B: Run specific number of pages**
```bash
# Fetch 100 pages
python scrape_tenders.py --max-pages 100

# Fetch 1000 pages (~50,000 records)
python scrape_tenders.py --max-pages 1000

# Fetch all available (~4,672 pages for 233,591 records)
python scrape_tenders.py --max-pages 5000
```

**Option C: Resume from specific page**
```bash
# Resume from page 500
python scrape_tenders.py --start-page 500

# Resume from page 500, fetch 1000 more pages
python scrape_tenders.py --start-page 500 --max-pages 1000
```

### Features

✓ **Duplicate Handling:** Uses `tender_id` as unique key, updates existing records
✓ **Error Recovery:** Continues on errors, stops after 5 consecutive failures
✓ **Progress Tracking:** Shows progress every 10 pages
✓ **Polite Scraping:** 0.5 second delay between requests
✓ **Resume Support:** Can resume from any page number
✓ **Keyboard Interrupt:** Ctrl+C to stop gracefully

### Current Status

- ✓ Database created
- ✓ Table schema defined
- ✓ Initial data loaded (50 records)
- ✓ Scraper tested (170 total records)
- ⏳ Ready to fetch all records

### Estimated Time

- **Records per page:** 50
- **Delay per request:** 0.5 seconds
- **Pages for 240,000 records:** ~4,800 pages
- **Estimated time:** ~40-50 minutes (0.5s × 4,800 pages)

### Query Examples

```sql
-- Count total records
SELECT COUNT(*) FROM tenders;

-- Count by category
SELECT category, COUNT(*) as count
FROM tenders
GROUP BY category;

-- Recent tenders
SELECT title, category, open_date, closing_date, organization
FROM tenders
ORDER BY open_date DESC
LIMIT 10;

-- Amended tenders
SELECT COUNT(*) FROM tenders WHERE is_amended = 1;

-- Tenders by organization
SELECT organization, COUNT(*) as count
FROM tenders
WHERE organization IS NOT NULL
GROUP BY organization
ORDER BY count DESC
LIMIT 10;

-- Open tenders (closing in future)
SELECT title, closing_date, organization
FROM tenders
WHERE closing_date > CURDATE()
ORDER BY closing_date;
```

### Next Steps

Run the full scraper to collect all records:
```bash
python scrape_tenders.py
```

The script will:
1. Fetch data page by page
2. Parse and insert into database
3. Show progress every 10 pages
4. Stop when 240,000 records reached or no more data available
5. Can be interrupted (Ctrl+C) and resumed later