import requests
from bs4 import BeautifulSoup
from datetime import datetime
import time
import sys
import os
import threading
from concurrent.futures import ThreadPoolExecutor, as_completed

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

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

SOURCE = 'plenders'

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 fetch_detail_page(url, session):
    try:
        response = session.get(url, timeout=30)
        if response.status_code == 200:
            return response.text
        else:
            print(f"    HTTP {response.status_code}")
            return None
    except Exception as e:
        print(f"    Request error: {e}")
        return None


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


def extract_detail_data(html):
    soup = BeautifulSoup(html, 'html.parser')

    data = {
        'country': None,
        'summary': None,
        'financier': None,
        'purchaser_ownership': None,
        'tender_value': None,
        'budget_currency': None,
        'notice_type': None,
        'deadline': None,
        'document_ref_no': None,
    }

    details_div = soup.find('div', class_='datails-inner')
    if not details_div:
        return data

    items = details_div.find_all('li')
    for item in items:
        strong = item.find('strong')
        if not strong:
            continue

        label = strong.get_text(strip=True).rstrip(':').strip()
        strong.extract()
        value = item.get_text(strip=True)

        if not value:
            continue

        if label == 'Country':
            data['country'] = value
        elif label == 'Summary':
            data['summary'] = value
        elif label == 'Financier':
            data['financier'] = value
        elif label == 'Purchaser Ownership':
            data['purchaser_ownership'] = value
        elif label == 'Tender Value':
            parts = value.split(None, 1)
            if len(parts) == 2:
                data['budget_currency'] = parts[0]
                data['tender_value'] = parts[1]
            else:
                data['budget_currency'] = None
                data['tender_value'] = value
        elif label == 'Notice Type':
            data['notice_type'] = value
        elif label == 'Deadline':
            data['deadline'] = parse_deadline(value)
        elif label == 'Document Ref. No.':
            data['document_ref_no'] = value

    return data


def save_detail(tender_id, detail_data, conn):
    try:
        cursor = conn.cursor()

        deadline = detail_data['deadline']
        status = 'Open' if deadline and deadline >= datetime.now().date() else 'Closed'
        language = 'English'

        # Update organization in tenders table
        cursor.execute(
            "UPDATE tenders SET organization = %s, detail = 1, closing_date = %s, status = %s, updated_at = NOW() WHERE id = %s",
            (detail_data['purchaser_ownership'], deadline, status, tender_id)
        )

        # Insert or update tender_details
        cursor.execute(
            "SELECT id FROM tender_details WHERE tender_id = %s LIMIT 1",
            (tender_id,)
        )
        row = cursor.fetchone()

        if row:
            cursor.execute(
                """UPDATE tender_details SET
                       contracting_org_country = %s,
                       summary = %s,
                       financier_type = %s,
                       budget = %s,
                       budget_currency = %s,
                       notice_type = %s,
                       deadline = %s,
                       document_ref_no = %s,
                       languages = %s,
                       updated_at = NOW()
                   WHERE tender_id = %s""",
                (
                    detail_data['country'],
                    detail_data['summary'],
                    detail_data['financier'],
                    detail_data['tender_value'],
                    detail_data['budget_currency'],
                    detail_data['notice_type'],
                    detail_data['deadline'],
                    detail_data['document_ref_no'],
                    language,
                    tender_id,
                )
            )
        else:
            cursor.execute(
                """INSERT INTO tender_details
                       (tender_id, contracting_org_country, summary, financier_type, budget, budget_currency, notice_type, deadline, document_ref_no, languages, created_at, updated_at)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())""",
                (
                    tender_id,
                    detail_data['country'],
                    detail_data['summary'],
                    detail_data['financier'],
                    detail_data['tender_value'],
                    detail_data['budget_currency'],
                    detail_data['notice_type'],
                    detail_data['deadline'],
                    detail_data['document_ref_no'],
                    language,
                )
            )

        conn.commit()
        cursor.close()
        return True

    except Exception as e:
        print(f"    DB error: {e}")
        return False


def process_record(record, session, db_lock, print_lock, counters, total):
    """Fetch, parse and save a single record (runs in thread)."""
    tender_id = record['id']
    url       = record['url']
    title     = record['title']
    ref       = record['reference_number']

    if not url:
        with print_lock:
            counters['errors'] += 1
            counters['done']   += 1
            print(f"  [{counters['done']}/{total}] ✗ {ref} — no URL")
        return

    html = fetch_detail_page(url, session)
    if not html:
        with print_lock:
            counters['errors'] += 1
            counters['done']   += 1
            print(f"  [{counters['done']}/{total}] ✗ {ref} — fetch failed")
        return

    try:
        detail_data  = extract_detail_data(html)
        fields_found = sum(1 for v in detail_data.values() if v)

        conn = get_db_connection()
        try:
            saved = save_detail(tender_id, detail_data, conn)
        finally:
            conn.close()

        with print_lock:
            counters['done'] += 1
            if saved:
                counters['success'] += 1
                print(f"  [{counters['done']}/{total}] ✓ {ref} | {(title or '')[:50]} | fields={fields_found}")
            else:
                counters['errors'] += 1
                print(f"  [{counters['done']}/{total}] ✗ {ref} — save failed")

            if counters['done'] % 50 == 0:
                print(f"\n{'─' * 70}")
                print(f"Progress: {counters['done']}/{total} | Success: {counters['success']} | Errors: {counters['errors']}")
                print(f"{'─' * 70}\n")

    except Exception as e:
        with print_lock:
            counters['errors'] += 1
            counters['done']   += 1
            print(f"  [{counters['done']}/{total}] ✗ {ref} — error: {e}")


def scrape_details(batch_size=None, workers=5):
    print("=" * 70)
    print("Portugal Tenders - Detail Scraper")
    print("=" * 70)

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT COUNT(*) as total FROM tenders WHERE source = %s AND detail = 0", (SOURCE,))
    total = cursor.fetchone()['total']
    print(f"Total records to process: {total:,}")

    query = "SELECT id, url, title, reference_number FROM tenders WHERE source = %s AND detail = 0 ORDER BY id"
    params = [SOURCE]
    if batch_size:
        query += " LIMIT %s"
        params.append(batch_size)

    cursor.execute(query, params)
    records = cursor.fetchall()
    cursor.close()
    conn.close()

    total_records = len(records)
    print(f"Processing {total_records} records with {workers} workers...")
    print("=" * 70)

    session    = requests.Session()
    session.headers.update(HEADERS)
    db_lock    = threading.Lock()
    print_lock = threading.Lock()
    counters   = {'done': 0, 'success': 0, 'errors': 0}

    with ThreadPoolExecutor(max_workers=workers) as executor:
        futures = [
            executor.submit(process_record, record, session, db_lock, print_lock, counters, total_records)
            for record in records
        ]
        for future in as_completed(futures):
            try:
                future.result()
            except Exception as e:
                print(f"  ✗ Unexpected thread error: {e}")

    session.close()

    print("\n" + "=" * 70)
    print("Scraping Complete!")
    print("=" * 70)
    print(f"  Total processed: {total_records}")
    print(f"  Successful: {counters['success']}")
    print(f"  Errors: {counters['errors']}")
    print("=" * 70)


if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser(description='Scrape Portugal Tender details')
    parser.add_argument('--batch-size', type=int, default=None, help='Number of records to process')

    args = parser.parse_args()

    try:
        scrape_details(batch_size=args.batch_size)
    except KeyboardInterrupt:
        print("\n\nInterrupted by user")
    except Exception as e:
        print(f"\nFatal Error: {e}")
        import traceback
        traceback.print_exc()