import requests
from bs4 import BeautifulSoup
import re
import time
import sys
import os
import random
import string
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

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

URL    = "https://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspx"
SOURCE = 'nepps'

HEADERS = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Accept-Language': 'el-GR,el;q=0.9,en;q=0.8',
}

AJAX_HEADERS = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36',
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
    'Adf-Rich-Message': 'true',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Origin': 'https://nepps-search.eprocurement.gov.gr',
    'Referer': 'https://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspx',
}

VIEWPORT_SIZE = 37


def safe_date_greek(val):
    """Parse Greek datetime format: 'D/M/YYYY H:MM:SS μμ/πμ'"""
    if not val or not str(val).strip():
        return None
    try:
        date_part = str(val).strip().split(' ')[0]
        return datetime.strptime(date_part, '%d/%m/%Y').date()
    except Exception:
        return None


def insert_tenders(tenders):
    if not tenders:
        return 0, 0

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

    inserted = 0
    updated  = 0

    for t in tenders:
        procedure_id = t['procedure_id']
        closing_date = safe_date_greek(t.get('submission_end'))
        pub_date     = safe_date_greek(t.get('publication_date'))
        sub_start    = safe_date_greek(t.get('submission_start'))

        try:
            cursor.execute(
                "SELECT id FROM tenders WHERE source = %s AND source_id = %s LIMIT 1",
                (SOURCE, procedure_id)
            )
            row = cursor.fetchone()

            if row:
                tender_id = row[0]
                cursor.execute(
                    """UPDATE tenders SET title = %s, organization = %s, url = %s,
                       status = %s, closing_date = %s, updated_at = NOW() WHERE id = %s""",
                    (t['title'], t['authority'], t['url'], t['status'], closing_date, tender_id)
                )
                cursor.execute(
                    """UPDATE tender_details SET
                           contracting_authority_name = %s, budget = %s,
                           deadline = %s, submission_start = %s, submission_end = %s,
                           publication_date = %s, cpv_code = %s, cpv_description = %s,
                           delivery_place = %s, place_of_performance = %s,
                           award_amount = %s, updated_at = NOW()
                       WHERE tender_id = %s""",
                    (t['authority'], t['budget'],
                     closing_date, sub_start, closing_date,
                     pub_date, t['cpv_code'], t['cpv_description'],
                     t['delivery_place'], t['delivery_place'],
                     t['award_amount'], tender_id)
                )
                updated += 1
            else:
                cursor.execute(
                    """INSERT INTO tenders
                           (source, source_id, title, reference_number, url, organization,
                            status, closing_date, detail, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 0, NOW(), NOW())""",
                    (SOURCE, procedure_id, t['title'], procedure_id, t['url'],
                     t['authority'], t['status'], closing_date)
                )
                tender_id = cursor.lastrowid
                cursor.execute(
                    """INSERT INTO tender_details
                           (tender_id, procedure_id, reference_number,
                            contracting_authority_name, budget,
                            deadline, submission_start, submission_end,
                            publication_date, cpv_code, cpv_description,
                            delivery_place, place_of_performance,
                            award_amount, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())""",
                    (tender_id, procedure_id, procedure_id,
                     t['authority'], t['budget'],
                     closing_date, sub_start, closing_date,
                     pub_date, t['cpv_code'], t['cpv_description'],
                     t['delivery_place'], t['delivery_place'],
                     t['award_amount'])
                )
                inserted += 1

        except Exception as e:
            print(f"  DB Error: {e}")
            continue

    conn.commit()
    cursor.close()
    conn.close()
    return inserted, updated


def get_current_count():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM tenders WHERE source = %s", (SOURCE,))
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count


