"""
Database models — SQLite-backed User, Wallet, Deposit, OrderHistory.
"""

import hashlib
import re
import secrets
import sqlite3
import os
import time
from pathlib import Path
from werkzeug.security import generate_password_hash, check_password_hash

API_KEY_PREFIX = "jtz_live_"


def _hash_api_key(plain: str) -> str:
    return hashlib.sha256((plain or "").encode("utf-8")).hexdigest()

DB_PATH = Path(__file__).parent / "smilehub.db"
_GA_INFO_UPLOAD_DIR = Path(__file__).parent / "static" / "uploads" / "ga_info"
_GA_CAT_UPLOAD_DIR = Path(__file__).parent / "static" / "uploads" / "ga_cat"

def get_db():
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    conn = get_db()
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        username    TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        is_admin    INTEGER DEFAULT 0,
        profile_image TEXT DEFAULT '',
        created_at  REAL DEFAULT (strftime('%s','now'))
    );
    CREATE TABLE IF NOT EXISTS wallets (
        user_id     INTEGER PRIMARY KEY REFERENCES users(id),
        balance_mmk INTEGER DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS deposits (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id         INTEGER NOT NULL REFERENCES users(id),
        amount_mmk      INTEGER NOT NULL,
        payment_method  TEXT NOT NULL,
        sender_name     TEXT NOT NULL DEFAULT '',
        receipt_image   TEXT DEFAULT '',
        status          TEXT DEFAULT 'pending',
        admin_note      TEXT DEFAULT '',
        created_at      REAL DEFAULT (strftime('%s','now')),
        reviewed_at     REAL
    );
    CREATE TABLE IF NOT EXISTS order_history (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id         INTEGER NOT NULL REFERENCES users(id),
        product_url     TEXT NOT NULL,
        product_name    TEXT DEFAULT '',
        package_name    TEXT DEFAULT '',
        amount_mmk      INTEGER DEFAULT 0,
        status          TEXT DEFAULT 'processing',
        smile_step      TEXT DEFAULT '',
        smile_message   TEXT DEFAULT '',
        created_at      REAL DEFAULT (strftime('%s','now'))
    );
    CREATE TABLE IF NOT EXISTS password_resets (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id     INTEGER NOT NULL REFERENCES users(id),
        status      TEXT DEFAULT 'pending',
        created_at  REAL DEFAULT (strftime('%s','now')),
        reviewed_at REAL
    );
    """)
    # migrate existing tables
    for col, default in [("profile_image", "''"), ("api_key_hash", "''")]:
        try:
            conn.execute(f"ALTER TABLE users ADD COLUMN {col} TEXT DEFAULT {default}")
            conn.commit()
        except sqlite3.OperationalError:
            pass
    try:
        conn.execute("SELECT 1 FROM password_resets LIMIT 1")
    except sqlite3.OperationalError:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS password_resets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL REFERENCES users(id),
            status TEXT DEFAULT 'pending',
            created_at REAL DEFAULT (strftime('%s','now')),
            reviewed_at REAL
        );
        """)
        conn.commit()
    try:
        conn.execute("SELECT 1 FROM api_audit LIMIT 1")
    except sqlite3.OperationalError:
        conn.executescript("""
        CREATE TABLE IF NOT EXISTS api_audit (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            audit_key_hint TEXT,
            method TEXT NOT NULL,
            path TEXT NOT NULL,
            ip TEXT,
            status_code INTEGER,
            created_at REAL DEFAULT (strftime('%s','now'))
        );
        """)
        conn.commit()
    _ensure_ga_tables(conn)
    _migrate_ga_category_card_image(conn)
    _migrate_chat_image_column(conn)
    _ensure_ga_account_info_images_table(conn)
    _ensure_social_boost_orders_table(conn)
    _ensure_shop_fx_settings_table(conn)
    conn.close()
    _seed_ga_categories_if_empty()


def _ensure_shop_fx_settings_table(conn: sqlite3.Connection) -> None:
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS shop_fx_settings (
            key TEXT PRIMARY KEY,
            value REAL NOT NULL,
            updated_at REAL DEFAULT (strftime('%s','now'))
        )
        """
    )
    conn.commit()


def _ensure_social_boost_orders_table(conn: sqlite3.Connection) -> None:
    try:
        conn.execute("SELECT 1 FROM social_boost_orders LIMIT 1")
    except sqlite3.OperationalError:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS social_boost_orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id),
                provider_order_id INTEGER,
                service_id INTEGER NOT NULL,
                service_name TEXT DEFAULT '',
                service_type TEXT DEFAULT '',
                link TEXT NOT NULL,
                quantity INTEGER DEFAULT 0,
                comments_preview TEXT DEFAULT '',
                amount_mmk INTEGER NOT NULL,
                status TEXT DEFAULT 'submitted',
                provider_message TEXT DEFAULT '',
                created_at REAL DEFAULT (strftime('%s','now'))
            );
            CREATE INDEX IF NOT EXISTS idx_sb_orders_user ON social_boost_orders(user_id);
            """
        )
        conn.commit()


