import sys
import os
import re
import time

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
from playwright.sync_api import sync_playwright

SOURCE     = 'cerpp'
BATCH_SIZE = 50
PAGE_WAIT  = 3


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


def fetch_pending(cursor, limit=BATCH_SIZE):
    cursor.execute(
        """SELECT id, source_id, url FROM tenders
           WHERE detail = 0 AND source = %s
           ORDER BY id ASC
           LIMIT %s""",
        (SOURCE, limit)
    )
    return cursor.fetchall()


def scrape_page(page, url):
    page.goto(url, wait_until='networkidle', timeout=60000)
    time.sleep(PAGE_WAIT)

    data = {}

    # ── Deadline (Η/νία Λήξης) ──────────────────────────────────────────────
    deadline_input = page.query_selector(
        'label:has-text("Η/νία Λήξης") ~ div input[type="date"]'
    )
    if not deadline_input:
        # fallback: find label then sibling input
        label = page.query_selector('label:has-text("Η/νία Λήξης")')
        if label:
            deadline_input = label.evaluate_handle(
                'el => el.closest("div").querySelector("input[type=\'date\']")'
            )
    if deadline_input:
        val = deadline_input.get_attribute('value')
        data['deadline'] = val if val else None
    else:
        data['deadline'] = None

    # ── Print-description: extract fields from text content ──────────────────
    pd_el = page.query_selector('.print-description')
    if pd_el:
        pd_html = pd_el.inner_html()
        # Strip tags to get plain text for regex matching
        pd_text = re.sub(r'<[^>]+>', ' ', pd_html)
        pd_text = re.sub(r'&amp;', '&', pd_text)
        pd_text = re.sub(r'&nbsp;', ' ', pd_text)
        pd_text = re.sub(r'\s+', ' ', pd_text)
    else:
        pd_text = ''

    def _grep(pattern, text=pd_text, group=1):
        m = re.search(pattern, text, re.IGNORECASE | re.MULTILINE)
        if not m:
            return None
        try:
            return m.group(group).strip() or None
        except IndexError:
            return m.group(0).strip() or None

    # CPV code — e.g. "CPV: 38434540-3", "Κωδικός/περιγραφή CPV: 38434540-3", "Κωδικός CPV: 38434540-3"
    data['cpv_code'] = (
        _grep(r'Κωδικός\s+CPV\s*:\s*(\d{8}(?:-\d)?)')
        or _grep(r'Κωδικός[/\s]περιγραφή\s+CPV\s*:\s*(\d{8}(?:-\d)?)')
        or _grep(r'CPV[:\s/περιγραφή]*\s*:\s*(\d{8}(?:-\d)?)')
        or _grep(r'CPV[:\s]+(\d{8}(?:-\d)?)')
        or _grep(r'(\d{8}-\d)')
    )

    # Country — ΕΛΛΗΝΙΚΗ ΔΗΜΟΚΡΑΤΙΑ = Greece
    if _grep(r'ΕΛΛΗΝΙΚΗ\s+ΔΗΜΟΚΡΑΤΙΑ') or _grep(r'Χώρα\s*:\s*(Ελλάδα|Greece|GR)'):
        country = 'Greece'
    else:
        country = _grep(r'Χώρα\s*:\s*(.+)') or 'Greece'
    data['contracting_org_country'] = country
    data['country']                 = country

    # Authority name — multiple patterns, first match wins
    data['contracting_authority_name'] = (
        # Explicit label (most reliable) — stop at next field keyword
        _grep(r'Αναθέτουσα\s+[αΑ]ρχή\s*:\s*(.+?)(?=\s*(?:Αντικείμενο|Κωδικός|Τρόπος|Διαδικτυακός|Ταχ\.|Τηλ|[Εe]-?mail|$))')
        or _grep(r'Αναθέτουσα\s+[αΑ]ρχή\s*[:\-→]?\s*((?:[Α-Ωα-ωΆ-ώ][Α-Ωα-ωΆ-ώ\s]+){2,}(?:\([^)]+\))?)')
        or _grep(r'ΕΛΛΗΝΙΚΗ\s+ΔΗΜΟΚΡΑΤΙΑ[^\n]*\n+([^\n]+)')
        # Mixed-case hospital/org names with optional abbreviation in parens
        or _grep(r'((?:Πανεπιστημιακό|Γενικό|Ειδικό|Περιφερειακό)\s+[Α-Ωα-ωΆ-ώ\s]+(?:\([Α-ΩΑ-Ω]+\))?)')
        or _grep(r'((?:Νοσοκομείο|Νοσηλευτικό\s+Ίδρυμα)[Α-Ωα-ωΆ-ώ\s«»\"]+(?:\([Α-ΩΑ-Ω]+\))?)')
        # All-caps Greek org names
        or _grep(r'\b(ΠΕΡΙΦΕΡΕΙΑ\s+[Α-ΩΆΈΉΊΌΎΏ\s]+)')
        or _grep(r'\b(ΔΗΜΟΣ\s+[Α-ΩΆΈΉΊΌΎΏ\s]+)')
        or _grep(r'\b(ΝΟΣΟΚΟΜΕΙΟ\s+[Α-ΩΆΈΉΊΌΎΏ\s]+)')
        or _grep(r'\b(ΥΠΟΥΡΓΕΙΟ\s+[Α-ΩΆΈΉΊΌΎΏ\s]+)')
        or _grep(r'\b(ΠΑΝΕΠΙΣΤΗΜΙΟ\s+[Α-ΩΆΈΉΊΌΎΏ\s]+)')
        or _grep(r'\b(ΓΕΝΙΚΟ\s+ΝΟΣΟΚΟΜΕΙΟ\s+[Α-ΩΆΈΉΊΌΎΏ\s«»]+)')
        # Dotted abbreviation followed by full name in parens e.g. ΠΑ.Γ.Ν.Η. (Πανεπιστημιακό...)
        or _grep(r'([Α-Ω]{1,4}(?:\.[Α-Ω]{1,4})+\.?\s*\([^)]{5,}\))')
        # Full mixed-case name followed by dotted abbreviation e.g. Πανεπιστημιακό Γενικό Νοσοκομείο Ηρακλείου (ΠΑΓΝΗ)
        or _grep(r'((?:[Α-Ωα-ωΆ-ώ]+\s+){2,}(?:Νοσοκομείο|Κέντρο|Ίδρυμα)[Α-Ωα-ωΆ-ώ\s]+\([Α-Ω.]+\))')
    )

    # Address — Ταχ. Δ/νση or Ταχ.Δ/νση or inline Greek address pattern
    street   = (
        _grep(r'Ταχ\.?\s*Δ[/.]?νση\s*[:\-]\s*(.+?)(?=\s*(?:Ταχ\.|Τηλ|Tηλ|[ΕE]-?mail|Πληροφορίες|$))')
        or _grep(r'Ταχ\.?\s*Δ[/.]?νση\s*[:\-]\s*([^Τ]+?)(?=\s*Τ[ηη]λ)')
        or _grep(r'Ταχ\.?\s*Δ[/.]?νση\s*[:\-]\s*(.+)')
    )
    postcode = (
        _grep(r'Ταχ\.?\s*Κώδικας?\s*[:\-]\s*(\d{5})')
        or _grep(r',\s*(\d{5})\b')           # e.g. "Θεσσαλονίκη, 54627"
        or _grep(r'\b(\d{5})\s+[Α-Ω]')      # postcode before city name
    )
    data['contracting_authority_address'] = (
        f'{street}, {postcode}' if street and postcode
        else street or postcode
        or _grep(r'Διεύθυνση\s*:\s*(.+)')
    )
    data['contracting_org_address'] = data['contracting_authority_address']

    # Email — Ε-mail / e-mail / email
    data['contracting_authority_email'] = (
        _grep(r'[ΕE]-?mail\s*[:\-]\s*([\w._%+\-]+@[\w.\-]+\.[a-zA-Z]{2,})')
        or _grep(r'([\w._%+\-]+@[\w.\-]+\.[a-zA-Z]{2,})')
    )

    # Language — default EL for Greek platform
    data['language'] = _grep(r'Γλώσσα\s*:\s*(.+)') or 'EL'

    # ── Attachments ─────────────────────────────────────────────────────────
    attachments = page.eval_on_selector_all(
        'a[href*="/deliberationWebApi/file/"]',
        """links => links.map(a => ({
            url  : a.href,
            name : a.closest('tr')
                     ? (a.closest('tr').querySelector('td:first-child') || a).innerText.trim()
                     : a.innerText.trim() || a.href
        }))"""
    )
    data['attachments'] = attachments

    return data


