import os
import sqlite3
import json
import hmac
import hashlib
from urllib.parse import parse_qs, unquote

from flask import Flask, request, jsonify, send_from_directory
from flask_cors import CORS
from dotenv import load_dotenv

load_dotenv()

app = Flask(__name__, static_folder="static")
CORS(app)

BOT_TOKEN = os.getenv("BOT_TOKEN", "")
DB_PATH = os.getenv("DB_PATH", "database.db")


def get_db():
    if not os.path.exists(DB_PATH):
        return None
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def discover_tables():
    conn = get_db()
    if conn is None:
        return {}
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [row["name"] for row in cursor.fetchall()]
    schemas = {}
    for table in tables:
        cursor.execute(f"PRAGMA table_info({table})")
        columns = [{"name": row["name"], "type": row["type"]} for row in cursor.fetchall()]
        schemas[table] = columns
    conn.close()
    return schemas


def validate_telegram_init_data(init_data: str) -> dict | None:
    if not BOT_TOKEN:
        return None
    try:
        parsed = parse_qs(init_data)
        items = {k: v[0] for k, v in parsed.items()}
        hash_value = items.pop("hash", None)
        if not hash_value:
            return None
        sorted_items = sorted(items.items())
        data_check_str = "\n".join(f"{k}={v}" for k, v in sorted_items)
        secret_key = hmac.new(b"WebAppData", BOT_TOKEN.encode(), hashlib.sha256).digest()
        computed_hash = hmac.new(secret_key, data_check_str.encode(), hashlib.sha256).hexdigest()
        if computed_hash != hash_value:
            return None
        user_str = items.get("user")
        return json.loads(unquote(user_str)) if user_str else None
    except Exception:
        return None


@app.route("/")
def index():
    return send_from_directory("static", "index.html")


@app.route("/api/schema")
def api_schema():
    return jsonify(discover_tables())


@app.route("/api/user/<int:telegram_id>")
def api_user(telegram_id):
    schemas = discover_tables()
    user_data = None
    points = None
    harem = []
    harem_count = 0

    conn = get_db()
    cursor = conn.cursor()

    for table, columns in schemas.items():
        col_names = [c["name"] for c in columns]
        lower_table = table.lower()

        if "user" in lower_table or "player" in lower_table:
            if "user_id" in col_names or "telegram_id" in col_names or "id" in col_names:
                id_col = next(c for c in col_names if c in ("user_id", "telegram_id", "id"))
                cursor.execute(f"SELECT * FROM {table} WHERE {id_col} = ?", (telegram_id,))
                row = cursor.fetchone()
                if row:
                    user_data = dict(row)
                    if "points" in col_names or "point" in col_names or "balance" in col_names:
                        for pcol in ("points", "point", "balance"):
                            if pcol in col_names:
                                points = row[pcol]
                                break

        if "harem" in lower_table or "collection" in lower_table or "characters" in lower_table or "cards" in lower_table:
            fk_col = None
            for c in col_names:
                if c in ("user_id", "telegram_id", "owner_id", "player_id"):
                    fk_col = c
                    break
            if fk_col:
                cursor.execute(f"SELECT * FROM {table} WHERE {fk_col} = ?", (telegram_id,))
                rows = cursor.fetchall()
                for r in rows:
                    harem.append(dict(r))
                harem_count = len(harem)

    conn.close()

    if not user_data:
        return jsonify({"error": "User not found"}), 404

    return jsonify({
        "user": user_data,
        "points": points,
        "harem": harem,
        "harem_count": harem_count,
        "total_harem": harem_count,
    })


@app.route("/api/verify", methods=["POST"])
def api_verify():
    data = request.get_json()
    if not data or "initData" not in data:
        return jsonify({"ok": False, "error": "Missing initData"}), 400

    user = validate_telegram_init_data(data["initData"])
    if user:
        return jsonify({"ok": True, "user": user})
    return jsonify({"ok": False, "error": "Invalid initData"}), 403


@app.route("/api/test")
def api_test():
    conn = get_db()
    if conn is None:
        return jsonify({
            "user": {"user_id": 123456789, "first_name": "Galaxy", "username": "catcher"},
            "points": 42000,
            "harem": [
                {"id": 1, "character_name": "Nebula Queen", "rarity": "legendary", "points": 9999, "image": ""},
                {"id": 2, "character_name": "Star Knight", "rarity": "epic", "points": 7500, "image": ""},
                {"id": 3, "character_name": "Cosmic Mage", "rarity": "epic", "points": 6200, "image": ""},
                {"id": 4, "character_name": "Void Hunter", "rarity": "rare", "points": 3400, "image": ""},
                {"id": 5, "character_name": "Astral Drifter", "rarity": "rare", "points": 2800, "image": ""},
                {"id": 6, "character_name": "Moon Priestess", "rarity": "common", "points": 1200, "image": ""},
            ],
            "harem_count": 6,
            "total_harem": 6,
        })

    schemas = discover_tables()
    return jsonify({"db_path": DB_PATH, "tables": schemas, "note": "Database found"})


@app.route("/api/db-info")
def api_db_info():
    schemas = discover_tables()
    return jsonify({
        "db_path": DB_PATH,
        "tables": schemas,
    })


if __name__ == "__main__":
    print(f"[Galaxy Mini App] DB: {DB_PATH}")
    print(f"[Galaxy Mini App] Tables: {list(discover_tables().keys())}")
    app.run(host="0.0.0.0", port=5000, debug=True)