supabase_utils.py

import os
import re
import traceback
from datetime import datetime

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')
        
        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 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'])):
            time_str = prediction_results['times'][i].strftime('%Y-%m-%dT%H:%M:%S')
            
            insert_data.append({
                'station_id': station_id,
                'predicted_at': time_str,
                'predicted_residual': float(prediction_results['residual'][i]),
                'harmonic_level': float(prediction_results['harmonic'][i]),
                'final_tide_level': float(prediction_results['final_tide'][i])
            })
        
        result = supabase.table('tide_predictions')\
            .insert(insert_data)\
            .execute()
        
        return len(insert_data)
    except Exception as e:
        print(f"예측 결과 저장 오류: {e}")
        traceback.print_exc()
        return 0