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

import os
import aiosqlite
from datetime import datetime

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 funded_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',
                target_count     INTEGER NOT NULL DEFAULT 100,
                current_count    INTEGER DEFAULT 0,
                is_active        INTEGER DEFAULT 1,
                added_at         TEXT,
                completed_at     TEXT
            )
        """)
        await db.execute("""
            CREATE TABLE IF NOT EXISTS funded_subscriptions (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                funded_channel_id INTEGER NOT NULL,
                user_id          INTEGER NOT NULL,
                subscribed_at    TEXT,
                UNIQUE(funded_channel_id, user_id)
            )
        """)
        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",   "🔴 البوت مقفول حالياً."),
            ("welcome_message",  ""),
            ("notify_new_users", "1"),
            ("notify_blocked",   "1"),
            ("fund_notify",      "1"),
        ]
        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:
            return {r[0]: r[1] for r in await cur.fetchall()}


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}
        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,username,first_name FROM users WHERE is_banned=0 AND is_blocked=0"
        ) as cur:
            return [{"id": r[0], "username": r[1], "first_name": r[2]}
                    for r in await cur.fetchall()]


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_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:
            return [{"id": r[0], "username": r[1], "first_name": r[2]}
                    for r in await cur.fetchall()]


async def get_statistics(bot_username: str) -> dict:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT COUNT(*) FROM users") as cur:
            total = (await cur.fetchone())[0]
        async with db.execute("SELECT COUNT(*) FROM users WHERE is_banned=1") as cur:
            banned = (await cur.fetchone())[0]
        async with db.execute("SELECT COUNT(*) FROM users WHERE is_blocked=1") as cur:
            blocked = (await cur.fetchone())[0]
        async with db.execute(
            "SELECT COUNT(*) FROM users WHERE is_banned=0 AND is_blocked=0"
        ) as cur:
            active = (await cur.fetchone())[0]
    return {"total": total, "banned": banned, "blocked": blocked, "active": active}


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 id,username,first_name FROM admins") as cur:
            return [{"id": r[0], "username": r[1], "first_name": r[2]}
                    for r in await cur.fetchall()]


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 bool(await cur.fetchone())


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_mandatory_channels(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT id,channel_id,channel_username,channel_title,invite_link,display_type "
            "FROM mandatory_channels WHERE is_active=1"
        ) as cur:
            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 await cur.fetchall()]


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,?)",
            (channel_id, channel_username, channel_title, invite_link, display_type, now)
        )
        await db.commit()


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 get_funded_channels(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT id,channel_id,channel_username,channel_title,invite_link,"
            "display_type,target_count,current_count "
            "FROM funded_channels WHERE is_active=1"
        ) as cur:
            return [{"id": r[0], "channel_id": r[1], "channel_username": r[2],
                     "channel_title": r[3], "invite_link": r[4], "display_type": r[5],
                     "target_count": r[6], "current_count": r[7]}
                    for r in await cur.fetchall()]


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


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


async def record_funded_sub(bot_username: str, channel_db_id: int,
                             user_id: int) -> bool:
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT id FROM funded_subscriptions WHERE funded_channel_id=? AND user_id=?",
            (channel_db_id, user_id)
        ) as cur:
            if await cur.fetchone():
                return False
        await db.execute(
            "INSERT INTO funded_subscriptions (funded_channel_id,user_id,subscribed_at) "
            "VALUES(?,?,?)",
            (channel_db_id, user_id, now)
        )
        await db.execute(
            "UPDATE funded_channels SET current_count=current_count+1 WHERE id=?",
            (channel_db_id,)
        )
        await db.commit()
        return True


async def get_funded_channel_count(bot_username: str,
                                    channel_db_id: int) -> tuple[int, int]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT current_count,target_count FROM funded_channels WHERE id=?",
            (channel_db_id,)
        ) as cur:
            row = await cur.fetchone()
            return (row[0], row[1]) if row else (0, 0)


async def mark_funded_complete(bot_username: str, channel_db_id: int):
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "UPDATE funded_channels SET is_active=0,completed_at=? WHERE id=?",
            (now, 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()