import sys
import os
import re
import requests
import time

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   = 'tendera.at'
API_URL  = 'https://www.tendera.at/api/tenders/_search'
HEADERS  = {'Content-Type': 'application/json', 'Accept': 'application/json'}
BATCH    = 100   # records per API call


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


NUTS_COUNTRY_MAP = {
    'AT': 'Austria',       'BE': 'Belgium',        'BG': 'Bulgaria',
    'CY': 'Cyprus',        'CZ': 'Czech Republic', 'DE': 'Germany',
    'DK': 'Denmark',       'EE': 'Estonia',        'EL': 'Greece',
    'ES': 'Spain',         'FI': 'Finland',        'FR': 'France',
    'HR': 'Croatia',       'HU': 'Hungary',        'IE': 'Ireland',
    'IT': 'Italy',         'LT': 'Lithuania',      'LU': 'Luxembourg',
    'LV': 'Latvia',        'MT': 'Malta',          'NL': 'Netherlands',
    'PL': 'Poland',        'PT': 'Portugal',       'RO': 'Romania',
    'SE': 'Sweden',        'SI': 'Slovenia',       'SK': 'Slovakia',
    'NO': 'Norway',        'CH': 'Switzerland',    'UK': 'United Kingdom',
}

def derive_country_from_nuts(nuts):
    if not nuts:
        return None
    return NUTS_COUNTRY_MAP.get(str(nuts).strip()[:2].upper())


def extract_timezone(value):
    if not value:
        return None
    m = re.search(r'([+-]\d{2}:?\d{2})$', str(value))
    return m.group(1) if m else None


def fetch_by_ids(source_ids):
    """Fetch raw source data for a list of Elasticsearch document IDs."""
    payload = {
        'size': len(source_ids),
        'from': 0,
        'query': {'ids': {'values': source_ids}},
        'stored_fields': ['*'],
        '_source': {'excludes': []},
    }
    resp = requests.post(API_URL, headers=HEADERS, json=payload, timeout=30)
    resp.raise_for_status()
    hits = resp.json().get('hits', {}).get('hits', [])
    return {h['_id']: h.get('_source', {}) for h in hits}


