import asyncio
import os
import re
import sys

from dotenv import load_dotenv
import mysql.connector
from playwright.async_api import async_playwright

load_dotenv(os.path.join(os.path.dirname(__file__), '../.env'))

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

# ─── Config ────────────────────────────────────────────────────────────────────
DB_CONFIG = {
    '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'),
}

TEST_LIMIT  = None  # set to a number (e.g. 5) to test with fewer records
PAGE_WAIT_MS = 4000


# ─── Summary field parsers ─────────────────────────────────────────────────────

def _strip_html(html):
    return re.sub(r'<[^>]+>', ' ', html or '') if html else ''


def extract_cpv_code(notice_html):
    if not notice_html:
        return None
    # HTML: ...BT-262-Procedure">Main classification</span>...<span class="data">48000000</span>
    m = re.search(
        r'BT-262-Procedure[^>]*>.*?<span[^>]*class="data"[^>]*>(\d{8})</span>',
        notice_html, re.IGNORECASE | re.DOTALL
    )
    return m.group(1) if m else None


def extract_procurement_method(summary_html):
    if not summary_html:
        return None
    # HTML structure: ...BT-105">Type of procedure</span><span>:&nbsp;</span><span ...>VALUE</span>
    m = re.search(
        r'BT-105[^>]*>[^<]*Type of procedure[^<]*</span>\s*<span>[^<]*</span>\s*<span[^>]*>([^<]+)</span>',
        summary_html, re.IGNORECASE
    )
    return m.group(1).strip() if m else None


def extract_budget(summary_html):
    """Returns (amount_str, currency) e.g. ('893976.22', 'EUR')."""
    if not summary_html:
        return None, None
    # HTML: ...BT-27">Estimated value excluding VAT</span><span>:&nbsp;</span>
    #       <span class="data">893 976,22</span><span>&nbsp;</span><span class="data">EUR</span>
    m = re.search(
        r'BT-27[^>]*>[^<]*Estimated value excluding VAT[^<]*</span>'
        r'\s*<span>[^<]*</span>\s*'
        r'<span[^>]*class="data"[^>]*>([\d\s,\.]+)</span>'
        r'\s*<span>[^<]*</span>\s*'
        r'<span[^>]*class="data"[^>]*>([A-Z]{3})</span>',
        summary_html, re.IGNORECASE
    )
    if m:
        raw = m.group(1).strip().replace(' ', '').replace(',', '.')
        return raw, m.group(2).upper()
    return None, None


# ─── Scrape one tender detail page ─────────────────────────────────────────────
async def scrape_detail(page, url):
    await page.goto(url, wait_until='networkidle', timeout=60000)
    await asyncio.sleep(PAGE_WAIT_MS / 1000)

    result = {'summary': None, 'notice': None, 'languages': None}

    sections = await page.query_selector_all('.custom-react-classes-MuiPaper-root')

    for section in sections:
        h3 = await section.query_selector('h3.custom-react-classes-MuiTypography-root')
        if not h3:
            continue

        heading = (await h3.inner_text()).strip().lower()

        collapse_div = await section.query_selector('.custom-react-classes-MuiCollapse-root')
        if not collapse_div:
            continue

        if 'summary' in heading:
            result['summary'] = (await collapse_div.inner_html()).strip() or None

        elif 'languages' in heading:
            spans = await collapse_div.eval_on_selector_all(
                'span',
                "els => els.map(s => s.innerText.trim()).filter(t => t.length > 0)"
            )
            unique_spans = list(dict.fromkeys(spans))  # deduplicate preserving order
            result['languages'] = ', '.join(unique_spans) if unique_spans else None

        elif 'notice' in heading:
            result['notice'] = (await collapse_div.inner_html()).strip() or None

    return result


# ─── Main ──────────────────────────────────────────────────────────────────────
async def main():
    db = mysql.connector.connect(**DB_CONFIG)
    print('✔  Database connected')

    cursor = db.cursor(dictionary=True)
    limit_clause = f'LIMIT {TEST_LIMIT}' if TEST_LIMIT else ''
    cursor.execute(
        f"SELECT id, url, title FROM tenders WHERE source = 'europa' AND detail = 0 {limit_clause}"
    )
    tenders = cursor.fetchall()
    cursor.close()

    if not tenders:
        print('No pending tenders (detail=0) found.')
        db.close()
        return

    print(f'Processing {len(tenders)} tender(s)…\n')

    async with async_playwright() as p:
        browser = await p.chromium.launch(headless=True)
        context = await browser.new_context(
            user_agent=(
                'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 '
                '(KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36'
            )
        )
        page = await context.new_page()

        for tender in tenders:
            print(f'─── id={tender["id"]}  [{tender["title"]}]')
            print(f'    URL: {tender["url"]}')

            try:
                detail = await scrape_detail(page, tender['url'])

                summary   = detail['summary']
                notice    = detail['notice']
                languages = detail['languages']

                cpv_code           = extract_cpv_code(notice)
                procurement_method = extract_procurement_method(summary)
                budget, currency   = extract_budget(summary)

                print(f'    summary            : {summary[:80] + "…" if summary else "null"}')
                print(f'    notice             : {notice[:80] + "…" if notice else "null"}')
                print(f'    languages          : {languages or "null"}')
                print(f'    cpv_code           : {cpv_code or "null"}')
                print(f'    procurement_method : {procurement_method or "null"}')
                print(f'    budget             : {budget or "null"} {currency or ""}')

                cur = db.cursor()
                cur.execute(
                    '''UPDATE tender_details
                       SET summary         = %s,
                           notice          = %s,
                           languages       = %s,
                           cpv_code        = %s,
                           procedure_type  = %s,
                           budget          = %s,
                           budget_currency = %s
                       WHERE tender_id = %s''',
                    (summary, notice, languages, cpv_code, procurement_method, budget, currency, tender['id'])
                )
                cur.execute(
                    'UPDATE tenders SET detail = 1 WHERE id = %s',
                    (tender['id'],)
                )
                db.commit()
                cur.close()
                print('    ✔  Updated\n')

            except Exception as e:
                print(f'    ✖  Error: {e}\n')

        await browser.close()

    db.close()
    print('═══ Detail scraping complete ═══')


if __name__ == '__main__':
    asyncio.run(main())