from __future__ import annotations

from src.database import connect


def one(sql, args=()):
    with connect() as c:
        return c.execute(sql, args).fetchone()


def all(sql, args=()):
    with connect() as c:
        return c.execute(sql, args).fetchall()


def execute(sql, args=()):
    with connect() as c:
        cur = c.execute(sql, args)
        return cur.lastrowid


def setting(key, default=""):
    row = one("SELECT value FROM settings WHERE key=?", (key,))
    return row["value"] if row else default


def setting_detail(key):
    return one(
        "SELECT key, value, COALESCE(default_value, value) default_value FROM settings WHERE key=?",
        (key,),
    )


def set_setting(key, value):
    execute(
        """
        INSERT INTO settings(key, value, default_value, updated_at)
        VALUES(?, ?, ?, CURRENT_TIMESTAMP)
        ON CONFLICT(key) DO UPDATE SET
            value=excluded.value,
            updated_at=CURRENT_TIMESTAMP
        """,
        (key, value, value),
    )


def reset_setting(key):
    execute(
        """
        UPDATE settings
        SET value=COALESCE(default_value, value), updated_at=CURRENT_TIMESTAMP
        WHERE key=?
        """,
        (key,),
    )
    return setting(key)


def reset_settings(keys):
    with connect() as c:
        c.executemany(
            """
            UPDATE settings
            SET value=COALESCE(default_value, value), updated_at=CURRENT_TIMESTAMP
            WHERE key=?
            """,
            [(key,) for key in keys],
        )


def upsert_user(uid, username, name):
    execute(
        """
        INSERT INTO users(id,username,full_name) VALUES(?,?,?)
        ON CONFLICT(id) DO UPDATE SET
            username=excluded.username,
            full_name=excluded.full_name,
            last_seen=CURRENT_TIMESTAMP
        """,
        (uid, username, name),
    )


def user(uid):
    return one("SELECT * FROM users WHERE id=?", (uid,))


def users(limit=None, offset=0):
    if limit is None:
        return all("SELECT * FROM users ORDER BY joined_at DESC")
    return all("SELECT * FROM users ORDER BY joined_at DESC LIMIT ? OFFSET ?", (limit, offset))


def user_count():
    row = one("SELECT COUNT(*) n FROM users")
    return row["n"] if row else 0


def ban_user(uid, banned=True):
    execute("UPDATE users SET is_banned=? WHERE id=?", (1 if banned else 0, uid))


def categories():
    return all("SELECT * FROM categories WHERE active=1 ORDER BY name")


def category(cid):
    return one("SELECT * FROM categories WHERE id=?", (cid,))


def category_media_count(category_id: int) -> int:
    row = one(
        "SELECT COUNT(*) AS total FROM media WHERE category_id=? AND active=1",
        (category_id,),
    )
    return int(row["total"] or 0) if row else 0


def category_media_list(category_id: int, limit: int = 8, offset: int = 0):
    return all(
        """
        SELECT m.*, c.name AS category,
        (SELECT COUNT(*) FROM media_views v WHERE v.media_id=m.id) AS views
        FROM media m
        JOIN categories c ON c.id=m.category_id
        WHERE m.category_id=? AND m.active=1
        ORDER BY m.id DESC
        LIMIT ? OFFSET ?
        """,
        (category_id, limit, offset),
    )


def add_category(name):
    clean = name.strip()
    existing = one("SELECT id FROM categories WHERE name=? COLLATE NOCASE", (clean,))
    if existing:
        return existing["id"]
    return execute("INSERT INTO categories(name) VALUES(?)", (clean,))


def rename_category(cid, name):
    execute("UPDATE categories SET name=? WHERE id=?", (name.strip(), cid))


def delete_category(cid):
    fallback = one("SELECT id FROM categories WHERE name='masvir'")
    if fallback and fallback["id"] == cid:
        raise ValueError("Kategori masvir tidak boleh dihapus")
    execute("UPDATE media SET category_id=? WHERE category_id=?", (fallback["id"], cid))
    execute("DELETE FROM categories WHERE id=?", (cid,))


def templates():
    return all(
        """
        SELECT * FROM caption_templates
        ORDER BY is_default DESC, is_system DESC, name
        """
    )


def template(tid):
    return one("SELECT * FROM caption_templates WHERE id=?", (tid,))


def default_template():
    return one(
        "SELECT * FROM caption_templates ORDER BY is_default DESC,id LIMIT 1"
    )


def add_template(name, content):
    return execute(
        "INSERT INTO caption_templates(name,content) VALUES(?,?)",
        (name.strip(), content),
    )


def update_template(tid, content):
    execute("UPDATE caption_templates SET content=? WHERE id=?", (content, tid))


def set_default_template(tid):
    with connect() as c:
        c.execute("UPDATE caption_templates SET is_default=0")
        c.execute("UPDATE caption_templates SET is_default=1 WHERE id=?", (tid,))


