import gradio as gr
import pandas as pd
from datetime import datetime
import pymssql

# Function to connect to SQL Server and run stored procedure
def run_procedure(server, database, username, password, start_date, end_date):
    try:
        start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
        end_date = datetime.strptime(end_date, "%Y-%m-%d").date()

        conn = pymssql.connect(server=server, user=username, password=password, database=database)
        cursor = conn.cursor()
        cursor.execute("EXEC dbo.Temp_Get_EQUITY_DocumentProcurement %s, %s", (start_date, end_date))
        conn.commit()

        query = """
            SELECT * FROM [DocumentAcquisition].dbo.Temp_Document_Procurement_Timeline
            WHERE Document_Procurement_Name = 'EQUITY'
        """
        df = pd.read_sql(query, conn)
        conn.close()

        if df.empty:
            return None, None, "No data found for the given dates."
        else:
            csv_path = "document_procurement_report.csv"
            df.to_csv(csv_path, index=False)
            return df, csv_path, "Success"

    except Exception as e:
        return None, None, f"Error: {str(e)}"

# Updated Gradio UI
iface = gr.Interface(
    fn=run_procedure,
    inputs=[
        gr.Textbox(label="Server Name", placeholder="e.g., myserver.database.windows.net"),
        gr.Textbox(label="Database Name", placeholder="e.g., DocumentAcquisition"),
        gr.Textbox(label="Username"),
        gr.Textbox(label="Password", type="password"),
        gr.Textbox(label="Start Date (YYYY-MM-DD)", value="2024-01-01"),
        gr.Textbox(label="End Date (YYYY-MM-DD)", value="2024-01-07"),
    ],
    outputs=[
        gr.Dataframe(label="Procedure Output"),
        gr.File(label="Download CSV Report"),
        gr.Textbox(label="Status / Error Message")
    ],
    title="EQUITY Document Procurement Report Generator",
    description="Enter your SQL Server details and date range to run the stored procedure and download the report."
)

iface.launch()