import os
import re
import traceback
from datetime import datetime

import numpy as np
import pandas as pd
import pytz
from dateutil import parser as date_parser

from config import SUPABASE_URL, SUPABASE_KEY

# Supabase 연동 추가
try:
    from supabase import create_client, Client
    SUPABASE_AVAILABLE = True
except ImportError:
    SUPABASE_AVAILABLE = False
    print("Supabase 패키지가 설치되지 않았습니다.")

def clean_string(s):
    """문자열에서 특수 유니코드 문자 제거"""
    if s is None:
        return None
    cleaned = s.replace('\u2028', '').replace('\u2029', '')
    cleaned = re.sub(r'[\x00-\x1f\x7f-\x9f]', '', cleaned)
    return cleaned.strip()

def get_supabase_client():
    """Supabase 클라이언트 생성"""
    if not SUPABASE_AVAILABLE:
        return None
    
    try:
        if not SUPABASE_URL or not SUPABASE_KEY:
            print("Supabase 환경변수가 설정되지 않았습니다.")
            return None
        
        url = clean_string(SUPABASE_URL)
        key = clean_string(SUPABASE_KEY)
        
        if not url.startswith('http'):
            print(f"잘못된 SUPABASE_URL 형식: {url}")
            return None
            
        return create_client(url, key)
    except Exception as e:
        print(f"Supabase 연결 오류: {e}")
        traceback.print_exc()
        return None

def get_harmonic_predictions(station_id, start_time, end_time):
    """해당 시간 범위의 조화 예측값 조회"""
    supabase = get_supabase_client()
    if not supabase:
        print("Supabase 클라이언트를 생성할 수 없습니다.")
        return []
    
    try:
        kst = pytz.timezone('Asia/Seoul')
        
        # 안전한 datetime 변환
        if isinstance(start_time, str):
            start_time = pd.to_datetime(start_time)
        if isinstance(end_time, str):
            end_time = pd.to_datetime(end_time)
            
        # pandas Timestamp를 datetime으로 변환
        if hasattr(start_time, 'to_pydatetime'):
            start_time = start_time.to_pydatetime()
        if hasattr(end_time, 'to_pydatetime'):
            end_time = end_time.to_pydatetime()
        
        # 시간대 처리
        if start_time.tzinfo is None:
            start_time = kst.localize(start_time)
        if end_time.tzinfo is None:
            end_time = kst.localize(end_time)
            
        start_utc = start_time.astimezone(pytz.UTC)
        end_utc = end_time.astimezone(pytz.UTC)
        
        start_str = start_utc.strftime('%Y-%m-%dT%H:%M:%S+00:00')
        end_str = end_utc.strftime('%Y-%m-%dT%H:%M:%S+00:00')
        
        result = supabase.table('harmonic_predictions')\
            .select('predicted_at, harmonic_level')\
            .eq('station_id', station_id)\
            .gte('predicted_at', start_str)\
            .lte('predicted_at', end_str)\
            .order('predicted_at')\
            .limit(1000)\
            .execute()
        
        return result.data if result.data else []
    except Exception as e:
        print(f"조화 예측값 조회 오류: {e}")
        traceback.print_exc()
        return []

def get_tide_predictions(station_id, start_time, end_time):
    """사용자가 입력한 KST 시간을 UTC로 변환하여 조석 예측 데이터 조회"""
    supabase = get_supabase_client()
    if not supabase:
        print("Supabase 클라이언트를 생성할 수 없습니다.")
        return []
    
    try:
        kst = pytz.timezone('Asia/Seoul')
        
        # 안전한 datetime 변환
        if isinstance(start_time, str):
            start_time = pd.to_datetime(start_time)
        if isinstance(end_time, str):
            end_time = pd.to_datetime(end_time)
            
        # pandas Timestamp를 datetime으로 변환
        if hasattr(start_time, 'to_pydatetime'):
            start_time = start_time.to_pydatetime()
        if hasattr(end_time, 'to_pydatetime'):
            end_time = end_time.to_pydatetime()
        
        # KST 시간대가 없으면 추가
        if start_time.tzinfo is None:
            start_time = kst.localize(start_time)
        if end_time.tzinfo is None:
            end_time = kst.localize(end_time)
            
        # UTC로 변환
        start_utc = start_time.astimezone(pytz.UTC)
        end_utc = end_time.astimezone(pytz.UTC)
        
        start_str = start_utc.strftime('%Y-%m-%dT%H:%M:%S+00:00')
        end_str = end_utc.strftime('%Y-%m-%dT%H:%M:%S+00:00')
        
        result = supabase.table('tide_predictions')\
            .select('*')\
            .eq('station_id', station_id)\
            .gte('predicted_at', start_str)\
            .lte('predicted_at', end_str)\
            .order('predicted_at')\
            .limit(1000)\
            .execute()
        
        return result.data if result.data else []
    except Exception as e:
        print(f"조석 예측값 조회 오류: {e}")
        traceback.print_exc()
        return []

def save_predictions_to_supabase(station_id, prediction_results):
    """예측 결과를 Supabase에 저장"""
    supabase = get_supabase_client()
    if not supabase:
        print("Supabase 클라이언트를 생성할 수 없습니다.")
        return 0
    
    try:
        if prediction_results['times']:
            start_time = prediction_results['times'][0].strftime('%Y-%m-%dT%H:%M:%S')
            end_time = prediction_results['times'][-1].strftime('%Y-%m-%dT%H:%M:%S')
            
            supabase.table('tide_predictions')\
                .delete()\
                .eq('station_id', station_id)\
                .gte('predicted_at', start_time)\
                .lte('predicted_at', end_time)\
                .execute()
        
        insert_data = []
        for i in range(len(prediction_results['times'])):
            # KST 시간을 UTC로 변환하여 저장
            kst_time = prediction_results['times'][i]
            if kst_time.tzinfo is None:
                kst = pytz.timezone('Asia/Seoul')
                kst_time = kst.localize(kst_time)
            utc_time = kst_time.astimezone(pytz.UTC)
            time_str = utc_time.strftime('%Y-%m-%dT%H:%M:%S')
            
            # NaN 값을 0.0으로 대체하여 JSON 직렬화 오류 방지
            residual_val = prediction_results['residual'][i]
            if pd.isna(residual_val) or not np.isfinite(residual_val):
                residual_val = 0.0
            
            harmonic_val = prediction_results['harmonic'][i] 
            if pd.isna(harmonic_val) or not np.isfinite(harmonic_val):
                harmonic_val = 0.0
                
            final_tide_val = prediction_results['final_tide'][i]
            if pd.isna(final_tide_val) or not np.isfinite(final_tide_val):
                final_tide_val = 0.0
            
            insert_data.append({
                'station_id': station_id,
                'predicted_at': time_str,
                'predicted_residual': float(residual_val),
                'harmonic_level': float(harmonic_val),
                'final_tide_level': float(final_tide_val)
            })
        
        result = supabase.table('tide_predictions')\
            .upsert(insert_data, on_conflict='station_id,predicted_at')\
            .execute()
        
        return len(insert_data)
    except Exception as e:
        print(f"예측 결과 저장 오류: {e}")
        traceback.print_exc()
        return 0