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://pwgopendata.eprocurement.gov.gr/actSearchErgwn/faces/active_search_main.jspx"
SOURCE = 'pwgopendata'

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://pwgopendata.eprocurement.gov.gr',
    'Referer': URL,
}

VIEWPORT_SIZE = 37


def parse_budget(raw):
    """Returns (amount_str, currency) from raw text like '1.000,00 €' or '€1.000,00'."""
    if not raw or not str(raw).strip():
        return None, None
    raw = str(raw).strip()
    currency_map = {'€': 'EUR', '$': 'USD', '£': 'GBP', '¥': 'JPY'}
    currency = None
    for sym, code in currency_map.items():
        if sym in raw:
            currency = code
            raw = raw.replace(sym, '').strip()
            break
    if not currency:
        m = re.search(r'\b([A-Z]{3})\b', raw)
        if m:
            currency = m.group(1)
            raw = raw.replace(currency, '').strip()
    amount = raw.strip() or None
    return amount, currency or 'EUR'


def safe_date_greek(val):
    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 safe_datetime_greek(val):
    """Parse Greek datetime format: 'DD-MM-YYYY HH:MM:SS'"""
    if not val or not str(val).strip():
        return None
    try:
        return datetime.strptime(str(val).strip(), '%d-%m-%Y %H:%M:%S')
    except Exception:
        try:
            date_part = str(val).strip().split(' ')[0]
            return datetime.strptime(date_part, '%d-%m-%Y')
        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'))
        tech_unseal         = safe_datetime_greek(t.get('technical_unsealing_date'))
        fin_unseal          = safe_datetime_greek(t.get('financial_unsealing_date'))
        legal_unseal        = safe_datetime_greek(t.get('legal_unsealing_date'))
        budget_amount, budget_currency = parse_budget(t.get('budget'))

        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, short_title = %s, organization = %s, url = %s,
                       status = %s, closing_date = %s, updated_at = NOW() WHERE id = %s""",
                    (t['title'], t['short_title'], t['authority'], t['url'], t['status'], closing_date, tender_id)
                )
                cursor.execute(
                    """UPDATE tender_details SET
                           contracting_authority_name = %s, budget = %s, budget_currency = %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, funding = %s, procedure_type = %s,
                           technical_unsealing_date = %s, financial_unsealing_date = %s,
                           legal_unsealing_date = %s, updated_at = NOW()
                       WHERE tender_id = %s""",
                    (t['authority'], budget_amount, budget_currency,
                     closing_date, sub_start, closing_date,
                     pub_date, t['cpv_code'], t['cpv_description'],
                     t['delivery_place'], t['delivery_place'],
                     t['award_amount'], t['funding'], t['procedure_type'],
                     tech_unseal, fin_unseal, legal_unseal, tender_id)
                )
                updated += 1
            else:
                cursor.execute(
                    """INSERT INTO tenders
                           (source, source_id, title, short_title, reference_number, url, organization,
                            status, closing_date, detail, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, 0, NOW(), NOW())""",
                    (SOURCE, procedure_id, t['title'], t['short_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, budget_currency,
                            deadline, submission_start, submission_end,
                            publication_date, cpv_code, cpv_description,
                            delivery_place, place_of_performance,
                            award_amount, funding, procedure_type,
                            technical_unsealing_date, financial_unsealing_date,
                            legal_unsealing_date, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())""",
                    (tender_id, procedure_id, procedure_id,
                     t['authority'], budget_amount, budget_currency,
                     closing_date, sub_start, closing_date,
                     pub_date, t['cpv_code'], t['cpv_description'],
                     t['delivery_place'], t['delivery_place'],
                     t['award_amount'], t['funding'], t['procedure_type'],
                     tech_unseal, fin_unseal, legal_unseal)
                )
                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):
    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 + (';jsessionid=' + jsessionid if jsessionid else '')
    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
    for pattern in [
        r'name="javax\.faces\.ViewState"\s+value="([^"]+)"',
        r'javax\.faces\.ViewState["\s>]*value="([^"]+)"',
        r'ViewState.*?(?:value|CDATA)\W+([^\s"<>]+)',
    ]:
        m = re.search(pattern, text)
        if m:
            view_state = m.group(1)
            break

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

    return view_state, window_id


