import os
import time
from datetime import date
from pathlib import Path

import pandas as pd
from loguru import logger

from ..db.session import get_db, get_db_session
from ..db.queries import QueryLoader
from ..config.paths import QUERY_PATHS, PATHS
from ..config.tables import TABLES
from ..config.email import SPOTIFY_CONFIG


class DataService:
    def __init__(self):
        self.db = get_db()
        self.dbs = get_db_session()
        self.query_loader = QueryLoader()
    
    def generate(self, target_date: date | None = None) -> bool:
        report_date = target_date or self._get_latest_date()
        logger.info(f"Generating dataset for {report_date}")
        
        data = self._get_dataset(report_date)
        if data.empty:
            logger.warning("Dataset is empty")
            return False
        
        data = self._fill_missing_artwork(data)
        
        filepath = PATHS.reports / f"{report_date}.csv"
        data.to_csv(filepath, index=False, encoding="utf-8-sig")
        logger.success(f"Saved report to {filepath}")
        
        return True
    
    def _get_latest_date(self) -> date:
        query = f"SELECT MAX(report_date) as latest_date FROM {TABLES['main']}"
        result = self.db.query(query)
        return result["latest_date"][0]
    
    def _get_dataset(self, report_date: date) -> pd.DataFrame:
        try:
            queries = [
                ("main-tracks", QUERY_PATHS.main_tracks),
                ("main-streams", QUERY_PATHS.main_streams),
                ("main-s200", QUERY_PATHS.main_s200),
                ("main-metadata", QUERY_PATHS.main_metadata),
                ("main-tiktok", QUERY_PATHS.main_tiktok),
                ("main-shazam", QUERY_PATHS.main_shazam),
                ("main-meta", QUERY_PATHS.main_meta),
            ]
            
            for name, path in queries:
                query = self.query_loader.load_with_date(path, str(report_date))
                self.dbs.execute(query)
            
            self.dbs.commit()
            self._copy_into_history(report_date)
            
            query = self.query_loader.load_with_date(QUERY_PATHS.main, str(report_date))
            return self.db.query(query)
            
        except Exception as e:
            logger.error(f"Failed to generate dataset: {e}")
            return pd.DataFrame()
    
    def _copy_into_history(self, report_date: date) -> None:
        check_query = self.query_loader.load_with_date(QUERY_PATHS.check_history, str(report_date))
        result = self.db.query(check_query)
        
        if result["filled"][0]:
            logger.debug(f"History already filled for {report_date}")
            return
        
        try:
            logger.info(f"Copying {report_date} into history table")
            copy_query = self.query_loader.load_with_date(QUERY_PATHS.copy_history, str(report_date))
            self.dbs.execute(copy_query)
            self.dbs.commit()
        except Exception as e:
            logger.error(f"Failed to copy history: {e}")
    
    def _fill_missing_artwork(self, data: pd.DataFrame) -> pd.DataFrame:
        na_mask = data["artwork_url"].isna() | (data["artwork_url"].astype(str).str.strip() == "")
        to_fill = data.loc[na_mask, "isrc_cd"].dropna().astype(str).str.strip()
        
        if to_fill.empty:
            logger.debug("No missing artwork to fill")
            return data
        
        if not SPOTIFY_CONFIG.client_id or not SPOTIFY_CONFIG.client_secret:
            logger.warning("Spotify credentials not configured, skipping artwork fill")
            return data
        
        try:
            from djagitit.api import spotify
            sp = spotify.connect_spotify(SPOTIFY_CONFIG.client_id, SPOTIFY_CONFIG.client_secret)
        except Exception as e:
            logger.error(f"Failed to connect to Spotify: {e}")
            return data
        
        unique_isrcs = to_fill.unique().tolist()
        logger.info(f"Filling artwork for {len(unique_isrcs)} ISRCs ({na_mask.sum()} rows)")
        
        cache: dict[str, str] = {}
        filled = 0
        
        for isrc in unique_isrcs:
            if not isrc:
                continue
            
            if isrc in cache:
                url = cache[isrc]
            else:
                url = self._fetch_artwork_url(sp, isrc)
                cache[isrc] = url or ""
                time.sleep(0.05)
            
            if url:
                row_mask = na_mask & (data["isrc_cd"].astype(str).str.strip() == isrc)
                data.loc[row_mask, "artwork_url"] = url
                filled += int(row_mask.sum())
        
        logger.info(f"Filled artwork for {filled}/{na_mask.sum()} rows")
        return data
    
    @staticmethod
    def _fetch_artwork_url(sp, isrc: str) -> str | None:
        try:
            result = sp.search(q=f"isrc:{isrc}", type="track", limit=1)
            items = (result or {}).get("tracks", {}).get("items", [])
            if not items:
                return None
            
            images = items[0].get("album", {}).get("images", [])
            if not images:
                return None
            
            best = min(images, key=lambda im: abs((im.get("width") or 0) - 300))
            return best.get("url")
        except Exception:
            return None