import sys
import os
import re
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

SOURCE     = 'www.evergabe-online.de'
BATCH_SIZE = 200

DB_CONFIG = {
    '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 get_db_connection():
    return mysql.connector.connect(**DB_CONFIG)


# ─── Parsers (same logic as evergabe_detail.py) ────────────────────────────────

def parse_auftraggeber(notice_html):
    if not notice_html:
        return {}
    soup = BeautifulSoup(notice_html, 'html.parser')
    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 parse_ort_der_ausfuehrung(notice_html):
    if not notice_html:
        return {}
    soup = BeautifulSoup(notice_html, 'html.parser')
    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() != '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 = province = None
        country = 'DE'
        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
        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': province,
            'contracting_org_postal':   postal,
        }
    return {}


def parse_eligibility(notice_html):
    if not notice_html:
        return None
    soup = BeautifulSoup(notice_html, 'html.parser')
    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_summary(notice_html):
    if not notice_html:
        return None
    soup = BeautifulSoup(notice_html, 'html.parser')
    for label in ('f)', 'd)'):
        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() != label:
                continue
            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:
                    return text
    return None


def parse_cpv_code(notice_html):
    if not notice_html:
        return None
    soup = BeautifulSoup(notice_html, 'html.parser')
    text = soup.get_text(separator=' ')
    m = re.search(r'Hauptklassifizierungscode\s*\(cpv\)\s*[:\-]?\s*(\d{8})', text, re.IGNORECASE)
    if m:
        return m.group(1)
    m = re.search(r'\bCPV\b[^\d]*(\d{8})', text, re.IGNORECASE)
    if m:
        return m.group(1)
    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


# ─── Main ──────────────────────────────────────────────────────────────────────

def run():
    print('=' * 70)
    print('Evergabe - Backfill New Fields from Stored Notice HTML')
    print('=' * 70)

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

    cursor.execute(
        """SELECT COUNT(*) FROM tenders t
           JOIN tender_details td ON td.tender_id = t.id
           WHERE t.source = %s AND t.detail = 1 AND td.notice IS NOT NULL""",
        (SOURCE,)
    )
    total = cursor.fetchone()[0]
    print(f'Total records to backfill: {total:,}')
    print('=' * 70)

    offset       = 0
    total_done   = 0
    total_failed = 0

    while True:
        cursor.execute(
            """SELECT t.id, td.tender_id, td.notice
               FROM tenders t
               JOIN tender_details td ON td.tender_id = t.id
               WHERE t.source = %s AND t.detail = 1 AND td.notice IS NOT NULL
               ORDER BY t.id ASC
               LIMIT %s OFFSET %s""",
            (SOURCE, BATCH_SIZE, offset)
        )
        batch = cursor.fetchall()
        if not batch:
            break

        for t_id, td_tender_id, notice_html in batch:
            try:
                authority   = parse_auftraggeber(notice_html)
                loc         = parse_ort_der_ausfuehrung(notice_html)
                eligibility = parse_eligibility(notice_html)
                summary     = parse_summary(notice_html)
                cpv_code    = parse_cpv_code(notice_html)

                cursor.execute(
                    """UPDATE tender_details SET
                           contracting_authority_name  = %s,
                           contracting_authority_email = %s,
                           contracting_org_address     = %s,
                           contracting_org_city       = %s,
                           contracting_org_postal     = %s,
                           contracting_org_country    = %s,
                           contracting_org_province   = %s,
                           languages                  = %s,
                           framework_agreement        = %s,
                           trade_agreements           = %s,
                           summary                    = %s,
                           cpv_code                   = %s
                       WHERE tender_id = %s""",
                    (
                        authority.get('contracting_authority_name'),
                        authority.get('contracting_authority_email'),
                        authority.get('contracting_org_address'),
                        authority.get('contracting_org_city'),
                        authority.get('contracting_org_postal'),
                        loc.get('contracting_org_country'),
                        loc.get('contracting_org_province'),
                        'German',
                        eligibility,
                        eligibility,
                        summary,
                        cpv_code,
                        td_tender_id,
                    )
                )
                total_done += 1

            except Exception as e:
                total_failed += 1
                print(f'  FAILED tender_id={td_tender_id}: {e}')

        conn.commit()
        offset += BATCH_SIZE
        print(f'  Processed {min(offset, total):,} / {total:,} ...')

    cursor.close()
    conn.close()

    print('\n' + '=' * 70)
    print('Backfill Complete!')
    print('=' * 70)
    print(f'  Updated : {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()