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

import os
import aiosqlite
from datetime import datetime, timedelta, date

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_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,
               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 downloads (
               id            INTEGER PRIMARY KEY AUTOINCREMENT,
               user_id       INTEGER NOT NULL,
               platform      TEXT NOT NULL,
               downloaded_at TEXT
           )
       """)

       await db.execute("""
           CREATE TABLE IF NOT EXISTS daily_downloads (
               user_id  INTEGER NOT NULL,
               dl_date  TEXT NOT NULL,
               count    INTEGER DEFAULT 0,
               PRIMARY KEY (user_id, dl_date)
           )
       """)

       defaults = [
           ("is_open",           "1"),
           ("closed_message",    "🔴 البوت مقفول حالياً."),
           ("notify_new_users",  "1"),
           ("notify_blocked",    "1"),
           ("daily_limit",       "unlimited"),
           ("max_duration",      "10"),
           ("welcome_message",   ""),
           ("notify_funded_sub", "1"),
       ]
       for key, value in defaults:
           await db.execute(
               "INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)",
               (key, value)
           )
       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
           )
       """)

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


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_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]
       total_dl = (await (await db.execute("SELECT COUNT(*) FROM downloads")).fetchone())[0]
       dl_by_platform = {}
       for p in ["youtube", "tiktok", "instagram", "facebook", "snapchat", "pinterest"]:
           count = (await (await db.execute("SELECT COUNT(*) FROM downloads WHERE platform = ?", (p,))).fetchone())[0]
           dl_by_platform[p] = count
       mand_count = (await (await db.execute("SELECT COUNT(*) FROM mandatory_channels WHERE is_active = 1")).fetchone())[0]
       fund_count = (await (await db.execute("SELECT COUNT(*) FROM funded_channels WHERE is_active = 1")).fetchone())[0]
   return {
       "total_users": total, "active_24h": active, "banned_users": banned,
       "blocked_bot": blocked, "total_dl": total_dl,
       "dl_by_platform": dl_by_platform,
       "mand_count": mand_count, "fund_count": fund_count,
   }


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 add_funded_channel(bot_username: str, channel_id: str, channel_title: str,
                              channel_username: str | None, invite_link: str | None,
                              target_count: int, 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 funded_channels
              (channel_id, channel_username, channel_title, invite_link,
               display_type, target_count, current_count, is_active, added_at)
              VALUES (?, ?, ?, ?, ?, ?, 0, 1, ?)""",
           (str(channel_id), channel_username, channel_title, invite_link, display_type, target_count, now)
       )
       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 * FROM funded_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],
                    "target_count": r[6], "current_count": r[7]} for r in rows]


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_subscription(bot_username: str, funded_channel_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 = ?",
           (funded_channel_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 (?, ?, ?)",
           (funded_channel_id, user_id, now)
       )
       await db.execute(
           "UPDATE funded_channels SET current_count = current_count + 1 WHERE id = ?",
           (funded_channel_id,)
       )
       async with db.execute(
           "SELECT current_count, target_count FROM funded_channels WHERE id = ?",
           (funded_channel_id,)
       ) as cur:
           row = await cur.fetchone()
       completed = row and row[0] >= row[1]
       if completed:
           await db.execute(
               "UPDATE funded_channels SET is_active = 0, completed_at = ? WHERE id = ?",
               (now, funded_channel_id)
           )
       await db.commit()
       return completed


async def record_download(bot_username: str, user_id: int, platform: str):
   now   = datetime.utcnow().isoformat()
   today = date.today().isoformat()
   async with aiosqlite.connect(get_db_path(bot_username)) as db:
       await db.execute(
           "INSERT INTO downloads (user_id, platform, downloaded_at) VALUES (?, ?, ?)",
           (user_id, platform, now)
       )
       await db.execute(
           """INSERT INTO daily_downloads (user_id, dl_date, count) VALUES (?, ?, 1)
              ON CONFLICT(user_id, dl_date) DO UPDATE SET count = count + 1""",
           (user_id, today)
       )
       await db.commit()


async def get_user_daily_count(bot_username: str, user_id: int) -> int:
   today = date.today().isoformat()
   async with aiosqlite.connect(get_db_path(bot_username)) as db:
       async with db.execute(
           "SELECT count FROM daily_downloads WHERE user_id = ? AND dl_date = ?",
           (user_id, today)
       ) as cur:
           row = await cur.fetchone()
           return row[0] if row else 0


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