import sys
import os
import time
import requests
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

sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))
from keyword_tracker import is_keyword_done, mark_keyword_done

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

SOURCE    = 'ezamowienia.gov.pl'
API_URL   = 'https://ezamowienia.gov.pl/mp-readmodels/api/Search/SearchTenders'
PAGE_SIZE = 10
MAX_PAGES = int(os.getenv('EZAMOWIENIA_MAX_PAGES', 10))

# KEYWORDS = [k.strip() for k in os.getenv('KEYWORDS', '').split(',') if k.strip()]
# POLISH_KEYWORDS = [k.strip() for k in os.getenv('POLISH_KEYWORDS', '').split(',') if k.strip()]
# ALL_KEYWORDS = KEYWORDS + POLISH_KEYWORDS


def load_keywords_from_db():
    """Fetch keywords from managed_keywords table for 'en' and 'pl' languages."""
    result = {'en': [], 'pl': []}
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT language_code, keyword FROM managed_keywords WHERE language_code IN ('en', 'pl')"
        )
        for lang_code, keyword_cell in cursor.fetchall():
            for kw in keyword_cell.split(','):
                kw = kw.strip()
                if kw:
                    result[lang_code].append(kw)
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"  Warning: Could not load keywords from DB: {e}")
    return result


_kw_by_lang     = load_keywords_from_db()
KEYWORDS        = _kw_by_lang['en']
POLISH_KEYWORDS = _kw_by_lang['pl']
ALL_KEYWORDS    = KEYWORDS + POLISH_KEYWORDS

print(f"EN keywords     ({len(KEYWORDS)}): {KEYWORDS}")
print(f"PL keywords     ({len(POLISH_KEYWORDS)}): {POLISH_KEYWORDS}")
print(f"Total keywords  : {len(ALL_KEYWORDS)}")
# exit()


def parse_iso_datetime(raw):
    """Parse ISO 8601 datetime string, return (date, datetime) tuple."""
    if not raw:
        return None, None
    try:
        dt = datetime.fromisoformat(raw.replace('Z', '+00:00'))
        return dt.date(), dt.replace(tzinfo=None)
    except Exception:
        return None, None


def fetch_page(keyword, page_number, session):
    """Call the search API and return list of tender dicts, or None on error."""
    params = {
        'title': keyword,
        'SortingColumnName': 'InitiationDate',
        'SortingDirection': 'DESC',
        'PageNumber': page_number,
        'PageSize': PAGE_SIZE,
    }
    try:
        resp = session.get(API_URL, params=params, timeout=30)
        if resp.status_code == 200:
            return resp.json()
        else:
            print(f'    HTTP {resp.status_code} — skipping page.')
            return None
    except requests.RequestException as e:
        print(f'    Request error: {e}')
        return None


def insert_tender(cursor, row, keyword):
    """Insert tender + tender_detail. Returns new id or None if duplicate."""
    source_id = row['objectId']

    cursor.execute(
        'SELECT id FROM tenders WHERE source = %s AND source_id = %s LIMIT 1',
        (SOURCE, source_id)
    )
    if cursor.fetchone():
        return None  # duplicate

    closing_date, _      = parse_iso_datetime(row.get('submissionOffersDate'))
    pub_date, pub_dt     = parse_iso_datetime(row.get('initiationDate'))
    _, submission_end_dt = parse_iso_datetime(row.get('submissionOffersDate'))

    url = f'https://ezamowienia.gov.pl/mp-client/search/list/{source_id}'

    cursor.execute(
        """INSERT INTO tenders
               (source, source_id, title, reference_number, organization,
                url, status, keyword, detail, closing_date, publication_type,
                created_at, updated_at)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 0, %s, %s, NOW(), NOW())""",
        (
            SOURCE,
            source_id,
            row.get('title'),
            row.get('bzpNumber'),
            row.get('organizationName'),
            url,
            row.get('tenderState'),
            keyword,
            closing_date,
            pub_dt,
        )
    )
    tender_id = cursor.lastrowid

    cursor.execute(
        """INSERT INTO tender_details
               (tender_id, procedure_type, contracting_authority_name,
                contracting_org_city, contracting_org_province,
                procedure_id, bulletin_code,
                submission_end, deadline, publication_date,
                detail, created_at, updated_at)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, NOW(), NOW())""",
        (
            tender_id,
            row.get('tenderType'),
            row.get('organizationName'),
            row.get('organizationCity'),
            row.get('organizationProvince'),
            source_id,
            row.get('bzpNumber'),
            submission_end_dt,
            submission_end_dt,
            pub_dt,
        )
    )

    return tender_id


def scrape_keyword(keyword, conn, cursor, session):
    print(f"\n  Keyword: '{keyword}'")
    page_number = 1
    kw_inserted = 0
    kw_skipped  = 0

    while True:
        print(f'    [Page {page_number}] Fetching...', end=' ')
        rows = fetch_page(keyword, page_number, session)

        if rows is None:
            print('Error — stopping keyword.')
            break

        if not rows:
            print('No results.')
            break

        print(f'{len(rows)} result(s)')

        for row in rows:
            try:
                tender_id = insert_tender(cursor, row, keyword)
                if tender_id:
                    kw_inserted += 1
                    print(f'      ✔ Inserted id={tender_id}  [{row["objectId"]}]')
                else:
                    kw_skipped += 1
            except Exception as e:
                print(f'      ✖ DB error: {e}')
                conn.rollback()
                continue

        conn.commit()

        if page_number >= MAX_PAGES:
            print(f'    Reached max pages ({MAX_PAGES}) — stopping.')
            break

        if len(rows) < PAGE_SIZE:
            break

        page_number += 1
        time.sleep(1)

    return kw_inserted, kw_skipped


def run():
    print('=' * 70)
    print('EZamowienia.gov.pl — Keyword Scraper')
    print('=' * 70)
    print(f'English keywords : {len(KEYWORDS)}')
    print(f'Polish keywords  : {len(POLISH_KEYWORDS)}')
    print(f'Total keywords   : {len(ALL_KEYWORDS)}')
    print('=' * 70)

    if not ALL_KEYWORDS:
        print('ERROR: No keywords found in managed_keywords table for language_code in ("en", "pl")')
        return

    conn   = get_db_connection()
    cursor = conn.cursor()
    session = requests.Session()

    total_inserted = 0
    total_skipped  = 0

    for i, keyword in enumerate(ALL_KEYWORDS, 1):
        print(f'\n[{i}/{len(ALL_KEYWORDS)}]', end='')
        if is_keyword_done(conn, keyword, SOURCE):
            print(f"  [SKIP] Already ran today: \"{keyword}\"")
            continue
        inserted, skipped = scrape_keyword(keyword, conn, cursor, session)
        total_inserted += inserted
        total_skipped  += skipped
        mark_keyword_done(conn, keyword, SOURCE)
        print(f"  [SAVED] Marked keyword done: \"{keyword}\"")
        time.sleep(1)

    session.close()
    cursor.close()
    conn.close()

    print('\n' + '=' * 70)
    print('Scraping Complete!')
    print('=' * 70)
    print(f'  Keywords processed : {len(ALL_KEYWORDS)}')
    print(f'  New records        : {total_inserted:,}')
    print(f'  Duplicates skipped : {total_skipped:,}')
    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()