import re
import time
import random
from dataclasses import dataclass
from datetime import datetime, timezone, timedelta
from decimal import Decimal, InvalidOperation
from typing import Optional, Callable
import mysql.connector
from mysql.connector import Error as MySQLError
from playwright.sync_api import sync_playwright


DB_CONFIG = {
    "host": "dev1.atguru.work",
    "port": 3306,
    "user": "eslamayman",
    "password": "AymanE@1",
    "database": "eslamayman",
    "connection_timeout": 15,
}

TABLE_NAME = "fin_equity"
BASE_URL = "https://finance.yahoo.com/quote/{ticker}/"
PRICE_SELECTOR = 'span[data-testid="qsp-price"]'

MAX_RETRIES = 3
INITIAL_RETRY_DELAY = 2
MAX_RETRY_DELAY = 30
RATE_LIMIT_COOLDOWN = 60
MAX_RATE_LIMIT_COOLDOWN = 180
MIN_DELAY_SEC = 0.5
MAX_DELAY_SEC = 1.5
PAGE_TIMEOUT = 45000
PRICE_TIMEOUT = 30000
HEADLESS = True

EST = timezone(timedelta(hours=-5))

@dataclass
class EquityRecord:
    equity_id: int
    ticker: str


@dataclass
class QuoteResult:
    equity: EquityRecord
    price: Optional[Decimal] = None
    attempts: int = 0
    error: Optional[str] = None
    db_error: Optional[str] = None
    saved: bool = False


def print_header():
    print("=" * 60)
    print("             YAHOO FINANCE DATA COLLECTION")
    print("=" * 60)
    print()


def print_step(number: int, description: str):
    print(f"[{number}] {description}")


def update_progress(index: int, total: int, ticker: str, step: str):
    message = f"[{index}/{total}] {ticker} | {step}"
    print(f"\r\033[2K{message}", end="", flush=True)


def clear_progress():
    print("\r\033[2K", end="", flush=True)


def get_est_time() -> datetime:
    return datetime.now(EST).replace(tzinfo=None)


def format_duration(seconds: float) -> str:
    total_seconds = int(seconds)

    hours, remainder = divmod(total_seconds, 3600)
    minutes, seconds = divmod(remainder, 60)

    if hours:
        return f"{hours}h {minutes}m {seconds}s"

    if minutes:
        return f"{minutes}m {seconds}s"

    return f"{seconds}s"


def clean_ticker(value: str) -> str:
    if not isinstance(value, str):
        raise ValueError("Invalid ticker format")

    ticker = value.strip().upper()

    if not ticker:
        raise ValueError("Empty ticker")

    if not re.fullmatch(r"[A-Z0-9.^=-]+", ticker):
        raise ValueError(f"Invalid ticker: {ticker}")

    return ticker


def parse_price(value: str) -> Decimal:
    if not value:
        raise ValueError("Empty price")

    cleaned = value.strip().replace(",", "")

    try:
        price = Decimal(cleaned)
    except InvalidOperation:
        raise ValueError(f"Invalid price format: {value}")

    if not price.is_finite() or price <= 0:
        raise ValueError(f"Invalid price value: {price}")

    return price


def connect_database():
    if not DB_CONFIG["password"]:
        raise RuntimeError("Database password is not configured")

    return mysql.connector.connect(**DB_CONFIG)


def get_active_equities(connection) -> list[EquityRecord]:
    query = f"""
        SELECT idfin_equity, ticker
        FROM `{TABLE_NAME}`
        WHERE chk_stat = %s
        ORDER BY idfin_equity
    """

    cursor = connection.cursor()

    try:
        cursor.execute(query, ("Y",))
        rows = cursor.fetchall()

        return [
            EquityRecord(
                equity_id=row[0],
                ticker=str(row[1]).strip() if row[1] is not None else "",
            )
            for row in rows
        ]

    finally:
        cursor.close()


def update_equity_price(
    connection,
    equity_id: int,
    price: Decimal
):
    query = f"""
        UPDATE `{TABLE_NAME}`
        SET last_pr = %s, chk_lst_dt = %s
        WHERE idfin_equity = %s
    """

    cursor = connection.cursor()

    try:
        cursor.execute(
            query,
            (
                str(price),
                get_est_time(),
                equity_id,
            ),
        )

        if cursor.rowcount == 0:
            cursor.execute(
                f"""
                    SELECT idfin_equity
                    FROM `{TABLE_NAME}`
                    WHERE idfin_equity = %s
                """,
                (equity_id,),
            )

            if cursor.fetchone() is None:
                raise RuntimeError("Equity record does not exist")

        connection.commit()

    except Exception:
        connection.rollback()
        raise

    finally:
        cursor.close()


def navigate_to_ticker(page, ticker: str):
    url = BASE_URL.format(ticker=ticker)

    response = page.goto(
        url,
        wait_until="domcontentloaded",
        timeout=PAGE_TIMEOUT,
    )

    if response is not None:
        if response.status == 429:
            raise RuntimeError("HTTP 429: Yahoo Finance rate limit")

        if response.status >= 400:
            raise RuntimeError(
                f"HTTP {response.status} while loading Yahoo Finance"
            )


