import sys
import os
import time
import re
from datetime import datetime
from bs4 import BeautifulSoup

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

from playwright.sync_api import sync_playwright

SOURCE     = 'www.evergabe-online.de'
SITE_ROOT  = 'https://www.evergabe-online.de'
BATCH_SIZE = 50

SCOPE_KEYWORDS = {
    "type and scope of service",
    "type and scope of services",
    "art und umfang",
    "leistungsbeschreibung",
}

DEADLINE_KEYWORDS = {
    "request deadline",
    "angebotsfrist",
    "schlusstermin",
    "submission deadline",
}


# ---------------------------------------------------------------------------
# DB helpers
# ---------------------------------------------------------------------------

def fetch_pending(cursor, limit=BATCH_SIZE):
    cursor.execute(
        """SELECT id, url FROM tenders
           WHERE detail = 0 AND source = %s
           ORDER BY id ASC
           LIMIT %s""",
        (SOURCE, limit)
    )
    return cursor.fetchall()


def compute_status(closing_date):
    if not closing_date:
        return 'Open'
    try:
        deadline = datetime.strptime(closing_date, '%Y-%m-%d').date()
        return 'Open' if deadline >= datetime.today().date() else 'Closed'
    except ValueError:
        return 'Open'


# ---------------------------------------------------------------------------
# Scraping helpers
# ---------------------------------------------------------------------------

def extract_url_id(url):
    """Extract numeric id from URL like tenderdetails.html?id=845946"""
    m = re.search(r'[?&]id=(\d+)', url)
    return m.group(1) if m else None


def scrape_notice(page, url):
    """Visit detail page, return inner HTML of notice div."""
    page.goto(url, wait_until='networkidle', timeout=60000)
    try:
        page.wait_for_selector('div.procedure-details.nat-form, div#htmlDocument', timeout=10000)
    except Exception:
        pass

    div = page.query_selector('div.procedure-details.nat-form')
    if div:
        return div.inner_html()

    div = page.query_selector('div#htmlDocument')
    if div:
        return div.inner_html()

    return None


def parse_versanddatum(raw):
    """Parse '18.03.26, 07:45' or '18.03.2026, 07:45' into 'YYYY-MM-DD', else None."""
    if not raw or raw.strip() == '-':
        return None
    m = re.match(r'(\d{1,2})\.(\d{2})\.(\d{2,4})', raw.strip())
    if not m:
        return None
    day, month, year = m.group(1), m.group(2), m.group(3)
    if len(year) == 2:
        year = '20' + year
    return f'{year}-{month}-{int(day):02d}'


def scrape_documents(page, url_id):
    """Visit documents page, return list of {document_name, document_url, date_added}."""
    docs_url = f'{SITE_ROOT}/tenderdocuments.html?0&id={url_id}'
    page.goto(docs_url, wait_until='networkidle', timeout=60000)
    try:
        page.wait_for_selector('table tbody', timeout=10000)
    except Exception:
        pass

    docs = page.eval_on_selector_all(
        'table tbody tr',
        """trs => trs.map(tr => {
            const a = tr.querySelector('td.filename a.title');
            if (!a) return null;
            const dlBtn = tr.querySelector('td.text-right a.icon[download]');
            const dateEl = tr.querySelector('td span.inquiry-date');
            return {
                document_name : a.getAttribute('title') || a.innerText.trim(),
                document_url  : dlBtn ? dlBtn.href : a.href,
                date_raw      : dateEl ? dateEl.innerText.trim() : null,
            };
        }).filter(r => r !== null)"""
    )

    for doc in docs:
        doc['date_added'] = parse_versanddatum(doc.pop('date_raw', None))

    return docs


# ---------------------------------------------------------------------------
# Chapter helpers (shared by all parse_* functions)
# ---------------------------------------------------------------------------

def _chapter_label(chapter):
    """Return normalised label of a chapter div, e.g. 'f)' or 'd)'."""
    num_div = chapter.select_one("div.chapter-number")
    if not num_div:
        return None
    return num_div.get_text(strip=True).strip(".").strip()


def _chapter_text(chapter):
    """Return plain text of chapter-content with <br> → newline."""
    content_div = chapter.select_one("div.chapter-content")
    if not content_div:
        return ""
    for br in content_div.find_all("br"):
        br.replace_with("\n")
    return content_div.get_text(separator="\n", strip=True)


# ---------------------------------------------------------------------------
# Parse functions
# ---------------------------------------------------------------------------

