# database.py
import aiosqlite
from datetime import datetime
from config import DB_PATH, DEFAULT_SETTINGS

async def init_db():
    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 sponsor_channels (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                channel_id   TEXT UNIQUE,
                channel_name TEXT
            )
        """)

        # کاربران
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id    INTEGER PRIMARY KEY,
                first_name TEXT,
                username   TEXT,
                joined_at  TEXT
            )
        """)

        # صف تایید آهنگ
        await db.execute("""
            CREATE TABLE IF NOT EXISTS pending_music (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id      INTEGER,
                first_name   TEXT,
                username     TEXT,
                file_id      TEXT,
                performer    TEXT,
                title        TEXT,
                duration     INTEGER,
                is_voice     INTEGER DEFAULT 0,
                is_anon      INTEGER DEFAULT 0,
                submitted_at TEXT
            )
        """)

        # لاگ آمار
        await db.execute("""
            CREATE TABLE IF NOT EXISTS stats (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER,
                action     TEXT,
                detail     TEXT,
                created_at TEXT
            )
        """)

        # تنظیمات پیش‌فرض
        for k, v in DEFAULT_SETTINGS.items():
            await db.execute(
                "INSERT OR IGNORE INTO settings (key,value) VALUES (?,?)", (k, v)
            )
        await db.commit()

async def get_setting(key: str) -> str:
    async with aiosqlite.connect(DB_PATH) 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(key: str, value: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "INSERT OR REPLACE INTO settings (key,value) VALUES (?,?)", (key, value)
        )
        await db.commit()

async def upsert_user(user):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("""
            INSERT OR IGNORE INTO users (user_id, first_name, username, joined_at)
            VALUES (?,?,?,?)
        """, (user.id, user.first_name, user.username, datetime.now().isoformat()))
        await db.commit()

async def log_stat(user_id: int, action: str, detail: str = ""):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "INSERT INTO stats (user_id, action, detail, created_at) VALUES (?,?,?,?)",
            (user_id, action, detail, datetime.now().isoformat())
        )
        await db.commit()

async def get_stats() -> dict:
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute("SELECT COUNT(*) FROM users") as c:
            total_users = (await c.fetchone())[0]
        async with db.execute("SELECT COUNT(*) FROM stats WHERE action='send_music'") as c:
            total_songs = (await c.fetchone())[0]
        async with db.execute("SELECT COUNT(*) FROM stats WHERE action='send_anon'") as c:
            total_anon = (await c.fetchone())[0]
        async with db.execute("SELECT COUNT(*) FROM pending_music") as c:
            pending_count = (await c.fetchone())[0]
        async with db.execute("""
            SELECT user_id, COUNT(*) as cnt FROM stats
            WHERE action IN ('send_music','send_anon')
            GROUP BY user_id ORDER BY cnt DESC LIMIT 1
        """) as c:
            top = await c.fetchone()
        async with db.execute("""
            SELECT COUNT(*) FROM stats
            WHERE action IN ('send_music','send_anon')
              AND date(created_at) = date('now')
        """) as c:
            today_songs = (await c.fetchone())[0]
    return {
        "total_users": total_users,
        "total_songs": total_songs,
        "total_anon": total_anon,
        "pending": pending_count,
        "top_user": top,
        "today_songs": today_songs,
    }

async def get_all_user_ids() -> list:
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute("SELECT user_id FROM users") as cur:
            rows = await cur.fetchall()
    return [r[0] for r in rows]

async def add_pending(user, file_id, performer, title, duration, is_voice, is_anon) -> int:
    async with aiosqlite.connect(DB_PATH) as db:
        cur = await db.execute("""
            INSERT INTO pending_music
              (user_id, first_name, username, file_id, performer,
               title, duration, is_voice, is_anon, submitted_at)
            VALUES (?,?,?,?,?,?,?,?,?,?)
        """, (
            user.id, user.first_name, user.username or "",
            file_id, performer or "", title or "",
            duration or 0,
            1 if is_voice else 0,
            1 if is_anon else 0,
            datetime.now().isoformat()
        ))
        await db.commit()
        return cur.lastrowid

async def get_pending(pending_id: int):
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute("SELECT * FROM pending_music WHERE id=?", (pending_id,)) as cur:
            return await cur.fetchone()

async def delete_pending(pending_id: int):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("DELETE FROM pending_music WHERE id=?", (pending_id,))
        await db.commit()

async def get_sponsor_channels():
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute("SELECT channel_id, channel_name FROM sponsor_channels") as cur:
            return await cur.fetchall()

async def add_sponsor_channel(channel_id: str, channel_name: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            "INSERT OR IGNORE INTO sponsor_channels (channel_id, channel_name) VALUES (?,?)",
            (channel_id, channel_name)
        )
        await db.commit()

async def remove_sponsor_channel(channel_id: str):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("DELETE FROM sponsor_channels WHERE channel_id=?", (channel_id,))
        await db.commit()

async def user_daily_uploads(user_id: int) -> int:
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute("""
            SELECT COUNT(*)
            FROM stats
            WHERE user_id=?
            AND action IN ('send_music', 'send_anon')
            AND datetime(created_at) >= datetime('now', '-1 day')
        """, (user_id,)) as cur:
            row = await cur.fetchone()
            return row[0]