"""
SQLite bilan ishlash uchun yordamchi funksiyalar.
Bot kichik/o'rta hajmdagi auditoriya uchun mo'ljallangan — juda katta
yuklama (o'nlab ming faol foydalanuvchi) bo'lsa PostgreSQL'ga o'tish tavsiya
etiladi.
"""
import sqlite3
from contextlib import closing
from datetime import datetime, timedelta

import config

DATETIME_FMT = "%Y-%m-%dT%H:%M:%S"


def _now() -> str:
    return datetime.utcnow().strftime(DATETIME_FMT)


def _conn():
    con = sqlite3.connect(config.DB_PATH)
    con.row_factory = sqlite3.Row
    return con


def init_db():
    with closing(_conn()) as con, con:
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                username TEXT,
                joined_at TEXT,
                is_lifetime INTEGER DEFAULT 0,
                premium_until TEXT
            )
            """
        )
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS pending (
                sender_id INTEGER PRIMARY KEY,
                target_id INTEGER NOT NULL,
                is_reply INTEGER DEFAULT 0
            )
            """
        )
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS blocked (
                user_id INTEGER NOT NULL,
                blocked_id INTEGER NOT NULL,
                PRIMARY KEY (user_id, blocked_id)
            )
            """
        )
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS messages (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sender_id INTEGER NOT NULL,
                target_id INTEGER NOT NULL,
                header_msg_id INTEGER,
                content_msg_id INTEGER,
                confirm_msg_id INTEGER,
                is_reply INTEGER DEFAULT 0,
                created_at TEXT,
                deleted INTEGER DEFAULT 0
            )
            """
        )
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS payments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                item_type TEXT NOT NULL,
                stars INTEGER NOT NULL,
                charge_id TEXT,
                created_at TEXT
            )
            """
        )
        con.execute(
            """
            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT
            )
            """
        )


# ---------------------------------------------------------------- users ----
def register_user(user_id: int, username: str | None):
    with closing(_conn()) as con, con:
        con.execute(
            """
            INSERT INTO users (user_id, username, joined_at)
            VALUES (?, ?, ?)
            ON CONFLICT(user_id) DO UPDATE SET username=excluded.username
            """,
            (user_id, username, _now()),
        )


def user_exists(user_id: int) -> bool:
    with closing(_conn()) as con:
        return con.execute(
            "SELECT 1 FROM users WHERE user_id=?", (user_id,)
        ).fetchone() is not None


def get_user(user_id: int) -> sqlite3.Row | None:
    with closing(_conn()) as con:
        return con.execute(
            "SELECT * FROM users WHERE user_id=?", (user_id,)
        ).fetchone()


def get_all_user_ids() -> list[int]:
    with closing(_conn()) as con:
        return [r[0] for r in con.execute("SELECT user_id FROM users")]


# -------------------------------------------------------------- premium ----
def is_premium(user_id: int) -> bool:
    row = get_user(user_id)
    if not row:
        return False
    if row["is_lifetime"]:
        return True
    if row["premium_until"]:
        try:
            return datetime.strptime(row["premium_until"], DATETIME_FMT) > datetime.utcnow()
        except ValueError:
            return False
    return False


def grant_premium(user_id: int, plan_key: str):
    plan = config.PREMIUM_PLANS[plan_key]
    with closing(_conn()) as con, con:
        if plan["days"] is None:  # umrbod
            con.execute(
                "UPDATE users SET is_lifetime=1, premium_until=NULL WHERE user_id=?",
                (user_id,),
            )
            return
        row = con.execute(
            "SELECT premium_until, is_lifetime FROM users WHERE user_id=?", (user_id,)
        ).fetchone()
        base = datetime.utcnow()
        if row and row["premium_until"]:
            try:
                current = datetime.strptime(row["premium_until"], DATETIME_FMT)
                if current > base:
                    base = current
            except ValueError:
                pass
        new_until = base + timedelta(days=plan["days"])
        con.execute(
            "UPDATE users SET premium_until=? WHERE user_id=?",
            (new_until.strftime(DATETIME_FMT), user_id),
        )


def revoke_premium(user_id: int):
    with closing(_conn()) as con, con:
        con.execute(
            "UPDATE users SET is_lifetime=0, premium_until=NULL WHERE user_id=?",
            (user_id,),
        )


def premium_label(user_id: int) -> str:
    row = get_user(user_id)
    if not row:
        return "Yo'q"
    if row["is_lifetime"]:
        return "Umrbod"
    if row["premium_until"] and is_premium(user_id):
        return f"{row['premium_until']} gacha"
    return "Yo'q"


# ------------------------------------------------------------- pending -----
def set_pending(sender_id: int, target_id: int, is_reply: bool = False):
    with closing(_conn()) as con, con:
        con.execute(
            """
            INSERT INTO pending (sender_id, target_id, is_reply) VALUES (?, ?, ?)
            ON CONFLICT(sender_id) DO UPDATE SET
                target_id=excluded.target_id, is_reply=excluded.is_reply
            """,
            (sender_id, target_id, int(is_reply)),
        )


def pop_pending(sender_id: int):
    with closing(_conn()) as con, con:
        row = con.execute(
            "SELECT target_id, is_reply FROM pending WHERE sender_id=?", (sender_id,)
        ).fetchone()
        if row:
            con.execute("DELETE FROM pending WHERE sender_id=?", (sender_id,))
            return row["target_id"], bool(row["is_reply"])
        return None, False


# ------------------------------------------------------------- blocking ----
def block_user(user_id: int, blocked_id: int):
    with closing(_conn()) as con, con:
        con.execute(
            "INSERT OR IGNORE INTO blocked (user_id, blocked_id) VALUES (?, ?)",
            (user_id, blocked_id),
        )


def is_blocked(user_id: int, blocked_id: int) -> bool:
    with closing(_conn()) as con:
        return con.execute(
            "SELECT 1 FROM blocked WHERE user_id=? AND blocked_id=?",
            (user_id, blocked_id),
        ).fetchone() is not None


# -------------------------------------------------------------- messages ---
def log_message(sender_id, target_id, header_msg_id, content_msg_id,
                 confirm_msg_id, is_reply=False) -> int:
    with closing(_conn()) as con, con:
        cur = con.execute(
            """
            INSERT INTO messages
                (sender_id, target_id, header_msg_id, content_msg_id,
                 confirm_msg_id, is_reply, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (sender_id, target_id, header_msg_id, content_msg_id,
             confirm_msg_id, int(is_reply), _now()),
        )
        return cur.lastrowid


