pages/Payment_Working.py

import os
import pandas as pd
import streamlit as st
from google.cloud import bigquery
from utils import load_gcp_credentials, authenticate_bigquery_updated
from html_templates import logo, payment_title, progress_bar_style, process_file_title, process_date_title, button_styles, download_button_styles, tooltip_message_payment, update_sett_placeholder,update_sett_title, conso_date_title
import io
import zipfile
from datetime import datetime
import time
from payment_queries import queries
from streamlit_option_menu import option_menu
from merge_queries import queries_merged_01, queries_merged_02, queries_merged_03, conso_query, disburse_query
from io import BytesIO




# ---- 🔹 Inject Custom CSS for Styling ----
st.markdown(progress_bar_style,unsafe_allow_html=True)

# ---- 🔹 Streamlit UI ----
st.markdown(logo, unsafe_allow_html=True)
st.logo("alerter_4.jpeg")

st.markdown(payment_title, unsafe_allow_html=True)
st.write("")
st.markdown(tooltip_message_payment, unsafe_allow_html = True)
st.write("")

selected = option_menu(
    menu_title=None,
    options=["Payment File", "Disbursement File"],
    icons=["database", "bar-chart"],
    menu_icon="cast",
    default_index=0,
    orientation="horizontal",
    styles={
        "container": {
            "padding": "0px", 
            "background-color": "#800000 !important"  # Maroon with force override
        },
        "icon": {
            "color": "#ffffff !important",  # White icons with forced override
            "font-size": "20px"
        },
        "nav-link": {
            "font-size": "18px",
            "text-align": "center",
            "margin": "0px",
            "padding": "10px",
            "color": "#ffffff !important",  # White text for unselected tabs
            "background-color": "#800000 !important",  # Force maroon
        },
        "nav-link-selected": {
            "background-color": "#a52a2a !important",  # Reddish maroon for selected tab
            "color": "#ffffff !important",  # White text for selected tab
            "font-weight": "bold",
        },
    },
)


