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/techSpecs/faces/techSpecs_data.jspx"
SOURCE = 'nepps_techspecs'

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': URL,
}

VIEWPORT_SIZE = 37


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 date_from_year(year_str):
    """Convert a year string to a date: today if current year, else Dec 31 of that year."""
    current_year = datetime.now().year
    try:
        year = int(str(year_str).strip())
        if year == current_year:
            return datetime.now().date()
        else:
            return datetime(year, 12, 31).date()
    except Exception:
        return None


def status_from_year(year_str):
    current_year = datetime.now().year
    try:
        year = int(str(year_str).strip())
        return 'open' if year >= current_year else 'closed'
    except Exception:
        return 'open'


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:
        source_id    = t['source_id']
        closing_date = t.get('closing_date')  # already a date object from date_from_year()
        pub_date     = safe_date_greek(t.get('publication_date'))

        try:
            cursor.execute(
                "SELECT id FROM tenders WHERE source = %s AND source_id = %s LIMIT 1",
                (SOURCE, source_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,
                           deadline = %s, submission_end = %s,
                           publication_date = %s, cpv_code = %s,
                           updated_at = NOW()
                       WHERE tender_id = %s""",
                    (t['authority'], closing_date, closing_date,
                     pub_date, t.get('cpv_code', ''), 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, source_id, t['title'], source_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,
                            deadline, submission_end,
                            publication_date, cpv_code,
                            created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())""",
                    (tender_id, source_id, source_id,
                     t['authority'],
                     closing_date, closing_date,
                     pub_date, t.get('cpv_code', ''))
                )
                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 empty keyword to fetch all records."""
    form_data = {
        'qryId1:oper0': 'CONTAINS', 'qryId1:val00': '',
        '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:resId1={viewportSize=26,rows=50},pc1:_clmCxt={_shown=}}',
        'event': 'pc1:resId1,qryId1',
        'event.pc1:resId1': '<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:resId1,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': 'CONTAINS', 'qryId1:val00': '',
        '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:resId1={{first={first},scrollTopRowKey|p={row_key},viewportSize={VIEWPORT_SIZE}}}}}',
        'event': 'pc1:resId1',
        'event.pc1:resId1': fetch_event,
        'oracle.adf.view.rich.PROCESS': 'pc1:resId1',
    }
    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)

    # TechSpecs uses pc1:resId1 instead of pc1:t1
    table_match = re.search(
        r'<update id="pc1:resId1"><!\[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

        first_link = cells[0].find('a')
        source_id = first_link.get_text(strip=True) if first_link else cells[0].get_text(strip=True)
        if not source_id or not source_id.strip():
            source_id = str(afrrk)

        title     = cells[1].get_text(strip=True) if len(cells) > 1 else ''
        authority = cells[2].get_text(strip=True) if len(cells) > 2 else ''
        cpv_code  = cells[3].get_text(strip=True) if len(cells) > 3 else ''
        pub_date  = cells[4].get_text(strip=True) if len(cells) > 4 else ''
        year_str  = cells[6].get_text(strip=True) if len(cells) > 6 else ''
        closing   = date_from_year(year_str)
        status    = status_from_year(year_str)

        url = f"{URL}?sourceId={source_id}"

        tenders.append({
            'source_id': source_id,
            'url': url,
            'title': title,
            'authority': authority,
            'cpv_code': cpv_code,
            'publication_date': pub_date,
            'closing_date': closing,
            'status': status,
        })

    return tenders, new_view_state, row_count, start_row, row_keys


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


def scrape_tenders():
    print("=" * 70)
    print("NEPPS TechSpecs (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['source_id'] not in seen_ids]
    for t in new_tenders:
        seen_ids.add(t['source_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['source_id'] not in seen_ids]
        for t in new_tenders:
            seen_ids.add(t['source_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()