import os
import re
import sys

from dotenv import load_dotenv
import mysql.connector

load_dotenv(os.path.join(os.path.dirname(__file__), '../.env'))

if sys.platform == 'win32':
    sys.stdout.reconfigure(encoding='utf-8')

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

# Set to 1 for test run, None to fix all
LIMIT = 1


# ─── Parsers ───────────────────────────────────────────────────────────────────

def _strip_html(html):
    return re.sub(r'<[^>]+>', ' ', html or '')


def extract_cpv_code(notice_html):
    if not notice_html:
        return None
    # HTML: ...BT-262-Procedure">Main classification</span>...<span class="data">48000000</span>
    m = re.search(
        r'BT-262-Procedure[^>]*>.*?<span[^>]*class="data"[^>]*>(\d{8})</span>',
        notice_html, re.IGNORECASE | re.DOTALL
    )
    return m.group(1) if m else None


def extract_procurement_method(summary_html):
    if not summary_html:
        return None
    # HTML structure: ...BT-105">Type of procedure</span><span>:&nbsp;</span><span ...>VALUE</span>
    m = re.search(
        r'BT-105[^>]*>[^<]*Type of procedure[^<]*</span>\s*<span>[^<]*</span>\s*<span[^>]*>([^<]+)</span>',
        summary_html, re.IGNORECASE
    )
    return m.group(1).strip() if m else None


def extract_budget(summary_html):
    if not summary_html:
        return None, None
    # HTML: ...BT-27">Estimated value excluding VAT</span><span>:&nbsp;</span>
    #       <span class="data">893 976,22</span><span>&nbsp;</span><span class="data">EUR</span>
    m = re.search(
        r'BT-27[^>]*>[^<]*Estimated value excluding VAT[^<]*</span>'
        r'\s*<span>[^<]*</span>\s*'
        r'<span[^>]*class="data"[^>]*>([\d\s,\.]+)</span>'
        r'\s*<span>[^<]*</span>\s*'
        r'<span[^>]*class="data"[^>]*>([A-Z]{3})</span>',
        summary_html, re.IGNORECASE
    )
    if m:
        raw = m.group(1).strip().replace(' ', '').replace(',', '.')
        return raw, m.group(2).upper()
    return None, None


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

def main():
    db = mysql.connector.connect(**DB_CONFIG)
    print('✔  Database connected')

    limit_clause = f'LIMIT {LIMIT}' if LIMIT else ''
    cur = db.cursor(dictionary=True)
    cur.execute(f'''
        SELECT td.tender_id, td.notice, td.summary
        FROM tender_details td
        JOIN tenders t ON t.id = td.tender_id
        WHERE t.source = 'europa'
        AND (td.cpv_code IS NULL OR td.procedure_type IS NULL OR td.budget IS NULL OR budget_currency IS NULL)
     
    ''')
    rows = cur.fetchall()
    cur.close()

    if not rows:
        print('No records to fix.')
        db.close()
        return

    print(f'Processing {len(rows)} record(s)…\n')

    upd = db.cursor()
    for row in rows:
        tid                = row['tender_id']
        cpv_code           = extract_cpv_code(row['notice'])
        procurement_method = extract_procurement_method(row['summary'])
        budget, currency   = extract_budget(row['summary'])

        print(f'  tender_id          : {tid}')
        print(f'  cpv_code           : {cpv_code or "null"}')
        print(f'  procurement_method : {procurement_method or "null"}')
        print(f'  budget             : {budget or "null"}')
        print(f'  budget_currency    : {currency or "null"}')
        print()

        upd.execute(
            '''UPDATE tender_details
               SET cpv_code       = COALESCE(cpv_code, %s),
                   procedure_type = COALESCE(procedure_type, %s),
                   budget         = COALESCE(budget, %s),
                   budget_currency = COALESCE(budget_currency, %s)
               WHERE tender_id = %s''',
            (cpv_code, procurement_method, budget, currency, tid)
        )

    db.commit()
    upd.close()
    db.close()
    print('✔  Done.')


if __name__ == '__main__':
    main()