from db.database import conn, cursor


def add_link(link: str, chat_id: int, group_name: str, user_id: int, promo: str = None, username: str = None):
    cursor.execute("""
        INSERT OR IGNORE INTO member_links (user_id, link, chat_id, group_name, status, promo, username)
        VALUES (?, ?, ?, ?, 'Aktif', ?, ?)
    """, (user_id, link, chat_id, group_name, promo, username))
    conn.commit()


def get_active_links():
    cursor.execute("SELECT * FROM member_links WHERE status = 'Aktif'")
    return [dict(r) for r in cursor.fetchall()]


def find_link(link_target: str):
    cursor.execute(
        "SELECT * FROM member_links WHERE link = ? AND status = 'Aktif'",
        (link_target,)
    )
    row = cursor.fetchone()
    return dict(row) if row else None


def revoke_link(link_data: dict):
    cursor.execute(
        "UPDATE member_links SET status = 'Dihapus' WHERE link = ?",
        (link_data["link"],)
    )
    conn.commit()


def get_links_grouped_by_group():
    cursor.execute("SELECT * FROM member_links WHERE status = 'Aktif' ORDER BY group_name")
    rows = [dict(r) for r in cursor.fetchall()]
    grouped = {}
    for row in rows:
        grup = row["group_name"]
        grouped.setdefault(grup, []).append(row)
    return grouped


def update_link(old_link: str, new_link: str):
    cursor.execute(
        "UPDATE member_links SET link = ? WHERE link = ?",
        (new_link, old_link)
    )
    conn.commit()


def get_links_by_chat_id(chat_id: int):
    cursor.execute(
        "SELECT * FROM member_links WHERE chat_id = ? AND status = 'Aktif' ORDER BY join_date",
        (chat_id,)
    )
    return [dict(r) for r in cursor.fetchall()]


def delete_link_by_id(row_id: int):
    cursor.execute("DELETE FROM member_links WHERE id = ?", (row_id,))
    conn.commit()
    return cursor.rowcount > 0


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


def get_links_by_user_id(user_id: int):
    cursor.execute(
        "SELECT * FROM member_links WHERE user_id = ? ORDER BY join_date",
        (user_id,)
    )
    return [dict(r) for r in cursor.fetchall()]


def get_links_by_group_name(group_name: str):
    cursor.execute(
        "SELECT * FROM member_links WHERE group_name LIKE ? AND status = 'Aktif' ORDER BY join_date",
        (f"%{group_name}%",)
    )
    return [dict(r) for r in cursor.fetchall()]