# modules/signals/commodities_view.py
import pandas as pd
from tabulate import tabulate
import logging

logger = logging.getLogger(__name__)

class CommoditiesView:
    def __init__(self, db_connection):
        self.db = db_connection

    def get_commodity_prices(self) -> pd.DataFrame:
        try:
            query = """
            WITH latest_date AS (
                SELECT MAX(date) as max_date FROM commodities_prices
            )
            SELECT 
                cr.symbol,
                cr.name,
                cp.close as last_price,
                cp.close - cp.open as daily_change,
                ((cp.close - cp.open) / cp.open * 100) as daily_change_percent,
                cp.date
            FROM commodities_ref cr
            JOIN commodities_prices cp ON cp.commodity_id = cr.id
            WHERE cp.date = (SELECT max_date FROM latest_date)
            ORDER BY ABS(cp.close - cp.open) DESC;
            """
            return self.db.execute_query(query)
        except Exception as e:
            logger.error(f"Error fetching commodity prices: {e}")
            return pd.DataFrame()

    def display(self):
        print("\n=== Commodities Market Overview ===")
        
        prices_data = self.get_commodity_prices()
        if not prices_data.empty:
            print("\nCommodity Prices and Trading Activity:")
            print(tabulate(prices_data, headers='keys', tablefmt='fancy_grid',
                         floatfmt=".2f"))
        else:
            print("\nNo recent commodities data available")