"""
One-time backfill script for canadabuys tender_details.

For all existing records in tender_details (source=canadabuys):
  - Sets status = 'open', status_text = 'Open'
  - Builds deadline (datetime) from tenders.closing_date + tender_details.closing_time
  - Sets deadline_timezone from closing_time timezone string
  - Sets publication_date = open_date
"""

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


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 = 'canadabuys'


def parse_closing_time(closing_time_str):
    """Parse closing time string like '2:00 pm EST' or '14:00 EDT'.
    Returns (time_str, timezone_str) or (None, None)."""
    if not closing_time_str or not closing_time_str.strip():
        return None, None
    match = re.match(
        r'(\d{1,2}:\d{2}(?:\s*[apAP][mM])?)\s*([A-Z]{2,5})?',
        closing_time_str.strip()
    )
    if not match:
        return None, None
    time_part = match.group(1).strip()
    timezone_part = match.group(2) or None
    return time_part, timezone_part


def build_deadline_datetime(closing_date, closing_time_str):
    """Combine closing_date and closing_time string into a datetime.
    Returns (deadline_datetime, timezone_str)."""
    if not closing_date:
        return None, None
    time_part, timezone_part = parse_closing_time(closing_time_str)
    if not time_part:
        return None, timezone_part
    try:
        for fmt in ('%I:%M %p', '%I:%M%p', '%H:%M'):
            try:
                t = datetime.strptime(time_part.upper(), fmt.upper()).time()
                break
            except ValueError:
                continue
        else:
            return None, timezone_part
        deadline_dt = datetime.combine(closing_date, t)
        return deadline_dt, timezone_part
    except Exception:
        return None, timezone_part


def backfill():
    print("=" * 80)
    print("Backfill: status, deadline, deadline_timezone, publication_date")
    print("=" * 80)

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

    cursor.execute("""
        SELECT td.id, td.tender_id, td.closing_time, td.open_date,
               t.closing_date
        FROM tender_details td
        JOIN tenders t ON t.id = td.tender_id
        WHERE t.source = %s
    """, (SOURCE,))
    rows = cursor.fetchall()
    cursor.close()

    total = len(rows)
    print(f"Records to backfill: {total:,}\n")

    updated = 0
    errors = 0

    update_cursor = conn.cursor()

    for i, row in enumerate(rows, 1):
        try:
            deadline_dt, deadline_tz = build_deadline_datetime(
                row['closing_date'], row['closing_time']
            )

            update_cursor.execute("""
                UPDATE tenders SET
                    status = 'open',
                    status_text = 'Open',
                    updated_at = NOW()
                WHERE id = %s
            """, (row['tender_id'],))

            update_cursor.execute("""
                UPDATE tender_details SET
                    deadline = %s,
                    deadline_timezone = %s,
                    publication_date = open_date,
                    updated_at = NOW()
                WHERE id = %s
            """, (deadline_dt, deadline_tz, row['id']))

            updated += 1

            if i % 500 == 0:
                conn.commit()
                print(f"  [{i}/{total}] committed...")

        except Exception as e:
            errors += 1
            print(f"  ✗ tender_id={row['tender_id']}: {e}")

    conn.commit()
    update_cursor.close()
    conn.close()

    print(f"\n{'=' * 80}")
    print(f"Done. Updated: {updated}  Errors: {errors}")
    print("=" * 80)


if __name__ == "__main__":
    try:
        backfill()
    except KeyboardInterrupt:
        print("\n✗ Interrupted by user")
    except Exception as e:
        print(f"\n✗ Fatal Error: {e}")
        import traceback
        traceback.print_exc()