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
FETCH_BATCH = 200   # rows loaded from DB per iteration


# ─── 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)
    try:
        await page.wait_for_selector('.custom-react-classes-MuiPaper-root', timeout=10000)
    except Exception:
        pass  # proceed even if selector not found; sections will be empty

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

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

        offset     = 0
        processed  = 0
        limit      = TEST_LIMIT if TEST_LIMIT else FETCH_BATCH

        while True:
            cursor = db.cursor(dictionary=True)
            cursor.execute(
                "SELECT id, url, title FROM tenders WHERE source = 'europa' AND detail = 0 ORDER BY id LIMIT %s OFFSET %s",
                (limit, offset)
            )
            tenders = cursor.fetchall()
            cursor.close()

            if not tenders:
                break

            print(f'Fetched {len(tenders)} tender(s) (offset={offset})…\n')

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

                processed += 1

            offset += len(tenders)

            if TEST_LIMIT:
                break  # TEST_LIMIT already applied as LIMIT — one batch only

        await browser.close()

    db.close()
    print(f'═══ Detail scraping complete — {processed} processed ═══')


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