# script was programmed by:  Kodo
# For any inquiries:         @DevKodo
# Data source and operation: @SourceKodo
# communication_db.py version: 1.1

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):
    db_path = get_db_path(bot_username)
    async with aiosqlite.connect(db_path) 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_muted      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 messages (
                id                  INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id             INTEGER NOT NULL,
                user_message_id     INTEGER NOT NULL,
                dev_message_id      INTEGER,
                group_message_id    INTEGER,
                group_chat_id       INTEGER,
                reply_message_id    INTEGER,
                reply_dev_msg_id    INTEGER,
                created_at          TEXT
            )
        """)

        await db.execute("""
            CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages (user_id)
        """)

        await db.execute("""
            CREATE TABLE IF NOT EXISTS mandatory_channels (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                channel_id       INTEGER 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 groups (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                chat_id     INTEGER UNIQUE NOT NULL,
                chat_title  TEXT NOT NULL,
                username    TEXT,
                invite_link TEXT,
                is_active   INTEGER DEFAULT 1,
                added_at    TEXT
            )
        """)

        await db.execute("""
            CREATE TABLE IF NOT EXISTS message_stats (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                sent        INTEGER DEFAULT 0,
                received    INTEGER DEFAULT 0,
                updated_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"),
            ("notify_new_users",  "1"),
            ("notify_blocked",    "1"),
            ("receive_in_group",  "0"),
            ("welcome_message",   ""),
            ("send_btn_label",    "إرسال"),
            ("send_btn_color",    "default"),
            ("send_btn_emoji",    ""),
            ("back_btn_label",    "رجوع"),
            ("back_btn_color",    "default"),
            ("back_btn_emoji",    ""),
        ]
        for key, value in defaults:
            await db.execute(
                "INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)",
                (key, value)
            )

        await db.execute(
            "INSERT OR IGNORE INTO message_stats (id, sent, received, updated_at) VALUES (1, 0, 0, ?)",
            (datetime.utcnow().isoformat(),)
        )

        await db.commit()


async def get_setting(bot_username: str, key: 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 ""


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_muted": 0, "is_blocked": 0, "joined_at": now}
        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_muted": row[3], "is_blocked": row[4],
                    "last_active": row[5], "joined_at": row[6]}


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_muted": row[3], "is_blocked": row[4],
                    "last_active": row[5], "joined_at": row[6]}


async def set_user_muted(bot_username: str, user_id: int, muted: bool):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute("UPDATE users SET is_muted = ? WHERE id = ?", (1 if muted else 0, user_id))
        await db.commit()


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


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


async def save_message(bot_username: str, user_id: int, user_message_id: int,
                        dev_message_id: int = None, group_message_id: int = None,
                        group_chat_id: int = None) -> int:
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        cur = await db.execute(
            """INSERT INTO messages
               (user_id, user_message_id, dev_message_id, group_message_id, group_chat_id, created_at)
               VALUES (?, ?, ?, ?, ?, ?)""",
            (user_id, user_message_id, dev_message_id, group_message_id, group_chat_id, now)
        )
        await db.commit()
        return cur.lastrowid


async def update_dev_message_id(bot_username: str, user_id: int,
                                  user_message_id: int, dev_message_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            """UPDATE messages SET dev_message_id = ?
               WHERE user_id = ? AND user_message_id = ?""",
            (dev_message_id, user_id, user_message_id)
        )
        await db.commit()


async def update_group_message_id(bot_username: str, user_id: int,
                                    user_message_id: int, group_message_id: int,
                                    group_chat_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            """UPDATE messages SET group_message_id = ?, group_chat_id = ?
               WHERE user_id = ? AND user_message_id = ?""",
            (group_message_id, group_chat_id, user_id, user_message_id)
        )
        await db.commit()


async def save_reply(bot_username: str, user_id: int, user_message_id: int,
                      reply_message_id: int, reply_dev_msg_id: int):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            """UPDATE messages SET reply_message_id = ?, reply_dev_msg_id = ?
               WHERE user_id = ? AND user_message_id = ?""",
            (reply_message_id, reply_dev_msg_id, user_id, user_message_id)
        )
        await db.commit()


async def get_message_by_user_msg_id(bot_username: str, user_id: int,
                                       user_message_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT * FROM messages WHERE user_id = ? AND user_message_id = ?",
            (user_id, user_message_id)
        ) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {
                "id": row[0], "user_id": row[1],
                "user_message_id": row[2], "dev_message_id": row[3],
                "group_message_id": row[4], "group_chat_id": row[5],
                "reply_message_id": row[6], "reply_dev_msg_id": row[7],
            }


