from pyngrok import ngrok
import threading
import time
import os

# ----------------- Streamlit Dashboard Code ------------------
dashboard_code = """
import streamlit as st
import pandas as pd
import io

# Helper to normalize college name
def normalize_name(name):
    if pd.isna(name):
        return ""
    return name.replace("(B)", "B").strip().lower()

# Transformation Logic
def transform_file(main_df, attendance_df):
    # Normalize college names in both DataFrames
    main_df['normalized_name'] = main_df.iloc[:, 2].apply(normalize_name)
    attendance_df['normalized_name'] = attendance_df.iloc[:, 2].apply(lambda x: str(x).strip().lower())

    # Create attendance lookup dictionary
    attendance_lookup = attendance_df.set_index('normalized_name')

    school_attendance_list = []
    inter_attendance_list = []

    for name in main_df['normalized_name']:
        if name in attendance_lookup.index:
            row = attendance_lookup.loc[name]
            school_attendance_list.append(row.iloc[6])  # 7th column
            inter_attendance_list.append(row.iloc[10]) # 11th column
        else:
            school_attendance_list.append(None)
            inter_attendance_list.append(None)

    # Construct final dataframe
    new_df = pd.DataFrame({
        'serial_no': main_df.iloc[:, 0],
        'district': main_df.iloc[:, 1],
        'college_name': main_df.iloc[:, 2],
        'class': 'School',
        'school_minority_sanction': main_df.iloc[:, 3],
        'school_minority_admitted': main_df.iloc[:, 4],
        'school_minority_vacancies': main_df.iloc[:, 3] - main_df.iloc[:, 4],
        'school_non_minority_sanction': main_df.iloc[:, 5],
        'school_non_minority_admitted': main_df.iloc[:, 6],
        'school_non_minority_vacancies': main_df.iloc[:, 5] - main_df.iloc[:, 6],
        'school_attendance': school_attendance_list,
        'school_attendance_percentage': '',  # Placeholder
        'class_grade': 'Intermediate',
        'inter_minority_sanction': main_df.iloc[:, 8],
        'inter_minority_admitted': main_df.iloc[:, 9],
        'inter_minority_vacancies': main_df.iloc[:, 8] - main_df.iloc[:, 9],
        'inter_non_minority_sanction': main_df.iloc[:, 10],
        'inter_non_minority_admitted': main_df.iloc[:, 11],
        'inter_non_minority_vacancies': main_df.iloc[:, 10] - main_df.iloc[:, 11],
        'inter_attendance': inter_attendance_list,
        'inter_attendance_percentage': ''  # Placeholder
    })

    return new_df

# Streamlit App
st.set_page_config(layout="wide", page_title="📝 File Format Converter")
st.title("📤 Upload Two Files to Convert Format")

uploaded_main = st.file_uploader("Upload Main File (Sanctions, Admissions)", type=["csv", "xlsx"], key="main")
uploaded_attendance = st.file_uploader("Upload Attendance File", type=["csv", "xlsx"], key="att")

if uploaded_main and uploaded_attendance:
    try:
        # Determine extension and read both files
        ext1 = uploaded_main.name.split('.')[-1].lower()
        ext2 = uploaded_attendance.name.split('.')[-1].lower()

        if ext1 == 'csv':
            main_df = pd.read_csv(uploaded_main, skiprows=4)
        else:
            main_df = pd.read_excel(uploaded_main, skiprows=4)

        if ext2 == 'csv':
            attendance_df = pd.read_csv(uploaded_attendance)
        else:
            attendance_df = pd.read_excel(uploaded_attendance)

        st.success("✅ Both files uploaded and read successfully!")

        new_df = transform_file(main_df, attendance_df)
        st.dataframe(new_df.head())

        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
            new_df.to_excel(writer, index=False, sheet_name='Sheet1')
            worksheet = writer.sheets['Sheet1']

            # Set wider column widths (20 characters)
            for i, col in enumerate(new_df.columns):
                worksheet.set_column(i, i, 20)

        st.download_button("📥 Download Converted Excel File", output.getvalue(), file_name="converted_output.xlsx")

    except Exception as e:
        st.error(f"❌ Error while processing files: {e}")
"""

# ----------------- Save Streamlit Code -----------------------
with open("app_file_filter.py", "w") as f:
    f.write(dashboard_code)

# ----------------- Ngrok Setup & Streamlit Launch ------------------
ngrok.set_auth_token("30VAe4T9qTjFG7urJrdYiwizPYO_3BVhHNvCaLGXkWDVAtnmu")

# Function to run Streamlit
def run():
    os.system("streamlit run app_file_filter.py")

# Start Streamlit in background thread
thread = threading.Thread(target=run)
thread.start()

# Give Streamlit time to boot
time.sleep(5)

# Close existing tunnels if any
for tunnel in ngrok.get_tunnels():
    ngrok.disconnect(tunnel.public_url)

# Open a new ngrok tunnel
public_url = ngrok.connect(addr=8501)
print("🔗 Public URL:", public_url)