"""
Indicator calculation module.
Provides IndicatorCalculator for computing various financial indicators
from SQLite candle data, including ratios, prices, moving averages, RSI,
and custom functions.
"""

import json
import os
import psycopg2
from contextlib import closing
import importlib
import logging
import pandas as pd
import numpy as np


class IndicatorCalculator:
    """
    Computes indicator values from SQLite candle data.
    Supports ratio, price, spread, diff_pct, ma, rsi, and custom types.
    """

    def __init__(self, config_path, db_path):
        self.config_path = config_path
        self.db_path = db_path
        self.config = self._load_config()

    def _load_config(self):
        """Load indicator definitions from JSON config file."""
        try:
            with open(self.config_path, 'r', encoding='utf-8') as f:
                return json.load(f)
        except (FileNotFoundError, json.JSONDecodeError) as e:
            logging.error(f"Failed to load indicators config from '{self.config_path}': {e}")
            return {}

    def _get_latest_close(self, coin, timeframe="1m"):
        """Get the latest close price from a candle table."""
        table = f"{coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT close FROM "{table}" ORDER BY timestamp_ms DESC LIMIT 1'
                ).fetchone()
                return float(result[0]) if result and result[0] is not None else None
        except Exception as e:
            logging.debug(f"Could not get latest close for {coin} ({timeframe}): {e}")
            return None

    def _get_close_n_candles_ago(self, coin, timeframe, n=1):
        """Get the close price from n candles ago (n=1 = most recent completed candle)."""
        table = f"{coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT close FROM "{table}" ORDER BY timestamp_ms DESC LIMIT 1 OFFSET {n}'
                ).fetchone()
                return float(result[0]) if result and result[0] is not None else None
        except Exception as e:
            logging.debug(f"Could not get close {n} candles ago for {coin} ({timeframe}): {e}")
            return None

    def _get_all_closes(self, coin, timeframe="1d"):
        """Get all close prices from a candle table, ordered by time."""
        table = f"{coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT close FROM "{table}" ORDER BY timestamp_ms'
                ).fetchall()
                return [float(r[0]) for r in result if r[0] is not None]
        except Exception as e:
            logging.debug(f"Could not get all closes for {coin} ({timeframe}): {e}")
            return []

    def _get_all_ratio(self, num_coin, den_coin, timeframe="1d"):
        """Get all ratio values (num/den) from candle tables, ordered by time."""
        num_table = f"{num_coin.replace(':', '_')}_{timeframe}"
        den_table = f"{den_coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT n.close / d.close as ratio '
                    f'FROM "{num_table}" n '
                    f'JOIN "{den_table}" d ON n.timestamp_ms = d.timestamp_ms '
                    f'ORDER BY n.timestamp_ms'
                ).fetchall()
                return [float(r[0]) for r in result if r[0] is not None]
        except Exception as e:
            logging.debug(f"Could not get ratio series for {num_coin}/{den_coin} ({timeframe}): {e}")
            return []

    def _get_all_spread(self, num_coin, den_coin, timeframe="1d"):
        """Get all spread values (num - den) from candle tables, ordered by time."""
        num_table = f"{num_coin.replace(':', '_')}_{timeframe}"
        den_table = f"{den_coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT n.close - d.close as spread '
                    f'FROM "{num_table}" n '
                    f'JOIN "{den_table}" d ON n.timestamp_ms = d.timestamp_ms '
                    f'ORDER BY n.timestamp_ms'
                ).fetchall()
                return [float(r[0]) for r in result if r[0] is not None]
        except Exception as e:
            logging.debug(f"Could not get spread series for {num_coin}/{den_coin} ({timeframe}): {e}")
            return []

    def _get_all_diff_pct(self, num_coin, den_coin, timeframe="1d"):
        """Get all percentage difference values ((num-den)/den*100) from candle tables."""
        num_table = f"{num_coin.replace(':', '_')}_{timeframe}"
        den_table = f"{den_coin.replace(':', '_')}_{timeframe}"
        try:
            with closing(psycopg2.connect(self.db_path)) as conn:
                result = conn.execute(
                    f'SELECT (n.close - d.close) / d.close * 100 as diff_pct '
                    f'FROM "{num_table}" n '
                    f'JOIN "{den_table}" d ON n.timestamp_ms = d.timestamp_ms '
                    f'ORDER BY n.timestamp_ms'
                ).fetchall()
                return [float(r[0]) for r in result if r[0] is not None]
        except Exception as e:
            logging.debug(f"Could not get diff_pct series for {num_coin}/{den_coin} ({timeframe}): {e}")
            return []

    def _compute_ma(self, closes, period):
        """Compute Simple Moving Average using pandas."""
        if len(closes) < period:
            return []
        series = pd.Series(closes)
        ma = series.rolling(window=period).mean()
        return ma.dropna().tolist()

    def _compute_rsi(self, closes, period):
        """Compute RSI using Wilder's smoothing method."""
        if len(closes) < period + 1:
            return []
        series = pd.Series(closes)
        delta = series.diff()
        gain = delta.where(delta > 0, 0)
        loss = (-delta).where(delta < 0, 0)
        avg_gain = gain.rolling(window=period, min_periods=period).mean()
        avg_loss = loss.rolling(window=period, min_periods=period).mean()
        rs = avg_gain / avg_loss.replace(0, np.nan)
        rsi = 100 - (100 / (1 + rs))
        return rsi.dropna().tolist()

    def _get_ma_value(self, coin, timeframe, period, n_candles_ago=0):
        """Get MA value from n candles ago (0 = latest, 1 = second-to-last)."""
        closes = self._get_all_closes(coin, timeframe)
        if not closes:
            return None
        ma_values = self._compute_ma(closes, period)
        if not ma_values:
            return None
        if n_candles_ago < len(ma_values):
            return ma_values[-(1 + n_candles_ago)]
        return None

    def _get_rsi_value(self, coin, timeframe, period, n_candles_ago=0):
        """Get RSI value from n candles ago (0 = latest, 1 = second-to-last)."""
        closes = self._get_all_closes(coin, timeframe)
        if not closes:
            return None
        rsi_values = self._compute_rsi(closes, period)
        if not rsi_values:
            return None
        if n_candles_ago < len(rsi_values):
            return rsi_values[-(1 + n_candles_ago)]
        return None

    def _format_change(self, current, past):
        """Compute percentage change between two values."""
        if past is None or past == 0 or current is None:
            return None
        return (current - past) / past * 100

    def calculate_indicator(self, ind_def):
        """
        Calculate a single indicator based on its definition.
        Returns a dict with value, changes, reference, and deviation.
        """
        ind_type = ind_def.get("type", "price")

        if ind_type == "ratio":
            return self._calc_ratio(ind_def)
        elif ind_type == "price":
            return self._calc_price(ind_def)
        elif ind_type == "spread":
            return self._calc_spread(ind_def)
        elif ind_type == "diff_pct":
            return self._calc_diff_pct(ind_def)
        elif ind_type == "ma":
            return self._calc_ma(ind_def)
        elif ind_type == "rsi":
            return self._calc_rsi(ind_def)
        elif ind_type == "custom":
            return self._calc_custom(ind_def)
        else:
            logging.warning(f"Unknown indicator type: {ind_type}")
            return None

    def _calc_ratio(self, ind_def):
        """Calculate a ratio indicator (numerator / denominator)."""
        num = ind_def["numerator"]
        den = ind_def["denominator"]

        num_now = self._get_latest_close(num)
        den_now = self._get_latest_close(den)
        if num_now is None or den_now is None or den_now == 0:
            return None
        current = num_now / den_now

        changes = {}
        for period in ind_def.get("changes", []):
            num_past = self._get_close_n_candles_ago(num, period, n=1)
            den_past = self._get_close_n_candles_ago(den, period, n=1)
            if num_past is not None and den_past is not None and den_past != 0:
                past = num_past / den_past
                changes[period] = self._format_change(current, past)
            else:
                changes[period] = None

        reference = None
        deviation = None
        if ind_def.get("show_deviation", False):
            ratios = self._get_all_ratio(num, den, "1d")
            if ratios:
                min_points = ind_def.get("min_data_points", 100)
                fallback_ref = ind_def.get("fallback_reference")
                if len(ratios) < min_points and fallback_ref is not None:
                    reference = fallback_ref
                else:
                    reference = sum(ratios) / len(ratios)
                deviation = self._format_change(current, reference)

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_price(self, ind_def):
        """Calculate a single price indicator."""
        coin = ind_def["coin"]

        current = self._get_latest_close(coin)
        if current is None:
            return None

        changes = {}
        for period in ind_def.get("changes", []):
            past = self._get_close_n_candles_ago(coin, period, n=1)
            changes[period] = self._format_change(current, past)

        reference = None
        deviation = None
        if ind_def.get("show_deviation", False):
            closes = self._get_all_closes(coin, "1d")
            if closes:
                min_points = ind_def.get("min_data_points", 100)
                fallback_ref = ind_def.get("fallback_reference")
                if len(closes) < min_points and fallback_ref is not None:
                    reference = fallback_ref
                else:
                    reference = sum(closes) / len(closes)
                deviation = self._format_change(current, reference)

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_spread(self, ind_def):
        """Calculate a spread indicator (numerator - denominator)."""
        num = ind_def["numerator"]
        den = ind_def["denominator"]

        num_now = self._get_latest_close(num)
        den_now = self._get_latest_close(den)
        if num_now is None or den_now is None:
            return None
        current = num_now - den_now

        changes = {}
        for period in ind_def.get("changes", []):
            num_past = self._get_close_n_candles_ago(num, period, n=1)
            den_past = self._get_close_n_candles_ago(den, period, n=1)
            if num_past is not None and den_past is not None:
                past = num_past - den_past
                changes[period] = self._format_change(current, past)
            else:
                changes[period] = None

        reference = None
        deviation = None
        if ind_def.get("show_deviation", False):
            spreads = self._get_all_spread(num, den, "1d")
            if spreads:
                min_points = ind_def.get("min_data_points", 100)
                fallback_ref = ind_def.get("fallback_reference")
                if len(spreads) < min_points and fallback_ref is not None:
                    reference = fallback_ref
                else:
                    reference = sum(spreads) / len(spreads)
                deviation = self._format_change(current, reference)

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_diff_pct(self, ind_def):
        """Calculate a percentage difference indicator ((num-den)/den*100)."""
        num = ind_def["numerator"]
        den = ind_def["denominator"]

        num_now = self._get_latest_close(num)
        den_now = self._get_latest_close(den)
        if num_now is None or den_now is None or den_now == 0:
            return None
        current = (num_now - den_now) / den_now * 100

        changes = {}
        for period in ind_def.get("changes", []):
            num_past = self._get_close_n_candles_ago(num, period, n=1)
            den_past = self._get_close_n_candles_ago(den, period, n=1)
            if num_past is not None and den_past is not None and den_past != 0:
                past = (num_past - den_past) / den_past * 100
                changes[period] = self._format_change(current, past)
            else:
                changes[period] = None

        reference = None
        deviation = None
        if ind_def.get("show_deviation", False):
            diffs = self._get_all_diff_pct(num, den, "1d")
            if diffs:
                min_points = ind_def.get("min_data_points", 100)
                fallback_ref = ind_def.get("fallback_reference")
                if len(diffs) < min_points and fallback_ref is not None:
                    reference = fallback_ref
                else:
                    reference = sum(diffs) / len(diffs)
                deviation = self._format_change(current, reference)

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_ma(self, ind_def):
        """Calculate a moving average indicator."""
        coin = ind_def["coin"]
        timeframe = ind_def.get("timeframe", "1h")
        period = ind_def.get("period", 20)

        current = self._get_ma_value(coin, timeframe, period, n_candles_ago=0)
        if current is None:
            return None

        changes = {}
        for period_label in ind_def.get("changes", []):
            if period_label == "1h":
                past = self._get_ma_value(coin, "1h", period, n_candles_ago=1)
            elif period_label == "1d":
                past = self._get_ma_value(coin, "1d", period, n_candles_ago=1)
            else:
                past = self._get_ma_value(coin, period_label, period, n_candles_ago=1)
            changes[period_label] = self._format_change(current, past)

        reference = None
        deviation = None
        if ind_def.get("show_deviation", False):
            live_price = self._get_latest_close(coin)
            if live_price is not None and current != 0:
                reference = current
                deviation = (live_price - current) / current * 100

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_rsi(self, ind_def):
        """Calculate an RSI indicator."""
        coin = ind_def["coin"]
        timeframe = ind_def.get("timeframe", "1h")
        period = ind_def.get("period", 14)

        current = self._get_rsi_value(coin, timeframe, period, n_candles_ago=0)
        if current is None:
            return None

        changes = {}
        for period_label in ind_def.get("changes", []):
            if period_label == "1h":
                past = self._get_rsi_value(coin, "1h", period, n_candles_ago=1)
            elif period_label == "1d":
                past = self._get_rsi_value(coin, "1d", period, n_candles_ago=1)
            else:
                past = self._get_rsi_value(coin, period_label, period, n_candles_ago=1)
            if past is not None:
                changes[period_label] = current - past
            else:
                changes[period_label] = None

        reference = 50.0
        deviation = None
        if ind_def.get("show_deviation", False):
            deviation = current - 50.0

        return {"value": current, "reference": reference, "changes": changes, "deviation": deviation}

    def _calc_custom(self, ind_def):
        """Calculate a custom indicator by calling a user-defined function."""
        module_path = ind_def.get("module")
        function_name = ind_def.get("function")
        args = ind_def.get("args", {})

        if not module_path or not function_name:
            logging.error(f"Custom indicator missing 'module' or 'function': {ind_def}")
            return None

        try:
            module = importlib.import_module(module_path)
            func = getattr(module, function_name)
        except (ImportError, AttributeError) as e:
            logging.error(f"Failed to load custom indicator {module_path}.{function_name}: {e}")
            return None

        try:
            result = func(self.db_path, **args)
            if not isinstance(result, dict):
                logging.error(f"Custom indicator {function_name} must return a dict, got {type(result)}")
                return None
            return result
        except Exception as e:
            logging.error(f"Custom indicator {function_name} raised an error: {e}", exc_info=True)
            return None

    def calculate_all(self):
        """Calculate all indicators defined in the config file."""
        results = {}
        for name, ind_def in self.config.items():
            result = self.calculate_indicator(ind_def)
            if result:
                results[name] = {
                    "display_name": ind_def.get("display_name", name),
                    **result
                }
            else:
                results[name] = {
                    "display_name": ind_def.get("display_name", name),
                    "value": None,
                    "reference": None,
                    "changes": {},
                    "deviation": None
                }
        return results