from datetime import datetime, timedelta
import pandas as pd
import pytz
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import logging
from supabase_utils import get_supabase_client
from config import STATION_NAMES

# Basic logging configuration
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def fetch_tide_data(station_id, start_utc, end_utc, table='historical_tide'):
    """Fetches data from a specified Supabase table within a date range."""
    supabase = get_supabase_client()
    if not supabase:
        logging.error("Failed to create a Supabase client.")
        raise ValueError("Supabase 클라이언트를 생성할 수 없습니다.")

    try:
        query_column = 'observed_at' if table == 'historical_tide' else 'predicted_at'
        select_columns = 'observed_at, tide_level' if table == 'historical_tide' else 'predicted_at, final_tide_level'

        result = supabase.table(table) \
            .select(select_columns) \
            .eq('station_id', station_id) \
            .gte(query_column, start_utc) \
            .lte(query_column, end_utc) \
            .order(query_column) \
            .execute()

        if not result.data:
            logging.warning(f"No data found for station {station_id} from {start_utc} to {end_utc} in table '{table}'.")
            return pd.DataFrame()  # Return empty DataFrame for robustness

        return pd.DataFrame(result.data)
    except Exception as e:
        logging.error(f"Error fetching data for station {station_id}: {e}", exc_info=True)
        raise ValueError(f"데이터 조회 오류: {e}")

def get_tide_data(station_id, start_date=None, end_date=None, include_extremes=False, return_plot=False):
    """
    Retrieves and processes tide data, optionally including tide extremes and a plot.

    :param station_id: The station identifier.
    :param start_date: Start date in 'YYYY-MM-DD' format. Defaults to today.
    :param end_date: End date in 'YYYY-MM-DD' format. Defaults to the start date.
    :param include_extremes: Whether to calculate and include tidal extremes (high/low tides).
    :param return_plot: Whether to generate and return a Plotly figure.
    :return: A dictionary containing the data, and optionally extremes and a plot.
    """
    # Default to today (in Seoul timezone) if start_date is not provided.
    start_date = start_date or datetime.now(pytz.timezone('Asia/Seoul')).strftime('%Y-%m-%d')
    start_time = pytz.timezone('Asia/Seoul').localize(datetime.strptime(start_date, '%Y-%m-%d'))

    # The query period ends 24 hours after the start of the end_date.
    # If no end_date is given, the period is 24 hours from the start_time.
    end_time = start_time + timedelta(hours=24) if not end_date else \
               pytz.timezone('Asia/Seoul').localize(datetime.strptime(end_date, '%Y-%m-%d')) + timedelta(hours=24)

    # Convert local time to UTC for the database query.
    start_utc = start_time.astimezone(pytz.UTC).isoformat()
    end_utc = end_time.astimezone(pytz.UTC).isoformat()

    df = fetch_tide_data(station_id, start_utc, end_utc)
    if df.empty:
        logging.warning(f"No tide data available for station {station_id} for the selected period.")
        return {"data": pd.DataFrame(), "extremes": pd.DataFrame(), "plot": go.Figure()}

    df['observed_at'] = pd.to_datetime(df['observed_at']).dt.tz_convert('Asia/Seoul')
    df['tide_level'] = pd.to_numeric(df['tide_level'])

    result = {"data": df}
    
    if include_extremes:
        df['min'] = df.tide_level[(df.tide_level.shift(1) > df.tide_level) & (df.tide_level.shift(-1) > df.tide_level)]
        df['max'] = df.tide_level[(df.tide_level.shift(1) < df.tide_level) & (df.tide_level.shift(-1) < df.tide_level)]
        extremes_df = df.dropna(subset=['min', 'max'], how='all').copy()
        extremes_df['type'] = extremes_df.apply(lambda row: 'High Tide' if pd.notna(row['max']) else 'Low Tide', axis=1)
        extremes_df['value'] = extremes_df.apply(lambda row: row['max'] if pd.notna(row['max']) else row['min'], axis=1)
        extremes_df['time'] = extremes_df['observed_at'].dt.strftime('%H:%M')
        result["extremes"] = extremes_df[['time', 'type', 'value']]

    if return_plot:
        fig = go.Figure()
        fig.add_trace(go.Scatter(x=df['observed_at'], y=df['tide_level'], mode='lines',
                                name=f'{STATION_NAMES.get(station_id, station_id)} Tide'))
        fig.update_layout(
            title=f'{STATION_NAMES.get(station_id, station_id)} Tide: {start_date} to {end_date or start_date}',
            xaxis_title='Time', yaxis_title='Tide Level (cm)', height=400
        )
        result["plot"] = fig

    return result

