import requests
from bs4 import BeautifulSoup
import re
from datetime import datetime
import time
import sys
import os
from urllib.parse import urljoin, urlparse, parse_qsl, urlencode, urlunparse

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))
# 0 means unlimited pages
MAX_PAGES = int(os.getenv('CANADABUYS_MAX_PAGES', 0))
ITEMS_PER_PAGE = int(os.getenv('CANADABUYS_ITEMS_PER_PAGE', 200))


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 merge_next_page_url(current_url, href):
    """Build next page URL while preserving current filters like words/status."""
    if not href:
        return None

    if href.startswith('http'):
        return href

    if href.startswith('?'):
        current_parts = urlparse(current_url)
        next_parts = urlparse(href)

        merged_query = dict(parse_qsl(current_parts.query, keep_blank_values=True))
        merged_query.update(dict(parse_qsl(next_parts.query, keep_blank_values=True)))

        return urlunparse((
            current_parts.scheme,
            current_parts.netloc,
            current_parts.path,
            current_parts.params,
            urlencode(merged_query, doseq=True),
            next_parts.fragment,
        ))

    return urljoin(BASE_URL, href)


def parse_tenders_from_page(html, current_url):
    """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:
            next_url = merge_next_page_url(current_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 build_search_url(keyword=None, filtered=True):
    """Build CanadaBuys search URL."""
    params = [
        ('search_filter', ''),
        ('record_per_page', str(ITEMS_PER_PAGE)),
        ('current_tab', 't'),
    ]

    if filtered:
        params.append(('status[87]', '87'))

    if keyword is not None:
        params.append(('words', keyword))

    return f"{SEARCH_URL}?{urlencode(params)}"


def fetch_total_count_for_url(url, session):
    """Fetch only total count for a search URL."""
    html = fetch_page(url, session)
    if not html:
        return None
    return get_total_count(html)


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 = build_search_url(keyword=keyword, filtered=True)

    page = 1
    total_inserted = 0
    total_updated = 0

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

        print(f"\n[Page {page}]")
        print("FULL URL:")
        print(url)
        print("-" * 100)

        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, url)

        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 MAX_PAGES > 0 and 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)

    # print("Counting source totals...", flush=True)
    # total_without_filter = fetch_total_count_for_url(
    #     build_search_url(keyword=None, filtered=False),
    #     session
    # )
    # total_with_status_filter = fetch_total_count_for_url(
    #     build_search_url(keyword=None, filtered=True),
    #     session
    # )

    # if total_without_filter is not None:
    #     print(f"  Total source data without filter: {total_without_filter:,}", flush=True)
    # else:
    #     print("  Total source data without filter: unavailable", flush=True)

    # if total_with_status_filter is not None:
    #     print(f"  Total source data with filter: {total_with_status_filter:,}", flush=True)
    # else:
    #     print("  Total source data with filter: unavailable", flush=True)

    # print("=" * 80, flush=True)

    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!")
    # if total_without_filter is not None:
    #     print(f"  Total source data without filter: {total_without_filter:,}")
    # else:
    #     print("  Total source data without filter: unavailable")

    # if total_with_status_filter is not None:
    #     print(f"  Total source data with filter: {total_with_status_filter:,}")
    # else:
    #     print("  Total source data with filter: unavailable")

    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()