import os
import json
import urllib.request
import telebot

from app import (
    _call_gemini,
    _heuristic_sql,
    execute_sql,
    _db_store,
    _schema_store
)

TOKEN     = os.getenv("BOT_TOKEN", "")
SPACE_URL = os.getenv("SPACE_URL", "")

if not TOKEN:
    print("❌ ERROR: BOT_TOKEN not found")

bot = telebot.TeleBot(TOKEN)


# ── Send message directly via HTTP (bypasses telebot outbound) ─

def send_message(chat_id: int, text: str):
    url = f"https://api.telegram.org/bot{TOKEN}/sendMessage"
    payload = json.dumps({
        "chat_id": chat_id,
        "text": text
    }).encode("utf-8")
    try:
        req = urllib.request.Request(
            url,
            data=payload,
            headers={"Content-Type": "application/json"}
        )
        with urllib.request.urlopen(req, timeout=15) as resp:
            return json.loads(resp.read().decode())
    except Exception as e:
        print(f"❌ SEND ERROR: {e}")


# ── Handlers ───────────────────────────────────────────────────

@bot.message_handler(commands=['start'])
def welcome(message):
    send_message(message.chat.id,
        "⚡ QueryMind Omni-Channel Active\n\n"
        "1. Upload your CSV on the web dashboard\n"
        "2. Ask questions here in plain English\n\n"
        "Examples:\n"
        "- Show first 5 rows\n"
        "- Count total records\n"
        "- Unique values in column\n"
        "- Group by answer"
    )


@bot.message_handler(func=lambda message: True)
def handle_query(message):
    chat_id = message.chat.id

    if not _db_store:
        send_message(chat_id,
            "⚠️ No dataset found.\n\nPlease upload a CSV file on the website first."
        )
        return

    try:
        session_id = list(_db_store.keys())[-1]
        data       = _db_store[session_id]
        schema     = _schema_store.get(session_id)

        if not schema:
            send_message(chat_id, "❌ Schema not found.")
            return

        question = message.text.strip()

        sql = _call_gemini(question, schema, data["cols"], data["table"])

        if not sql or sql.strip() == "":
            send_message(chat_id,
                "❌ Failed to generate SQL query.\n\n"
                "Possible reasons:\n"
                "- Invalid Gemini API key\n"
                "- Gemini API unavailable\n"
                "- Unsupported question"
            )
            return

        results = execute_sql(sql, data["bytes"])

        if results and isinstance(results, list) and "error" in results[0]:
            send_message(chat_id, f"❌ SQL Error:\n\n{results[0]['error']}")
            return

        response = f"🔍 SQL Query:\n{sql}\n\n"

        if not results:
            response += "📭 No records found."
        else:
            response += "📊 Results:\n\n"
            for i, row in enumerate(results[:10], start=1):
                response += f"{i}. {', '.join([f'{k}: {v}' for k, v in row.items()])}\n"
            if len(results) > 10:
                response += f"\n... and {len(results)-10} more rows."

        send_message(chat_id, response)

    except Exception as e:
        print(f"❌ BOT ERROR: {e}")
        send_message(chat_id, f"❌ System Error:\n\n{str(e)}")


# ── Webhook Setup ──────────────────────────────────────────────

def setup_webhook():
    if not TOKEN or not SPACE_URL:
        print("❌ BOT_TOKEN or SPACE_URL missing — webhook not set.")
        return
    webhook_url = f"{SPACE_URL}/webhook/{TOKEN}"
    try:
        url = f"https://api.telegram.org/bot{TOKEN}/setWebhook"
        payload = json.dumps({"url": webhook_url}).encode("utf-8")
        req = urllib.request.Request(
            url,
            data=payload,
            headers={"Content-Type": "application/json"}
        )
        with urllib.request.urlopen(req, timeout=15) as resp:
            result = json.loads(resp.read().decode())
            if result.get("ok"):
                print(f"✅ Webhook set: {webhook_url}")
            else:
                print(f"❌ Webhook failed: {result}")
    except Exception as e:
        print(f"❌ Webhook setup failed: {e}")