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

logger = logging.getLogger(__name__)

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

    def get_sector_performance(self) -> pd.DataFrame:
        try:
            query = """
            WITH latest_date AS (
                SELECT MAX(trade_date) as max_date FROM sector_performance
            )
            SELECT 
                sector,
                change_percentage,
                trade_date
            FROM sector_performance
            WHERE trade_date = (SELECT max_date FROM latest_date)
            ORDER BY change_percentage DESC;
            """
            return self.db.execute_query(query)
        except Exception as e:
            logger.error(f"Error fetching sector performance: {e}")
            return pd.DataFrame()

    def get_sector_pe_ratios(self) -> pd.DataFrame:
        try:
            query = """
            WITH latest_date AS (
                SELECT MAX(trade_date) as max_date FROM sector_pe_ratios
            )
            SELECT 
                sector,
                pe_ratio,
                trade_date
            FROM sector_pe_ratios
            WHERE trade_date = (SELECT max_date FROM latest_date)
            ORDER BY pe_ratio DESC;
            """
            return self.db.execute_query(query)
        except Exception as e:
            logger.error(f"Error fetching sector PE ratios: {e}")
            return pd.DataFrame()

    def get_sector_historical(self) -> pd.DataFrame:
        try:
            query = """
            WITH latest_date AS (
                SELECT MAX(trade_date) as max_date FROM sector_historical
            )
            SELECT 
                trade_date,
                basic_materials,
                communication_services,
                consumer_cyclical,
                consumer_defensive,
                energy,
                financial_services,
                healthcare,
                industrials,
                real_estate,
                technology,
                utilities
            FROM sector_historical
            WHERE trade_date = (SELECT max_date FROM latest_date);
            """
            return self.db.execute_query(query)
        except Exception as e:
            logger.error(f"Error fetching sector historical data: {e}")
            return pd.DataFrame()

    def display(self):
        print("\n=== Sector Analysis ===")
        
        performance_data = self.get_sector_performance()
        if not performance_data.empty:
            print("\nCurrent Sector Performance:")
            print(tabulate(performance_data, headers='keys', tablefmt='fancy_grid',
                         floatfmt=".2f"))
        else:
            print("\nNo recent sector performance data available")

        pe_data = self.get_sector_pe_ratios()
        if not pe_data.empty:
            print("\nSector PE Ratios:")
            print(tabulate(pe_data, headers='keys', tablefmt='fancy_grid',
                         floatfmt=".2f"))
        else:
            print("\nNo recent sector PE ratios available")

        hist_data = self.get_sector_historical()
        if not hist_data.empty:
            print("\nSector Historical Performance:")
            print(tabulate(hist_data, headers='keys', tablefmt='fancy_grid',
                         floatfmt=".2f"))
        else:
            print("\nNo sector historical data available")