def get_message(row_id: int) -> sqlite3.Row | None:
    with closing(_conn()) as con:
        return con.execute(
            "SELECT * FROM messages WHERE id=?", (row_id,)
        ).fetchone()


def mark_message_deleted(row_id: int):
    with closing(_conn()) as con, con:
        con.execute("UPDATE messages SET deleted=1 WHERE id=?", (row_id,))


# --------------------------------------------------------------- payments --
def log_payment(user_id: int, item_type: str, stars: int, charge_id: str | None):
    with closing(_conn()) as con, con:
        con.execute(
            """
            INSERT INTO payments (user_id, item_type, stars, charge_id, created_at)
            VALUES (?, ?, ?, ?, ?)
            """,
            (user_id, item_type, stars, charge_id, _now()),
        )


# --------------------------------------------------------------- settings --
def get_setting(key: str, default: str | None = None) -> str | None:
    with closing(_conn()) as con:
        row = con.execute("SELECT value FROM settings WHERE key=?", (key,)).fetchone()
        return row["value"] if row else default


def set_setting(key: str, value: str):
    with closing(_conn()) as con, con:
        con.execute(
            """
            INSERT INTO settings (key, value) VALUES (?, ?)
            ON CONFLICT(key) DO UPDATE SET value=excluded.value
            """,
            (key, value),
        )


def is_maintenance() -> bool:
    return get_setting("maintenance", "0") == "1"


def set_maintenance(on: bool):
    set_setting("maintenance", "1" if on else "0")


# ---------------------------------------------------------------- stats ----
def stats() -> dict:
    with closing(_conn()) as con:
        total_users = con.execute("SELECT COUNT(*) FROM users").fetchone()[0]
        lifetime = con.execute(
            "SELECT COUNT(*) FROM users WHERE is_lifetime=1"
        ).fetchone()[0]
        active_timed = con.execute(
            "SELECT COUNT(*) FROM users WHERE is_lifetime=0 AND premium_until IS NOT NULL "
            "AND premium_until > ?",
            (_now(),),
        ).fetchone()[0]
        total_messages = con.execute("SELECT COUNT(*) FROM messages").fetchone()[0]
        today = datetime.utcnow().strftime("%Y-%m-%d")
        today_messages = con.execute(
            "SELECT COUNT(*) FROM messages WHERE created_at LIKE ?", (f"{today}%",)
        ).fetchone()[0]
        revenue = con.execute("SELECT COALESCE(SUM(stars), 0) FROM payments").fetchone()[0]
        return {
            "total_users": total_users,
            "premium_lifetime": lifetime,
            "premium_active": active_timed,
            "total_messages": total_messages,
            "today_messages": today_messages,
            "revenue_stars": revenue,
        }