apps/ciq_2g_generator.py

from pathlib import Path

import pandas as pd
import streamlit as st

from queries.process_ciq_2g import generate_ciq_2g_excel, generate_free_bcf_excel
from utils.ciq_excel import get_ciq_sheet_selection_details


st.title(":material/description: CIQ 2G Generator")
st.markdown(
    """
Genere les exports CIQ 2G a partir du dump OML + CIQ brut.
- **Dump** : obligatoire (format .xlsb)
- **CIQ brut 2G** : obligatoire pour generer le CIQ
- **BCF forbidden** : optionnel (colonnes BSC/BCF ou BSCID/BCFID)
"""
)

samples_dir = Path(__file__).resolve().parents[1] / "samples"
sample_ciq_2g = samples_dir / "CIQ_2G.xlsx"
sample_forbidden = samples_dir / "FORBIDEN_SCF.xlsx"

if sample_ciq_2g.exists():
    st.download_button(
        label="Download CIQ 2G sample",
        data=sample_ciq_2g.read_bytes(),
        file_name=sample_ciq_2g.name,
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )
if sample_forbidden.exists():
    st.download_button(
        label="Download BCF forbidden sample",
        data=sample_forbidden.read_bytes(),
        file_name=sample_forbidden.name,
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )

col1, col2 = st.columns(2)
with col1:
    dump_file = st.file_uploader("Upload dump file", type=["xlsb"], key="ciq2g_dump")
with col2:
    ciq_file = st.file_uploader(
        "Upload CIQ brut 2G (Excel)", type=["xlsx", "xls"], key="ciq2g_ciq"
    )

ciq_sheet_name = None
if ciq_file is not None:
    try:
        auto_sheet, sheet_names = get_ciq_sheet_selection_details(ciq_file, "2g")
        ciq_sheet_name = st.selectbox(
            "Sheet CIQ 2G",
            options=sheet_names,
            index=sheet_names.index(auto_sheet),
            key="ciq2g_sheet_name",
        )
        if len(sheet_names) == 1:
            st.info(f"Classeur a une seule feuille: utilisation de `{ciq_sheet_name}`.")
        elif ciq_sheet_name == auto_sheet:
            st.info(f"Feuille detectee pour la 2G: `{ciq_sheet_name}`.")
        else:
            st.info(
                f"Feuille 2G choisie manuellement: `{ciq_sheet_name}` (auto: `{auto_sheet}`)."
            )
    except Exception as exc:
        st.warning(f"Detection de feuille 2G impossible: {exc}")

forbidden_file = st.file_uploader(
    "Upload forbidden BCF list (optional)",
    type=["xlsx", "xls", "xlsb"],
    key="ciq2g_forbidden",
    help="Expected columns: BSC, BCF (or BSCID/BCFID).",
)

col3, col4 = st.columns(2)
with col3:
    mcc = st.number_input("MCC", value=610, step=1, min_value=0, key="ciq2g_mcc")
with col4:
    mnc = st.number_input("MNC", value=2, step=1, min_value=0, key="ciq2g_mnc")

if dump_file is None:
    st.info("Upload dump xlsb + CIQ brut Excel to generate CIQ 2G.")
    st.stop()

st.subheader("Generation BCF libre")
if st.button("Generate BCF libre", type="secondary"):
    try:
        with st.spinner("Generating BCF libre..."):
            free_bcf_df, free_bcf_bytes = generate_free_bcf_excel(
                dump_file, forbidden_file=forbidden_file
            )
            st.session_state["ciq2g_free_bcf_df"] = free_bcf_df
            st.session_state["ciq2g_free_bcf_bytes"] = free_bcf_bytes
        st.success("BCF libre generated")
    except Exception as e:
        st.error(f"Error: {e}")

free_bcf_df = st.session_state.get("ciq2g_free_bcf_df")
free_bcf_bytes = st.session_state.get("ciq2g_free_bcf_bytes")
if free_bcf_df is not None:
    st.dataframe(free_bcf_df, use_container_width=True)
if free_bcf_bytes:
    st.download_button(
        label="Download BCF libre",
        data=free_bcf_bytes,
        file_name="BCF_LIBRE.xlsx",
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    )

st.subheader("Generation CIQ 2G")
if ciq_file is None:
    st.info("Upload CIQ brut 2G Excel to generate CIQ 2G.")

if ciq_file is not None and st.button("Generate", type="primary"):
    try:
        with st.spinner("Generating CIQ 2G... (dump is heavy)"):
            sheets, excel_bytes = generate_ciq_2g_excel(
                dump_file,
                ciq_file,
                mcc=int(mcc),
                mnc=int(mnc),
                forbidden_file=forbidden_file,
                ciq_sheet_name=ciq_sheet_name,
            )
            st.session_state["ciq2g_sheets"] = sheets
            st.session_state["ciq2g_excel_bytes"] = excel_bytes
        st.success("CIQ 2G generated")
    except Exception as e:
        st.error(f"Error: {e}")

sheets = st.session_state.get("ciq2g_sheets")
excel_bytes = st.session_state.get("ciq2g_excel_bytes")

if sheets:
    tab_names = list(sheets.keys())
    tabs = st.tabs(tab_names)
    for t, name in zip(tabs, tab_names):
        with t:
            df: pd.DataFrame = sheets[name]
            st.dataframe(df, use_container_width=True)

if excel_bytes:
    st.download_button(
        label="Download CIQ 2G Excel",
        data=excel_bytes,
        file_name="CIQ_2G.xlsx",
        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        type="primary",
    )