import logging
from datetime import datetime

from db.database import conn, cursor

log = logging.getLogger("bot.connected_channel")


def get_all_connected_channels():
    cursor.execute("SELECT * FROM connected_channels ORDER BY id")
    return [dict(r) for r in cursor.fetchall()]


def get_connected_channel_by_id(channel_id):
    cursor.execute("SELECT * FROM connected_channels WHERE id = ?", (int(channel_id),))
    row = cursor.fetchone()
    return dict(row) if row else None


def add_connected_channel(name, type_, chat_id=None, username=None, link=None):
    cursor.execute("""
        INSERT INTO connected_channels (name, type, chat_id, username, link)
        VALUES (?, ?, ?, ?, ?)
    """, (name.strip(), type_, chat_id, username, link))
    conn.commit()
    return dict(cursor.execute(
        "SELECT * FROM connected_channels WHERE id = ?", (cursor.lastrowid,)
    ).fetchone())


def update_connected_channel(channel_id, **kwargs):
    allowed = {"name", "chat_id", "username", "type", "link", "status", "last_checked"}
    fields = {k: v for k, v in kwargs.items() if k in allowed}
    if not fields:
        return None
    set_clause = ", ".join(f"{k} = ?" for k in fields)
    values = list(fields.values())
    cursor.execute(
        f"UPDATE connected_channels SET {set_clause}, updated_at = datetime('now', 'localtime') WHERE id = ?",
        (*values, int(channel_id))
    )
    conn.commit()
    return get_connected_channel_by_id(channel_id)


def delete_connected_channel(channel_id):
    cursor.execute("DELETE FROM connected_channels WHERE id = ?", (int(channel_id),))
    conn.commit()
    return cursor.rowcount > 0


def _resolve_target(channel):
    targets = _resolve_targets(channel)
    return targets[0] if targets else None


def _append_target(targets, target):
    if target is None:
        return
    if isinstance(target, str):
        target = target.strip()
        if not target:
            return
    if str(target) not in {str(t) for t in targets}:
        targets.append(target)


def _target_from_link(link):
    if not link:
        return None

    link = link.strip()
    if link.startswith("@"):
        return f"@{link.lstrip('@')}"
    if "t.me/" not in link:
        return None

    path = link.split("t.me/", 1)[1].split("?", 1)[0].strip("/")
    slug = path.split("/", 1)[0]
    if not slug or slug.startswith("+") or slug in {"c", "joinchat"}:
        return None
    return f"@{slug}"


def _resolve_targets(channel):
    targets = []
    username = channel.get("username")
    username_target = f"@{username.lstrip('@')}" if username else None
    link_target = _target_from_link(channel.get("link"))
    chat_id = channel.get("chat_id")

    if channel.get("type") == "public":
        _append_target(targets, username_target)
        _append_target(targets, link_target)
        _append_target(targets, chat_id)
    else:
        _append_target(targets, chat_id)
        _append_target(targets, username_target)
        _append_target(targets, link_target)

    return targets


async def get_connected_channel_chat(context, channel):
    last_error = None
    for target in _resolve_targets(channel):
        try:
            return await context.bot.get_chat(chat_id=target)
        except Exception as e:
            last_error = e

    if last_error:
        raise last_error
    raise ValueError("Target tidak valid")


async def get_connected_channel_member_count(context, chat_id):
    return await context.bot.get_chat_member_count(chat_id)


async def check_channel_connection(context, channel):
    if not _resolve_target(channel):
        return False, "Target tidak valid"

    try:
        chat = await get_connected_channel_chat(context, channel)
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        update_connected_channel(channel["id"], status="active", last_checked=now)

        info = f"✅ Terhubung sebagai {chat.type.title()}"
        try:
            member_count = await get_connected_channel_member_count(context, chat.id)
            info += f" | Member: {member_count}"
        except Exception:
            info += " | Member: ? (butuh admin)"

        try:
            admins = await chat.get_administrators()
            bot_id = (await context.bot.get_me()).id
            is_admin = any(a.user.id == bot_id for a in admins)
            info += f" | Bot {'✅ Admin' if is_admin else '❌ Bukan Admin'}"
        except Exception:
            info += " | Bot: ❌ Tidak bisa cek admin"

        if channel.get("chat_id") is None or channel["chat_id"] != chat.id:
            update_connected_channel(channel["id"], chat_id=chat.id)
        return True, info
    except Exception as e:
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        update_connected_channel(channel["id"], status="inactive", last_checked=now)
        return False, f"❌ Gagal: {str(e)[:80]}"