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


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 AND url LIKE %s AND id = 27385
    #        ORDER BY id ASC
    #        LIMIT %s""",
    #     ('%nepps%', '%/act%', limit)
    # )

    cursor.execute(
    """SELECT id, source_id, url FROM tenders
       WHERE detail = 0 
       AND source LIKE %s 
       AND url LIKE %s
       ORDER BY id ASC
       LIMIT %s""",
    ('%nepps%', '%/act%', 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_page(page, url):
    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

        # old
        #  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

    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('Συνοπτικός Τίτλος'),
        'aa_system':                  get_field('Α/Α Διαγωνιστικής Διαδικασίας'),
        'cpv_code':                   get_field('Κωδικός CPV'),
        'cpv_description':            get_field('Πρόσθετη περιγραφή ειδών/Υπηρεσιών'),
        'contracting_authority_name': get_field('Αναθέτουσα Αρχή/Αναθέτων Φορέας'),
        'place_of_performance':       get_field('Τόπος Παράδοσης'),
        'title':                      get_field('Τίτλος/Αντικείμενο'),
        'description':                get_field_multiline('Τίτλος/Αντικείμενο') or get_field('Πρόσθετη περιγραφή ειδών/Υπηρεσιών'),
        'funding':                    get_field('Χρηματοδοτήσεις'),
        'budget':                     get_field('Προϋπολογισμός (€ χωρίς ΦΠΑ)'),
        'publication_date_raw':       get_field('Ημ/νία Δημοσίευσης στο Portal'),
        'deadline_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)

    # ── Click Συνημμένα Αρχεία 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('deadline_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'))

    # ── 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  = %s, 
               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 '',
            data.get('description'), 
            tender_id,
        )
    )

    # ── Upsert tender_details ────────────────────────────────────────────────
    td = {
        'contracting_authority_name': data.get('contracting_authority_name'),
        'budget':                     data.get('budget'),
        'publication_date':           pub_date,
        'deadline':                   deadline,
        'submission_end':             deadline,
        'cpv_code':                   data.get('cpv_code'),
        'cpv_description':            data.get('cpv_description'),
        'place_of_performance':       data.get('place_of_performance'),
        'delivery_place':             data.get('place_of_performance'),
        'award_amount':               data.get('award_amount'),
        'funding':                    data.get('funding'),
        'technical_unsealing_date':   tech_unseal,
        'financial_unsealing_date':   fin_unseal,
        'legal_unsealing_date':       legal_unseal,
        'languages':                  data.get('language'),
        'contracting_org_country':    data.get('country'),
        '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', 'cpv_code', 'cpv_description',
        'place_of_performance', 'delivery_place', 'award_amount', 'funding',
        '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 ActSearch (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 AND url LIKE %s",
        ('%nepps%', '%/act%')
    )
    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}  {url}', end=' ... ', flush=True)

                try:
                    data = scrape_page(page, url)
                    save_data(cursor, tender_id, data)
                    conn.commit()
                    total_done += 1
                    print(
                        f'OK  (pub={data.get("publication_date_raw")}'
                        f', deadline={data.get("deadline_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()