# ---- 🔹 User Input Date ----
if selected == 'Payment File':
    st.markdown(process_date_title, unsafe_allow_html = True)
    st.write("")
    selected_date = st.date_input("", value=datetime.today())  # Default: Today
    formatted_date = selected_date.strftime("%Y-%m-%d")  # Convert to string format YYYY-MM-DD



    # ---- 🔹 Run Queries Button ----
    st.markdown(button_styles, unsafe_allow_html = True)
    if st.button("Generate Payment File"):
        st.markdown(process_file_title, unsafe_allow_html = True)
        st.write("")
        st.write("")

        # Define sheet names
        sheet_names = [
            "09", "11", "12", "MD", "Not_match_09", "Not_match_11", "Not_match_12"
        ]

        # Retrieve the service account credentials
        gcp_credentials = load_gcp_credentials()

        if gcp_credentials:
            bigquery_creds = authenticate_bigquery_updated()
            client = bigquery.Client(credentials=bigquery_creds)

            # Store DataFrames
            dataframes = {}

            # ---- 📌 **Dynamic Log Section** ----
            log_area = st.empty()
            log_text_list = []  # Store logs as a list

            # ---- 🔄 **Progress Bar Setup** ----
            progress_bar = st.progress(0)  # Initialize progress bar
            progress_text = st.empty()  # Placeholder for text

            total_steps = len(queries)  # Total number of queries to process

            for i, (query_name, query) in enumerate(queries.items()):
                sheet_name = sheet_names[i] if i < len(sheet_names) else f"Sheet_{i+1}"

                # 🔹 **Modify Query to Include Selected Date**
                dynamic_query = query.replace("{selected_date}", formatted_date)

                # 🔄 **Blinking Cursor Effect**
                cursor_state = ["|", "/", "-", "\\"]
                for cursor in cursor_state:
                    log_text_list.append(
                        f"<p class='gradient-text'>🔵 {sheet_name} is being processed... <span class='blinking-cursor'>{cursor}</span></p>"
                    )
                    log_area.markdown("".join(log_text_list), unsafe_allow_html=True)  # ✅ FORCE NEWLINES
                    log_text_list.pop()  # Remove last blinking message to replace it
                    time.sleep(0.3)

                try:
                    # Execute Query
                    query_job = client.query(dynamic_query)
                    df = query_job.to_dataframe()

                    # 🔹 **Fix: Remove Timezone from Datetime Columns**
                    for col in df.select_dtypes(include=["datetime64[ns, UTC]"]).columns:
                        df[col] = df[col].dt.tz_localize(None)

                    # Store the DataFrame
                    dataframes[sheet_name] = df

                    # 🔄 **Static Log with Record Count**
                    record_count = len(df)
                    log_text_list.append(
                        f"<p class='gradient-text'>✅ {sheet_name} sheet created successfully... Total <b>{record_count}</b> records.</p>"
                    )
                    log_area.markdown("".join(log_text_list), unsafe_allow_html=True)  # ✅ FORCE NEWLINES

                except Exception as e:
                    log_text_list.append(f"<p class='gradient-text'>❌ Error in {sheet_name}: {e}</p>")
                    log_area.markdown("".join(log_text_list), unsafe_allow_html=True)

                # ---- 🔄 **Update Progress Bar (Stay at 99%)** ----
                progress_percentage = int(((i + 1) / total_steps) * 98)  # Keep it at max 98%
                progress_bar.progress(progress_percentage)
                progress_text.markdown(
                    f'<p class="gradient-text">⚙️ Creating Payment File... {progress_percentage}%</p>',
                    unsafe_allow_html=True
                )
                time.sleep(0.5)  # Small delay to simulate processing

            # ---- 📥 Generate & Show Download Buttons (Only Now Progress Reaches 100%) ----
            if dataframes:
                progress_bar.progress(99)  # Keep at 99% while preparing file
                progress_text.markdown('<p class="gradient-text">⚙️ Finalizing file... Please wait...</p>', unsafe_allow_html=True)

                # 🔥 **Excel Generation**
                excel_buffer = io.BytesIO()
                with pd.ExcelWriter(excel_buffer, engine="openpyxl") as writer:
                    for sheet, df in dataframes.items():
                        df.to_excel(writer, sheet_name=sheet, index=False)

                excel_buffer.seek(0)

                # 🔥 **CSV ZIP Generation**
                csv_buffer = io.BytesIO()
                with zipfile.ZipFile(csv_buffer, "w", zipfile.ZIP_DEFLATED) as zipf:
                    for sheet, df in dataframes.items():
                        csv_data = df.to_csv(index=False).encode("utf-8")
                        zipf.writestr(f"{sheet}_{formatted_date}.csv", csv_data)

                csv_buffer.seek(0)

                # ---- ✅ **Now Move Progress to 100% and Show Download Options** ----
                progress_bar.progress(100)
                progress_text.markdown('<p class="gradient-text">✅ Payment file created. Ready for download.</p>', unsafe_allow_html=True)

                # ---- 📥 Download Buttons ----
                st.markdown(download_button_styles, unsafe_allow_html = True)
                st.download_button(
                    label=f"📥 Download Payment File",
                    data=excel_buffer,
                    file_name=f"queries_results_{formatted_date}.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                )


elif selected == 'Disbursement File':
    gcp_credentials = load_gcp_credentials()

    # Radio Button for Selection
    st.markdown(update_sett_placeholder,unsafe_allow_html=True)

    # Styled Radio Button
    st.markdown(update_sett_title, unsafe_allow_html = True)
    update_option = st.radio(
        "",
        ["Update 09", "Update 11", "Update 12"]
    )

    # Map selection to queries
    if update_option == "Update 09":
        selected_queries = queries_merged_01
    elif update_option == "Update 11":
        selected_queries = queries_merged_02
    elif update_option == "Update 12":
        selected_queries = queries_merged_03
    else:
        selected_queries = []

    # Button to execute queries
    st.markdown(button_styles, unsafe_allow_html = True)
    if st.button("Execute Updates") and selected_queries:
        st.markdown("<h3 style='text-align: center; color: maroon;'>Executing Queries & Updating IDs</h3>", unsafe_allow_html=True)

        gcp_credentials = load_gcp_credentials()
        
        if gcp_credentials:
            bigquery_creds = authenticate_bigquery_updated()
            client = bigquery.Client(credentials=bigquery_creds)

            total_queries = len(selected_queries)
            progress_bar = st.progress(0)
            updated_counts = {}

            for i, query in enumerate(selected_queries):
                st.markdown(f"### Running Query {i+1} of {total_queries}...")
                start_time = time.time()

                # Execute query
                query_job = client.query(query)
                query_job.result()

                updated_count = query_job.num_dml_affected_rows or 0
                updated_counts[f"Query {i+1}"] = updated_count

                # Update progress bar
                progress = int(((i + 1) / total_queries) * 100)
                progress_bar.progress(progress / 100)

                # Display execution details
                execution_time = time.time() - start_time
                st.success(f"✅ Query {i+1} executed in {execution_time:.2f} sec. **{updated_count} IDs updated.**")
                
                time.sleep(1)

            # Summary
            progress_bar.progress(1.0)

    # Streamlit Date Input
    st.markdown(conso_date_title, unsafe_allow_html = True)
    selected_date = st.date_input("")

    # If a date is selected, show the download button
    st.markdown(button_styles, unsafe_allow_html = True)
    if st.button('Generate disbursement') and selected_date:
        selected_date_str = selected_date.strftime("%Y-%m-%d")

        if selected_date_str:
        # ✅ Authenticate with BigQuery
            gcp_credentials = load_gcp_credentials()
            if gcp_credentials:
                bigquery_creds = authenticate_bigquery_updated()
                client = bigquery.Client(credentials=bigquery_creds)

                # ✅ Set query parameters
                job_config = bigquery.QueryJobConfig(
                    query_parameters=[
                        bigquery.ScalarQueryParameter("selected_date", "DATE", selected_date_str)
                    ]
                )

                # ✅ Execute queries
                conso_df = client.query(conso_query, job_config=job_config).to_dataframe()
                disburse_df = client.query(disburse_query, job_config=job_config).to_dataframe()

                # ✅ Remove timezone and convert datetime columns
                def clean_datetime_columns(df):
                    for col in df.select_dtypes(include=["datetime64[ns, UTC]"]).columns:
                        df[col] = df[col].dt.tz_localize(None)  # Remove timezone

                    for col in df.select_dtypes(include=["datetime64"]).columns:
                        df[col] = df[col].astype(str)  # Convert datetime to string

                clean_datetime_columns(conso_df)
                clean_datetime_columns(disburse_df)

                # ✅ Save to an in-memory Excel file
                output = BytesIO()
                with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
                    conso_df.to_excel(writer, sheet_name="Sheet1", index=False)
                    disburse_df.to_excel(writer, sheet_name="disbursement_summary", index=False)
                output.seek(0)  # ✅ Reset buffer position

                # ✅ Place buttons side by side
                col1, col2 = st.columns(2)

                with col1:
                    st.download_button(
                        label="Download Disbursement Data",
                        data=output,
                        file_name=f"disbursement_data_{selected_date_str}.xlsx",
                        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    )

                with col2:
                    if st.button("Generate Disbursement"):
                        st.success("Disbursement Generated Successfully ✅")