def parse_cpv_code(soup):
    """
    Parse CPV code from notice soup.
    Handles two formats:
      1. EU format:  'Hauptklassifizierungscode (cpv): 79710000 ...'
      2. Chapter format: div.chapter with label 'f)' containing an 8-digit code
    Returns the first 8-digit CPV code found, or None.
    """
    if soup is None:
        return None

    text = soup.get_text(separator=' ')

    # EU format: Hauptklassifizierungscode (cpv): 79710000
    m = re.search(r'Hauptklassifizierungscode\s*\(cpv\)\s*[:\-]?\s*(\d{8})', text, re.IGNORECASE)
    if m:
        return m.group(1)

    # Generic CPV label pattern
    m = re.search(r'\bCPV\b[^\d]*(\d{8})', text, re.IGNORECASE)
    if m:
        return m.group(1)

    # Chapter f) — look for 8-digit code in content
    for chapter in soup.select('div.chapter'):
        num_div = chapter.select_one('div.chapter-number')
        if not num_div:
            continue
        if num_div.get_text(strip=True).strip('.').strip() != 'f)':
            continue
        content_div = chapter.select_one('div.chapter-content')
        if content_div:
            m = re.search(r'\b(\d{8})\b', content_div.get_text())
            if m:
                return m.group(1)

    return None


def parse_eligibility(soup):
    """
    Parse 'w) Beurteilung der Eignung' from notice soup.
    Collects all chapter content from w) up to (but not including) x).
    """
    if soup is None:
        return None

    chapters = soup.select('div.chapter')
    collecting = False
    parts = []

    for chapter in chapters:
        num_div = chapter.select_one('div.chapter-number')
        if not num_div:
            continue
        label = num_div.get_text(strip=True).strip('.').strip()

        if label == 'w)':
            collecting = True

        if collecting:
            if label == 'x)':
                break

            content_div = chapter.select_one('div.chapter-content')
            if content_div:
                for br in content_div.find_all('br'):
                    br.replace_with('\n')
                text = content_div.get_text(separator='\n', strip=True)
                if text:
                    parts.append(text)

    return '\n\n'.join(parts) if parts else None


def parse_auftraggeber(soup):
    """
    Parse 'a) Öffentlicher Auftraggeber' from notice soup.
    Returns dict with contracting_authority_name, contracting_org_address,
    contracting_org_city, contracting_org_postal.
    """
    if soup is None:
        return {}

    for chapter in soup.select('div.chapter'):
        num_div = chapter.select_one('div.chapter-number')
        if not num_div:
            continue
        if num_div.get_text(strip=True).strip('.').strip() != 'a)':
            continue

        content_div = chapter.select_one('div.chapter-content')
        if not content_div:
            break

        for br in content_div.find_all('br'):
            br.replace_with('\n')
        lines = [l.strip() for l in content_div.get_text().split('\n') if l.strip()]

        result = {}
        for line in lines:
            if ':' in line:
                key, _, val = line.partition(':')
                key = key.strip().lower()
                val = val.strip()
                if 'name' in key:
                    result['contracting_authority_name'] = val
                elif 'straße' in key or 'strasse' in key or 'hausnummer' in key:
                    result['contracting_org_address'] = val
                elif 'postleitzahl' in key or 'plz' in key:
                    result['contracting_org_postal'] = val
                elif key == 'ort':
                    result['contracting_org_city'] = val
                elif 'e-mail' in key or 'email' in key:
                    result['contracting_authority_email'] = val
        return result

    return {}


def _strip_deadline_lines(text):
    """
    Remove lines that look like a deadline header or date value so they
    don't pollute the summary when both live inside the same chapter.
    """
    date_pattern = re.compile(
        r"""
        (
            \b\d{1,2}[./]\d{2}[./]\d{2,4}\b
          | \b(?:Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[a-z]* \s+ \d{1,2},? \s+ \d{4}\b
          | \d{1,2}:\d{2}(?:\s*(?:AM|PM|Uhr))?
        )
        """,
        re.IGNORECASE | re.VERBOSE,
    )

    kept = []
    for line in text.splitlines():
        stripped = line.strip()
        if not stripped:
            continue
        lower = stripped.lower()
        if any(kw in lower for kw in DEADLINE_KEYWORDS):
            continue
        if re.match(r'^\d+\.\s+details\b', lower):
            continue
        if date_pattern.search(stripped) and len(stripped) < 60:
            continue
        kept.append(stripped)
    return "\n".join(kept).strip()


