"""Enriches fin_equity with standard industry identifiers:
  - FIGI & Composite FIGI  (via OpenFIGI free API)
  - CUSIP & ISIN           (via registry / Wikipedia / ISO 6166 Luhn check-digit)

Uses only standard libraries + requests + beautifulsoup4.
"""

import logging
import re
from typing import Dict, List, Optional

import requests
from bs4 import BeautifulSoup
from db import get_cursor

logger = logging.getLogger(__name__)

OPENFIGI_URL = "https://api.openfigi.com/v3/mapping"

OPENFIGI_HEADERS = {
    "Content-Type": "application/json",
}

WIKI_HEADERS = {
    "User-Agent": "FinLensResearch/1.0 (hemantabhusal13@gmail.com)",
}

# Standard reference CUSIP registry for major US equities
KNOWN_CUSIPS = {
    "AAPL": "037833100",
    "NVDA": "67066G104",
    "META": "30303M102",
    "NFLX": "64110L106",
    "ORCL": "68389X105",
    "AMD":  "007903107",
    "INTC": "458140100",
    "IBM":  "459200101",
    "QCOM": "747525103",
    "CSCO": "17275R102",
    "MSFT": "594918104",
    "GOOGL":"02079K305",
    "GOOG": "02079K107",
    "AMZN": "023135106",
    "TSLA": "88160R101",
    "AVGO": "11135F101",
    "DELL": "24703L202",
}


def compute_isin_from_cusip(cusip: str, country_code: str = "US") -> str:
    """Calculate the 12-character ISIN from a 9-character CUSIP using ISO 6166 Luhn mod 10."""
    raw = f"{country_code}{cusip.strip()}"
    digits = []
    for char in raw:
        if char.isdigit():
            digits.append(int(char))
        else:
            val = ord(char.upper()) - 55  # A=10, B=11, ...
            digits.extend([val // 10, val % 10])

    s = 0
    for i, d in enumerate(reversed(digits)):
        if i % 2 == 0:
            doubled = d * 2
            s += (doubled // 10) + (doubled % 10)
        else:
            s += d
    check_digit = (10 - (s % 10)) % 10
    return f"{country_code}{cusip.strip()}{check_digit}"


def fetch_openfigi_batch(tickers: List[str], chunk_size: int = 10) -> Dict[str, Dict[str, str]]:
    """Batch fetch FIGI and composite FIGI from OpenFIGI (max 10 per call for unauthenticated API)."""
    results: Dict[str, Dict[str, str]] = {}
    if not tickers:
        return results

    for i in range(0, len(tickers), chunk_size):
        chunk = tickers[i:i + chunk_size]
        payload = [{"idType": "TICKER", "idValue": t, "exchCode": "US"} for t in chunk]
        try:
            resp = requests.post(OPENFIGI_URL, headers=OPENFIGI_HEADERS, json=payload, timeout=20)
            resp.raise_for_status()
            data = resp.json()
            for t, item in zip(chunk, data):
                entries = item.get("data", [])
                if entries:
                    first = entries[0]
                    results[t] = {
                        "figi": first.get("figi"),
                        "composite_figi": first.get("compositeFIGI"),
                    }
        except Exception as exc:
            logger.warning("OpenFIGI batch lookup error for chunk %s: %s", chunk, exc)

    return results


def discover_cusip_wiki(ticker: str) -> Optional[str]:
    """Attempt to discover CUSIP from Wikipedia Infobox if not in known registry."""
    try:
        url = f"https://en.wikipedia.org/wiki/{ticker}"
        resp = requests.get(url, headers=WIKI_HEADERS, timeout=10)
        if resp.status_code == 200:
            soup = BeautifulSoup(resp.text, "html.parser")
            infobox = soup.find("table", class_="infobox")
            text = infobox.get_text() if infobox else resp.text
            match = re.search(r"US([0-9A-Z]{9})[0-9]", text)
            if match:
                return match.group(1)
    except Exception:
        pass
    return None


def enrich_fin_equity() -> Dict[str, int]:
    """Enrich fin_equity rows in DB with FIGI, composite FIGI, CUSIP, and ISIN."""
    stats = {"enriched": 0, "failed": 0}

    # 1. Read existing tickers from DB
    with get_cursor() as cur:
        cur.execute("SELECT ticker, cusip_no, isin_no, figi FROM fin_equity")
        rows = cur.fetchall()

    if not rows:
        logger.info("No rows in fin_equity to enrich.")
        return stats

    tickers = [r["ticker"] for r in rows if r.get("ticker")]
    logger.info("Enriching %d tickers: %s", len(tickers), ", ".join(tickers))

    # 2. Get OpenFIGI identifiers
    figi_map = fetch_openfigi_batch(tickers)

    # 3. Update each row in DB
    with get_cursor(commit=True) as cur:
        for r in rows:
            ticker = r["ticker"]
            try:
                # FIGI
                figis = figi_map.get(ticker, {})
                figi = figis.get("figi") or r.get("figi")

                # CUSIP & ISIN
                cusip = r.get("cusip_no") or KNOWN_CUSIPS.get(ticker) or discover_cusip_wiki(ticker)
                isin = r.get("isin_no")
                if cusip and not isin:
                    isin = compute_isin_from_cusip(cusip)

                cur.execute(
                    """
                    UPDATE fin_equity
                    SET figi = %s,
                        cusip_no = %s,
                        isin_no = %s
                    WHERE ticker = %s
                    """,
                    (figi, cusip, isin, ticker),
                )
                stats["enriched"] += 1
                logger.info(
                    "[%s] Enriched: FIGI=%s | CUSIP=%s | ISIN=%s",
                    ticker, figi, cusip, isin,
                )
            except Exception as exc:
                logger.error("[%s] Enrichment failed: %s", ticker, exc, exc_info=True)
                stats["failed"] += 1

    return stats


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
    enrich_fin_equity()
