import pandas as pd
import gradio as gr
from openpyxl import load_workbook
from openpyxl.styles import Font

# Password for access
PASSWORD = "mypassword123"

# --- Excel filtering function with custom font ---
def filter_grades(file, grade, column_name, output_name, font_name="Ali_K_Alwand", font_size=12):
    try:
        # Load Excel
        df = pd.read_excel(file)
        df["زانكؤلاين"] = pd.to_numeric(df["زانكؤلاين"], errors="coerce")
        df["ثارالَيل"] = pd.to_numeric(df["ثارالَيل"], errors="coerce")

        # Filter range
        lower_bound = grade - 5
        upper_bound = grade + 5
        filtered_df = df[
            (df[column_name] >= lower_bound) & (df[column_name] <= upper_bound)
        ]

        if filtered_df.empty:
            return "❌ هیچ ئەنجامێك نەدۆزرایەوە", pd.DataFrame(), None

        # Save filtered DataFrame
        save_path = f"{output_name}.xlsx"
        filtered_df.to_excel(save_path, index=False)

        # Apply custom font in Excel
        wb = load_workbook(save_path)
        ws = wb.active
        my_font = Font(name=font_name, size=font_size)
        for row in ws.iter_rows():
            for cell in row:
                cell.font = my_font
        wb.save(save_path)

        return f"✅ ئەنجامەکان پاشەکەوتکران لە {save_path}", filtered_df, save_path

    except Exception as e:
        return f"❌ هەڵە ڕووی دا: {str(e)}", pd.DataFrame(), None

# --- Password check ---
def check_password(pw):
    return pw == PASSWORD, "❌ ووشەی نهێنی هەڵەیە!"

# --- Gradio App ---
with gr.Blocks() as demo:

    # Login screen
    login_screen = gr.Column(visible=True)
    with login_screen:
        gr.Markdown("""
        <div style="text-align:center; font-size:30px; font-weight:bold; color:#2c3e50; margin-bottom:20px;">
            🔒 تۆمارکردن
        </div>
        <div style="text-align:center; font-size:18px; margin-bottom:20px;">
            تکایە ووشەی نهێنی بنووسە بۆ دەرچوون بۆ ئامرازەکان
        </div>
        """)
        password_input = gr.Textbox(label="ووشەی نهێنی", type="password")
        login_button = gr.Button("چوونە ژوورەوە")
        login_status = gr.Textbox(label="", interactive=False, visible=True)

    # Main app (hidden initially)
    main_app = gr.Column(visible=False)
    with main_app:
        gr.Markdown("""
        <div style="text-align:center; font-size:28px; font-weight:bold; color:#34495e; margin-bottom:15px;">
            📊 فلتەری نمرەکان
        </div>
        <div style="text-align:center; font-size:16px; margin-bottom:15px;">
            فایلێکی Excel باربکە و نمرەکانی خۆت فلتەر بکە
        </div>
        """)

        file_input = gr.File(label="فایل Excel باربکە", type="filepath")
        grade_input = gr.Number(label="نمرەی خۆت", value=50)
        column_input = gr.Dropdown(choices=["زانكؤلاين", "ثارالَيل"], label="هەڵبژاردنی ستوون")
        output_name_input = gr.Textbox(label="ناوی فایلێکی دەرچوو (بێ .xlsx)", value="filtered_results")
        run_button = gr.Button("🏷️ فلتەر بکە و پاشەکەوت بکە")

        status_output = gr.Textbox(label="حاڵەت", interactive=False)
        table_output = gr.Dataframe(label="ئەنجامەکان", interactive=False, elem_id="entity_table_rtl")
        download_output = gr.File(label="📥 داونلۆد", file_types=[".xlsx"])

        run_button.click(
            fn=filter_grades,
            inputs=[file_input, grade_input, column_input, output_name_input],
            outputs=[status_output, table_output, download_output]
        )

    # --- Login behavior ---
    def handle_login(pw):
        correct, msg = check_password(pw)
        if correct:
            # Hide login, show main app
            return gr.update(visible=False), gr.update(visible=True), ""
        else:
            return gr.update(visible=True), gr.update(visible=False), msg

    login_button.click(
        fn=handle_login,
        inputs=password_input,
        outputs=[login_screen, main_app, login_status]
    )

    # --- Add CSS for Ali-K-Samic / Ali_K_Alwand font ---
    css = """
    #entity_table_rtl table {
        font-family: 'Ali_K_Alwand', sans-serif;
        font-size: 16px;
    }
    #entity_table_rtl th, #entity_table_rtl td {
        font-family: 'Ali_K_Alwand', sans-serif;
        font-size: 16px;
    }
    """
    gr.HTML(f"<style>{css}</style>")

# Launch the app
demo.launch(share=True, show_api=False)