def fetch_quote_once(
    page,
    ticker: str,
    progress_callback: Callable,
    index: int,
    total: int,
) -> Decimal:

    progress_callback(
        index, total, ticker, "Opening Yahoo Finance"
    )

    navigate_to_ticker(page, ticker)

    progress_callback(
        index, total, ticker, "Extracting price"
    )

    price_element = page.locator(PRICE_SELECTOR).first

    price_element.wait_for(
        state="visible",
        timeout=PRICE_TIMEOUT,
    )

    raw_price = price_element.inner_text().strip()

    return parse_price(raw_price)


def fetch_quote_with_retry(
    page,
    equity: EquityRecord,
    index: int,
    total: int,
) -> QuoteResult:

    result = QuoteResult(equity=equity)

    try:
        ticker = clean_ticker(equity.ticker)
        equity.ticker = ticker

    except ValueError as exc:
        result.error = str(exc)
        return result

    for attempt in range(1, MAX_RETRIES + 1):
        result.attempts = attempt

        try:
            result.price = fetch_quote_once(
                page,
                ticker,
                update_progress,
                index,
                total,
            )

            return result

        except Exception as exc:
            error_message = str(exc).strip() or type(exc).__name__

            is_rate_limit = (
                "429" in error_message
                or "rate limit" in error_message.lower()
            )

            if attempt >= MAX_RETRIES:
                result.error = error_message
                return result

            clear_progress()

            if is_rate_limit:
                delay = min(
                    RATE_LIMIT_COOLDOWN * attempt,
                    MAX_RATE_LIMIT_COOLDOWN,
                )

                print(
                    f"  Retry {attempt}/{MAX_RETRIES - 1} "
                    f"| {ticker} | Rate limited | Waiting {delay}s"
                )

            else:
                delay = min(
                    INITIAL_RETRY_DELAY * (2 ** (attempt - 1)),
                    MAX_RETRY_DELAY,
                )

                print(
                    f"  Retry {attempt}/{MAX_RETRIES - 1} "
                    f"| {ticker} | {error_message} | Waiting {delay}s"
                )

            time.sleep(delay)

    return result


def process_tickers(
    connection,
    equities: list[EquityRecord],
) -> list[QuoteResult]:

    results = []
    total = len(equities)

    with sync_playwright() as playwright:
        browser = playwright.chromium.launch(
            headless=HEADLESS
        )

        try:
            page = browser.new_page()
            page.set_default_navigation_timeout(PAGE_TIMEOUT)

            for index, equity in enumerate(equities, start=1):

                result = fetch_quote_with_retry(
                    page,
                    equity,
                    index,
                    total,
                )

                if result.error:
                    clear_progress()

                    print(
                        f"{equity.ticker or 'UNKNOWN':<10} | FAILED  | "
                        f"{result.error}"
                    )

                    results.append(result)
                    continue

                update_progress(
                    index,
                    total,
                    equity.ticker,
                    "Updating MySQL",
                )

                try:
                    update_equity_price(
                        connection,
                        equity.equity_id,
                        result.price,
                    )

                    result.saved = True

                except Exception as exc:
                    result.db_error = (
                        str(exc).strip() or type(exc).__name__
                    )

                clear_progress()

                if result.saved:
                    print(
                        f"{equity.ticker:<10} | UPDATED | {result.price}"
                    )

                else:
                    print(
                        f"{equity.ticker:<10} | FAILED  | "
                        f"Database update error: {result.db_error}"
                    )

                results.append(result)

                if index < total:
                    time.sleep(
                        random.uniform(
                            MIN_DELAY_SEC,
                            MAX_DELAY_SEC,
                        )
                    )

        finally:
            browser.close()

    return results


def print_summary(
    results: list[QuoteResult],
    execution_time: float,
):
    total = len(results)
    successful = sum(result.saved for result in results)
    failed = total - successful

    print()
    print("=" * 60)
    print("SUMMARY")
    print("=" * 60)

    print(f"Total tickers        : {total}")
    print(f"Successfully updated : {successful}")
    print(f"Failed               : {failed}")
    print(f"Execution time       : {format_duration(execution_time)}")

    print("=" * 60)


def main():
    start_time = time.perf_counter()

    print_header()

    connection = None
    results = []
    run_successful = False

    try:
        print_step(1, "Connecting to MySQL...")

        connection = connect_database()

        print("      Connected successfully.")
        print()

        print_step(2, "Loading active tickers...")

        equities = get_active_equities(connection)

        print(f"      Found {len(equities)} active tickers.")
        print()

        if not equities:
            print("No active tickers found.")
            run_successful = True
            return

        print_step(3, "Launching browser...")

        results = process_tickers(
            connection,
            equities,
        )

        print()
        execution_time = time.perf_counter() - start_time

        print_summary(
            results,
            execution_time,
        )

        run_successful = all(
            result.saved for result in results
        )

    except MySQLError as exc:
        print()
        print(f"Database error: {exc}")

    except Exception as exc:
        print()
        print(f"Process error: {exc}")

    finally:
        if connection is not None:
            try:
                if connection.is_connected():
                    connection.close()
            except MySQLError:
                run_successful = False

        if run_successful:
            print()
            print(
                "Run completed | Browser and MySQL connection closed."
            )

        else:
            print()
            print(
                "Run ended | Resources released where possible."
            )


if __name__ == "__main__":
    main()
