send_orders.py

import pandas as pd
import smtplib
from email.message import EmailMessage
import os
import sqlite3
from pathlib import Path



# Validation
if not EMAIL or not EMAIL.strip():
    raise ValueError("EMAIL configuration is missing. Please set EMAIL variable.")
if not PASSWORD or not PASSWORD.strip():
    raise ValueError("PASSWORD configuration is missing. Please set PASSWORD variable.")
if not NOTIFICATION_RECIPIENTS or len(NOTIFICATION_RECIPIENTS) == 0:
    raise ValueError("NOTIFICATION_RECIPIENTS is empty. Please configure at least one recipient.")

DB_PATH = Path(__file__).resolve().parent / "pharma_pulse.db"
OUTPUT_DIR = Path(__file__).resolve().parent / "outputs"
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)


def load_data():
    conn = sqlite3.connect(str(DB_PATH))
    forecast = pd.read_sql_query("SELECT * FROM demand_forecast", conn)
    risk = pd.read_sql_query("SELECT * FROM shortage_risk_output WHERE risk_level = 'High'", conn)

    # Load reference data from database tables
    medicine = pd.read_sql_query("SELECT * FROM dim_medicine", conn)
    branch = pd.read_sql_query("SELECT * FROM dim_branch", conn)

    supplier = pd.read_sql_query("SELECT * FROM supplier_updated", conn)
    bridge = pd.read_sql_query("SELECT * FROM bridge_medicine_supplier", conn)
    conn.close()

    return forecast, risk, medicine, branch, supplier, bridge


def build_order_dataframe():
    forecast, risk, medicine, branch, supplier, bridge = load_data()
    forecast = forecast.drop(columns=["city"], errors="ignore")
    branch = branch.drop(columns=["city"], errors="ignore")

    df = forecast.merge(risk, on=["branch_id", "medicine_id"])
    df = df.merge(medicine, on="medicine_id", how="left")
    df = df.merge(branch, on="branch_id", how="left")
    df = df.merge(bridge, on="medicine_id", how="left")
    df = df.merge(supplier, on="supplier_id", how="left")

    if "priority_rank" not in df.columns:
        raise Exception("Missing priority_rank column in merged order data")

    df = df[df["priority_rank"] == 1]

    demand_col = next((c for c in df.columns if "forecast" in c.lower() or "demand" in c.lower()), None)
    stock_col = next((c for c in df.columns if "stock" in c.lower()), None)

    if not demand_col or not stock_col:
        raise Exception("Missing demand/stock columns")

    df["predicted_demand_7d"] = pd.to_numeric(df[demand_col], errors="coerce")
    df["current_stock"] = pd.to_numeric(df[stock_col], errors="coerce")
    df["reorder_qty"] = df["predicted_demand_7d"] - df["current_stock"]
    df = df[df["reorder_qty"] > 0].copy()
    df["reorder_qty"] = df["reorder_qty"].round().astype(int)

    return df


def send_email(to_email, supplier_name, file_path):
    try:
        if not EMAIL or not EMAIL.strip():
            raise ValueError("EMAIL not configured")
        if not PASSWORD or not PASSWORD.strip():
            raise ValueError("PASSWORD not configured")
        if not to_email or not str(to_email).strip():
            raise ValueError(f"Supplier email is missing for {supplier_name}")
        
        msg = EmailMessage()
        msg["Subject"] = "Monthly Purchase Order"
        msg["From"] = EMAIL
        msg["To"] = to_email

        msg.set_content(
            f"""
Dear {supplier_name},

Attached purchase order (reorder only).

Regards,
PharmaPulse AI
"""
        )

        with open(file_path, "rb") as f:
            msg.add_attachment(
                f.read(),
                maintype="application",
                subtype="csv",
                filename=file_path.name,
            )

        with smtplib.SMTP_SSL("smtp.gmail.com", 465) as smtp:
            smtp.login(EMAIL, PASSWORD)
            smtp.send_message(msg)
    except ValueError as e:
        print(f"ERROR: Validation failed - {str(e)}")
        raise
    except FileNotFoundError as e:
        print(f"ERROR: File not found - {file_path}")
        raise
    except smtplib.SMTPException as e:
        print(f"ERROR: Email sending failed for {supplier_name}: {str(e)}")
        raise
    except Exception as e:
        print(f"ERROR: Unexpected error sending email to {to_email}: {str(e)}")
        raise