def save_data(cursor, tender_id, data):
    deadline = data.get('deadline') or None

    # Update closing_date in tenders
    if deadline:
        cursor.execute(
            "UPDATE tenders SET closing_date = %s, updated_at = NOW() WHERE id = %s",
            (deadline, tender_id)
        )

    # Upsert tender_details (deadline + all print-description fields)
    td_fields = {
        'deadline':                      deadline,
        'cpv_code':                      data.get('cpv_code'),
        'languages':                     data.get('language'),
        'contracting_org_country':       data.get('contracting_org_country'),
        'contracting_org_address':       data.get('contracting_org_address'),
        'contracting_authority_name':    data.get('contracting_authority_name'),
        'contract_authority_address':    data.get('contracting_authority_address'),
        'contracting_authority_email':   data.get('contracting_authority_email'),
        'contact_email':                 data.get('contracting_authority_email'),
    }
    set_clause = ', '.join(f'{k} = %s' for k in td_fields)
    values     = list(td_fields.values())

    cursor.execute(
        "SELECT id FROM tender_details WHERE tender_id = %s LIMIT 1", (tender_id,)
    )
    if cursor.fetchone():
        cursor.execute(
            f"UPDATE tender_details SET {set_clause}, updated_at = NOW() WHERE tender_id = %s",
            values + [tender_id]
        )
    else:
        cols = ', '.join(td_fields.keys())
        placeholders = ', '.join(['%s'] * len(td_fields))
        cursor.execute(
            f"INSERT INTO tender_details (tender_id, {cols}, created_at, updated_at) VALUES (%s, {placeholders}, NOW(), NOW())",
            [tender_id] + values
        )

    # Update contracting_authority_name / country in tenders if present
    if data.get('contracting_authority_name'):
        cursor.execute(
            "UPDATE tenders SET organization = COALESCE(NULLIF(organization,''), %s), updated_at = NOW() WHERE id = %s",
            (data['contracting_authority_name'], tender_id)
        )

    # Save attachments
    for att in data.get('attachments', []):
        doc_url  = att.get('url', '')
        doc_name = att.get('name', '') or doc_url
        if not doc_url:
            continue
        cursor.execute(
            "SELECT id FROM tender_documents WHERE tender_id = %s AND document_url = %s LIMIT 1",
            (tender_id, doc_url)
        )
        if not cursor.fetchone():
            cursor.execute(
                """INSERT INTO tender_documents (tender_id, document_name, document_url, date_added)
                   VALUES (%s, %s, %s, NOW())""",
                (tender_id, doc_name, doc_url)
            )

    # Mark detail done
    cursor.execute("UPDATE tenders SET detail = 1 WHERE id = %s", (tender_id,))


