from db.database import conn, cursor


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


def is_package_visible(pkg):
    value = pkg.get("is_visible", 1)
    return value is None or int(value) == 1


def get_visible_packages():
    cursor.execute("SELECT * FROM vip_packages WHERE COALESCE(is_visible, 1) = 1 ORDER BY id")
    return [dict(r) for r in cursor.fetchall()]


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


def add_package(nama, harga, group_id=None, link=None):
    cursor.execute("""
        INSERT INTO vip_packages (nama, harga, group_id, link)
        VALUES (?, ?, ?, ?)
    """, (nama.strip(), int(harga), int(group_id) if group_id else None, link))
    conn.commit()
    return get_package_by_id(cursor.lastrowid)


def update_package(pkg_id, nama=None, harga=None, group_id=None, link=None, is_visible=None):
    pkg = get_package_by_id(pkg_id)
    if not pkg:
        return None
    if nama is not None:
        cursor.execute("UPDATE vip_packages SET nama = ? WHERE id = ?", (nama.strip(), int(pkg_id)))
    if harga is not None:
        cursor.execute("UPDATE vip_packages SET harga = ? WHERE id = ?", (int(harga), int(pkg_id)))
    if group_id is not None:
        cursor.execute("UPDATE vip_packages SET group_id = ?, link = NULL WHERE id = ?", (int(group_id), int(pkg_id)))
    if link is not None:
        cursor.execute("UPDATE vip_packages SET link = ?, group_id = NULL WHERE id = ?", (link, int(pkg_id)))
    if is_visible is not None:
        cursor.execute("UPDATE vip_packages SET is_visible = ? WHERE id = ?", (1 if is_visible else 0, int(pkg_id)))
    conn.commit()
    return get_package_by_id(pkg_id)


def toggle_package_visibility(pkg_id):
    pkg = get_package_by_id(pkg_id)
    if not pkg:
        return None
    new_value = 0 if is_package_visible(pkg) else 1
    cursor.execute(
        "UPDATE vip_packages SET is_visible = ? WHERE id = ?",
        (new_value, int(pkg_id))
    )
    conn.commit()
    return get_package_by_id(pkg_id)


def set_package_banner(pkg_id, file_id):
    cursor.execute(
        "UPDATE vip_packages SET banner_file_id = ? WHERE id = ?",
        (file_id, int(pkg_id))
    )
    conn.commit()
    return get_package_by_id(pkg_id)


def clear_package_banner(pkg_id):
    cursor.execute(
        "UPDATE vip_packages SET banner_file_id = NULL WHERE id = ?",
        (int(pkg_id),)
    )
    conn.commit()
    return get_package_by_id(pkg_id)


def set_package_caption(pkg_id, caption):
    cursor.execute(
        "UPDATE vip_packages SET caption = ? WHERE id = ?",
        (caption, int(pkg_id))
    )
    conn.commit()
    return get_package_by_id(pkg_id)


def clear_package_caption(pkg_id):
    cursor.execute(
        "UPDATE vip_packages SET caption = NULL WHERE id = ?",
        (int(pkg_id),)
    )
    conn.commit()
    return get_package_by_id(pkg_id)


def delete_package(pkg_id):
    cursor.execute("DELETE FROM vip_packages WHERE id = ?", (int(pkg_id),))
    conn.commit()
    if cursor.rowcount == 0:
        return False
    _reorder_ids()
    return True


def _reorder_ids():
    packages = get_all_packages()
    for new_id, pkg in enumerate(packages, start=1):
        cursor.execute("UPDATE vip_packages SET id = ? WHERE id = ?", (new_id, pkg["id"]))
    cursor.execute("UPDATE sqlite_sequence SET seq = ? WHERE name = 'vip_packages'", (len(packages),))
    conn.commit()