def notify_owner():
    try:
        if not EMAIL or not EMAIL.strip():
            raise ValueError("EMAIL not configured")
        if not PASSWORD or not PASSWORD.strip():
            raise ValueError("PASSWORD not configured")
        if not NOTIFICATION_RECIPIENTS or len(NOTIFICATION_RECIPIENTS) == 0:
            print("WARNING: No notification recipients configured. Skipping owner notification.")
            return
        
        # Filter out empty/invalid recipients
        valid_recipients = [r for r in NOTIFICATION_RECIPIENTS if r and str(r).strip()]
        if not valid_recipients:
            print("WARNING: All notification recipients are invalid. Skipping owner notification.")
            return
        
        msg = EmailMessage()
        msg["Subject"] = "Orders Sent"
        msg["From"] = EMAIL
        msg["To"] = ", ".join(valid_recipients)
        msg.set_content("Orders sent successfully")

        with smtplib.SMTP_SSL("smtp.gmail.com", 465) as smtp:
            smtp.login(EMAIL, PASSWORD)
            smtp.send_message(msg)
        print(f"Owner notified at: {', '.join(valid_recipients)}")
    except ValueError as e:
        print(f"ERROR: Notification validation failed - {str(e)}")
    except smtplib.SMTPException as e:
        print(f"ERROR: Failed to send owner notification: {str(e)}")
    except Exception as e:
        print(f"ERROR: Unexpected error in notify_owner: {str(e)}")


def send_purchase_orders(max_messages: int | None = None):
    try:
        df = build_order_dataframe()
        sent_count = 0
        skipped_count = 0

        if max_messages is not None and max_messages <= 0:
            print("Max messages limit must be greater than zero.")
            return {"sent": 0, "skipped": 0}

        for supplier_id, group in df.groupby("supplier_id"):
            if max_messages is not None and sent_count >= max_messages:
                print(f"Reached max email limit of {max_messages}.")
                break

            if group["supplier_email"].isna().all():
                skipped_count += 1
                print(f"Skipping supplier {supplier_id} - no email found")
                continue
            
            supplier_email = group["supplier_email"].iloc[0]
            
            # Skip if supplier email is invalid
            if not supplier_email or not str(supplier_email).strip():
                skipped_count += 1
                print(f"Skipping supplier {supplier_id} - invalid email: {supplier_email}")
                continue

            supplier_name = group["supplier_name"].iloc[0]
            file_name = OUTPUT_DIR / f"order_supplier_{supplier_id}.csv"
            order_df = group[["branch_name", "brand_name", "reorder_qty"]].sort_values(by="branch_name")
            order_df.to_csv(file_name, index=False)
            print(f"Sending to {supplier_name}...")
            
            try:
                send_email(supplier_email, supplier_name, file_name)
                sent_count += 1
            except Exception as e:
                print(f"ERROR: Failed to send email to {supplier_name} ({supplier_email}): {str(e)}")
                skipped_count += 1
                continue

        if sent_count > 0:
            notify_owner()
        print(f"DONE - Emails are Sent! sent={sent_count} skipped={skipped_count}")
        return {"sent": sent_count, "skipped": skipped_count}
    except Exception as e:
        print(f"ERROR: Critical error in send_purchase_orders: {str(e)}")
        return {"sent": 0, "skipped": 0, "error": str(e)}


if __name__ == "__main__":
    send_purchase_orders()