def get_initial_state(session):
    """Handle ADF loopback flow and extract ViewState + Adf-Window-Id."""
    resp = session.get(URL, headers=HEADERS, timeout=30)
    resp.raise_for_status()
    text = resp.text

    afr_loop = None
    m = re.search(r"'_afrLoop',\s*'(\d+)'", text)
    if m:
        afr_loop = m.group(1)

    jsessionid = None
    m = re.search(r";jsessionid=([^'\"]+)'", text)
    if m:
        jsessionid = m.group(1)

    window_id = 'w' + ''.join(random.choices(string.ascii_lowercase + string.digits, k=10))

    loopback_params = {
        '_afrLoop': afr_loop or '1',
        '_afrWindowMode': '0',
        'Adf-Window-Id': window_id,
        '_afrFS': '16',
        '_afrMT': 'screen',
        '_afrMFW': '1920',
        '_afrMFH': '1080',
        '_afrMFDW': '1920',
        '_afrMFDH': '1080',
        '_afrMFC': '24',
        '_afrMFCI': '0',
        '_afrMFM': '0',
        '_afrMFR': '96',
        '_afrMFG': '0',
        '_afrMFS': '0',
        '_afrMFO': '0',
    }

    loopback_url = URL
    if jsessionid:
        loopback_url = URL + ';jsessionid=' + jsessionid

    resp = session.get(loopback_url, params=loopback_params, headers=HEADERS, timeout=30)
    resp.raise_for_status()
    text = resp.text

    if 'AdfLoopbackUtils.runLoopback' in text:
        m = re.search(r"'_afrLoop',\s*'(\d+)'", text)
        if m:
            afr_loop = m.group(1)
        m = re.search(r";jsessionid=([^'\"]+)'", text)
        if m:
            jsessionid = m.group(1)
            loopback_url = URL + ';jsessionid=' + jsessionid

        loopback_params['_afrLoop'] = afr_loop or '2'
        resp = session.get(loopback_url, params=loopback_params, headers=HEADERS, timeout=30)
        resp.raise_for_status()
        text = resp.text

    view_state = None
    m = re.search(r'name="javax\.faces\.ViewState"\s+value="([^"]+)"', text)
    if m:
        view_state = m.group(1)
    if not view_state:
        m = re.search(r'javax\.faces\.ViewState["\s>]*value="([^"]+)"', text)
        if m:
            view_state = m.group(1)
    if not view_state:
        m = re.search(r'ViewState.*?(?:value|CDATA)\W+([^\s"<>]+)', text)
        if m:
            view_state = m.group(1)

    form_match = re.search(r'<form[^>]+id="([^"]+)"', text)
    form_name = form_match.group(1) if form_match else 'f1'

    print(f"  Window-Id: {window_id}")
    print(f"  ViewState: {view_state}")
    print(f"  Form: {form_name}")
    print(f"  Page length: {len(text)} chars")

    if 'ViewState' not in text and view_state is None:
        print("  WARNING: Page does not contain ViewState — dumping snippet:")
        print(text[:1000])

    return view_state, window_id


def post_search(session, view_state, window_id):
    """POST the search form to trigger query and get initial results."""
    form_data = {
        'socLang': '0',
        'qryId1:oper0': '=',
        'qryId1:val00': '1',
        'qryId1:oper1': '=',
        'qryId1:val10': '',
        'qryId1:oper2': '=',
        'qryId1:val20': '',
        'qryId1:oper3': '=',
        'qryId1:val30': '',
        'qryId1:oper4': '=',
        'qryId1:val40': '',
        'qryId1:oper5': '=',
        'qryId1:val50': '',
        'qryId1:oper6': 'CONTAINS',
        'qryId1:val60': '',
        'qryId1:oper7': 'CONTAINS',
        'qryId1:val70': '',
        'org.apache.myfaces.trinidad.faces.FORM': 'f1',
        'Adf-Window-Id': window_id,
        'Adf-Page-Id': '0',
        'javax.faces.ViewState': view_state,
        'oracle.adf.view.rich.DELTAS': '{pc1:t1={viewportSize=26,rows=50},pc1:_clmCxt={_shown=}}',
        'event': 'pc1:t1,qryId1',
        'event.pc1:t1': '<m xmlns="http://oracle.com/richClient/comm"><k v="operation"><s>RESULT_COMPONENT_DECODE</s></k><k v="type"><s>queryInternal</s></k></m>',
        'event.qryId1': '<m xmlns="http://oracle.com/richClient/comm"><k v="clearAll"/><k v="type"><s>query</s></k></m>',
        'oracle.adf.view.rich.PROCESS': 'pc1:t1,qryId1',
    }

    post_url = f"{URL}?Adf-Window-Id={window_id}&Adf-Page-Id=0"
    resp = session.post(post_url, data=form_data, headers=AJAX_HEADERS, timeout=60)
    return resp.text


