import requests
from bs4 import BeautifulSoup
from datetime import datetime
import time
import sys
import os
from urllib.parse import quote_plus

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

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

SEARCH_BASE = "https://www.portugaltenders.com/tenders/search"
SOURCE      = 'plenders'
MAX_PAGES   = int(os.getenv('PLENDERS_MAX_PAGES', 3))

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',
}


def parse_deadline(date_str):
    try:
        if date_str and date_str.strip():
            return datetime.strptime(date_str.strip(), '%d %b %Y').date()
    except:
        pass
    return None


def fetch_url(url):
    try:
        response = requests.get(url, headers=HEADERS, 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 get_next_page_url(soup):
    pagination = soup.find('ul', class_='pagination')
    if pagination:
        for link in pagination.find_all('a'):
            if link.get_text(strip=True) == 'Next':
                href = link.get('href', '')
                if href:
                    if href.startswith('http'):
                        return href
                    return 'https://www.portugaltenders.com' + href
    return None


def parse_tenders_from_html(html):
    tenders = []
    soup = BeautifulSoup(html, 'html.parser')

    listing_div = soup.find('div', id='tenderlisting')
    if not listing_div:
        return tenders, None

    cards = listing_div.find_all('div', class_='tender-card')

    for card in cards:
        try:
            heading_link = card.find('a')
            title = None
            detail_url = None
            if heading_link:
                detail_url = heading_link.get('href', '')
                heading_p = heading_link.find('p', class_='tender-card-heading')
                if heading_p:
                    title = heading_p.get_text(strip=True)

            ptt_ref_no = None
            deadline = None

            content_div = card.find('div', class_='tender-card-content')
            if content_div:
                paragraphs = content_div.find_all('p')
                for p in paragraphs:
                    text = p.get_text(strip=True)
                    if 'PTT Ref No.' in text:
                        ptt_ref_no = text.split('PTT Ref No.:')[-1].replace('\xa0', '').strip()
                    elif 'Deadline:' in text:
                        deadline_str = text.split('Deadline:')[-1].replace('\xa0', '').strip()
                        deadline = parse_deadline(deadline_str)

            if ptt_ref_no:
                tenders.append({
                    'ptt_ref_no': ptt_ref_no,
                    'title': title,
                    'detail_url': detail_url,
                    'deadline': deadline,
                })
        except Exception as e:
            print(f"  Error parsing card: {e}")
            continue

    next_url = get_next_page_url(soup)
    return tenders, next_url


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

    conn = get_db_connection()
    cursor = conn.cursor()

    inserted = 0
    updated = 0

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

            if row:
                cursor.execute(
                    """UPDATE tenders SET title = %s, url = %s, closing_date = %s, keyword = %s, updated_at = NOW()
                       WHERE id = %s""",
                    (tender['title'], tender['detail_url'], tender['deadline'], keyword, row[0])
                )
                updated += 1
            else:
                cursor.execute(
                    """INSERT INTO tenders (source, source_id, title, reference_number, url, closing_date, keyword, detail, created_at, updated_at)
                       VALUES (%s, NULL, %s, %s, %s, %s, %s, 0, NOW(), NOW())""",
                    (SOURCE, tender['title'], tender['ptt_ref_no'], tender['detail_url'], tender['deadline'], keyword)
                )
                inserted += 1

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

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


def get_current_count():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM tenders WHERE source = %s", (SOURCE,))
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count


def scrape_keyword(keyword):
    first_url = f"{SEARCH_BASE}?q={quote_plus(keyword)}"
    print(f"\n  Keyword: '{keyword}'")
    print(f"  URL: {first_url}")

    current_url = first_url
    page = 1
    kw_fetched = 0
    kw_inserted = 0
    kw_updated = 0
    consecutive_errors = 0

    while current_url:
        print(f"    [Page {page}] Fetching...", end=' ')

        html = fetch_url(current_url)

        if html is None:
            consecutive_errors += 1
            print(f"Failed (Error #{consecutive_errors})")
            if consecutive_errors >= 3:
                print(f"    Stopping keyword after 3 consecutive errors")
                break
            time.sleep(2)
            continue

        tenders, next_url = parse_tenders_from_html(html)

        if not tenders:
            print(f"No tenders found")
            break

        consecutive_errors = 0
        inserted, updated = insert_tenders(tenders, keyword)
        kw_fetched += len(tenders)
        kw_inserted += inserted
        kw_updated += updated

        print(f"Got {len(tenders)} tenders (New: {inserted}, Updated: {updated})")

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

        current_url = next_url
        page += 1
        time.sleep(1)

    return kw_fetched, kw_inserted, kw_updated


def scrape_all_keywords():
    print("=" * 70)
    print("Portugal Tenders - Keyword Scraper")
    print("=" * 70)
    print(f"Keywords to search: {len(KEYWORDS)}")
    print("=" * 70)

    total_fetched = 0
    total_inserted = 0
    total_updated = 0

    tracker_conn = get_db_connection()

    for i, keyword in enumerate(KEYWORDS, 1):
        print(f"\n[{i}/{len(KEYWORDS)}]", end='')
        if is_keyword_done(tracker_conn, keyword, SOURCE):
            print(f"  [SKIP] Already ran today: \"{keyword}\"")
            continue
        fetched, inserted, updated = scrape_keyword(keyword)
        total_fetched += fetched
        total_inserted += inserted
        total_updated += updated
        mark_keyword_done(tracker_conn, keyword, SOURCE)
        print(f"  [SAVED] Marked keyword done: \"{keyword}\"")
        time.sleep(1)

    tracker_conn.close()

    print("\n" + "=" * 70)
    print("Scraping Complete!")
    print("=" * 70)
    print(f"  Keywords processed: {len(KEYWORDS)}")
    print(f"  Total fetched: {total_fetched:,}")
    print(f"  New records: {total_inserted:,}")
    print(f"  Updated records: {total_updated:,}")
    print(f"  Database total: {get_current_count():,}")
    print("=" * 70)


if __name__ == "__main__":
    try:
        scrape_all_keywords()
    except KeyboardInterrupt:
        print(f"\n\nInterrupted. DB total: {get_current_count():,}")
    except Exception as e:
        print(f"\nFatal Error: {e}")
        import traceback
        traceback.print_exc()