from datetime import datetime, timedelta
import pandas as pd
import pytz
import plotly.graph_objects as go
from plotly.subplots import make_subplots

from supabase_utils import get_supabase_client
from config import STATION_NAMES

def api_get_current_tide(station_id):
    """현재 조위 조회"""
    supabase = get_supabase_client()
    if not supabase:
        return {"error": "Supabase 클라이언트를 생성할 수 없습니다."}

    try:
        result = supabase.table('tide_predictions') \
            .select('predicted_at, final_tide_level') \
            .eq('station_id', station_id) \
            .order('predicted_at', desc=True) \
            .limit(1) \
            .execute()

        if result.data:
            return result.data[0]
        else:
            return {"error": "데이터가 없습니다."}
    except Exception as e:
        return {"error": f"데이터 조회 오류: {e}"}

def api_get_historical_tide(station_id, date_str, hours=24):
    """과거 특정 날짜의 조위 데이터 조회"""
    supabase = get_supabase_client()
    if not supabase:
        return {"error": "Supabase 클라이언트를 생성할 수 없습니다."}

    try:
        start_time = datetime.strptime(date_str, '%Y-%m-%d')
        start_time_kst = pytz.timezone('Asia/Seoul').localize(start_time)
        end_time_kst = start_time_kst + timedelta(hours=hours)

        start_utc = start_time_kst.astimezone(pytz.UTC).isoformat()
        end_utc = end_time_kst.astimezone(pytz.UTC).isoformat()

        result = supabase.table('historical_tide') \
            .select('observed_at, tide_level') \
            .eq('station_id', station_id) \
            .gte('observed_at', start_utc) \
            .lte('observed_at', end_utc) \
            .order('observed_at') \
            .execute()

        if not result.data:
            return {"error": f"{date_str}에 대한 과거 데이터가 없습니다."}

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

        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)} 조위'))
        fig.update_layout(
            title=f'{STATION_NAMES.get(station_id, station_id)} - {date_str} 조위',
            xaxis_title='시간',
            yaxis_title='조위 (cm)',
            height=400
        )
        return fig

    except Exception as e:
        return {"error": f"데이터 조회 중 오류 발생: {e}"}

def api_get_historical_extremes(station_id, date_str):
    """과거 특정 날짜의 만조/간조 정보"""
    supabase = get_supabase_client()
    if not supabase:
        return {"error": "Supabase 클라이언트를 생성할 수 없습니다."}

    try:
        start_time = datetime.strptime(date_str, '%Y-%m-%d')
        start_time_kst = pytz.timezone('Asia/Seoul').localize(start_time)
        end_time_kst = start_time_kst + timedelta(days=1)

        start_utc = start_time_kst.astimezone(pytz.UTC).isoformat()
        end_utc = end_time_kst.astimezone(pytz.UTC).isoformat()

        result = supabase.table('historical_tide') \
            .select('observed_at, tide_level') \
            .eq('station_id', station_id) \
            .gte('observed_at', start_utc) \
            .lte('observed_at', end_utc) \
            .order('observed_at') \
            .execute()

        if not result.data or len(result.data) < 3:
            return {"error": f"{date_str}의 만조/간조를 계산할 데이터가 부족합니다."}

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

        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: '만조' if pd.notna(row['max']) else '간조', 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')

        return extremes_df[['time', 'type', 'value']]

    except Exception as e:
        return {"error": f"데이터 처리 중 오류 발생: {e}"}

def api_compare_dates(station_id, date1, date2):
    """두 날짜의 조위 패턴 비교"""
    supabase = get_supabase_client()
    if not supabase:
        return {"error": "Supabase 클라이언트를 생성할 수 없습니다."}

    def get_data_for_date(target_date):
        start = pytz.timezone('Asia/Seoul').localize(datetime.strptime(target_date, '%Y-%m-%d'))
        end = start + timedelta(days=1)
        res = supabase.table('historical_tide') \
            .select('observed_at, tide_level') \
            .eq('station_id', station_id) \
            .gte('observed_at', start.astimezone(pytz.UTC).isoformat()) \
            .lte('observed_at', end.astimezone(pytz.UTC).isoformat()) \
            .order('observed_at') \
            .execute()
        return res.data

    data1 = get_data_for_date(date1)
    data2 = get_data_for_date(date2)

    if not data1 or not data2:
        return {"error": "두 날짜 중 하나의 데이터가 없습니다."}

    df1 = pd.DataFrame(data1)
    df1['tide_level'] = pd.to_numeric(df1['tide_level'])
    df1['minutes_from_start'] = (pd.to_datetime(df1['observed_at']) - pd.to_datetime(df1['observed_at']).iloc[0]).dt.total_seconds() / 60

    df2 = pd.DataFrame(data2)
    df2['tide_level'] = pd.to_numeric(df2['tide_level'])
    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)} 조위 비교: {date1} vs {date2}',
                      xaxis_title='자정부터 경과 시간(분)', yaxis_title='조위 (cm)')
    return fig

def api_get_monthly_summary(station_id, year, month):
    """월간 조위 요약 통계"""
    supabase = get_supabase_client()
    if not supabase:
        return {"error": "Supabase 클라이언트를 생성할 수 없습니다."}

    try:
        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')

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

        result = supabase.table('historical_tide') \
            .select('observed_at, tide_level') \
            .eq('station_id', station_id) \
            .gte('observed_at', start_utc) \
            .lte('observed_at', end_utc) \
            .order('observed_at') \
            .execute()

        if not result.data:
            return {"error": f"{year}년 {month}월 데이터가 없습니다."}

        df = pd.DataFrame(result.data)
        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 = {
            "최고 조위": f"{highest['tide_level']:.1f}cm ({highest['observed_at'].strftime('%Y-%m-%d %H:%M')})",
            "최저 조위": f"{lowest['tide_level']:.1f}cm ({lowest['observed_at'].strftime('%Y-%m-%d %H:%M')})",
            "평균 조위": f"{avg_tide:.1f}cm",
            "평균 조차": f"{avg_range:.1f}cm"
        }

        fig = make_subplots(rows=2, cols=1, subplot_titles=("일별 조위 변화", "일별 조차"))
        fig.add_trace(go.Box(x=df['observed_at'].dt.strftime('%Y-%m-%d'), y=df['tide_level'], name='조위'), row=1, col=1)
        fig.add_trace(go.Bar(x=daily_range.index.strftime('%Y-%m-%d'), y=daily_range.values, name='조차'), row=2, col=1)
        fig.update_layout(height=700, title_text=f"{STATION_NAMES.get(station_id, station_id)} - {year}년 {month}월 요약")

        return summary, fig

    except Exception as e:
        return {"error": f"월간 요약 생성 중 오류 발생: {e}"}, None