def compare_tide_patterns(station_id, date1, date2, time_window=24):
    """두 날짜의 조위 패턴 비교"""
    start1 = pytz.timezone('Asia/Seoul').localize(datetime.strptime(date1, '%Y-%m-%d'))
    start2 = pytz.timezone('Asia/Seoul').localize(datetime.strptime(date2, '%Y-%m-%d'))
    end1 = start1 + timedelta(hours=time_window)
    end2 = start2 + timedelta(hours=time_window)

    df1 = fetch_tide_data(station_id, start1.astimezone(pytz.UTC).isoformat(), end1.astimezone(pytz.UTC).isoformat())
    df2 = fetch_tide_data(station_id, start2.astimezone(pytz.UTC).isoformat(), end2.astimezone(pytz.UTC).isoformat())

    df1['minutes_from_start'] = (pd.to_datetime(df1['observed_at']) - pd.to_datetime(df1['observed_at']).iloc[0]).dt.total_seconds() / 60
    df2['minutes_from_start'] = (pd.to_datetime(df2['observed_at']) - pd.to_datetime(df2['observed_at']).iloc[0]).dt.total_seconds() / 60

    fig = go.Figure()
    fig.add_trace(go.Scatter(x=df1['minutes_from_start'], y=df1['tide_level'], mode='lines', name=date1))
    fig.add_trace(go.Scatter(x=df2['minutes_from_start'], y=df2['tide_level'], mode='lines', name=date2))
    fig.update_layout(
        title=f'{STATION_NAMES.get(station_id, station_id)} Tide Comparison: {date1} vs {date2}',
        xaxis_title='Minutes from Midnight', yaxis_title='Tide Level (cm)', height=400
    )
    return {"data": [df1, df2], "plot": fig}

def get_tide_summary(station_id, year, month, summary_type='monthly'):
    """월간/연간 조위 요약"""
    start_date = f"{year}-{int(month):02d}-01"
    end_date = (datetime.strptime(start_date, '%Y-%m-%d') + pd.offsets.MonthEnd(1)).strftime('%Y-%m-%d')

    df = fetch_tide_data(station_id, 
                        pytz.timezone('Asia/Seoul').localize(datetime.strptime(start_date, '%Y-%m-%d')).astimezone(pytz.UTC).isoformat(),
                        (pytz.timezone('Asia/Seoul').localize(datetime.strptime(end_date, '%Y-%m-%d')) + timedelta(days=1)).astimezone(pytz.UTC).isoformat())

    df['observed_at'] = pd.to_datetime(df['observed_at']).dt.tz_convert('Asia/Seoul')
    df['tide_level'] = pd.to_numeric(df['tide_level'])

    highest = df.loc[df['tide_level'].idxmax()]
    lowest = df.loc[df['tide_level'].idxmin()]
    avg_tide = df['tide_level'].mean()
    df['date'] = df['observed_at'].dt.date
    daily_range = df.groupby('date')['tide_level'].apply(lambda x: x.max() - x.min())
    avg_range = daily_range.mean()

    summary = {
        "Highest Tide": f"{highest['tide_level']:.1f}cm ({highest['observed_at'].strftime('%Y-%m-%d %H:%M')})",
        "Lowest Tide": f"{lowest['tide_level']:.1f}cm ({lowest['observed_at'].strftime('%Y-%m-%d %H:%M')})",
        "Average Tide": f"{avg_tide:.1f}cm",
        "Average Range": f"{avg_range:.1f}cm"
    }

    fig = make_subplots(rows=2, cols=1, subplot_titles=("Daily Tide Variation", "Daily Tide Range"))
    fig.add_trace(go.Box(x=df['observed_at'].dt.strftime('%Y-%m-%d'), y=df['tide_level'], name='Tide'), row=1, col=1)
    fig.add_trace(go.Bar(x=daily_range.index, y=daily_range.values, name='Range'), row=2, col=1)
    fig.update_layout(
        height=700, 
        title_text=f"{STATION_NAMES.get(station_id, station_id)} - {year} {month} Summary"
    )

    return {"summary": summary, "plot": fig}