def run():
    print('=' * 70)
    print('CERPP.EPROCUREMENT.GOV.GR - Detail Scraper')
    print('=' * 70)

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

    cursor.execute(
        "SELECT COUNT(*) FROM tenders WHERE detail = 0 AND source = %s", (SOURCE,)
    )
    total_pending = cursor.fetchone()[0]
    print(f'Pending: {total_pending:,}')
    print('=' * 70)

    total_done   = 0
    total_failed = 0
    processed    = 0
    failed_ids   = set()

    with sync_playwright() as pw:
        browser = pw.chromium.launch(headless=True)
        context = 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 = context.new_page()

        while True:
            batch = fetch_pending(cursor)
            if not batch:
                print('\nNo more pending tenders.')
                break

            for tender_id, source_id, url in batch:
                if tender_id in failed_ids:
                    continue
                processed += 1
                print(f'  [{processed}] id={tender_id}  {url}', end=' ... ', flush=True)

                try:
                    data = scrape_page(page, url)
                    save_data(cursor, tender_id, data)
                    conn.commit()
                    total_done += 1
                    print(
                        f'OK  (deadline={data["deadline"]}'
                        f', cpv={data.get("cpv_code")}'
                        f', country={data.get("country")}'
                        f', attachments={len(data["attachments"])})'
                    )

                except Exception as e:
                    conn.rollback()
                    total_failed += 1
                    failed_ids.add(tender_id)
                    print(f'FAILED: {e}')

                time.sleep(0.5)

            print(f'\n  Progress: {total_done:,} done, {total_failed:,} failed\n')

        browser.close()

    cursor.close()
    conn.close()

    print('\n' + '=' * 70)
    print('Detail Scraping Complete!')
    print('=' * 70)
    print(f'  Done   : {total_done:,}')
    print(f'  Failed : {total_failed:,}')
    print('=' * 70)


if __name__ == '__main__':
    try:
        run()
    except KeyboardInterrupt:
        print('\n\nInterrupted by user.')
    except Exception as e:
        import traceback
        print(f'\nFatal error: {e}')
        traceback.print_exc()