def _strip_scope_heading(text):
    """
    Remove ALL leading lines that are section headings until we hit
    real content. Handles cases like:
        Line 1: "Type and scope of service"   <-- strip
        Line 2: "Conception and implementation..."  <-- keep
    """
    lines = text.splitlines()
    for i, line in enumerate(lines):
        stripped = line.strip()
        if not stripped:
            continue
        # If this line is a known heading, skip it
        if any(kw in stripped.lower() for kw in SCOPE_KEYWORDS):
            continue
        # First non-heading line — return from here onwards
        return "\n".join(lines[i:]).strip()
    return text


def parse_summary(soup):
    if soup is None:
        return None

    chapters = soup.select("div.chapter")
    by_label = {}
    for ch in chapters:
        lbl = _chapter_label(ch)
        if lbl and lbl not in by_label:
            by_label[lbl] = ch

    # Strategy 1: classic 'f)' label
    if "f)" in by_label:
        text = _chapter_text(by_label["f)"])
        if text:
            return _strip_scope_heading(text)

    # Strategy 2: 'd)' chapter with scope keyword in content
    if "d)" in by_label:
        text = _chapter_text(by_label["d)"])
        if any(kw in text.lower() for kw in SCOPE_KEYWORDS):
            cleaned = _strip_scope_heading(_strip_deadline_lines(text))
            if cleaned:
                return cleaned

    # Strategy 3: scan ALL chapters for scope keyword — return ONLY the
    # content AFTER the heading line, not the heading itself
    for ch in chapters:
        text = _chapter_text(ch)
        if any(kw in text.lower() for kw in SCOPE_KEYWORDS):
            cleaned = _strip_scope_heading(_strip_deadline_lines(text))
            if cleaned:
                return cleaned

    # Strategy 4: raw 'd)' fallback
    if "d)" in by_label:
        text = _strip_deadline_lines(_chapter_text(by_label["d)"]))
        if text:
            return text

    return None


def parse_deadline_from_detail(soup):
    """
    Extract the submission deadline from the detail page soup when it is
    embedded inside a chapter (new-format pages).
    Returns 'YYYY-MM-DD' string or None.
    """
    if soup is None:
        return None

    date_patterns = [
        re.compile(
            r"\b(?P<mon>Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|"
            r"Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|"
            r"Nov(?:ember)?|Dec(?:ember)?)\s+(?P<day>\d{1,2}),?\s+(?P<yr>\d{4})",
            re.IGNORECASE,
        ),
        re.compile(r"\b(?P<day>\d{1,2})\.(?P<mo>\d{2})\.(?P<yr>\d{2,4})\b"),
    ]

    month_map = {
        "jan": 1, "feb": 2, "mar": 3, "apr": 4, "may": 5, "jun": 6,
        "jul": 7, "aug": 8, "sep": 9, "oct": 10, "nov": 11, "dec": 12,
    }

    for ch in soup.select("div.chapter"):
        text = _chapter_text(ch)
        if not any(kw in text.lower() for kw in DEADLINE_KEYWORDS):
            continue
        for line in text.splitlines():
            for pat in date_patterns:
                m = pat.search(line)
                if not m:
                    continue
                try:
                    gd = m.groupdict()
                    if "mon" in gd:
                        month = month_map[gd["mon"][:3].lower()]
                        day   = int(gd["day"])
                        year  = int(gd["yr"])
                    else:
                        day   = int(gd["day"])
                        month = int(gd["mo"])
                        year  = int(gd["yr"])
                        if year < 100:
                            year += 2000
                    return datetime(year, month, day).strftime("%Y-%m-%d")
                except (ValueError, KeyError):
                    continue
    return None


def parse_ort_der_ausfuehrung(soup):
    if soup is None:
        return {'contracting_org_country': 'Germany'}

    for chapter in soup.select('div.chapter'):
        num_div = chapter.select_one('div.chapter-number')
        if not num_div or num_div.get_text(strip=True).strip('.').strip() != 'e)':
            continue

        content_div = chapter.select_one('div.chapter-content')
        if not content_div:
            break

        for br in content_div.find_all('br'):
            br.replace_with('\n')

        lines = [l.strip() for l in content_div.get_text().split('\n') if l.strip()]

        postal = city = address = None
        country = 'Germany'

        postal_city_idx = None
        for i, line in enumerate(lines):
            m = re.match(r'^(\d{5})\s+(.+)$', line)
            if m:
                postal = m.group(1)
                city   = m.group(2).strip()
                postal_city_idx = i
                break

        for line in lines:
            line_lower = line.lower()
            if 'land:' in line_lower or 'staat:' in line_lower:
                country = line.split(':')[-1].strip()
            elif 'österreich' in line_lower:
                country = 'Austria'
            elif 'schweiz' in line_lower:
                country = 'Switzerland'
            elif 'luxemburg' in line_lower:
                country = 'Luxembourg'

        if postal_city_idx is not None and postal_city_idx > 0:
            address = ', '.join(lines[:postal_city_idx])
        elif postal_city_idx is None and lines:
            address = ', '.join(lines)

        return {
            'contracting_org_country':  country,
            'contracting_org_address':  address,
            'contracting_org_city':     city,
            'contracting_org_province': None,
            'contracting_org_postal':   postal,
        }

    return {'contracting_org_country': 'Germany'}


