import pandas as pd
import gradio as gr
from openpyxl import Workbook
from openpyxl.styles import PatternFill
import matplotlib.pyplot as plt
import smtplib
from email.mime.text import MIMEText
import uuid

def read_csv(file):
    return pd.read_csv(file)

def assess_risk(row):
    attendance = row.get("Attendance (%)", 0)
    marks = row.get("Marks (%)", 0)
    fees_paid = str(row.get("Fees Paid", "")).strip().lower()

    if attendance < 60 and marks < 50 and fees_paid != "yes":
        return "High Risk"
    elif attendance < 60 or marks < 50 or fees_paid != "yes":
        return "Moderate Risk"
    else:
        return "Safe"

def send_email(to_emails, subject, body, smtp_user, smtp_pass, smtp_host="smtp.gmail.com", smtp_port=587):
    msg = MIMEText(body)
    msg["Subject"] = subject
    msg["From"] = smtp_user
    msg["To"] = to_emails

    try:
        server = smtplib.SMTP(smtp_host, smtp_port)
        server.starttls()
        server.login(smtp_user, smtp_pass)
        server.sendmail(smtp_user, to_emails.split(","), msg.as_string())
        server.quit()
        return "✅ Email sent successfully."
    except Exception as e:
        return f"❌ Email failed: {e}"

def generate_report(attendance_file, tests_file, fees_file, notify_emails, smtp_user, smtp_pass):
    # Load CSVs
    att = read_csv(attendance_file)
    tst = read_csv(tests_file)
    fee = read_csv(fees_file)

    # Merge
    df = att.merge(tst, on="Roll Number", how="outer").merge(fee, on="Roll Number", how="outer")
    df["Name"] = df["Name"].fillna("")
    df["Risk"] = df.apply(assess_risk, axis=1)

    # Excel report
    wb = Workbook()
    ws = wb.active
    ws.title = "Dropout Risk Report"
    headers = list(df.columns)
    ws.append(headers)

    fills = {
        "Safe": PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid"),
        "Moderate Risk": PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid"),
        "High Risk": PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
    }

    for _, row in df.iterrows():
        ws.append(list(row))
        risk = row["Risk"]
        fill = fills.get(risk, None)
        if fill:
            risk_col = headers.index("Risk") + 1
            ws.cell(row=ws.max_row, column=risk_col).fill = fill

    excel_path = f"risk_report_{uuid.uuid4().hex}.xlsx"
    wb.save(excel_path)

    # Risk chart
    risk_counts = df["Risk"].value_counts()
    fig, ax = plt.subplots()
    ax.bar(risk_counts.index, risk_counts.values, color=["green", "orange", "red"])
    ax.set_title("Dropout Risk Distribution")
    ax.set_ylabel("Number of Students")
    chart_path = f"risk_chart_{uuid.uuid4().hex}.png"
    fig.savefig(chart_path)
    plt.close(fig)

    # Email notification
    email_status = ""
    if notify_emails and smtp_user and smtp_pass:
        email_status = send_email(
            to_emails=notify_emails,
            subject="Dropout Risk Report",
            body="The latest dropout risk report has been generated.",
            smtp_user=smtp_user,
            smtp_pass=smtp_pass
        )

    return excel_path, chart_path, email_status

# Gradio UI
with gr.Blocks() as demo:
    gr.Markdown("## 🎓 Student Dropout Risk Generator")

    with gr.Row():
        attendance = gr.File(label="Upload Attendance CSV")
        tests = gr.File(label="Upload Tests CSV")
        fees = gr.File(label="Upload Fees CSV")

    with gr.Accordion("📧 Email Notification (Optional)", open=False):
        notify_emails = gr.Textbox(label="Notify Emails (comma-separated)")
        smtp_user = gr.Textbox(label="SMTP Email")
        smtp_pass = gr.Textbox(label="SMTP Password", type="password")

    run_btn = gr.Button("Generate Report")

    excel_output = gr.File(label="Download Risk Report (.xlsx)")
    chart_output = gr.Image(label="Risk Distribution Chart")
    email_status_output = gr.Textbox(label="Email Status")

    run_btn.click(
        generate_report,
        inputs=[attendance, tests, fees, notify_emails, smtp_user, smtp_pass],
        outputs=[excel_output, chart_output, email_status_output]
    )

demo.launch()