def _ensure_ga_account_info_images_table(conn: sqlite3.Connection) -> None:
    try:
        conn.execute("SELECT 1 FROM ga_account_info_images LIMIT 1")
    except sqlite3.OperationalError:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS ga_account_info_images (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                account_id INTEGER NOT NULL REFERENCES ga_accounts(id) ON DELETE CASCADE,
                image_file TEXT NOT NULL,
                sort_order INTEGER DEFAULT 0,
                created_at REAL DEFAULT (strftime('%s','now'))
            );
            CREATE INDEX IF NOT EXISTS idx_ga_info_acc ON ga_account_info_images(account_id);
            """
        )
        conn.commit()


def _migrate_ga_category_card_image(conn: sqlite3.Connection) -> None:
    """Add ga_categories.card_image for category card thumbnails."""
    try:
        conn.execute("SELECT card_image FROM ga_categories LIMIT 1")
    except sqlite3.OperationalError:
        try:
            conn.execute("ALTER TABLE ga_categories ADD COLUMN card_image TEXT DEFAULT ''")
            conn.commit()
        except sqlite3.OperationalError:
            pass


def _migrate_chat_image_column(conn: sqlite3.Connection) -> None:
    """Add image_file to chat_messages for photo uploads."""
    try:
        conn.execute("SELECT 1 FROM chat_messages LIMIT 1")
    except sqlite3.OperationalError:
        return
    try:
        conn.execute("SELECT image_file FROM chat_messages LIMIT 1")
    except sqlite3.OperationalError:
        try:
            conn.execute("ALTER TABLE chat_messages ADD COLUMN image_file TEXT DEFAULT ''")
            conn.commit()
        except sqlite3.OperationalError:
            pass


def _ensure_ga_tables(conn: sqlite3.Connection) -> None:
    try:
        conn.execute("SELECT 1 FROM ga_categories LIMIT 1")
    except sqlite3.OperationalError:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS ga_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                slug TEXT UNIQUE NOT NULL,
                sort_order INTEGER DEFAULT 0,
                created_at REAL DEFAULT (strftime('%s','now'))
            );
            CREATE TABLE IF NOT EXISTS ga_accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                category_id INTEGER NOT NULL REFERENCES ga_categories(id) ON DELETE CASCADE,
                title TEXT NOT NULL,
                public_note TEXT DEFAULT '',
                price_mmk INTEGER NOT NULL,
                credentials TEXT NOT NULL,
                status TEXT DEFAULT 'available',
                sold_to_user_id INTEGER REFERENCES users(id),
                sold_at REAL,
                order_id INTEGER,
                created_at REAL DEFAULT (strftime('%s','now'))
            );
            CREATE TABLE IF NOT EXISTS chat_messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                is_admin INTEGER NOT NULL DEFAULT 0,
                body TEXT NOT NULL DEFAULT '',
                image_file TEXT DEFAULT '',
                created_at REAL DEFAULT (strftime('%s','now'))
            );
            CREATE INDEX IF NOT EXISTS idx_chat_user_time ON chat_messages(user_id, created_at);
            CREATE INDEX IF NOT EXISTS idx_ga_cat ON ga_accounts(category_id, status);
            """
        )
        conn.commit()


def _seed_ga_categories_if_empty() -> None:
    conn = get_db()
    try:
        n = conn.execute("SELECT COUNT(*) FROM ga_categories").fetchone()[0]
        if n and n > 0:
            return
        defaults = [
            ("Mobile Legends Game Accounts", "mobile-legends-game-accounts", 1),
            ("PUBG Mobile Account", "pubg-mobile-account", 2),
            ("Clash of Clans Account", "clash-of-clans-account", 3),
        ]
        for name, slug, so in defaults:
            try:
                conn.execute(
                    "INSERT INTO ga_categories (name, slug, sort_order) VALUES (?,?,?)",
                    (name, slug, so),
                )
            except sqlite3.IntegrityError:
                pass
        conn.commit()
    except sqlite3.OperationalError:
        pass
    finally:
        conn.close()


def _slugify(name: str) -> str:
    s = re.sub(r"[^\w\s-]", "", (name or "").lower().strip())
    s = re.sub(r"[-\s]+", "-", s).strip("-")[:80]
    return s or "category"


# ── Game account categories & listings ──


def ga_list_categories() -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM ga_categories ORDER BY sort_order ASC, id ASC"
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def ga_create_category(name: str) -> dict | None:
    name = (name or "").strip()
    if len(name) < 2:
        return None
    base = _slugify(name)
    conn = get_db()
    slug = base
    for i in range(20):
        try:
            mx = conn.execute("SELECT COALESCE(MAX(sort_order),0) FROM ga_categories").fetchone()[0]
            so = int(mx) + 1
            cur = conn.execute(
                "INSERT INTO ga_categories (name, slug, sort_order) VALUES (?,?,?)",
                (name, slug, so),
            )
            conn.commit()
            rid = cur.lastrowid
            row = conn.execute("SELECT * FROM ga_categories WHERE id=?", (rid,)).fetchone()
            conn.close()
            return dict(row) if row else None
        except sqlite3.IntegrityError:
            slug = f"{base}-{i+2}"
    conn.close()
    return None