async def get_message_by_dev_msg_id(bot_username: str, dev_message_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT * FROM messages WHERE dev_message_id = ?",
            (dev_message_id,)
        ) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {
                "id": row[0], "user_id": row[1],
                "user_message_id": row[2], "dev_message_id": row[3],
                "group_message_id": row[4], "group_chat_id": row[5],
                "reply_message_id": row[6], "reply_dev_msg_id": row[7],
            }


async def get_message_by_group_msg_id(bot_username: str, group_message_id: int,
                                        group_chat_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT * FROM messages WHERE group_message_id = ? AND group_chat_id = ?",
            (group_message_id, group_chat_id)
        ) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {
                "id": row[0], "user_id": row[1],
                "user_message_id": row[2], "dev_message_id": row[3],
                "group_message_id": row[4], "group_chat_id": row[5],
                "reply_message_id": row[6], "reply_dev_msg_id": row[7],
            }


async def get_message_by_reply_message_id(bot_username: str,
                                               reply_message_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT * FROM messages WHERE reply_message_id = ?",
            (reply_message_id,)
        ) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {
                "id": row[0], "user_id": row[1],
                "user_message_id": row[2], "dev_message_id": row[3],
                "group_message_id": row[4], "group_chat_id": row[5],
                "reply_message_id": row[6], "reply_dev_msg_id": row[7],
            }


async def get_message_by_reply_dev_msg_id(bot_username: str,
                                            reply_dev_msg_id: int) -> dict | None:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute(
            "SELECT * FROM messages WHERE reply_dev_msg_id = ?",
            (reply_dev_msg_id,)
        ) as cur:
            row = await cur.fetchone()
            if not row:
                return None
            return {
                "id": row[0], "user_id": row[1],
                "user_message_id": row[2], "dev_message_id": row[3],
                "group_message_id": row[4], "group_chat_id": row[5],
                "reply_message_id": row[6], "reply_dev_msg_id": row[7],
            }


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: int,
                                  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 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 update_mandatory_channel_link(bot_username: str, channel_db_id: int, invite_link: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "UPDATE mandatory_channels SET invite_link = ? WHERE id = ?",
            (invite_link, channel_db_id)
        )
        await db.commit()


async def add_group(bot_username: str, chat_id: int, chat_title: str,
                     username: str | None, invite_link: str | None):
    now = datetime.utcnow().isoformat()
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            """INSERT OR REPLACE INTO groups
               (chat_id, chat_title, username, invite_link, is_active, added_at)
               VALUES (?, ?, ?, ?, 1, ?)""",
            (chat_id, chat_title, username, invite_link, now)
        )
        await db.commit()


async def get_groups(bot_username: str) -> list[dict]:
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        async with db.execute("SELECT * FROM groups WHERE is_active = 1") as cur:
            rows = await cur.fetchall()
            return [{"id": r[0], "chat_id": r[1], "chat_title": r[2],
                     "username": r[3], "invite_link": r[4]} for r in rows]


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


async def increment_sent(bot_username: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "UPDATE message_stats SET sent = sent + 1, updated_at = ? WHERE id = 1",
            (datetime.utcnow().isoformat(),)
        )
        await db.commit()


async def increment_received(bot_username: str):
    async with aiosqlite.connect(get_db_path(bot_username)) as db:
        await db.execute(
            "UPDATE message_stats SET received = received + 1, updated_at = ? WHERE id = 1",
            (datetime.utcnow().isoformat(),)
        )
        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:
        async with db.execute("SELECT COUNT(*) FROM users") as cur:
            total_users = (await cur.fetchone())[0]

        async with db.execute(
            "SELECT COUNT(*) FROM users WHERE last_active >= ?", (last_24h,)
        ) as cur:
            active_24h = (await cur.fetchone())[0]

        async with db.execute("SELECT COUNT(*) FROM users WHERE is_muted = 1") as cur:
            muted_users = (await cur.fetchone())[0]

        async with db.execute("SELECT COUNT(*) FROM users WHERE is_blocked = 1") as cur:
            blocked_users = (await cur.fetchone())[0]

        async with db.execute(
            "SELECT sent, received FROM message_stats WHERE id = 1"
        ) as cur:
            row  = await cur.fetchone()
            sent = row[0] if row else 0
            recv = row[1] if row else 0

        async with db.execute(
            "SELECT COUNT(*) FROM mandatory_channels WHERE is_active = 1"
        ) as cur:
            mand_count = (await cur.fetchone())[0]

    return {
        "total_users":   total_users,
        "active_24h":    active_24h,
        "muted_users":   muted_users,
        "blocked_users": blocked_users,
        "sent":          sent,
        "received":      recv,
        "mand_count":    mand_count,
    }

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()