def post_fetch(session, view_state, window_id, first, row_key, fetch_id):
    fetch_event = (
        f'<m xmlns="http://oracle.com/richClient/comm">'
        f'<k v="id"><n>{fetch_id}</n></k>'
        f'<k v="subtype"><n>2</n></k>'
        f'<k v="index"><n>{first}</n></k>'
        f'<k v="clientKey"><s>{row_key}</s></k>'
        f'<k v="renderOnly"><b>1</b></k>'
        f'<k v="suppressMessageClear"><s>true</s></k>'
        f'<k v="type"><s>fetch</s></k>'
        f'</m>'
    )

    form_data = {
        'socLang': '0',
        'qryId1:oper0': '=',
        'qryId1:val00': '1',
        'qryId1:oper1': '=',
        'qryId1:val10': '',
        'qryId1:oper2': '=',
        'qryId1:val20': '',
        'qryId1:oper3': '=',
        'qryId1:val30': '',
        'qryId1:oper4': '=',
        'qryId1:val40': '',
        'qryId1:oper5': '=',
        'qryId1:val50': '',
        'qryId1:oper6': 'CONTAINS',
        'qryId1:val60': '',
        'qryId1:oper7': 'CONTAINS',
        'qryId1:val70': '',
        'org.apache.myfaces.trinidad.faces.FORM': 'f1',
        'Adf-Window-Id': window_id,
        'Adf-Page-Id': '0',
        'javax.faces.ViewState': view_state,
        'oracle.adf.view.rich.DELTAS': f'{{pc1:t1={{first={first},scrollTopRowKey|p={row_key},viewportSize={VIEWPORT_SIZE}}}}}',
        'event': 'pc1:t1',
        'event.pc1:t1': fetch_event,
        'oracle.adf.view.rich.PROCESS': 'pc1:t1',
    }

    post_url = f"{URL}?Adf-Window-Id={window_id}&Adf-Page-Id=0"
    resp = session.post(post_url, data=form_data, headers=AJAX_HEADERS, timeout=60)
    return resp.text


def parse_adf_response(xml_text):
    tenders = []
    new_view_state = None
    row_count = 0
    start_row = 0
    row_keys = []

    vs_match = re.search(
        r'<update id="javax\.faces\.ViewState"><!\[CDATA\[(.*?)\]\]></update>',
        xml_text
    )
    if vs_match:
        new_view_state = vs_match.group(1)

    table_match = re.search(
        r'<update id="pc1:t1"><!\[CDATA\[(.*?)\]\]></update>',
        xml_text,
        re.DOTALL
    )
    if not table_match:
        return tenders, new_view_state, row_count, start_row, row_keys

    table_html = table_match.group(1)

    rc = re.search(r'_rowCount="(\d+)"', table_html)
    if rc:
        row_count = int(rc.group(1))

    sr = re.search(r'_startRow="(\d+)"', table_html)
    if sr:
        start_row = int(sr.group(1))

    soup = BeautifulSoup(table_html, 'html.parser')
    rows = soup.find_all('tr', attrs={'role': 'row'})

    for row in rows:
        afrrk = row.get('_afrrk')
        if afrrk is None:
            continue

        row_keys.append(afrrk)

        cells = row.find_all('td', role='gridcell')
        if len(cells) < 12:
            continue

        proc_link = cells[0].find('a')
        procedure_id = proc_link.get_text(strip=True) if proc_link else cells[0].get_text(strip=True)

        if not procedure_id or not procedure_id.strip():
            continue

        title = cells[1].get_text(strip=True)

        budget_span = cells[3].find('span', id=re.compile(r'::content$'))
        budget = budget_span.get_text(strip=True) if budget_span else cells[3].get_text(strip=True)

        publication_date = cells[4].get_text(strip=True)
        submission_start = cells[5].get_text(strip=True)
        submission_end   = cells[6].get_text(strip=True)
        cpv_code         = cells[7].get_text(strip=True)
        authority        = cells[9].get_text(strip=True)
        status           = cells[11].get_text(strip=True)

        cpv_description = cells[8].get_text(strip=True) if len(cells) > 8 else ''
        delivery_place  = cells[10].get_text(strip=True) if len(cells) > 10 else ''

        award_span   = cells[12].find('span', id=re.compile(r'::content$')) if len(cells) > 12 else None
        award_amount = award_span.get_text(strip=True) if award_span else (cells[12].get_text(strip=True) if len(cells) > 12 else '')

        url = f"https://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspx?procedureId={procedure_id}" if procedure_id else ''

        tenders.append({
            'procedure_id': procedure_id,
            'url': url,
            'title': title,
            'budget': budget,
            'publication_date': publication_date,
            'submission_start': submission_start,
            'submission_end': submission_end,
            'cpv_code': cpv_code,
            'cpv_description': cpv_description,
            'authority': authority,
            'delivery_place': delivery_place,
            'status': status,
            'award_amount': award_amount,
        })

    return tenders, new_view_state, row_count, start_row, row_keys