def _unlink_ga_cat_file(filename: str) -> None:
    fn = (filename or "").strip()
    if not fn:
        return
    try:
        p = _GA_CAT_UPLOAD_DIR / fn
        if p.is_file():
            p.unlink()
    except OSError:
        pass


def ga_delete_category(category_id: int) -> bool:
    conn = get_db()
    try:
        crow = conn.execute(
            "SELECT card_image FROM ga_categories WHERE id=?",
            (category_id,),
        ).fetchone()
        if not crow:
            return False
        acc_ids = [
            r[0]
            for r in conn.execute(
                "SELECT id FROM ga_accounts WHERE category_id=?",
                (category_id,),
            ).fetchall()
        ]
        info_files: list[str] = []
        for aid in acc_ids:
            info_files.extend(
                r[0]
                for r in conn.execute(
                    "SELECT image_file FROM ga_account_info_images WHERE account_id=?",
                    (aid,),
                ).fetchall()
            )
        _unlink_ga_info_files(info_files)
        if (crow["card_image"] or "").strip():
            _unlink_ga_cat_file(crow["card_image"])
        cur = conn.execute("DELETE FROM ga_categories WHERE id=?", (category_id,))
        conn.commit()
        return cur.rowcount > 0
    finally:
        conn.close()


def ga_set_category_card_image(category_id: int, filename: str | None) -> bool:
    """Set or clear stored filename for category card (not a URL). Clears when filename is empty."""
    fn = (filename or "").strip()
    conn = get_db()
    try:
        row = conn.execute(
            "SELECT card_image FROM ga_categories WHERE id=?",
            (category_id,),
        ).fetchone()
        if not row:
            return False
        old = (row["card_image"] or "").strip()
        conn.execute(
            "UPDATE ga_categories SET card_image=? WHERE id=?",
            (fn, category_id),
        )
        conn.commit()
        if old and old != fn:
            _unlink_ga_cat_file(old)
        return True
    finally:
        conn.close()


GA_HOME_CATALOG_CARD_IMAGE = "/static/img/game-accounts-banner.png"


def ga_game_accounts_home_card_image_url() -> str:
    """Home catalog 'Game Accounts' tile: always the bundled banner (category card uploads only affect /game-accounts sub-pages)."""
    return GA_HOME_CATALOG_CARD_IMAGE


def ga_get_category_by_slug(slug: str) -> dict | None:
    conn = get_db()
    row = conn.execute("SELECT * FROM ga_categories WHERE slug=?", ((slug or "").strip(),)).fetchone()
    conn.close()
    return dict(row) if row else None


def ga_info_images_by_account_ids(account_ids: list[int]) -> dict[int, list[str]]:
    """Map account_id -> ordered list of stored filenames (not URLs)."""
    if not account_ids:
        return {}
    conn = get_db()
    qs = ",".join("?" * len(account_ids))
    rows = conn.execute(
        f"SELECT account_id, image_file FROM ga_account_info_images WHERE account_id IN ({qs}) "
        "ORDER BY account_id, sort_order, id",
        account_ids,
    ).fetchall()
    conn.close()
    out: dict[int, list[str]] = {}
    for r in rows:
        aid = int(r["account_id"])
        out.setdefault(aid, []).append(r["image_file"])
    return out


def ga_info_images_insert(account_id: int, filenames: list[str]) -> None:
    if not filenames:
        return
    conn = get_db()
    for i, fn in enumerate(filenames):
        fn = (fn or "").strip()
        if not fn:
            continue
        conn.execute(
            "INSERT INTO ga_account_info_images (account_id, image_file, sort_order) VALUES (?,?,?)",
            (account_id, fn[:220], i),
        )
    conn.commit()
    conn.close()


def _unlink_ga_info_files(filenames: list[str]) -> None:
    for fn in filenames:
        try:
            p = _GA_INFO_UPLOAD_DIR / (fn or "").strip()
            if p.is_file():
                p.unlink()
        except OSError:
            pass


def ga_list_accounts_public(category_id: int | None = None) -> list[dict]:
    """Available accounts only; no credentials."""
    conn = get_db()
    q = (
        "SELECT a.id, a.category_id, a.title, a.public_note, a.price_mmk, a.status, a.created_at, c.name AS category_name, c.slug AS category_slug "
        "FROM ga_accounts a JOIN ga_categories c ON c.id = a.category_id "
        "WHERE a.status = 'available'"
    )
    params: list = []
    if category_id is not None:
        q += " AND a.category_id = ?"
        params.append(category_id)
    q += " ORDER BY a.price_mmk ASC, a.id ASC"
    rows = conn.execute(q, params).fetchall()
    accs = [dict(r) for r in rows]
    conn.close()
    ids = [a["id"] for a in accs]
    by_id = ga_info_images_by_account_ids(ids)
    for a in accs:
        files = by_id.get(a["id"], [])
        a["info_image_urls"] = [f"/static/uploads/ga_info/{f}" for f in files]
    return accs


