import asyncio
import os
from datetime import datetime

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.async_api import async_playwright

SOURCE = 'www.publicprocurement.be'

# These 4 columns are not available via the API — scraped from detail page only
# - subject_name      (Specific purpose)
# - amendment_date    (Last dispatched at)
# - submission_start  (Questions can be submitted from)
# - submission_end    (Questions can be submitted until)


def parse_datetime(value):
    if not value:
        return None
    formats = [
        '%d/%m/%Y %H:%M:%S', '%d/%m/%Y %H:%M', '%d/%m/%Y',
        '%Y-%m-%dT%H:%M:%S', '%Y-%m-%d %H:%M:%S', '%Y-%m-%d',
    ]
    for fmt in formats:
        try:
            return datetime.strptime(value.strip(), fmt).strftime('%Y-%m-%d %H:%M:%S')
        except ValueError:
            continue
    return None


def parse_date(value):
    dt = parse_datetime(value)
    return dt[:10] if dt else None


async def scrape_detail_page(page, url):
    print(f"  Visiting: {url}")
    try:
        await page.goto(url, wait_until='networkidle', timeout=60000)
    except Exception as e:
        print(f"  ERROR loading page: {e}")
        return None

    try:
        await page.wait_for_selector('[title]', timeout=20000)
    except Exception:
        print(f"  WARNING: no titled elements found")

    data = await page.evaluate("""
        () => {
            function getVal(titleVal) {
                const els = document.querySelectorAll('[title]');
                for (const el of els) {
                    if (el.getAttribute('title').trim().toLowerCase() === titleVal.trim().toLowerCase()) {
                        const text = (el.innerText || el.textContent || '').trim();
                        if (text.toLowerCase() === titleVal.trim().toLowerCase()) {
                            const next = el.nextElementSibling;
                            return next ? (next.innerText || next.textContent || '').trim() : '';
                        }
                        return text;
                    }
                }
                return null;
            }
            return {
                specific_purpose:  getVal('Specific purpose'),
                last_dispatched_at:getVal('Last dispatched at'),
                questions_from:    getVal('Questions can be submitted from'),
                questions_until:   getVal('Questions can be submitted until'),
            };
        }
    """)
    return data


def update_tender_detail(cursor, tender_id, data, closing_date):
    cursor.execute("SELECT id FROM tender_details WHERE tender_id = %s", (tender_id,))
    existing = cursor.fetchone()

    fields = {
        'subject_name':   data.get('specific_purpose'),
        'amendment_date': parse_date(data.get('last_dispatched_at')),
        'submission_start': parse_datetime(data.get('questions_from')),
        'submission_end':   parse_datetime(data.get('questions_until')),
    }

    if existing:
        set_clause = ', '.join(f"`{col}` = %s" for col in fields)
        sql = f"UPDATE tender_details SET {set_clause}, updated_at = CURRENT_TIMESTAMP WHERE tender_id = %s"
        cursor.execute(sql, list(fields.values()) + [tender_id])
    else:
        cols = ', '.join(f"`{col}`" for col in fields)
        placeholders = ', '.join('%s' for _ in fields)
        sql = f"INSERT INTO tender_details (tender_id, {cols}, created_at, updated_at) VALUES (%s, {placeholders}, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)"
        cursor.execute(sql, [tender_id] + list(fields.values()))

    cursor.execute(
        "UPDATE tenders SET detail = 1, closing_date = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s",
        (closing_date, tender_id)
    )


async def main():
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute(
        "SELECT id, url, closing_date FROM tenders WHERE source = %s AND detail = 0",
        (SOURCE,)
    )
    rows = cursor.fetchall()
    print(f"Found {len(rows)} tenders to process.")

    if not rows:
        cursor.close()
        conn.close()
        return

    async with async_playwright() as p:
        browser = await p.chromium.launch(headless=True)
        context = await browser.new_context(
            user_agent='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 '
                       '(KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36'
        )
        page = await context.new_page()

        for i, (tender_id, url, closing_date) in enumerate(rows, 1):
            print(f"[{i}/{len(rows)}] tender_id={tender_id}")
            if not url:
                print("  No URL, skipping.")
                continue

            data = await scrape_detail_page(page, url)
            if data is None:
                print("  Failed to scrape, skipping.")
                continue

            update_tender_detail(cursor, tender_id, data, closing_date)
            conn.commit()
            print(f"  Done. detail=1 set.")

        await browser.close()

    cursor.close()
    conn.close()
    print(f"\nFinished. Processed {len(rows)} tenders.")


if __name__ == '__main__':
    asyncio.run(main())