def post_search(session, view_state, window_id):
    """POST search with no filter to fetch all records."""
    form_data = {
        'qryId1:oper0': '2',   'qryId1:val00': '',
        'qryId1:oper1': '2',   'qryId1:val10': '',
        'qryId1:oper2': '2',   'qryId1:val20': '',
        'qryId1:oper3': '0',   'qryId1:val30': '',
        'qryId1:oper4': '0',   'qryId1:val40': '',
        'qryId1:oper5': '0',   'qryId1:val50': '',
        'qryId1:oper6': '2',   'qryId1:val60': '',
        'qryId1:oper7': '10',  '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}}',
        '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 = {
        'qryId1:oper0': '2',   'qryId1:val00': '',
        'qryId1:oper1': '2',   'qryId1:val10': '',
        'qryId1:oper2': '2',   'qryId1:val20': '',
        'qryId1:oper3': '0',   'qryId1:val30': '',
        'qryId1:oper4': '0',   'qryId1:val40': '',
        'qryId1:oper5': '0',   'qryId1:val50': '',
        'qryId1:oper6': '2',   'qryId1:val60': '',
        'qryId1:oper7': '10',  '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) < 2:
            continue

        # Column order: c2(id), c3(short title), c5(project title), c13(funding),
        #   c11(budget), c9(pub date), c10(sub start), c1(sub end), c7(cpv code),
        #   c4(cpv desc), c8(authority), c6(delivery), c12(status), c14(award), ...
        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

        short_title      = cells[1].get_text(strip=True) if len(cells) > 1 else ''
        title            = cells[2].get_text(strip=True) if len(cells) > 2 else ''
        funding_span     = cells[3].find('span', id=re.compile(r'::content$')) if len(cells) > 3 else None
        funding          = funding_span.get_text(strip=True) if funding_span else (cells[3].get_text(strip=True) if len(cells) > 3 else '')
        budget_span      = cells[4].find('span', id=re.compile(r'::content$')) if len(cells) > 4 else None
        budget           = budget_span.get_text(strip=True) if budget_span else (cells[4].get_text(strip=True) if len(cells) > 4 else '')
        publication_date = cells[5].get_text(strip=True) if len(cells) > 5 else ''
        submission_start = cells[6].get_text(strip=True) if len(cells) > 6 else ''
        submission_end   = cells[7].get_text(strip=True) if len(cells) > 7 else ''
        cpv_code         = cells[8].get_text(strip=True) if len(cells) > 8 else ''
        cpv_description  = cells[9].get_text(strip=True) if len(cells) > 9 else ''
        authority        = cells[10].get_text(strip=True) if len(cells) > 10 else ''
        delivery_place   = cells[11].get_text(strip=True) if len(cells) > 11 else ''
        status           = cells[12].get_text(strip=True) if len(cells) > 12 else ''
        award_span       = cells[13].find('span', id=re.compile(r'::content$')) if len(cells) > 13 else None
        award_amount     = award_span.get_text(strip=True) if award_span else (cells[13].get_text(strip=True) if len(cells) > 13 else '')
        procedure_type            = cells[14].get_text(strip=True) if len(cells) > 14 else ''
        technical_unsealing_date  = cells[15].find('span', title=True).get_text(strip=True) if len(cells) > 15 and cells[15].find('span', title=True) else (cells[15].get_text(strip=True) if len(cells) > 15 else '')
        financial_unsealing_date  = cells[16].find('span', title=True).get_text(strip=True) if len(cells) > 16 and cells[16].find('span', title=True) else (cells[16].get_text(strip=True) if len(cells) > 16 else '')
        legal_unsealing_date      = cells[17].find('span', title=True).get_text(strip=True) if len(cells) > 17 and cells[17].find('span', title=True) else (cells[17].get_text(strip=True) if len(cells) > 17 else '')

        url = f"http://pwgopendata.eprocurement.gov.gr/actSearchErgwn/resources/search/{procedure_id}"

        tenders.append({
            'procedure_id': procedure_id, 'url': url,
            'short_title': short_title, 'title': title, 'funding': funding,
            '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,
            'procedure_type': procedure_type,
            'technical_unsealing_date': technical_unsealing_date,
            'financial_unsealing_date': financial_unsealing_date,
            'legal_unsealing_date': legal_unsealing_date,
        })

    return tenders, new_view_state, row_count, start_row, row_keys


MAX_PAGES = int(os.getenv('PWGOPENDATA_MAX_PAGES', 0)) or None


def scrape_tenders():
    print("=" * 70)
    print("PWGOpenData (Greece) - All Tenders Scraper")
    print("=" * 70)
    print(f"Source: {URL}")
    if MAX_PAGES:
        print(f"Max pages: {MAX_PAGES}")
    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
    key_offset = int(row_keys[0]) - start_row if row_keys else 0
    consecutive_errors = 0

    while current_first < row_count and (not MAX_PAGES or page_num < MAX_PAGES):
        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()