def ga_list_accounts_admin(category_id: int | None = None) -> list[dict]:
    conn = get_db()
    q = (
        "SELECT a.*, c.name AS category_name, c.slug AS category_slug FROM ga_accounts a "
        "JOIN ga_categories c ON c.id = a.category_id WHERE 1=1"
    )
    params: list = []
    if category_id is not None:
        q += " AND a.category_id = ?"
        params.append(category_id)
    q += " ORDER BY a.id DESC"
    rows = conn.execute(q, params).fetchall()
    accs = [dict(r) for r in rows]
    conn.close()
    ids = [a["id"] for a in accs]
    by_id = ga_info_images_by_account_ids(ids)
    for a in accs:
        a["info_image_count"] = len(by_id.get(a["id"], []))
    return accs


def ga_add_account(
    category_id: int,
    title: str,
    price_mmk: int,
    credentials: str,
    public_note: str = "",
) -> int | None:
    title = (title or "").strip()
    credentials = (credentials or "").strip()
    if not title or not credentials or price_mmk <= 0:
        return None
    conn = get_db()
    try:
        cur = conn.execute(
            "INSERT INTO ga_accounts (category_id, title, public_note, price_mmk, credentials, status) VALUES (?,?,?,?,?,'available')",
            (category_id, title, (public_note or "").strip(), int(price_mmk), credentials),
        )
        conn.commit()
        rid = cur.lastrowid
        conn.close()
        return rid
    except sqlite3.IntegrityError:
        conn.close()
        return None


def ga_delete_account(account_id: int) -> bool:
    conn = get_db()
    files = [
        r[0]
        for r in conn.execute(
            "SELECT image_file FROM ga_account_info_images WHERE account_id=?",
            (account_id,),
        ).fetchall()
    ]
    cur = conn.execute("DELETE FROM ga_accounts WHERE id=? AND status='available'", (account_id,))
    conn.commit()
    ok = cur.rowcount > 0
    conn.close()
    if ok and files:
        _unlink_ga_info_files(files)
    return ok


def ga_get_account_admin(account_id: int) -> dict | None:
    conn = get_db()
    row = conn.execute(
        "SELECT a.*, c.name AS category_name, c.slug AS category_slug "
        "FROM ga_accounts a JOIN ga_categories c ON c.id = a.category_id "
        "WHERE a.id=?",
        (account_id,),
    ).fetchone()
    conn.close()
    return dict(row) if row else None


def ga_update_account(
    account_id: int,
    *,
    title: str,
    price_mmk: int,
    public_note: str,
    credentials: str,
) -> bool:
    title = (title or "").strip()
    credentials = (credentials or "").strip()
    if not title or not credentials or price_mmk <= 0:
        return False
    conn = get_db()
    cur = conn.execute(
        "UPDATE ga_accounts SET title=?, public_note=?, price_mmk=?, credentials=? "
        "WHERE id=? AND status='available'",
        (
            title,
            (public_note or "").strip(),
            int(price_mmk),
            credentials,
            account_id,
        ),
    )
    conn.commit()
    n = cur.rowcount
    conn.close()
    return n > 0


def ga_info_images_list_rows(account_id: int) -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT id, image_file FROM ga_account_info_images WHERE account_id=? "
        "ORDER BY sort_order, id",
        (account_id,),
    ).fetchall()
    conn.close()
    return [{"id": int(r["id"]), "image_file": r["image_file"]} for r in rows]


def ga_info_images_delete_rows(account_id: int, row_ids: list[int]) -> None:
    if not row_ids:
        return
    conn = get_db()
    to_unlink: list[str] = []
    for rid in row_ids:
        row = conn.execute(
            "SELECT image_file FROM ga_account_info_images WHERE id=? AND account_id=?",
            (rid, account_id),
        ).fetchone()
        if row:
            to_unlink.append(row["image_file"])
            conn.execute("DELETE FROM ga_account_info_images WHERE id=?", (rid,))
    conn.commit()
    conn.close()
    _unlink_ga_info_files(to_unlink)


def ga_info_images_append_bounded(
    account_id: int,
    filenames: list[str],
    *,
    max_total: int | None = None,
) -> int:
    """Append new filenames until max_total images (None = no cap). Returns how many were added."""
    have = len(ga_info_images_list_rows(account_id))
    if max_total is None:
        room = len(filenames)
    else:
        room = max(0, max_total - have)
    if not room or not filenames:
        return 0
    to_add = [(fn or "").strip() for fn in filenames if (fn or "").strip()][:room]
    if not to_add:
        return 0
    ga_info_images_insert(account_id, to_add)
    return len(to_add)


