import json
import os

from db.database import conn, cursor

STORE_FILE = "data/pending_approvals.json"
MIGRATION_KEY = "pending_approvals_json_migrated"


def _get_setting(key):
    cursor.execute("SELECT value FROM settings WHERE key = ?", (key,))
    row = cursor.fetchone()
    return row[0] if row else None


def _set_setting(key, value):
    cursor.execute("""
        INSERT INTO settings (key, value) VALUES (?, ?)
        ON CONFLICT(key) DO UPDATE SET value = excluded.value
    """, (key, value))
    conn.commit()


def _migrate_json_if_needed():
    if _get_setting(MIGRATION_KEY) == "1":
        return

    if not os.path.exists(STORE_FILE):
        return

    try:
        with open(STORE_FILE, "r", encoding="utf-8") as f:
            raw = json.load(f)
    except Exception:
        raw = {}

    if isinstance(raw, dict):
        for key, entry in raw.items():
            try:
                user_id = int(key)
            except (TypeError, ValueError):
                continue
            if not isinstance(entry, dict):
                continue
            cursor.execute("""
                INSERT OR IGNORE INTO pending_approvals (user_id, data_json, status, created_at)
                VALUES (?, ?, ?, COALESCE(?, datetime('now', 'localtime')))
            """, (
                user_id,
                json.dumps(entry, ensure_ascii=False),
                entry.get("status", "open"),
                entry.get("created_at")
            ))
        conn.commit()

    _set_setting(MIGRATION_KEY, "1")


def load_pending():
    _migrate_json_if_needed()
    cursor.execute("SELECT user_id, data_json FROM pending_approvals ORDER BY created_at")
    data = {}
    for row in cursor.fetchall():
        try:
            data[int(row["user_id"])] = json.loads(row["data_json"])
        except Exception:
            continue
    return data


def save_pending(data):
    cursor.execute("DELETE FROM pending_approvals")
    for user_id, entry in data.items():
        if not isinstance(entry, dict):
            continue
        cursor.execute("""
            INSERT INTO pending_approvals (user_id, data_json, status, created_at)
            VALUES (?, ?, ?, COALESCE(?, datetime('now', 'localtime')))
        """, (
            int(user_id),
            json.dumps(entry, ensure_ascii=False),
            entry.get("status", "open"),
            entry.get("created_at")
        ))
    conn.commit()
    _set_setting(MIGRATION_KEY, "1")


def _upsert_pending(user_id: int, entry: dict):
    cursor.execute("""
        INSERT INTO pending_approvals (user_id, data_json, status, created_at)
        VALUES (?, ?, ?, COALESCE(?, datetime('now', 'localtime')))
        ON CONFLICT(user_id) DO UPDATE SET
            data_json = excluded.data_json,
            status = excluded.status,
            updated_at = datetime('now', 'localtime')
    """, (
        int(user_id),
        json.dumps(entry, ensure_ascii=False),
        entry.get("status", "open"),
        entry.get("created_at")
    ))
    conn.commit()


def set_pending(user_id: int, entry: dict):
    _migrate_json_if_needed()
    _upsert_pending(user_id, entry)


def get_pending(user_id: int):
    _migrate_json_if_needed()
    cursor.execute(
        "SELECT data_json FROM pending_approvals WHERE user_id = ?",
        (int(user_id),)
    )
    row = cursor.fetchone()
    if not row:
        return {}
    try:
        return json.loads(row["data_json"])
    except Exception:
        return {}


def get_all_pending():
    return load_pending()


def get_pending_count():
    _migrate_json_if_needed()
    cursor.execute("SELECT COUNT(*) FROM pending_approvals")
    return cursor.fetchone()[0]


def add_pending_message(user_id: int, message: dict):
    entry = get_pending(user_id)
    if not isinstance(entry, dict):
        return False

    messages = entry.setdefault("messages", [])
    messages.append(message)
    _upsert_pending(user_id, entry)
    return True


def delete_pending(user_id: int):
    _migrate_json_if_needed()
    cursor.execute("DELETE FROM pending_approvals WHERE user_id = ?", (int(user_id),))
    conn.commit()