import requests
from bs4 import BeautifulSoup
import re
from datetime import datetime
import time
import sys
import os

if sys.platform == 'win32':
    sys.stdout.reconfigure(encoding='utf-8')

from dotenv import load_dotenv
load_dotenv(os.path.join(os.path.dirname(__file__), '../.env'))
import mysql.connector

sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))
from keyword_tracker import is_keyword_done, mark_keyword_done

def get_db_connection():
    return mysql.connector.connect(
        host=os.getenv('DB_HOST'),
        port=int(os.getenv('DB_PORT', 3306)),
        user=os.getenv('DB_USER'),
        password=os.getenv('DB_PASSWORD'),
        database=os.getenv('DB_NAME'),
    )

BASE_URL = "https://canadabuys.canada.ca"
SEARCH_URL = "https://canadabuys.canada.ca/en/tender-opportunities"

HEADERS = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Accept-Language': 'en-US,en;q=0.9',
}

raw_keywords = os.getenv('KEYWORDS', '')
KEYWORDS = [kw.strip() for kw in raw_keywords.split(',') if kw.strip()]

SOURCE    = 'canadabuys'
MAX_PAGES = int(os.getenv('CANADABUYS_MAX_PAGES', 3))


def parse_date(date_str):
    try:
        if date_str and date_str.strip():
            date_match = re.search(r'(\d{4}/\d{2}/\d{2})', date_str)
            if date_match:
                return datetime.strptime(date_match.group(1), '%Y/%m/%d').date()
    except:
        pass
    return None


def extract_tender_id_from_url(url):
    if url:
        parts = url.strip('/').split('/')
        if len(parts) > 0:
            return parts[-1]
    return None


def fetch_page(url, session):
    """Fetch a search result page by URL"""
    try:
        response = session.get(url, timeout=30)
        if response.status_code == 200:
            return response.text
        else:
            print(f"  ✗ HTTP Error {response.status_code}")
            return None
    except requests.RequestException as e:
        print(f"  ✗ Request Error: {e}")
        return None


def parse_tenders_from_page(html):
    """Parse tenders from HTML page, return (tenders, next_page_url)"""
    soup = BeautifulSoup(html, 'html.parser')
    tenders = []

    table = soup.select_one('table.eps-table tbody')
    if not table:
        return tenders, None

    for row in table.find_all('tr'):
        try:
            tds = row.find_all('td')
            if len(tds) < 5:
                continue

            # Title + URL
            link = tds[0].find('a')
            if not link:
                continue
            title = link.get('title') or link.get_text(strip=True)
            url = link.get('href', '')
            if url and not url.startswith('http'):
                url = BASE_URL + url

            # Skip external links (e.g. NATO portal) — can't scrape details
            if url and not url.startswith(BASE_URL):
                continue

            tender_id = extract_tender_id_from_url(url)

            # Category
            category = tds[1].get_text(strip=True)

            # Open date + amended
            open_date_text = tds[2].get_text()
            open_date = parse_date(open_date_text)
            is_amended = 'Amended' if tds[2].find(class_='amended-icon') else ''

            # Closing date
            closing_date = parse_date(tds[3].get_text(strip=True))

            # Organization
            org_span = tds[4].find('span')
            organization = org_span.get_text(strip=True) if org_span else tds[4].get_text(strip=True)

            tenders.append({
                'tender_id': tender_id,
                'title': title,
                'url': url,
                'category': category,
                'open_date': open_date,
                'is_amended': is_amended,
                'closing_date': closing_date,
                'organization': organization,
            })

        except Exception:
            continue

    # Next page URL — "Load more" button with rel="next"
    next_link = soup.find('a', rel='next')
    next_url = None
    if next_link:
        href = next_link.get('href', '')
        if href:
            if href.startswith('http'):
                next_url = href
            elif href.startswith('?'):
                next_url = SEARCH_URL + href
            else:
                next_url = BASE_URL + href

    return tenders, next_url


def get_total_count(html):
    """Extract total result count from page"""
    soup = BeautifulSoup(html, 'html.parser')
    total = soup.select_one('span.search-total-count')
    if total:
        try:
            return int(total.get_text(strip=True).replace(',', ''))
        except:
            pass
    return None