def scrape_tenders():
    print("=" * 70)
    print("NEPPS (ESIDIS) Greece - AJAX Scraper")
    print("=" * 70)
    print(f"Source: {URL}")
    print("=" * 70)

    session = requests.Session()

    print("\n[1] Getting initial page state...")
    view_state, window_id = get_initial_state(session)

    if not view_state:
        print("ERROR: Could not extract ViewState from page")
        return

    print("\n[2] Posting search query...")
    resp_text = post_search(session, view_state, window_id)

    tenders, new_vs, row_count, start_row, row_keys = parse_adf_response(resp_text)

    if new_vs:
        view_state = new_vs

    total_fetched = 0
    total_inserted = 0
    total_updated = 0
    seen_ids = set()
    page_num = 1

    if not tenders:
        print("ERROR: No tenders in search response")
        print(f"Response preview: {resp_text[:500]}")
        return

    new_tenders = [t for t in tenders if t['procedure_id'] not in seen_ids]
    for t in new_tenders:
        seen_ids.add(t['procedure_id'])

    inserted, updated = insert_tenders(new_tenders)
    total_fetched += len(new_tenders)
    total_inserted += inserted
    total_updated += updated

    print(f"\n[Page {page_num}] Got {len(new_tenders)} tenders (Inserted: {inserted}, Updated: {updated})")
    print(f"  Total rows on server: {row_count} | Start row: {start_row} | Row keys: {row_keys[0]}..{row_keys[-1]}" if row_keys else "")

    current_first = start_row + len(tenders)
    fetch_id = 2

    if row_keys:
        key_offset = int(row_keys[0]) - start_row
    else:
        key_offset = 0

    consecutive_errors = 0

    while current_first < row_count:
        page_num += 1

        next_row_key = current_first + key_offset

        print(f"\n[Page {page_num}] Fetching from row {current_first} (key={next_row_key})...", end=' ')

        try:
            resp_text = post_fetch(session, view_state, window_id,
                                   first=current_first,
                                   row_key=next_row_key,
                                   fetch_id=fetch_id)
            fetch_id += 1
        except Exception as e:
            print(f"Request failed: {e}")
            consecutive_errors += 1
            if consecutive_errors >= 3:
                print("Too many errors, stopping.")
                break
            time.sleep(3)
            continue

        tenders, new_vs, rc, sr, rk = parse_adf_response(resp_text)

        if new_vs:
            view_state = new_vs
        if rc > 0:
            row_count = rc

        if rk and sr > 0:
            key_offset = int(rk[0]) - sr

        new_tenders = [t for t in tenders if t['procedure_id'] not in seen_ids]
        for t in new_tenders:
            seen_ids.add(t['procedure_id'])

        if not new_tenders:
            consecutive_errors += 1
            print(f"No new tenders (attempt {consecutive_errors}).")
            if consecutive_errors >= 3:
                print("Stopping after repeated empty pages.")
                break
            current_first += VIEWPORT_SIZE
            time.sleep(2)
            continue

        consecutive_errors = 0

        inserted, updated = insert_tenders(new_tenders)
        total_fetched += len(new_tenders)
        total_inserted += inserted
        total_updated += updated

        print(f"Got {len(new_tenders)} tenders (Inserted: {inserted}, Updated: {updated}) | Total: {len(seen_ids)}/{row_count}")

        current_first = sr + len(tenders) if sr > 0 else current_first + len(tenders)
        time.sleep(1)

    print("\n" + "=" * 70)
    print("Scraping Complete!")
    print("=" * 70)
    print(f"  Pages: {page_num}")
    print(f"  Fetched: {total_fetched:,}")
    print(f"  New: {total_inserted:,}")
    print(f"  Updated: {total_updated:,}")
    print(f"  DB total: {get_current_count():,}")
    print("=" * 70)


if __name__ == "__main__":
    try:
        scrape_tenders()
    except KeyboardInterrupt:
        print(f"\n\nInterrupted.")
    except Exception as e:
        print(f"\nFatal Error: {e}")
        import traceback
        traceback.print_exc()