# script was programmed by:  Kodo
# For any inquiries:         @DevKodo
# Data source and operation: @SourceKodo

import os
import aiosqlite
from datetime import datetime, timedelta

DB_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), "database")


def get_db_path(bot_username: str) -> str:
    os.makedirs(DB_DIR, exist_ok=True)
    return os.path.join(DB_DIR, f"{bot_username.lower()}.db")


async def init_db(bot_username: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("""
            CREATE TABLE IF NOT EXISTS settings (
                key   TEXT PRIMARY KEY,
                value TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id          INTEGER PRIMARY KEY,
                username    TEXT,
                first_name  TEXT,
                is_banned   INTEGER DEFAULT 0,
                is_blocked  INTEGER DEFAULT 0,
                last_active TEXT,
                joined_at   TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS admins (
                id         INTEGER PRIMARY KEY,
                username   TEXT,
                first_name TEXT,
                added_at   TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS mandatory_channels (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                channel_id       TEXT UNIQUE NOT NULL,
                channel_username TEXT,
                channel_title    TEXT NOT NULL,
                invite_link      TEXT,
                display_type     TEXT DEFAULT 'buttons',
                is_active        INTEGER DEFAULT 1,
                added_at         TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS activity (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER NOT NULL,
                action     TEXT NOT NULL,
                created_at TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS custom_buttons (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                btn_type    TEXT NOT NULL,
                label       TEXT NOT NULL,
                content     TEXT NOT NULL,
                position    INTEGER DEFAULT 0,
                created_at  TEXT
            )
        """)

        defaults = [
            ("is_open",          "1"),
            ("closed_message",   "🔴 البوت مقفول حالياً."),
            ("notify_new_users", "1"),
            ("notify_blocked",   "1"),
            ("welcome_message",  ""),
        ]
        for key, value in defaults:
            await db.execute(
                "INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)",
                (key, value)
            )
        await db.commit()


async def get_setting(bot_username: str, key: str, default: str = "") -> str:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT value FROM settings WHERE key = ?", (key,)) as cur:
            row = await cur.fetchone()
            return row[0] if row else default


async def set_setting(bot_username: str, key: str, value: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)",
            (key, value)
        )
        await db.commit()


async def get_all_settings(bot_username: str) -> dict:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT key, value FROM settings") as cur:
            rows = await cur.fetchall()
            return {row[0]: row[1] for row in rows}


async def get_or_create_user(bot_username: str, user_id: int,
                              username: str | None, first_name: str) -> dict:
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT * FROM users WHERE id = ?", (user_id,)) as cur:
            row = await cur.fetchone()
        if not row:
            await db.execute(
                "INSERT INTO users (id, username, first_name, last_active, joined_at) VALUES (?, ?, ?, ?, ?)",
                (user_id, username, first_name, now, now)
            )
            await db.commit()
            return {"id": user_id, "username": username, "first_name": first_name,
                    "is_banned": 0, "is_blocked": 0, "is_new": True}
        else:
            await db.execute(
                "UPDATE users SET username = ?, first_name = ?, last_active = ? WHERE id = ?",
                (username, first_name, now, user_id)
            )
            await db.commit()
            return {"id": row[0], "username": row[1], "first_name": row[2],
                    "is_banned": row[3], "is_blocked": row[4], "is_new": False}


async def get_user(bot_username: str, user_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT * FROM users WHERE id = ?", (user_id,)) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {"id": row[0], "username": row[1], "first_name": row[2],
                    "is_banned": row[3], "is_blocked": row[4]}


async def get_all_active_users(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT id FROM users WHERE is_blocked = 0 AND is_banned = 0") as cur:
            rows = await cur.fetchall()
            return [{"id": r[0]} for r in rows]


async def get_banned_users(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT id, username, first_name FROM users WHERE is_banned = 1"
        ) as cur:
            rows = await cur.fetchall()
            return [{"id": r[0], "username": r[1], "first_name": r[2]} for r in rows]


async def ban_user(bot_username: str, user_id: int) -> bool:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT id FROM users WHERE id = ?", (user_id,)) as cur:
            if not await cur.fetchone():
                return False
        await db.execute("UPDATE users SET is_banned = 1 WHERE id = ?", (user_id,))
        await db.commit()
        return True


async def unban_user(bot_username: str, user_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("UPDATE users SET is_banned = 0 WHERE id = ?", (user_id,))
        await db.commit()


async def get_statistics(bot_username: str) -> dict:
    now      = datetime.utcnow()
    last_24h = (now - timedelta(hours=24)).isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        total       = (await (await db.execute("SELECT COUNT(*) FROM users")).fetchone())[0]
        active      = (await (await db.execute("SELECT COUNT(*) FROM users WHERE last_active >= ?", (last_24h,))).fetchone())[0]
        banned      = (await (await db.execute("SELECT COUNT(*) FROM users WHERE is_banned = 1")).fetchone())[0]
        blocked     = (await (await db.execute("SELECT COUNT(*) FROM users WHERE is_blocked = 1")).fetchone())[0]
        messages    = (await (await db.execute("SELECT COUNT(*) FROM activity WHERE action = 'message'")).fetchone())[0]
        decorations = (await (await db.execute("SELECT COUNT(*) FROM activity WHERE action = 'decorate'")).fetchone())[0]
        mand_count  = (await (await db.execute("SELECT COUNT(*) FROM mandatory_channels WHERE is_active = 1")).fetchone())[0]
    return {
        "total_users":  total,  "active_24h":   active,
        "banned_users": banned, "blocked_bot":  blocked,
        "messages":     messages, "decorations": decorations,
        "mand_count":   mand_count,
    }


async def record_activity(bot_username: str, user_id: int, action: str):
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "INSERT INTO activity (user_id, action, created_at) VALUES (?, ?, ?)",
            (user_id, action, now)
        )
        await db.commit()


async def add_admin(bot_username: str, user_id: int, username: str | None, first_name: str):
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "INSERT OR REPLACE INTO admins (id, username, first_name, added_at) VALUES (?, ?, ?, ?)",
            (user_id, username, first_name, now)
        )
        await db.commit()