def ga_purchase_account(user_id: int, account_id: int) -> dict:
    """Wallet debit; mark sold; order_history row. Returns credentials on success."""
    conn = get_db()
    conn.execute("BEGIN IMMEDIATE")
    try:
        row = conn.execute(
            "SELECT * FROM ga_accounts WHERE id=? AND status='available'",
            (account_id,),
        ).fetchone()
        if not row:
            conn.rollback()
            conn.close()
            return {"ok": False, "error": "not_available"}
        price = int(row["price_mmk"])
        bal_row = conn.execute("SELECT balance_mmk FROM wallets WHERE user_id=?", (user_id,)).fetchone()
        if not bal_row or bal_row["balance_mmk"] < price:
            conn.rollback()
            conn.close()
            return {"ok": False, "error": "insufficient_balance"}
        conn.execute(
            "UPDATE wallets SET balance_mmk = balance_mmk - ? WHERE user_id=?",
            (price, user_id),
        )
        cat = conn.execute("SELECT name FROM ga_categories WHERE id=?", (row["category_id"],)).fetchone()
        cat_name = (cat["name"] if cat else "Game Account")[:120]
        pkg = f"{cat_name} — {row['title']}"
        cur = conn.execute(
            "INSERT INTO order_history (user_id, product_url, product_name, package_name, amount_mmk, status, smile_step, smile_message) "
            "VALUES (?,?,?,?,?,'completed','game_account','')",
            (
                user_id,
                "internal://game-accounts",
                "Game Account",
                pkg,
                price,
            ),
        )
        oid = cur.lastrowid
        conn.execute(
            "UPDATE ga_accounts SET status='sold', sold_to_user_id=?, sold_at=?, order_id=? WHERE id=?",
            (user_id, time.time(), oid, account_id),
        )
        info_rows = conn.execute(
            "SELECT image_file FROM ga_account_info_images WHERE account_id=?",
            (account_id,),
        ).fetchall()
        info_files = [r["image_file"] for r in info_rows]
        conn.execute("DELETE FROM ga_account_info_images WHERE account_id=?", (account_id,))
        conn.commit()
        cred = row["credentials"]
        conn.close()
        if info_files:
            _unlink_ga_info_files(info_files)
        return {
            "ok": True,
            "order_id": oid,
            "credentials": cred,
            "title": row["title"],
            "amount_mmk": price,
        }
    except Exception as e:
        conn.rollback()
        conn.close()
        return {"ok": False, "error": str(e)[:200]}


def ga_get_purchased_accounts(user_id: int) -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT a.*, c.name AS category_name FROM ga_accounts a "
        "JOIN ga_categories c ON c.id = a.category_id "
        "WHERE a.sold_to_user_id=? AND a.status='sold' ORDER BY a.sold_at DESC",
        (user_id,),
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


# ── Support chat ──


def chat_append(
    user_id: int,
    body: str,
    *,
    is_admin: bool = False,
    image_file: str = "",
) -> int | None:
    body = (body or "").strip()
    image_file = (image_file or "").strip()
    if not body and not image_file:
        return None
    if len(body) > 4000 or len(image_file) > 220:
        return None
    conn = get_db()
    cur = conn.execute(
        "INSERT INTO chat_messages (user_id, is_admin, body, image_file) VALUES (?,?,?,?)",
        (user_id, 1 if is_admin else 0, body, image_file),
    )
    conn.commit()
    mid = cur.lastrowid
    conn.close()
    return mid


def chat_list_for_user(user_id: int, after_id: int = 0, limit: int = 200) -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM chat_messages WHERE user_id=? AND id>? ORDER BY id ASC LIMIT ?",
        (user_id, int(after_id), int(limit)),
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def chat_get_message_by_id(message_id: int) -> dict | None:
    conn = get_db()
    row = conn.execute("SELECT * FROM chat_messages WHERE id = ?", (int(message_id),)).fetchone()
    conn.close()
    return dict(row) if row else None


def chat_list_threads_for_admin() -> list[dict]:
    """One row per user with last message preview (includes [Photo] when image-only)."""
    conn = get_db()
    try:
        mids = conn.execute(
            "SELECT user_id, MAX(id) AS mid FROM chat_messages GROUP BY user_id ORDER BY mid DESC"
        ).fetchall()
    except sqlite3.OperationalError:
        conn.close()
        return []
    out: list[dict] = []
    for uid, mid in mids:
        m = conn.execute(
            "SELECT m.*, u.username FROM chat_messages m JOIN users u ON u.id = m.user_id WHERE m.id = ?",
            (mid,),
        ).fetchone()
        if not m:
            continue
        d = dict(m)
        preview = (d.get("body") or "").strip()
        img = (d.get("image_file") or "").strip()
        if img:
            preview = ((preview + " · ") if preview else "") + "[Photo]"
        d["last_body"] = (preview or "—")[:200]
        d["last_id"] = mid
        d["last_at"] = d.get("created_at")
        out.append(
            {
                "user_id": d["user_id"],
                "username": d["username"],
                "last_body": d["last_body"],
                "last_id": d["last_id"],
                "last_at": d["last_at"],
            }
        )
    conn.close()
    return out


def chat_history_for_admin(user_id: int, limit: int = 300) -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM chat_messages WHERE user_id=? ORDER BY id ASC LIMIT ?",
        (user_id, int(limit)),
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


# ── User ──

