from dotenv import load_dotenv
load_dotenv()

import os
# 環境変数から取得
SUPABASE_URL = os.environ.get('SUPABASE_URL')

SUPABASE_KEY = os.environ.get('SUPABASE_KEY')

import supabase

# クライアントの初期化
supabase_client = supabase.create_client(SUPABASE_URL, SUPABASE_KEY)

import gradio as gr
import pandas as pd
import math
import time
from io import StringIO
import supabase 

# =============================
# 型変換ヘルパー
# =============================
def pandas_dtype_to_supabase_type(dtype):
    """Maps pandas data types to Supabase SQL data types."""
    if pd.api.types.is_integer_dtype(dtype):
        return "integer"
    elif pd.api.types.is_float_dtype(dtype):
        return "double precision"
    elif pd.api.types.is_bool_dtype(dtype):
        return "boolean"
    elif pd.api.types.is_datetime64_any_dtype(dtype):
        return "timestamp with time zone"
    else:
        return "text"

# =============================
# テーブル作成関数
# =============================
def create_table_if_not_exists(db_name, dataframe):
    """
    Creates a table in Supabase if it doesn't exist, using RPC `exec_sql`.
    """
    columns = []
    for col_name, dtype in dataframe.dtypes.items():
        supabase_type = pandas_dtype_to_supabase_type(dtype)
        columns.append(f'"{col_name}" {supabase_type}')

    columns_sql = ", ".join(columns)
    create_table_sql = f"""
    CREATE TABLE IF NOT EXISTS public."{db_name}" (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        {columns_sql}
    );
    """
    print(f"Executing SQL for table creation:\n{create_table_sql}")

    try:
        response = supabase_client.rpc("exec_sql", {"sql": create_table_sql}).execute()

        resp_data = getattr(response, "data", None)
        resp_error = getattr(response, "error", None)

        if resp_error:
            return False, f"Error creating table: {getattr(resp_error, 'message', str(resp_error))}"

        if resp_data:
            return True, f"Table '{db_name}' created or already exists. RPC returned data: {resp_data}"
        else:
            return True, f"Table '{db_name}' created or already exists. (no data returned)"

    except Exception as e:
        print(f"Exception during table creation RPC call: {e}")
        return False, f"Exception during table creation: {e}"

# =============================
# DataFrame の JSON 変換ヘルパー
# =============================
def clean_dataframe_for_json(df: pd.DataFrame):
    """
    Convert DataFrame to JSON-compliant list of dicts
    (replace NaN, NaT, inf, -inf with None).
    """
    records = df.to_dict(orient="records")
    cleaned = []
    for row in records:
        new_row = {}
        for k, v in row.items():
            if v is None:
                new_row[k] = None
            elif isinstance(v, float) and (math.isnan(v) or math.isinf(v)):
                new_row[k] = None
            else:
                new_row[k] = v
        cleaned.append(new_row)
    return cleaned

# =============================
# メイン処理関数
# =============================
def import_data_to_supabase(text_input: str, db_name: str):
    db_name = db_name.strip().lower()  # 小文字に統一
    full_message = []

    try:
        # 1. DataFrame 読み込み（タブ区切り & null対応）
        df = pd.read_csv(
            StringIO(text_input),
            sep="\t",
            na_values=["null", "NULL", "NaN", ""],
            keep_default_na=True
        )

        # 2. テーブル作成
        table_creation_success, table_creation_msg = create_table_if_not_exists(db_name, df)
        full_message.append(f"Table Creation Status: {table_creation_msg}")

        if not table_creation_success:
            return "\n".join(full_message)

        # 3. 数秒待機（スキーマキャッシュ反映待ち）
        time.sleep(3)

        # 4. JSON対応にクリーニング（NaN, NaT, inf → None）
        data = clean_dataframe_for_json(df)

        if not data:
            full_message.append("Data Insertion Status: No data to insert.")
            return "\n".join(full_message)

        # 5. データ挿入
        print(f"Attempting to insert {len(data)} rows into table '{db_name}'.")
        response = supabase_client.table(db_name).insert(data).execute()

        if getattr(response, "error", None):
            full_message.append(
                f"Data Insertion Status: Error inserting data - {response.error}"
            )
        elif getattr(response, "data", None):
            full_message.append(
                f"Data Insertion Status: Successfully inserted {len(response.data)} rows."
            )
        else:
            full_message.append("Data Insertion Status: Insert executed, but no data returned.")

    except Exception as e:
        full_message.append(f"An unexpected error occurred during processing: {e}")

    return "\n".join(full_message)

# =============================
# Gradio アプリ
# =============================
iface = gr.Interface(
    fn=import_data_to_supabase,
    inputs=[
        gr.Textbox(label="Paste TSV Data Here", lines=15, placeholder="ここにタブ区切りデータを貼り付け"),
        gr.Textbox(label="Supabase Database Table Name")
    ],
    outputs="text",
    title="Supabase Data Importer",
    description="タブ区切りデータを貼り付け、Supabase データベースのテーブルにインポートします。テーブルが存在しない場合は新しく作成します。"
)

iface.launch(mcp_server=True)