import sys
import os
import re
import time
from datetime import datetime

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     = 'pwgopendata'
BATCH_SIZE = 50
PAGE_WAIT  = 3
BASE_URL   = 'http://pwgopendata.eprocurement.gov.gr'
DETAIL_URL = BASE_URL + '/actSearchErgwn/resources/search/{}'


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 LIKE %s
           ORDER BY id ASC
           LIMIT %s""",
        ('%pwgo%', limit)
    )
    return cursor.fetchall()


def parse_date(val):
    """Parse various Greek date formats → date"""
    if not val or not str(val).strip():
        return None
    val = str(val).strip().split(' ')[0]
    for fmt in ('%d/%m/%Y', '%d-%m-%Y', '%Y-%m-%d'):
        try:
            return datetime.strptime(val, fmt).date()
        except Exception:
            pass
    return None


def parse_datetime(val):
    """Parse various Greek datetime formats → datetime"""
    if not val or not str(val).strip():
        return None
    val = str(val).strip()
    for fmt in ('%d/%m/%Y %H:%M:%S', '%d-%m-%Y %H:%M:%S', '%d/%m/%Y', '%d-%m-%Y'):
        try:
            return datetime.strptime(val, fmt)
        except Exception:
            pass
    return None


def scrape_detail(page, source_id):
    url = DETAIL_URL.format(source_id)
    page.goto(url, wait_until='networkidle', timeout=60000)
    time.sleep(PAGE_WAIT)

    # ── Extract page text ────────────────────────────────────────────────────
    text = page.inner_text('body')
    # Normalize spaces but keep newlines for field boundary detection
    text = re.sub(r'[ \t]+', ' ', text)

    def get_field(label):
        """Extract value on the same line after a Greek label."""
        m = re.search(
            re.escape(label) + r'\s*:?\s*([^\n]+)',
            text, re.IGNORECASE
        )
        return m.group(1).strip() or None if m else None

    def get_field_multiline(label):
        """Extract full multi-line value after a label, stopping at the next label."""
        m = re.search(
            re.escape(label) + r'\s*:?\s*([\s\S]+?)(?=\n[ \t]*[Α-Ωα-ωΆ-ώA-Za-z/][^\n]*:|\Z)',
            text, re.IGNORECASE
        )
        if m:
            val = re.sub(r'\s+', ' ', m.group(1)).strip()
            return val if val else None
        return None

    data = {
        'short_title':                get_field('Συνοπτικός Τίτλος/Αρ. Διακήρυξης'),
        'cpv_code':                   get_field('Κωδικός CPV'),
        'cpv_description':            get_field('Πρόσθετη περιγραφή ειδών/Υπηρεσιών'),
        'contracting_authority_name': get_field('Αναθέτουσα Αρχή'),
        'delivery_place':             get_field('Τοποθεσίες Έργου'),
        'title':                      get_field('Τίτλος Έργου/Μελέτη'),
        'description':                get_field_multiline('Τίτλος Έργου/Μελέτη'),
        'funding':                    get_field('Χρηματοδοτήσεις'),
        'budget':                     get_field('Συνολικός Προϋπολογισμός (με ΦΠΑ)'),
        'publication_date_raw':       get_field('Ημερομηνία Δημοσίευσης'),
        'submission_end_raw':         get_field('Καταληκτική Ημ/νία Υποβολής Προσφορών'),
        'award_amount':               get_field('Ποσό Κατακύρωσης'),
        'technical_unsealing_raw':    get_field('Ημ/νία Τεχνικής Αποσφράγισης'),
        'financial_unsealing_raw':    get_field('Ημ/νία Οικονομικής Αποσφράγισης'),
        'legal_unsealing_raw':        get_field('Ημ/νία Αποσφράγισης Δικαιολογητικών'),
        'country':                    'Greece',
        'language':                   'EL',
        'documents':                  [],
    }

    # Keep only the numeric CPV code if description is appended
    if data['cpv_code']:
        m = re.match(r'(\d{8}(?:-\d)?)', data['cpv_code'])
        if m:
            data['cpv_code'] = m.group(1)

    # Extract NUTS code from delivery_place e.g. "EL513 - Ροδόπη (Rodopi)"
    nuts_code = None
    if data.get('delivery_place'):
        m = re.match(r'(EL\w+)', data['delivery_place'])
        if m:
            nuts_code = m.group(1)
    data['nuts_code'] = nuts_code

    # ── Documents tab ────────────────────────────────────────────────────────
    try:
        # Use native JS click to properly trigger ADF event listeners
        page.evaluate('document.getElementById("sdi2::disAcr").click()')
        try:
            page.wait_for_function(
                '''() => {
                    const p = document.querySelector('[id="sdi2::body"]');
                    return p && p.querySelectorAll("tr[_afrrk]").length > 0;
                }''',
                timeout=20000
            )
        except Exception:
            pass

        rows = page.locator('[id="sdi2::body"] tr[_afrrk]').all()
        for row in rows:
            cells = row.locator('td[role="gridcell"]').all()
            if len(cells) < 3:
                continue

            doc_type     = cells[0].inner_text().strip()
            doc_category = cells[1].inner_text().strip()
            filename     = cells[2].inner_text().strip()

            # ADF serves files via POST with session — no direct shareable URL exists.
            # Store the tender page URL so users can navigate and download directly.
            doc_url = url

            data['documents'].append({
                'doc_type':     doc_type,
                'doc_category': doc_category,
                'filename':     filename,
                'url':          doc_url,
            })

    except Exception as e:
        print(f'\n    [docs] {e}', end='')

    return data


def save_data(cursor, tender_id, data):
    pub_date     = parse_date(data.get('publication_date_raw'))
    deadline     = parse_date(data.get('submission_end_raw'))
    tech_unseal  = parse_datetime(data.get('technical_unsealing_raw'))
    fin_unseal   = parse_datetime(data.get('financial_unsealing_raw'))
    legal_unseal = parse_datetime(data.get('legal_unsealing_raw'))

    # Parse budget: Greek format "500.000,00" → "500000.00"
    budget_amount = None
    raw_budget = data.get('budget')
    if raw_budget:
        cleaned = re.sub(r'[€$£\s]', '', str(raw_budget))
        cleaned = cleaned.replace('.', '').replace(',', '.')
        try:
            budget_amount = str(float(cleaned))
        except ValueError:
            budget_amount = raw_budget

    # ── Update tenders ───────────────────────────────────────────────────────
    cursor.execute(
        """UPDATE tenders SET
               title        = COALESCE(NULLIF(%s, ''), title),
               short_title  = COALESCE(NULLIF(%s, ''), short_title),
               organization = COALESCE(NULLIF(%s, ''), organization),
               closing_date = COALESCE(%s, closing_date),
               description  = COALESCE(NULLIF(%s, ''), description),
               updated_at   = NOW()
           WHERE id = %s""",
        (
            data.get('title') or '',
            data.get('short_title') or '',
            data.get('contracting_authority_name') or '',
            deadline,
            data.get('description') or '',
            tender_id,
        )
    )

    # ── Upsert tender_details ────────────────────────────────────────────────
    td = {
        'contracting_authority_name': data.get('contracting_authority_name'),
        'budget':                     budget_amount,
        'budget_currency':            'EUR',
        'publication_date':           pub_date,
        'deadline':                   deadline,
        'submission_end':             deadline,
        'cpv_code':                   data.get('cpv_code'),
        'cpv_description':            data.get('cpv_description'),
        'delivery_place':             data.get('delivery_place'),
        'place_of_performance':       data.get('delivery_place'),
        'award_amount':               data.get('award_amount') or None,
        'funding':                    data.get('funding'),
        'technical_unsealing_date':   tech_unseal,
        'financial_unsealing_date':   fin_unseal,
        'legal_unsealing_date':       legal_unseal,
        'nuts_place':                 data.get('nuts_code'),
        'languages':                  data.get('language', 'EL'),
        'contracting_org_country':    data.get('country', 'Greece'),
        'summary':                    data.get('description'),
    }

    # Only update fields that have actual values — don't overwrite existing data with None
    td = {k: v for k, v in td.items() if v is not None}

    STRING_FIELDS = {
        'contracting_authority_name', 'budget', 'budget_currency', 'cpv_code',
        'cpv_description', 'delivery_place', 'place_of_performance', 'award_amount',
        'funding', 'nuts_place', 'languages', 'contracting_org_country', 'summary',
    }
    set_clause = ', '.join(
        f"{k} = COALESCE(NULLIF(%s, ''), {k})" if k in STRING_FIELDS else f'{k} = COALESCE(%s, {k})'
        for k in td
    )
    values = list(td.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.keys())
        placeholders = ', '.join(['%s'] * len(td))
        cursor.execute(
            f"INSERT INTO tender_details (tender_id, {cols}, created_at, updated_at)"
            f" VALUES (%s, {placeholders}, NOW(), NOW())",
            [tender_id] + values
        )

    # ── Save documents ───────────────────────────────────────────────────────
    for doc in data.get('documents', []):
        doc_name = doc.get('filename') or doc.get('doc_type') or ''
        doc_url  = doc.get('url', '')
        if not doc_name:
            continue
        cursor.execute(
            "SELECT id FROM tender_documents WHERE tender_id = %s AND document_name = %s LIMIT 1",
            (tender_id, doc_name)
        )
        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('PWGOpenData (Greece) - Detail Scraper')
    print('=' * 70)

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

    cursor.execute(
        "SELECT COUNT(*) FROM tenders WHERE detail = 0 AND source LIKE %s",
        ('%pwgo%',)
    )
    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'
            ),
            accept_downloads=True,
        )
        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}  source_id={source_id}', end=' ... ', flush=True)

                try:
                    data = scrape_detail(page, source_id)
                    save_data(cursor, tender_id, data)
                    conn.commit()
                    total_done += 1
                    print(
                        f'OK  (pub={data.get("publication_date_raw")}'
                        f', deadline={data.get("submission_end_raw")}'
                        f', cpv={data.get("cpv_code")}'
                        f', docs={len(data.get("documents", []))})'
                    )
                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()