def create_user(username: str, password: str) -> dict | None:
    conn = get_db()
    try:
        pw_hash = generate_password_hash(password)
        cur = conn.execute(
            "INSERT INTO users (username, password_hash) VALUES (?, ?)",
            (username.strip(), pw_hash),
        )
        uid = cur.lastrowid
        conn.execute("INSERT INTO wallets (user_id, balance_mmk) VALUES (?, 0)", (uid,))
        conn.commit()
        return {"id": uid, "username": username.strip()}
    except sqlite3.IntegrityError:
        return None
    finally:
        conn.close()


def authenticate_user(username: str, password: str) -> dict | None:
    conn = get_db()
    row = conn.execute("SELECT * FROM users WHERE username = ?", (username.strip(),)).fetchone()
    conn.close()
    if row and check_password_hash(row["password_hash"], password):
        return dict(row)
    return None


def get_user(user_id: int) -> dict | None:
    conn = get_db()
    row = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    conn.close()
    return dict(row) if row else None


def list_users() -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT u.id, u.username, u.is_admin, u.created_at, u.profile_image, w.balance_mmk "
        "FROM users u LEFT JOIN wallets w ON u.id = w.user_id ORDER BY u.id"
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def update_profile_image(user_id: int, filename: str):
    conn = get_db()
    conn.execute("UPDATE users SET profile_image = ? WHERE id = ?", (filename, user_id))
    conn.commit()
    conn.close()


def change_password(user_id: int, current_password: str, new_password: str) -> bool:
    conn = get_db()
    row = conn.execute("SELECT password_hash FROM users WHERE id = ?", (user_id,)).fetchone()
    if not row or not check_password_hash(row["password_hash"], current_password):
        conn.close()
        return False
    new_hash = generate_password_hash(new_password)
    conn.execute("UPDATE users SET password_hash = ? WHERE id = ?", (new_hash, user_id))
    conn.commit()
    conn.close()
    return True


def admin_reset_password(user_id: int, new_password: str):
    conn = get_db()
    new_hash = generate_password_hash(new_password)
    conn.execute("UPDATE users SET password_hash = ? WHERE id = ?", (new_hash, user_id))
    conn.commit()
    conn.close()


# ── Password Reset Requests ──

def create_password_reset(user_id: int) -> int:
    conn = get_db()
    cur = conn.execute("INSERT INTO password_resets (user_id) VALUES (?)", (user_id,))
    conn.commit()
    rid = cur.lastrowid
    conn.close()
    return rid