# ---------------------------------------------------------------------------
# DB write functions
# ---------------------------------------------------------------------------

def save_notice(cursor, tender_id, notice_html, loc, authority, eligibility=None, summary=None, cpv_code=None):
    cursor.execute(
        """UPDATE tender_details SET
               notice                     = %s,
               contracting_authority_name  = %s,
               contracting_authority_email = %s,
               contracting_org_address     = %s,
               contracting_org_city        = %s,
               contracting_org_province    = %s,
               contracting_org_postal      = %s,
               contracting_org_country     = %s,
               languages                   = %s,
               framework_agreement         = %s,
               trade_agreements            = %s,
               summary                     = %s,
               cpv_code                    = %s
           WHERE tender_id = %s""",
        (
            notice_html,
            authority.get('contracting_authority_name'),
            authority.get('contracting_authority_email'),
            authority.get('contracting_org_address'),
            authority.get('contracting_org_city'),
            loc.get('contracting_org_province'),
            authority.get('contracting_org_postal'),
            loc.get('contracting_org_country', 'Germany'),
            'German',
            eligibility,
            eligibility,
            summary,
            cpv_code,
            tender_id,
        )
    )


def save_documents(cursor, tender_id, docs):
    for doc in docs:
        cursor.execute(
            """INSERT INTO tender_documents (tender_id, document_name, document_url, date_added)
               VALUES (%s, %s, %s, %s)""",
            (tender_id, doc['document_name'], doc['document_url'], doc.get('date_added'))
        )


def mark_done(cursor, tender_id):
    cursor.execute(
        "UPDATE tenders SET detail = 1 WHERE id = %s",
        (tender_id,)
    )


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def run():
    print('=' * 70)
    print('Evergabe-Online - Detail Scraper')
    print('=' * 70)

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

    cursor.execute(
        "SELECT COUNT(*) FROM tenders WHERE detail=0 AND source=%s",
        (SOURCE,)
    )
    total_pending = cursor.fetchone()[0]
    print(f'Pending tenders: {total_pending:,}')
    print('=' * 70)

    total_done   = 0
    total_failed = 0

    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'
            )
        )
        page = context.new_page()

        while True:
            batch = fetch_pending(cursor)
            if not batch:
                print('\nNo more pending tenders.')
                break

            for tender_id, url in batch:
                print(f'  [{tender_id}] {url}', end=' ... ', flush=True)
                try:
                    # 1. Scrape notice
                    notice_html = scrape_notice(page, url)
                    if not notice_html:
                        print('notice div not found', end=' ')

                    # 2. Scrape documents
                    url_id = extract_url_id(url)
                    docs   = []
                    if url_id:
                        docs = scrape_documents(page, url_id)

                    # 3. Parse fields from notice
                    soup      = BeautifulSoup(notice_html, 'html.parser') if notice_html else None
                    loc       = parse_ort_der_ausfuehrung(soup)
                    authority = parse_auftraggeber(soup)
                    eligibility = parse_eligibility(soup)
                    summary   = parse_summary(soup)
                    cpv_code  = parse_cpv_code(soup)

                    # 4. Update closing_date/status from detail page if available
                    detail_deadline = parse_deadline_from_detail(soup)
                    if detail_deadline:
                        cursor.execute(
                            "UPDATE tenders SET closing_date=%s, status=%s WHERE id=%s",
                            (detail_deadline, compute_status(detail_deadline), tender_id)
                        )

                    # 5. Save to DB
                    save_notice(cursor, tender_id, notice_html, loc, authority, eligibility, summary, cpv_code)
                    save_documents(cursor, tender_id, docs)
                    mark_done(cursor, tender_id)
                    conn.commit()

                    total_done += 1
                    city_info = loc.get('contracting_org_city', '-') or '-'
                    print(f'OK  (notice={"yes" if notice_html else "no"}, docs={len(docs)}, city={city_info})')

                except Exception as e:
                    conn.rollback()
                    total_failed += 1
                    print(f'FAILED: {e}')

            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()