def main():
    print('=' * 60)
    print('TENDERA.AT - Fix existing records')
    print('=' * 60)

    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    # Load all existing tendera records that need fixing
    cursor.execute("""
        SELECT t.source_id, td.id AS detail_id, td.nuts_place, td.award_amount
        FROM tenders t
        JOIN tender_details td ON td.tender_id = t.id
        WHERE t.source = %s
    """, (SOURCE,))
    rows = cursor.fetchall()
    total = len(rows)
    print(f'Records to fix: {total}')

    # --- Step 1: Fix languages, budget, country, status via SQL (no API needed) ---
    print('\n[1/2] Fixing languages, budget, contracting_org_country, status via SQL...')
    fix_cursor = conn.cursor()

    fix_cursor.execute("""
        UPDATE tender_details td
        JOIN tenders t ON td.tender_id = t.id
        SET td.languages = 'DE'
        WHERE t.source = %s
          AND (td.languages IS NULL OR td.languages = '')
    """, (SOURCE,))
    print(f'  languages updated : {fix_cursor.rowcount}')

    fix_cursor.execute("""
        UPDATE tender_details td
        JOIN tenders t ON td.tender_id = t.id
        SET td.budget = td.award_amount
        WHERE t.source = %s
          AND (td.budget IS NULL OR td.budget = '')
          AND td.award_amount IS NOT NULL
    """, (SOURCE,))
    print(f'  budget updated    : {fix_cursor.rowcount}')

    fix_cursor.execute("""
        UPDATE tender_details td
        JOIN tenders t ON td.tender_id = t.id
        SET td.contracting_org_country = CASE
            WHEN LEFT(td.nuts_place, 2) = 'AT' THEN 'Austria'
            WHEN LEFT(td.nuts_place, 2) = 'BE' THEN 'Belgium'
            WHEN LEFT(td.nuts_place, 2) = 'BG' THEN 'Bulgaria'
            WHEN LEFT(td.nuts_place, 2) = 'CY' THEN 'Cyprus'
            WHEN LEFT(td.nuts_place, 2) = 'CZ' THEN 'Czech Republic'
            WHEN LEFT(td.nuts_place, 2) = 'DE' THEN 'Germany'
            WHEN LEFT(td.nuts_place, 2) = 'DK' THEN 'Denmark'
            WHEN LEFT(td.nuts_place, 2) = 'EE' THEN 'Estonia'
            WHEN LEFT(td.nuts_place, 2) = 'EL' THEN 'Greece'
            WHEN LEFT(td.nuts_place, 2) = 'ES' THEN 'Spain'
            WHEN LEFT(td.nuts_place, 2) = 'FI' THEN 'Finland'
            WHEN LEFT(td.nuts_place, 2) = 'FR' THEN 'France'
            WHEN LEFT(td.nuts_place, 2) = 'HR' THEN 'Croatia'
            WHEN LEFT(td.nuts_place, 2) = 'HU' THEN 'Hungary'
            WHEN LEFT(td.nuts_place, 2) = 'IE' THEN 'Ireland'
            WHEN LEFT(td.nuts_place, 2) = 'IT' THEN 'Italy'
            WHEN LEFT(td.nuts_place, 2) = 'LT' THEN 'Lithuania'
            WHEN LEFT(td.nuts_place, 2) = 'LU' THEN 'Luxembourg'
            WHEN LEFT(td.nuts_place, 2) = 'LV' THEN 'Latvia'
            WHEN LEFT(td.nuts_place, 2) = 'MT' THEN 'Malta'
            WHEN LEFT(td.nuts_place, 2) = 'NL' THEN 'Netherlands'
            WHEN LEFT(td.nuts_place, 2) = 'PL' THEN 'Poland'
            WHEN LEFT(td.nuts_place, 2) = 'PT' THEN 'Portugal'
            WHEN LEFT(td.nuts_place, 2) = 'RO' THEN 'Romania'
            WHEN LEFT(td.nuts_place, 2) = 'SE' THEN 'Sweden'
            WHEN LEFT(td.nuts_place, 2) = 'SI' THEN 'Slovenia'
            WHEN LEFT(td.nuts_place, 2) = 'SK' THEN 'Slovakia'
            WHEN LEFT(td.nuts_place, 2) = 'NO' THEN 'Norway'
            WHEN LEFT(td.nuts_place, 2) = 'CH' THEN 'Switzerland'
            WHEN LEFT(td.nuts_place, 2) = 'UK' THEN 'United Kingdom'
            ELSE 'Austria'
        END
        WHERE t.source = %s
          AND (td.contracting_org_country IS NULL OR td.contracting_org_country = '')
    """, (SOURCE,))
    print(f'  country updated   : {fix_cursor.rowcount}')

    # Award notices
    fix_cursor.execute("""
        UPDATE tenders t
        JOIN tender_details td ON td.tender_id = t.id
        SET t.status = 'Awarded', t.status_text = 'Award Notice'
        WHERE t.source = %s
          AND td.notice_type IN ('8_2_Z1', '8_2_Z3', '8_1_Z4', '8_1_Z5', '7_2_Z1')
          AND (t.status IS NULL OR t.status = '')
    """, (SOURCE,))
    print(f'  status=Awarded    : {fix_cursor.rowcount}')

    # Open — deadline in the future
    fix_cursor.execute("""
        UPDATE tenders t
        JOIN tender_details td ON td.tender_id = t.id
        SET t.status = 'Open', t.status_text = 'Open for Submissions'
        WHERE t.source = %s
          AND td.notice_type NOT IN ('8_2_Z1', '8_2_Z3', '8_1_Z4', '8_1_Z5', '7_2_Z1')
          AND td.deadline IS NOT NULL
          AND td.deadline >= NOW()
          AND (t.status IS NULL OR t.status = '')
    """, (SOURCE,))
    print(f'  status=Open       : {fix_cursor.rowcount}')

    # Closed — deadline passed or no deadline
    fix_cursor.execute("""
        UPDATE tenders t
        JOIN tender_details td ON td.tender_id = t.id
        SET t.status = 'Closed', t.status_text = 'Deadline Passed'
        WHERE t.source = %s
          AND td.notice_type NOT IN ('8_2_Z1', '8_2_Z3', '8_1_Z4', '8_1_Z5', '7_2_Z1')
          AND (td.deadline IS NULL OR td.deadline < NOW())
          AND (t.status IS NULL OR t.status = '')
    """, (SOURCE,))
    print(f'  status=Closed     : {fix_cursor.rowcount}')

    conn.commit()
    fix_cursor.close()

    # --- Step 2: Fix deadline_timezone by re-fetching from API ---
    print(f'\n[2/2] Fixing deadline_timezone via API re-fetch ({total} records)...')

    # Only process records that still have no deadline_timezone
    cursor.execute("""
        SELECT t.source_id, td.id AS detail_id
        FROM tenders t
        JOIN tender_details td ON td.tender_id = t.id
        WHERE t.source = %s
          AND (td.deadline_timezone IS NULL OR td.deadline_timezone = '')
    """, (SOURCE,))
    tz_rows = cursor.fetchall()
    print(f'  Records needing timezone: {len(tz_rows)}')

    updated = 0
    not_found = 0

    for i in range(0, len(tz_rows), BATCH):
        batch = tz_rows[i:i + BATCH]
        ids   = [r['source_id'] for r in batch]
        id_map = {r['source_id']: r['detail_id'] for r in batch}

        try:
            src_map = fetch_by_ids(ids)
        except Exception as e:
            print(f'  API error at batch {i//BATCH + 1}: {e}')
            time.sleep(2)
            continue

        upd_cursor = conn.cursor()
        for source_id, src in src_map.items():
            tz = extract_timezone(src.get('IMPORT_DATE'))
            if tz:
                upd_cursor.execute(
                    "UPDATE tender_details SET deadline_timezone = %s WHERE id = %s",
                    (tz, id_map[source_id])
                )
                updated += 1
            else:
                not_found += 1

        conn.commit()
        upd_cursor.close()

        done = min(i + BATCH, len(tz_rows))
        print(f'  Progress: {done}/{len(tz_rows)} | Updated: {updated} | No TZ: {not_found}')
        if i + BATCH < len(tz_rows):
            time.sleep(0.5)

    cursor.close()
    conn.close()

    print('\n' + '=' * 60)
    print('Fix Complete!')
    print('=' * 60)
    print(f'  languages fixed  : all tendera records set to DE')
    print(f'  budget fixed     : copied from award_amount')
    print(f'  country fixed    : derived from nuts_place')
    print(f'  status fixed     : Awarded / Open / Closed based on notice type + deadline')
    print(f'  timezone fixed   : {updated} updated, {not_found} had no TZ in API')
    print('=' * 60)


if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        print('\nInterrupted.')
    except Exception as e:
        import traceback
        print(f'\nFatal Error: {e}')
        traceback.print_exc()