def reset_template(tid):
    execute(
        """
        UPDATE caption_templates
        SET content=COALESCE(default_content, content)
        WHERE id=? AND is_system=1
        """,
        (tid,),
    )


def delete_template(tid):
    execute(
        "DELETE FROM caption_templates WHERE id=? AND is_default=0 AND is_system=0",
        (tid,),
    )


def add_media(code, file_id, thumb, title, caption, filename, category_id):
    return execute(
        """
        INSERT INTO media(
            code,telegram_file_id,thumbnail_file_id,title,
            user_caption,filename,category_id
        ) VALUES(?,?,?,?,?,?,?)
        """,
        (code, file_id, thumb, title, caption, filename, category_id),
    )


def media_by_code(code):
    return one(
        """
        SELECT m.*,c.name category FROM media m
        JOIN categories c ON c.id=m.category_id
        WHERE m.code=? AND m.active=1
        """,
        (code,),
    )


def media_by_id(mid):
    return one(
        """
        SELECT m.*,c.name category FROM media m
        JOIN categories c ON c.id=m.category_id
        WHERE m.id=?
        """,
        (mid,),
    )


def media_count():
    row = one("SELECT COUNT(*) n FROM media WHERE active=1")
    return row["n"] if row else 0


def media_list(limit=10, offset=0):
    return all(
        """
        SELECT m.*,c.name category,
        (SELECT COUNT(*) FROM media_views v WHERE v.media_id=m.id) views
        FROM media m JOIN categories c ON c.id=m.category_id
        WHERE m.active=1 ORDER BY m.id DESC LIMIT ? OFFSET ?
        """,
        (limit, offset),
    )


def search_media(q):
    return all(
        """
        SELECT m.*,c.name category FROM media m
        JOIN categories c ON c.id=m.category_id
        WHERE m.active=1 AND (m.title LIKE ? OR c.name LIKE ?)
        ORDER BY m.id DESC LIMIT 20
        """,
        (f"%{q}%", f"%{q}%"),
    )


def update_media(mid, field, value):
    allowed = {"title", "user_caption", "filename", "thumbnail_file_id", "category_id", "telegram_file_id"}
    if field not in allowed:
        raise ValueError("Field tidak valid")
    execute(
        f"UPDATE media SET {field}=?,updated_at=CURRENT_TIMESTAMP WHERE id=?",
        (value, mid),
    )


def set_channel_post(mid, chat_id, msg_id):
    execute(
        "UPDATE media SET channel_chat_id=?,channel_message_id=? WHERE id=?",
        (chat_id, msg_id, mid),
    )


def delete_media(mid):
    execute("DELETE FROM media WHERE id=?", (mid,))


def add_view(mid, uid):
    execute("INSERT INTO media_views(media_id,user_id) VALUES(?,?)", (mid, uid))


def media_view_stats(mid):
    row = one(
        "SELECT COUNT(*) total_views, COUNT(DISTINCT user_id) total_viewers FROM media_views WHERE media_id=?",
        (mid,),
    )
    return {
        "total_views": int(row["total_views"] or 0) if row else 0,
        "total_viewers": int(row["total_viewers"] or 0) if row else 0,
    }


def fsubs(include_inactive=False):
    where = "" if include_inactive else "WHERE active=1"
    return all(f"SELECT * FROM force_sub_channels {where} ORDER BY id")


def fsub(fid):
    return one("SELECT * FROM force_sub_channels WHERE id=?", (fid,))


def update_fsub(fid, field, value):
    allowed = {"chat_id", "title", "invite_link", "button_text"}
    if field not in allowed:
        raise ValueError("Field force-sub tidak valid")
    execute(f"UPDATE force_sub_channels SET {field}=? WHERE id=?", (value, fid))


def toggle_fsub(fid):
    execute("UPDATE force_sub_channels SET active=CASE active WHEN 1 THEN 0 ELSE 1 END WHERE id=?", (fid,))


def add_fsub(chat_id, title, link, button):
    return execute(
        """
        INSERT INTO force_sub_channels(chat_id,title,invite_link,button_text)
        VALUES(?,?,?,?)
        """,
        (chat_id, title, link, button),
    )


def delete_fsub(fid):
    execute("DELETE FROM force_sub_channels WHERE id=?", (fid,))


def buttons(include_inactive=False):
    where = "" if include_inactive else "WHERE wb.active=1"
    return all(
        f"""
        SELECT wb.*,
               CASE
                 WHEN wb.setting_key IS NOT NULL THEN COALESCE(s.value, wb.url)
                 ELSE wb.url
               END AS resolved_url
        FROM welcome_buttons wb
        LEFT JOIN settings s ON s.key=wb.setting_key
        {where}
        ORDER BY wb.position, wb.id
        """
    )