def insert_tenders(tenders, keyword, conn):
    if not tenders:
        return 0, 0

    cursor = conn.cursor()
    inserted = 0
    updated = 0

    for tender in tenders:
        try:
            cursor.execute(
                "SELECT id FROM tenders WHERE source = %s AND (source_id = %s OR reference_number = %s) LIMIT 1",
                (SOURCE, tender['tender_id'], tender['tender_id'])
            )
            row = cursor.fetchone()

            if row:
                tender_db_id = row[0]
                cursor.execute(
                    """UPDATE tenders SET title=%s, organization=%s, closing_date=%s,
                       updated_at=NOW() WHERE id=%s""",
                    (tender['title'], tender['organization'], tender['closing_date'], tender_db_id)
                )
                cursor.execute(
                    """UPDATE tender_details SET category=%s, open_date=%s, is_amended=%s,
                       deadline=%s, publication_date=%s, updated_at=NOW() WHERE tender_id=%s""",
                    (tender['category'], tender['open_date'], tender['is_amended'], tender['closing_date'], tender['open_date'], tender_db_id)
                )
                updated += 1
            else:
                cursor.execute(
                    """INSERT INTO tenders
                       (source, source_id, reference_number, title, url, organization, closing_date, detail, keyword, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, 0, %s, NOW(), NOW())""",
                    (SOURCE, tender['tender_id'], tender['tender_id'], tender['title'], tender['url'],
                     tender['organization'], tender['closing_date'], keyword)
                )
                tender_db_id = cursor.lastrowid
                cursor.execute(
                    """INSERT INTO tender_details
                       (tender_id, category, open_date, is_amended, deadline, publication_date, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())""",
                    (tender_db_id, tender['category'], tender['open_date'], tender['is_amended'], tender['closing_date'], tender['open_date'])
                )
                inserted += 1

        except Exception as e:
            print(f"  ✗ DB Error: {e}")
            continue

    conn.commit()
    cursor.close()
    return inserted, updated


def scrape_keyword(keyword, conn, session):
    """Scrape all pages for a single keyword"""
    print(f"\n{'─' * 80}")
    print(f"Keyword: \"{keyword}\"")
    print(f"{'─' * 80}")

    # Build first page URL
    url = (
        f"{SEARCH_URL}?search_filter=&status%5B87%5D=87"
        f"&record_per_page=50&current_tab=t&words={requests.utils.quote(keyword)}"
    )

    page = 1
    total_inserted = 0
    total_updated = 0

    while url:
        print(f"  [Page {page}] {url[:80]}...")

        html = fetch_page(url, session)
        if not html:
            break

        if page == 1:
            total = get_total_count(html)
            if total is not None:
                print(f"  Total results: {total}")

        tenders, next_url = parse_tenders_from_page(html)

        if not tenders:
            print(f"  No tenders found — end of results")
            break

        inserted, updated = insert_tenders(tenders, keyword, conn)
        total_inserted += inserted
        total_updated += updated
        print(f"  ✓ {len(tenders)} records (New: {inserted}, Updated: {updated})")

        if page >= MAX_PAGES:
            print(f"  Reached max pages ({MAX_PAGES}) — stopping.")
            break

        url = next_url
        page += 1
        time.sleep(0.5)

    print(f"  Done — Inserted: {total_inserted}, Updated: {total_updated}")
    return total_inserted, total_updated


def scrape_all_keywords():
    print("=" * 80)
    print("Canada Buys - Website Keyword Scraper")
    print("=" * 80)
    print(f"Keywords ({len(KEYWORDS)}):")
    for kw in KEYWORDS:
        print(f"  - {kw}")
    print("=" * 80)

    grand_inserted = 0
    grand_updated = 0

    conn = get_db_connection()
    session = requests.Session()
    session.headers.update(HEADERS)

    for i, keyword in enumerate(KEYWORDS, 1):
        print(f"\n[{i}/{len(KEYWORDS)}]", end='')
        if is_keyword_done(conn, keyword, SOURCE):
            print(f"  [SKIP] Already ran today: \"{keyword}\"")
            continue
        ins, upd = scrape_keyword(keyword, conn, session)
        grand_inserted += ins
        grand_updated += upd
        mark_keyword_done(conn, keyword, SOURCE)
        print(f"  [SAVED] Marked keyword done: \"{keyword}\"")
        time.sleep(1)

    session.close()
    conn.close()

    print("\n" + "=" * 80)
    print("All Keywords Done!")
    print(f"  Total Inserted: {grand_inserted}")
    print(f"  Total Updated:  {grand_updated}")
    print("=" * 80)


if __name__ == "__main__":
    try:
        scrape_all_keywords()
    except KeyboardInterrupt:
        print("\n\n✗ Interrupted by user")
    except Exception as e:
        print(f"\n✗ Fatal Error: {e}")
        import traceback
        traceback.print_exc()