async def remove_admin(bot_username: str, user_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("DELETE FROM admins WHERE id = ?", (user_id,))
        await db.commit()


async def get_admins(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT * FROM admins") as cur:
            rows = await cur.fetchall()
            return [{"id": r[0], "username": r[1], "first_name": r[2]} for r in rows]


async def is_admin(bot_username: str, user_id: int) -> bool:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT id FROM admins WHERE id = ?", (user_id,)) as cur:
            return await cur.fetchone() is not None


async def clear_admins(bot_username: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("DELETE FROM admins")
        await db.commit()


async def add_mandatory_channel(bot_username: str, channel_id: str, channel_title: str,
                                  channel_username: str | None, invite_link: str | None,
                                  display_type: str = "buttons"):
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            """INSERT OR REPLACE INTO mandatory_channels
               (channel_id, channel_username, channel_title, invite_link, display_type, is_active, added_at)
               VALUES (?, ?, ?, ?, ?, 1, ?)""",
            (str(channel_id), channel_username, channel_title, invite_link, display_type, now)
        )
        await db.commit()


async def get_mandatory_channels(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT * FROM mandatory_channels WHERE is_active = 1") as cur:
            rows = await cur.fetchall()
            return [{"id": r[0], "channel_id": r[1], "channel_username": r[2],
                     "channel_title": r[3], "invite_link": r[4], "display_type": r[5]} for r in rows]


async def remove_mandatory_channel(bot_username: str, channel_db_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "UPDATE mandatory_channels SET is_active = 0 WHERE id = ?", (channel_db_id,)
        )
        await db.commit()


async def _ensure_cbtn_columns(db):
    for col, ddl in [
        ("emoji",           "ALTER TABLE custom_buttons ADD COLUMN emoji TEXT"),
        ("custom_emoji_id", "ALTER TABLE custom_buttons ADD COLUMN custom_emoji_id TEXT"),
        ("color",           "ALTER TABLE custom_buttons ADD COLUMN color TEXT"),
        ("is_visible",      "ALTER TABLE custom_buttons ADD COLUMN is_visible INTEGER DEFAULT 1"),
    ]:
        try:
            await db.execute(ddl)
        except Exception:
            pass


async def add_custom_button(bot_username: str, btn_type: str, label: str, content: str) -> int:
    from datetime import datetime as _dt
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await _ensure_cbtn_columns(db)
        cur = await db.execute(
            "INSERT INTO custom_buttons (btn_type, label, content, position, created_at, is_visible) "
            "VALUES (?, ?, ?, (SELECT COALESCE(MAX(position),0)+1 FROM custom_buttons), ?, 1)",
            (btn_type, label, content, _dt.utcnow().isoformat())
        )
        await db.commit()
        return cur.lastrowid


async def get_custom_buttons(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await _ensure_cbtn_columns(db)
        async with db.execute(
            "SELECT id, btn_type, label, content, position, emoji, custom_emoji_id, color, "
            "COALESCE(is_visible,1) FROM custom_buttons ORDER BY position"
        ) as cur:
            rows = await cur.fetchall()
            return [
                {"id": r[0], "btn_type": r[1], "label": r[2], "content": r[3], "position": r[4],
                 "emoji": r[5], "custom_emoji_id": r[6], "color": r[7], "is_visible": r[8]}
                for r in rows
            ]


async def get_custom_button(bot_username: str, btn_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await _ensure_cbtn_columns(db)
        async with db.execute(
            "SELECT id, btn_type, label, content, position, emoji, custom_emoji_id, color, "
            "COALESCE(is_visible,1) FROM custom_buttons WHERE id = ?",
            (btn_id,)
        ) as cur:
            r = await cur.fetchone()
            if not r:
                return None
            return {"id": r[0], "btn_type": r[1], "label": r[2], "content": r[3], "position": r[4],
                    "emoji": r[5], "custom_emoji_id": r[6], "color": r[7], "is_visible": r[8]}


async def update_custom_button(bot_username: str, btn_id: int, btn_type: str = None,
                                label: str = None, content: str = None,
                                emoji: str = "__keep__", custom_emoji_id: str = "__keep__",
                                color: str = "__keep__", is_visible: int = None):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await _ensure_cbtn_columns(db)
        sets, vals = [], []
        if btn_type is not None:
            sets.append("btn_type = ?"); vals.append(btn_type)
        if label is not None:
            sets.append("label = ?"); vals.append(label)
        if content is not None:
            sets.append("content = ?"); vals.append(content)
        if emoji != "__keep__":
            sets.append("emoji = ?"); vals.append(emoji)
        if custom_emoji_id != "__keep__":
            sets.append("custom_emoji_id = ?"); vals.append(custom_emoji_id)
        if color != "__keep__":
            sets.append("color = ?"); vals.append(color)
        if is_visible is not None:
            sets.append("is_visible = ?"); vals.append(is_visible)
        if not sets:
            return
        vals.append(btn_id)
        await db.execute(f"UPDATE custom_buttons SET {', '.join(sets)} WHERE id = ?", vals)
        await db.commit()


async def delete_custom_button(bot_username: str, btn_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("DELETE FROM custom_buttons WHERE id = ?", (btn_id,))
        await db.commit()