def button(bid):
    return one(
        """
        SELECT wb.*,
               CASE
                 WHEN wb.setting_key IS NOT NULL THEN COALESCE(s.value, wb.url)
                 ELSE wb.url
               END AS resolved_url
        FROM welcome_buttons wb
        LEFT JOIN settings s ON s.key=wb.setting_key
        WHERE wb.id=?
        """,
        (bid,),
    )


def add_button(label, url):
    return execute(
        """
        INSERT INTO welcome_buttons(label,url,position)
        VALUES(?,?,COALESCE((SELECT MAX(position)+1 FROM welcome_buttons),0))
        """,
        (label.strip(), url.strip()),
    )


def update_button_label(bid, label):
    execute("UPDATE welcome_buttons SET label=? WHERE id=?", (label.strip(), bid))


def update_button_url(bid, url):
    row = button(bid)
    if row and row["setting_key"]:
        set_setting(row["setting_key"], url.strip())
    else:
        execute("UPDATE welcome_buttons SET url=? WHERE id=?", (url.strip(), bid))


def toggle_button(bid):
    execute(
        "UPDATE welcome_buttons SET active=CASE active WHEN 1 THEN 0 ELSE 1 END WHERE id=?",
        (bid,),
    )


def reset_button(bid):
    row = button(bid)
    if not row or not row["is_system"]:
        return
    execute(
        "UPDATE welcome_buttons SET label=COALESCE(default_label,label), active=1 WHERE id=?",
        (bid,),
    )
    if row["setting_key"]:
        reset_setting(row["setting_key"])


def delete_button(bid):
    execute("DELETE FROM welcome_buttons WHERE id=? AND is_system=0", (bid,))


def stats():
    with connect() as c:
        return dict(
            users=c.execute("SELECT COUNT(*) n FROM users").fetchone()["n"],
            banned=c.execute("SELECT COUNT(*) n FROM users WHERE is_banned=1").fetchone()["n"],
            media=c.execute("SELECT COUNT(*) n FROM media").fetchone()["n"],
            views=c.execute("SELECT COUNT(*) n FROM media_views").fetchone()["n"],
            unique_views=c.execute(
                "SELECT COUNT(*) n FROM (SELECT DISTINCT media_id,user_id FROM media_views)"
            ).fetchone()["n"],
            today=c.execute(
                "SELECT COUNT(*) n FROM media_views WHERE date(viewed_at)=date('now')"
            ).fetchone()["n"],
        )


def top_media():
    return all(
        """
        SELECT m.title,m.code,COUNT(v.id) views,
               COUNT(DISTINCT v.user_id) unique_views
        FROM media m LEFT JOIN media_views v ON v.media_id=m.id
        GROUP BY m.id ORDER BY views DESC LIMIT 10
        """
    )


def add_star_donation(user_id, amount, charge_id, provider_charge_id, payload):
    return execute(
        """
        INSERT OR IGNORE INTO star_donations(
            user_id,amount,telegram_payment_charge_id,
            provider_payment_charge_id,invoice_payload
        ) VALUES(?,?,?,?,?)
        """,
        (user_id, amount, charge_id, provider_charge_id or "", payload),
    )


def donation_stats():
    with connect() as c:
        row = c.execute(
            "SELECT COUNT(*) transactions, COALESCE(SUM(amount),0) stars FROM star_donations"
        ).fetchone()
        return {
            "total_donations": row["transactions"],
            "total_stars": row["stars"],
            "transactions": row["transactions"],
            "stars": row["stars"],
        }


def recent_donations(limit=20):
    return all(
        """
        SELECT d.*,u.username,u.full_name
        FROM star_donations d LEFT JOIN users u ON u.id=d.user_id
        ORDER BY d.id DESC LIMIT ?
        """,
        (limit,),
    )


# ── Broadcast Templates ───────────────────────────────────────────────────────

def broadcast_templates():
    return all("SELECT * FROM broadcast_templates ORDER BY created_at DESC")


def broadcast_template(tid):
    return one("SELECT * FROM broadcast_templates WHERE id=?", (tid,))


def add_broadcast_template(name, content_type, file_id, caption, button_text, button_url):
    return execute(
        """
        INSERT INTO broadcast_templates(name, content_type, file_id, caption, button_text, button_url)
        VALUES(?,?,?,?,?,?)
        """,
        (name.strip(), content_type, file_id or "", caption or "", button_text or "", button_url or ""),
    )


def update_broadcast_template_name(tid, name):
    execute("UPDATE broadcast_templates SET name=? WHERE id=?", (name.strip(), tid))


def update_broadcast_template_content(tid, content_type, file_id, caption, button_text, button_url):
    execute(
        """
        UPDATE broadcast_templates
        SET content_type=?, file_id=?, caption=?, button_text=?, button_url=?
        WHERE id=?
        """,
        (content_type, file_id or "", caption or "", button_text or "", button_url or "", tid),
    )


def delete_broadcast_template(tid):
    execute("DELETE FROM broadcast_templates WHERE id=?", (tid,))