def get_password_resets(status: str | None = None) -> list[dict]:
    conn = get_db()
    q = "SELECT pr.*, u.username FROM password_resets pr JOIN users u ON pr.user_id = u.id WHERE 1=1"
    params: list = []
    if status:
        q += " AND pr.status = ?"
        params.append(status)
    q += " ORDER BY pr.created_at DESC"
    rows = conn.execute(q, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def resolve_password_reset(reset_id: int, new_password: str) -> bool:
    conn = get_db()
    row = conn.execute("SELECT * FROM password_resets WHERE id = ? AND status = 'pending'", (reset_id,)).fetchone()
    if not row:
        conn.close()
        return False
    new_hash = generate_password_hash(new_password)
    conn.execute("UPDATE users SET password_hash = ? WHERE id = ?", (new_hash, row["user_id"]))
    conn.execute("UPDATE password_resets SET status = 'resolved', reviewed_at = ? WHERE id = ?", (time.time(), reset_id))
    conn.commit()
    conn.close()
    return True


def dismiss_password_reset(reset_id: int) -> bool:
    conn = get_db()
    conn.execute("UPDATE password_resets SET status = 'dismissed', reviewed_at = ? WHERE id = ?", (time.time(), reset_id))
    conn.commit()
    conn.close()
    return True


def get_user_by_username(username: str) -> dict | None:
    conn = get_db()
    row = conn.execute("SELECT * FROM users WHERE username = ?", (username.strip(),)).fetchone()
    conn.close()
    return dict(row) if row else None


def get_user_by_api_key(plain_key: str) -> dict | None:
    """Lookup user by raw API key (hash stored in DB)."""
    p = (plain_key or "").strip()
    if not p or not p.startswith(API_KEY_PREFIX):
        return None
    want = _hash_api_key(p)
    conn = get_db()
    row = conn.execute("SELECT * FROM users WHERE api_key_hash = ?", (want,)).fetchone()
    conn.close()
    return dict(row) if row else None


def user_has_api_key(user_id: int) -> bool:
    conn = get_db()
    row = conn.execute("SELECT api_key_hash FROM users WHERE id = ?", (user_id,)).fetchone()
    conn.close()
    return bool(row and (row["api_key_hash"] or "").strip())


def generate_api_key(user_id: int) -> str:
    """Returns the plain key once; store hash only."""
    plain = API_KEY_PREFIX + secrets.token_urlsafe(32)
    h = _hash_api_key(plain)
    conn = get_db()
    conn.execute("UPDATE users SET api_key_hash = ? WHERE id = ?", (h, user_id))
    conn.commit()
    conn.close()
    return plain


def revoke_api_key(user_id: int) -> None:
    conn = get_db()
    conn.execute("UPDATE users SET api_key_hash = '' WHERE id = ?", (user_id,))
    conn.commit()
    conn.close()


# ── Wallet ──

def get_balance(user_id: int) -> int:
    conn = get_db()
    row = conn.execute("SELECT balance_mmk FROM wallets WHERE user_id = ?", (user_id,)).fetchone()
    conn.close()
    return row["balance_mmk"] if row else 0


def adjust_balance(user_id: int, delta_mmk: int) -> int:
    conn = get_db()
    conn.execute("UPDATE wallets SET balance_mmk = balance_mmk + ? WHERE user_id = ?", (delta_mmk, user_id))
    conn.commit()
    bal = conn.execute("SELECT balance_mmk FROM wallets WHERE user_id = ?", (user_id,)).fetchone()
    conn.close()
    return bal["balance_mmk"] if bal else 0


def set_balance(user_id: int, amount_mmk: int) -> int:
    conn = get_db()
    conn.execute("UPDATE wallets SET balance_mmk = ? WHERE user_id = ?", (amount_mmk, user_id))
    conn.commit()
    conn.close()
    return amount_mmk


# ── Deposit ──

def create_deposit(user_id: int, amount_mmk: int, payment_method: str, sender_name: str, receipt_image: str = "") -> int:
    conn = get_db()
    cur = conn.execute(
        "INSERT INTO deposits (user_id, amount_mmk, payment_method, sender_name, receipt_image) VALUES (?,?,?,?,?)",
        (user_id, amount_mmk, payment_method, sender_name, receipt_image),
    )
    conn.commit()
    dep_id = cur.lastrowid
    conn.close()
    return dep_id


def get_deposits(user_id: int | None = None, status: str | None = None) -> list[dict]:
    conn = get_db()
    q = "SELECT d.*, u.username FROM deposits d JOIN users u ON d.user_id = u.id WHERE 1=1"
    params: list = []
    if user_id is not None:
        q += " AND d.user_id = ?"
        params.append(user_id)
    if status:
        q += " AND d.status = ?"
        params.append(status)
    q += " ORDER BY d.created_at DESC"
    rows = conn.execute(q, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def approve_deposit(deposit_id: int) -> bool:
    conn = get_db()
    row = conn.execute("SELECT * FROM deposits WHERE id = ? AND status = 'pending'", (deposit_id,)).fetchone()
    if not row:
        conn.close()
        return False
    conn.execute("UPDATE deposits SET status = 'approved', reviewed_at = ? WHERE id = ?", (time.time(), deposit_id))
    conn.execute("UPDATE wallets SET balance_mmk = balance_mmk + ? WHERE user_id = ?", (row["amount_mmk"], row["user_id"]))
    conn.commit()
    conn.close()
    return True


def reject_deposit(deposit_id: int) -> bool:
    conn = get_db()
    row = conn.execute("SELECT * FROM deposits WHERE id = ? AND status = 'pending'", (deposit_id,)).fetchone()
    if not row:
        conn.close()
        return False
    conn.execute("UPDATE deposits SET status = 'rejected', reviewed_at = ? WHERE id = ?", (time.time(), deposit_id))
    conn.commit()
    conn.close()
    return True


# ── Order History ──

def create_order(user_id: int, product_url: str, product_name: str, package_name: str, amount_mmk: int) -> int:
    conn = get_db()
    cur = conn.execute(
        "INSERT INTO order_history (user_id, product_url, product_name, package_name, amount_mmk) VALUES (?,?,?,?,?)",
        (user_id, product_url, product_name, package_name, amount_mmk),
    )
    conn.commit()
    oid = cur.lastrowid
    conn.close()
    return oid


def update_order_status(order_id: int, status: str, smile_step: str = "", smile_message: str = ""):
    conn = get_db()
    conn.execute(
        "UPDATE order_history SET status = ?, smile_step = ?, smile_message = ? WHERE id = ?",
        (status, smile_step, smile_message, order_id),
    )
    conn.commit()
    conn.close()


def get_orders(user_id: int | None = None) -> list[dict]:
    conn = get_db()
    q = "SELECT o.*, u.username FROM order_history o JOIN users u ON o.user_id = u.id WHERE 1=1"
    params: list = []
    if user_id is not None:
        q += " AND o.user_id = ?"
        params.append(user_id)
    q += " ORDER BY o.created_at DESC"
    rows = conn.execute(q, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_order_by_id(order_id: int, user_id: int) -> dict | None:
    conn = get_db()
    row = conn.execute(
        "SELECT o.*, u.username FROM order_history o JOIN users u ON o.user_id = u.id "
        "WHERE o.id = ? AND o.user_id = ?",
        (order_id, user_id),
    ).fetchone()
    conn.close()
    return dict(row) if row else None


def insert_social_boost_order(
    user_id: int,
    *,
    provider_order_id: int | None,
    service_id: int,
    service_name: str,
    service_type: str,
    link: str,
    quantity: int,
    comments_preview: str,
    amount_mmk: int,
    status: str,
    provider_message: str,
) -> int:
    conn = get_db()
    cur = conn.execute(
        """
        INSERT INTO social_boost_orders (
            user_id, provider_order_id, service_id, service_name, service_type,
            link, quantity, comments_preview, amount_mmk, status, provider_message
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?)
        """,
        (
            user_id,
            provider_order_id,
            service_id,
            service_name[:500],
            service_type[:120],
            link[:2000],
            quantity,
            comments_preview[:2000],
            amount_mmk,
            status[:80],
            provider_message[:500],
        ),
    )
    conn.commit()
    oid = cur.lastrowid
    conn.close()
    return oid


def get_social_boost_orders(user_id: int, limit: int = 50) -> list[dict]:
    conn = get_db()
    rows = conn.execute(
        "SELECT * FROM social_boost_orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?",
        (user_id, limit),
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_unified_user_orders(user_id: int, limit: int = 200) -> list[dict]:
    """
    Game credit orders (order_history) + social boosting rows, newest first.
    Each row has order_kind: 'game_credit' | 'social_boost'.
    """
    game = get_orders(user_id)
    sb = get_social_boost_orders(user_id, limit=max(limit, 100))
    out: list[dict] = []
    for o in game:
        out.append(
            {
                "order_kind": "game_credit",
                "created_at": o.get("created_at"),
                "id": o["id"],
                "amount_mmk": o.get("amount_mmk") or 0,
                "status": o.get("status") or "",
                "product_name": o.get("product_name") or "",
                "package_name": o.get("package_name") or "",
                "product_url": o.get("product_url") or "",
                "smile_step": o.get("smile_step") or "",
                "smile_message": o.get("smile_message") or "",
            }
        )
    for s in sb:
        out.append(
            {
                "order_kind": "social_boost",
                "created_at": s.get("created_at"),
                "id": s["id"],
                "amount_mmk": s.get("amount_mmk") or 0,
                "status": s.get("status") or "",
                "provider_order_id": s.get("provider_order_id"),
                "service_id": s.get("service_id"),
                "service_name": s.get("service_name") or "",
                "service_type": s.get("service_type") or "",
                "link": s.get("link") or "",
                "quantity": s.get("quantity") or 0,
                "comments_preview": s.get("comments_preview") or "",
                "provider_message": (s.get("provider_message") or "")[:400],
            }
        )
    out.sort(key=lambda r: float(r.get("created_at") or 0), reverse=True)
    return out[:limit]


def append_api_audit(
    user_id: int | None,
    audit_key_hint: str,
    method: str,
    path: str,
    ip: str | None,
    status_code: int,
) -> None:
    """Append one API access log row; trim old rows if over limit."""
    try:
        from config import API_AUDIT_MAX_ROWS
    except Exception:
        API_AUDIT_MAX_ROWS = 50_000

    conn = get_db()
    try:
        conn.execute(
            "INSERT INTO api_audit (user_id, audit_key_hint, method, path, ip, status_code) VALUES (?,?,?,?,?,?)",
            (user_id, (audit_key_hint or "")[:32], method[:16], path[:512], (ip or "")[:64], status_code),
        )
        conn.commit()
        n = conn.execute("SELECT COUNT(*) FROM api_audit").fetchone()[0]
        if n > API_AUDIT_MAX_ROWS:
            overflow = n - API_AUDIT_MAX_ROWS + 5000
            conn.execute(
                "DELETE FROM api_audit WHERE id IN (SELECT id FROM api_audit ORDER BY id ASC LIMIT ?)",
                (overflow,),
            )
            conn.commit()
    except sqlite3.OperationalError:
        pass
    finally:
        conn.close()


SHOP_FX_KEYS = frozenset({"php_to_mmk_rate", "brl_to_mmk_fallback_rate"})


def shop_fx_get_rate(key: str) -> float | None:
    if key not in SHOP_FX_KEYS:
        return None
    conn = get_db()
    try:
        row = conn.execute("SELECT value FROM shop_fx_settings WHERE key = ?", (key,)).fetchone()
        if not row:
            return None
        v = float(row[0])
        return v if 0 < v <= 1e9 else None
    finally:
        conn.close()


def shop_fx_set_rate(key: str, value: float) -> None:
    if key not in SHOP_FX_KEYS:
        raise ValueError("invalid shop fx key")
    v = float(value)
    if not (0 < v <= 1e9):
        raise ValueError("invalid rate")
    conn = get_db()
    try:
        conn.execute(
            """
            INSERT INTO shop_fx_settings (key, value, updated_at) VALUES (?, ?, strftime('%s','now'))
            ON CONFLICT(key) DO UPDATE SET value = excluded.value,
              updated_at = strftime('%s','now')
            """,
            (key, v),
        )
        conn.commit()
    finally:
        conn.close()


def shop_fx_delete(key: str) -> None:
    if key not in SHOP_FX_KEYS:
        return
    conn = get_db()
    try:
        conn.execute("DELETE FROM shop_fx_settings WHERE key = ?", (key,))
        conn.commit()
    finally:
        conn.close()


def shop_fx_rates_for_admin() -> dict[str, float | None]:
    return {
        "php_to_mmk_rate": shop_fx_get_rate("php_to_mmk_rate"),
        "brl_to_mmk_fallback_rate": shop_fx_get_rate("brl_to_mmk_fallback_rate"),
    }


init_db()