"""
Service to load and refresh ETF mapping tables from remote CSV sources.

Sources:
- CUSIP mapping: https://raw.githubusercontent.com/yoshishima/Stock_Data/refs/heads/master/CUSIP.csv
- ETF CIK mapping: https://raw.githubusercontent.com/yoshishima/Stock_Data/refs/heads/master/SEC_CIKs_Symbols.csv

These files are used to create local mapping tables for fast lookups.
"""

from typing import Optional
import csv
import io
import asyncio
import aiohttp
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import delete

from app.models.etf import CusipMap, ETFCIKMap


CUSIP_CSV_URL = "https://raw.githubusercontent.com/yoshishima/Stock_Data/refs/heads/master/CUSIP.csv"
CIK_CSV_URL = "https://raw.githubusercontent.com/yoshishima/Stock_Data/refs/heads/master/SEC_CIKs_Symbols.csv"


class ETFLoaderService:
    async def _fetch_text(self, url: str, timeout: int = 30) -> str:
        async with aiohttp.ClientSession(timeout=aiohttp.ClientTimeout(total=timeout)) as session:
            async with session.get(url) as resp:
                resp.raise_for_status()
                return await resp.text()

    async def refresh_cusip_map(self, db: AsyncSession) -> int:
        """Fetch CUSIP CSV and refresh table.
        Columns: cusip,symbol,description
        Returns number of rows inserted.
        """
        text = await self._fetch_text(CUSIP_CSV_URL)
        reader = csv.DictReader(io.StringIO(text))

        # Truncate existing and commit to avoid conflicts
        await db.execute(delete(CusipMap))
        await db.commit()

        count = 0
        to_add = []
        seen = set()
        for row in reader:
            cusip = (row.get("cusip") or "").strip()
            symbol = (row.get("symbol") or "").strip().upper()
            description = row.get("description")
            if not cusip or not symbol or cusip in seen:
                continue
            seen.add(cusip)
            to_add.append(CusipMap(cusip=cusip, symbol=symbol, description=description))
            count += 1

        if to_add:
            db.add_all(to_add)
        await db.commit()
        return count

    async def refresh_etf_cik_map(self, db: AsyncSession) -> int:
        """Fetch SEC CIKs CSV and refresh ETF CIK map.
        Expected columns include: Ticker, CIK or similar (we normalize).
        Returns number of rows inserted.
        """
        text = await self._fetch_text(CIK_CSV_URL)
        reader = csv.DictReader(io.StringIO(text))

        # Truncate existing and commit to avoid conflicts
        await db.execute(delete(ETFCIKMap))
        await db.commit()

        count = 0
        to_add = []
        seen_ticker = set()
        # Try to detect column names
        headers = [h.lower() for h in reader.fieldnames or []]
        ticker_key: Optional[str] = None
        cik_key: Optional[str] = None
        name_key: Optional[str] = None
        for h in headers:
            if h in ("ticker", "symbol"):
                ticker_key = h
            if h in ("cik", "ciknumber", "cik_num", "cik number"):
                cik_key = h
            if h in ("name", "companyname", "company name"):
                name_key = h

        # Fallback sensible defaults
        if ticker_key is None:
            ticker_key = "symbol" if "symbol" in headers else "ticker"
        if cik_key is None:
            cik_key = "cik"
        if name_key is None:
            name_key = "name" if "name" in headers else None

        for row in reader:
            ticker = (row.get(ticker_key) or "").strip().upper()
            cik_raw = (row.get(cik_key) or "").strip()
            if not ticker or not cik_raw or ticker in seen_ticker:
                continue
            seen_ticker.add(ticker)
            # Normalize CIK to digits only (leading zeros removed)
            digits = "".join(ch for ch in cik_raw if ch.isdigit())
            if not digits:
                continue
            name = (row.get(name_key) or "").strip() if name_key else None
            to_add.append(ETFCIKMap(ticker=ticker, cik=str(int(digits)), name=name))
            count += 1

        if to_add:
            db.add_all(to_add)
        await db.commit()
        return count

    async def refresh_all(self, db: AsyncSession) -> dict:
        cusips = await self.refresh_cusip_map(db)
        etfs = await self.refresh_etf_cik_map(db)
        return {"cusip_rows": cusips, "etf_rows": etfs}


etf_loader_service = ETFLoaderService()