WebsiteOpenOrders.py

#!/usr/bin/env python3
"""
WebsiteOpenOrders_Automation_Final.py

Complete automation script that:
1. Fetches order data from Yahoo email
2. Generates formatted Excel report
3. (Email sending disabled for finalization)

Workflow:
1. Connect to Yahoo Mail and fetch "Website Open Orders" emails
2. Extract CSV data between separator markers
3. Generate formatted Excel report with proper styling
4. Save report to local directory
"""

import os
import re
import imaplib
import email
from datetime import datetime, timedelta, time
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo
import io
import csv
import sys
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.utils import COMMASPACE, formatdate
from email import encoders
import smtplib
import importlib.util
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# ---------------- CONFIG ----------------
YAHOO_EMAIL = os.getenv('EDUFUN_YAHOO_EMAIL', '[YOUR_YAHOO_EMAIL]')
YAHOO_APP_PASSWORD = os.getenv('EDUFUN_YAHOO_APP_PASSWORD', '[YOUR_APP_PASSWORD]')
IMAP_SERVER = "imap.mail.yahoo.com"
IMAP_PORT = 993

OUTPUT_PATH = "./Reports"
today_str = datetime.today().strftime("%d-%m-%Y")  # Format: DD-MM-YYYY
OUTPUT_FILE = f"{OUTPUT_PATH}/Website Open Orders _ {today_str}.xlsx"

# Ensure output directory exists
os.makedirs(OUTPUT_PATH, exist_ok=True)

# Columns to remove from original CSV (if present) BEFORE renaming Name -> Order
DROP_COLS = ["Currency", "Card Type", "Payment Method", "Status", "Gateway", "Kind", "Order"]

# ---------------- helpers ----------------
def safe_float(x):
    """Convert common formatted currency strings (like '1,532.25 EGP') to float or None."""
    if pd.isna(x):
        return None
    if isinstance(x, (int, float)):
        return float(x)
    s = str(x).strip()
    # parentheses as negative
    neg = False
    if s.startswith("(") and s.endswith(")"):
        neg = True
        s = s[1:-1]
    # keep digits, dot and minus
    s = re.sub(r"[^\d\.\-]", "", s)
    if s in ("", ".", "-"):
        return None
    try:
        v = float(s)
        return -v if neg else v
    except Exception:
        return None

def connect_to_yahoo_mail():
    """Connect to Yahoo Mail using IMAP"""
    try:
        mail = imaplib.IMAP4_SSL(IMAP_SERVER, IMAP_PORT)
        mail.login(YAHOO_EMAIL, YAHOO_APP_PASSWORD)
        print(f"✅ Successfully connected to Yahoo Mail: {YAHOO_EMAIL}")
        return mail
    except Exception as e:
        print(f"❌ Failed to connect to Yahoo Mail: {str(e)}")
        raise

def search_emails(mail, sender_email=None, subject_keyword=None, days_back=1):
    """Search for relevant emails"""
    try:
        # Calculate date range
        from_date = (datetime.now() - timedelta(days=days_back)).strftime("%d-%b-%Y")
        
        # Build search criteria
        search_parts = [f'SINCE "{from_date}"']
        if sender_email:
            search_parts.append(f'FROM "{sender_email}"')
        if subject_keyword:
            search_parts.append(f'SUBJECT "{subject_keyword}"')
        
        search_criteria = "(" + " ".join(search_parts) + ")"
        
        mail.select('inbox')
        status, messages = mail.search(None, search_criteria)
        
        email_ids = messages[0].split()
        print(f"🔍 Found {len(email_ids)} emails matching criteria: {search_criteria}")
        
        # Sort email IDs in descending order to get the most recent first
        # and return only the most recent email ID
        if email_ids:
            email_ids = sorted(email_ids, reverse=True)[:1]  # Get only the most recent email
            print(f"📧 Processing only the most recent email")
        
        return email_ids
    except Exception as e:
        print(f"❌ Error searching emails: {str(e)}")
        return []

def extract_data_from_email_body(email_body):
    """Extract order data from email body using the separator patterns"""
    # Define the start and end markers
    start_marker = "Open Orders Data Start"
    end_marker = "Open Orders Data End"
    
    # Find the data between the markers
    start_pos = email_body.find(start_marker)
    end_pos = email_body.find(end_marker)
    
    if start_pos == -1 or end_pos == -1 or start_pos >= end_pos:
        print("⚠️ Could not find data between markers in email")
        return []
    
    # Extract the content between the markers
    start_pos = email_body.find('\n', start_pos) + 1  # Move past the start marker
    data_content = email_body[start_pos:end_pos].strip()
    
    # Clean up the data content by removing extra separators
    lines = data_content.split('\n')
    cleaned_lines = []
    
    for line in lines:
        stripped_line = line.strip()
        # Skip separator lines that contain only dashes and underscores
        if not re.match(r'^[-_\s]+$', stripped_line) and stripped_line:
            cleaned_lines.append(stripped_line)
    
    # Join the cleaned lines back together
    cleaned_content = '\n'.join(cleaned_lines)

    # Parse the CSV data - Skip the problematic CSV reader and use manual parsing
    orders = []

    # Manual parsing approach to avoid CSV reader issues
    lines = cleaned_content.split('\n')
    header_found = False
    header_keys = []
    
    for line in lines:
        line = line.strip()
        if not line:
            continue

        # Check if this looks like a header row (contains common column names)
        if not header_found and ('no.' in line.lower() or 'order' in line.lower() or 'created' in line.lower() or 'amount' in line.lower()):
            header_parts = line.split(',')
            header_keys = []
            for h in header_parts:
                if h is not None:
                    # Clean HTML tags and whitespace
                    clean_header = h.strip()
                    # Remove HTML tags if present
                    clean_header = re.sub(r'<[^>]+>', '', clean_header)
                    header_keys.append(clean_header)
                else:
                    header_keys.append("")
            header_found = True
            continue

        # Parse data rows
        if header_found and ',' in line:
            # Parse values safely
            line_parts = line.split(',')
            values = []
            for v in line_parts:
                if v is not None:
                    values.append(v.strip())
                else:
                    values.append("")

            if len(values) == len(header_keys):
                order_entry = {}
                for i, key in enumerate(header_keys):
                    clean_key = key.strip() if key else ""
                    clean_value = values[i] if i < len(values) else ""
                    
                    # Clean HTML tags from values too
                    clean_value = re.sub(r'<[^>]+>', '', clean_value) if clean_value else ""

                    # Map headers to expected format
                    if clean_key.lower() in ['order id', 'id', 'order']:
                        order_entry['Order'] = clean_value
                    elif clean_key.lower() in ['created at', 'date', 'created']:
                        # Parse the "Created At" field to extract date and time
                        if clean_value:
                            # Example: "2026-01-24 18:32:31" (based on your data format)
                            try:
                                # Try parsing with seconds first
                                dt = datetime.strptime(clean_value.strip(), '%Y-%m-%d %H:%M:%S')

                                # Format date as "Weekday, YYYY-MM-DD"
                                order_entry['Date'] = dt.strftime('%A, %Y-%m-%d')
                                # Store time separately
                                order_entry['Time'] = dt.time()
                            except ValueError:
                                try:
                                    # If parsing with seconds fails, try without seconds
                                    dt = datetime.strptime(clean_value.strip(), '%Y-%m-%d %H:%M')

                                    # Format date as "Weekday, YYYY-MM-DD"
                                    order_entry['Date'] = dt.strftime('%A, %Y-%m-%d')
                                    # Store time separately
                                    order_entry['Time'] = dt.time()
                                except ValueError:
                                    # If parsing fails, store as is
                                    order_entry['Date'] = clean_value
                                    order_entry['Time'] = ""
                        else:
                            order_entry['Date'] = clean_value
                            order_entry['Time'] = ""
                    elif clean_key.lower() in ['amount', 'total']:
                        # Clean the amount value to remove any extra spaces or characters
                        clean_amount = clean_value.strip() if clean_value else ""
                        order_entry['Amount'] = clean_amount
                    elif clean_key.lower() in ['no.', 'number', '#']:
                        order_entry['#'] = clean_value
                    else:
                        order_entry[clean_key] = clean_value

                if order_entry:
                    orders.append(order_entry)

    print(f"📊 Extracted {len(orders)} orders from email")

    # Debug: Print first few orders to see the data
    if orders:
        print("🔍 Debug: First order sample:")
        print(f"   Keys: {list(orders[0].keys())}")
        print(f"   Values: {list(orders[0].values())}")
        if len(orders) > 1:
            print(f"   Second order values: {list(orders[1].values())}")
    
    return orders

def fetch_email_data(sender_email=None, subject_keyword=None, days_back=1):
    """Main function to fetch data from emails"""
    print("📧 Starting email data fetch...")

    # Connect to Yahoo Mail
    mail = connect_to_yahoo_mail()

    # Search for relevant emails
    email_ids = search_emails(mail, sender_email, subject_keyword, days_back)

    if not email_ids:
        print("📭 No relevant emails found.")
        return []

    # Extract data from the latest email only
    all_orders = []
    for email_id in email_ids:  # Process only the latest email
        try:
            # Fetch email content
            status, msg_data = mail.fetch(email_id, '(RFC822)')
            msg = email.message_from_bytes(msg_data[0][1])

            # Get email subject and date for logging
            subject = msg.get('Subject', 'No Subject')
            date = msg.get('Date', 'Unknown Date')
            print(f"📄 Processing email: '{subject}' from {date}")

            # Extract data from email body
            email_body = ""

            if msg.is_multipart():
                for part in msg.walk():
                    if part.get_content_type() == "text/plain":
                        payload = part.get_payload(decode=True)
                        if payload:
                            email_body = payload.decode('utf-8', errors='ignore')
                        break
            else:
                payload = msg.get_payload(decode=True)
                if payload:
                    email_body = payload.decode('utf-8', errors='ignore')

            # Extract order data from email body
            orders = extract_data_from_email_body(email_body)
            all_orders.extend(orders)

        except Exception as e:
            print(f"⚠️ Error processing email ID {email_id}: {str(e)}")
            continue

    # Close connection
    mail.close()
    mail.logout()

    print(f"✅ Completed email data fetch. Total orders extracted: {len(all_orders)}")
    return all_orders

def generate_excel_report(orders_data):
    """Generate the Excel report with proper formatting"""
    print("📊 Generating Excel report...")
    
    # Convert to DataFrame
    df = pd.DataFrame(orders_data)
    
    # Ensure required columns exist
    required_cols = ["#", "Order", "Date", "Time", "Amount"]
    for col in required_cols:
        if col not in df.columns:
            if col == "#":
                df[col] = range(1, len(df) + 1)  # Add numbering
            else:
                df[col] = None
    
    # Reorder columns
    df = df[required_cols]
    
    # Normalize Amount column - ensure it's processed as float
    if "Amount" in df.columns:
        df["Amount"] = df["Amount"].apply(safe_float)
    else:
        # If Amount column wasn't found, try to identify it by other possible names
        possible_amount_cols = [col for col in df.columns if 'amount' in col.lower() or 'total' in col.lower()]
        if possible_amount_cols:
            # Use the first column that looks like an amount
            amount_col = possible_amount_cols[0]
            df["Amount"] = df[amount_col].apply(safe_float)
    
    # ---------------- remove existing output file if exists ----------------
    if os.path.exists(OUTPUT_FILE):
        try:
            os.remove(OUTPUT_FILE)
        except Exception as e:
            print(f"⚠️ Could not remove existing output file '{OUTPUT_FILE}': {e}")

    # ---------------- create workbook and write data ----------------
    wb = Workbook()
    ws = wb.active

    # force left-to-right
    ws.sheet_view.rightToLeft = False

    # Title row (we'll merge later across used columns)
    title_text = "Website Open Orders"
    ws.append([title_text])  # this is row 1 (title)

    # Add date row (row 2) with formatted date
    formatted_date = datetime.today().strftime("%A, %d %B %Y")  # e.g., "Sunday, 01 May 2026"
    ws.append([formatted_date])  # this is row 2 (date)

    # Append dataframe (header=True writes header on next row => row 3)
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)

    # determine used columns now
    min_row = 1
    header_row = 3  # Header is now on row 3
    max_row = ws.max_row
    min_col = 1
    max_col = ws.max_column

    # Merge title across all used columns
    if max_col >= 1:
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)

    # Merge date row across all used columns
    if max_col >= 1:
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=max_col)

    # ---------------- styles ----------------
    Liberation_Font_Rows = Font(name="Liberation Sans Narrow", size=12, bold=True)
    Liberation_Font_Title = Font(name="Liberation Sans Narrow", size=16, bold=True)
    Liberation_Font_Header = Font(name="Liberation Sans Narrow", size=14, bold=True)
    center_align = Alignment(horizontal="center", vertical="center")
    right_vertical_align = Alignment(horizontal="right", vertical="center")

    header_fill = PatternFill("solid", fgColor="2FE57E")  # header & title background

    thick_side = Side(style="thick", color="000000")     # thick for title/header and outer border
    dotted_side = Side(style="dotted", color="000000")   # dotted for inner data borders

    # Apply Title Row styles (row 1). We apply style to each cell in merged area so vertical inner lines are thick
    for c in range(min_col, max_col + 1):
        cell = ws.cell(1, c)
        if c == min_col:
            cell.value = title_text  # ensure text in first cell
        cell.font = Liberation_Font_Title
        cell.alignment = center_align
        cell.fill = header_fill
        # set thick on all sides for title row
        cell.border = Border(left=thick_side, right=thick_side, top=thick_side, bottom=thick_side)

    # Apply Date Row styles (row 2). Similar to title but with different font
    for c in range(min_col, max_col + 1):
        cell = ws.cell(2, c)
        if c == min_col:
            cell.value = formatted_date  # ensure text in first cell
        cell.font = Liberation_Font_Header  # Using header font size for date
        cell.alignment = center_align
        cell.fill = header_fill
        # set thick on all sides for date row
        cell.border = Border(left=thick_side, right=thick_side, top=thick_side, bottom=thick_side)

    # Apply Header Row styles (row 3). All sides thick (inner & outer)
    for c in range(min_col, max_col + 1):
        cell = ws.cell(header_row, c)
        cell.font = Liberation_Font_Header
        cell.alignment = center_align
        cell.fill = header_fill
        cell.border = Border(left=thick_side, right=thick_side, top=thick_side, bottom=thick_side)

    # Apply basic Data Rows styles (row 4 .. max_row) with alternating row colors
    data_start = header_row + 1  # Data starts after header row (row 4)
    data_end = max_row

    # Define fills for alternating rows
    light_greenish_fill = PatternFill("solid", fgColor="E0F0E0")  # Light greenish for even rows
    white_fill = PatternFill("solid", fgColor="FFFFFF")          # White for odd rows

    # Define border styles
    thick_side = Side(style="thick", color="000000")     # thick for outer borders
    dotted_side = Side(style="dotted", color="000000")   # dotted for inner borders

    for r in range(data_start, data_end + 1):
        for c in range(min_col, max_col + 1):
            cell = ws.cell(r, c)
            cell.font = Liberation_Font_Rows  # Liberation Sans Narrow font for data rows

            # Alternate row colors
            if (r - data_start) % 2 == 1:  # Even data rows (second, fourth, etc.)
                cell.fill = light_greenish_fill
            else:  # Odd data rows (first, third, etc.)
                cell.fill = white_fill

            # Amount column -> right aligned with vertical center, others center aligned
            header_name = ws.cell(header_row, c).value
            if header_name == "Amount":
                cell.alignment = right_vertical_align
            else:
                cell.alignment = center_align

            # Decide border sides: outer edges thick, internal sides dotted
            left_side = thick_side if c == min_col else dotted_side
            right_side = thick_side if c == max_col else dotted_side

            # Top: thick if first data row (to match header bottom), else dotted
            top_side = thick_side if r == data_start else dotted_side
            # Bottom: thick if last data row (to close outer border), else dotted
            bottom_side = thick_side if r == data_end else dotted_side

            cell.border = Border(left=left_side, right=right_side, top=top_side, bottom=bottom_side)

    # ---------------- number formats ----------------
    # Build map header -> col idx
    header_map = {}
    for c in range(min_col, max_col + 1):
        header_map[ws.cell(header_row, c).value] = c

    # Date format - already formatted as string with weekday
    if "Date" in header_map:
        col_idx = header_map["Date"]
        for r in range(data_start, data_end + 1):
            cell = ws.cell(r, col_idx)
            cell.alignment = center_align

    # Time format
    if "Time" in header_map:
        col_idx = header_map["Time"]
        for r in range(data_start, data_end + 1):
            cell = ws.cell(r, col_idx)
            cell.number_format = "hh:mm:ss"
            cell.alignment = center_align

    # Amount numeric format with EGP suffix (only apply when numeric)
    if "Amount" in header_map:
        col_idx = header_map["Amount"]
        for r in range(data_start, data_end + 1):
            cell = ws.cell(r, col_idx)
            if isinstance(cell.value, (int, float)):
                cell.number_format = '#,##0.00 "EGP"'
            # keep right alignment
            cell.alignment = right_vertical_align

    # ---------------- create table with autofilter ----------------
    # Define the data range for the table (excluding title row)
    table_range = f"{get_column_letter(min_col)}{header_row}:{get_column_letter(max_col)}{max_row}"

    # Create a table
    table = Table(displayName="OrdersTable", ref=table_range)

    # Add a default table style with better colors
    style = TableStyleInfo(
        name="TableStyleMedium9",
        showFirstColumn=False,
        showLastColumn=False,
        showRowStripes=True,  # Alternating row colors
        showColumnStripes=False  # No alternating column colors
    )
    table.tableStyleInfo = style

    # Add the table to the worksheet
    ws.add_table(table)

    # ---------------- row heights ----------------
    # Set row heights: 1.00 cm for title row (row 1), 0.80 cm for all other rows
    ws.row_dimensions[1].height = 28.35  # 1.00 cm in points (1 cm ≈ 28.35 points)
    for r in range(2, max_row + 1):  # For all other rows
        ws.row_dimensions[r].height = 22.68  # 0.80 cm in points (0.80 cm ≈ 22.68 points)

    # ---------------- Column Width Auto-Fit ----------------
    def autofit_columns(ws):
        """
        Adjust all used columns to fit the longest cell value in each column.
        This function considers all cells in each column to determine the optimal width.
        """
        for col in range(1, ws.max_column + 1):  # Iterate through each column
            max_length = 0
            col_letter = get_column_letter(col)

            # Find the maximum length in the column
            for row in range(1, ws.max_row + 1):
                cell_value = ws.cell(row=row, column=col).value
                if cell_value is not None:
                    # Convert to string and measure length
                    cell_length = len(str(cell_value))
                    if cell_length > max_length:
                        max_length = cell_length

            # Set the column width with some padding, but set reasonable min/max limits
            adjusted_width = min(50, max(10, max_length + 3))  # Min width 10, Max width 50, padding 3
            ws.column_dimensions[col_letter].width = adjusted_width

    # ---------------- Call Fit Content ----------------
    autofit_columns(ws)

    # ---------------- save workbook ----------------
    wb.save(OUTPUT_FILE)
    print(f"✅ Formatted report saved: {OUTPUT_FILE}")

def send_email_with_attachment(file_path, to_emails, cc_emails, subject, body_html):
    """
    Send an email with an attachment
    NOTE: This function is included but not called in final execution
    """
    # Create message
    msg = MIMEMultipart()
    msg['From'] = YAHOO_EMAIL
    msg['To'] = COMMASPACE.join(to_emails)
    if cc_emails:
        msg['Cc'] = COMMASPACE.join(cc_emails)
    msg['Date'] = formatdate(localtime=True)
    msg['Subject'] = subject
    
    # Create a multipart/alternative container for the email body
    body_part = MIMEMultipart('alternative')
    
    # Add plain text part
    text_part = MIMEText("Body text would go here", 'plain', 'utf-8')
    body_part.attach(text_part)
    
    # Add HTML part
    html_part = MIMEText(body_html, 'html', 'utf-8')
    body_part.attach(html_part)
    
    # Add the body part to the main message
    msg.attach(body_part)
    
    # Add the logo image if it exists
    logo_path = "./SignatureLogo.png"
    if os.path.exists(logo_path):
        with open(logo_path, 'rb') as img_file:
            img = MIMEImage(img_file.read())
            img.add_header('Content-ID', '<signature_logo>')
            msg.attach(img)

    # Open file in binary mode and attach it
    with open(file_path, "rb") as attachment:
        # Instance of MIMEBase and named as part
        part = MIMEBase('application', 'octet-stream')
        part.set_payload(attachment.read())

    # Encode file in ASCII characters to send by email    
    encoders.encode_base64(part)

    # Add header as key/value pair to attachment part
    attachment_filename = os.path.basename(file_path)
    part.add_header(
        'Content-Disposition',
        f'attachment; filename="{attachment_filename}"',
    )

    # Attach the part to message
    msg.attach(part)

    # Add CC recipients to the list of all recipients
    all_recipients = to_emails + cc_emails
    
    try:
        # Create SMTP session
        server = smtplib.SMTP('smtp.mail.yahoo.com', 587)  # Yahoo SMTP server
        server.starttls()  # Enable security
        server.login(YAHOO_EMAIL, YAHOO_APP_PASSWORD)  # Login with sender email and password
        
        # Send email
        server.sendmail(YAHOO_EMAIL, all_recipients, msg.as_string())
        server.quit()
        
        print(f"✅ Email sent successfully to {len(all_recipients)} recipients")
        return True
        
    except Exception as e:
        print(f"❌ Failed to send email: {str(e)}")
        return False

def main():
    print("🚀 Starting Website Open Orders Automation")

    # Fetch data from emails
    orders_data = fetch_email_data(
        sender_email="flow@shopify.com",  # ShopifyFlow App sender
        subject_keyword="Website Open Orders",  # Specify subject keyword if needed
        days_back=1  # Look back 1 day
    )

    if not orders_data:
        print("❌ No order data found in emails. Exiting.")
        return

    # Generate the Excel report
    generate_excel_report(orders_data)

    print("🎉 Complete workflow finished successfully!")
    print(f"   Report: {OUTPUT_FILE}")

    # Email sending functionality (enabled)
    # Prepare email content
    subject = f"Website Open Orders Report - {datetime.today().strftime('%d %B %Y')}"

    # Create HTML body with signature image
    html_body = f"""<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
</head>
<body>
    <div style="font-family: Arial, sans-serif;">
        <div style="text-align: right;">
            <p><strong>السادة المحترمين</strong></p>
            <p>تحية طيبة</p>
            <p>تم ارفاق الملف الخاص  بالاوردرات المفتوحة  بموقع مصر</p>
        </div>

        <br/>
        <div style="border-top: 1px solid #ccc; padding-top: 10px; margin-top: 20px; text-align: left;">
            <p><strong>Best Regards..</strong></p>
            <p></p> <!-- Empty line after Best Regards -->
            <img src="cid:signature_logo" alt="Signature Logo" style="max-width: 150px; height: auto; display: block; margin-bottom: 10px;" />
            <div style="font-weight: bold; font-size: 14px;">{os.getenv('EDUFUN_SIGNATURE_NAME', '[YOUR_NAME]')}</div>
            <div style="font-size: 12px; color: #555;">{os.getenv('EDUFUN_SIGNATURE_TITLE', '[YOUR_TITLE]')}</div>
            <div style="font-size: 12px; margin-top: 5px;"><span style="font-weight: bold; color: #333;">🏠</span> {os.getenv('EDUFUN_SIGNATURE_ADDRESS', '[YOUR_ADDRESS]')}</div>
            <div style="font-size: 12px; margin-top: 2px; margin-left: 15px;">{os.getenv('EDUFUN_SIGNATURE_CITY_STATE', '[YOUR_CITY_STATE]')}</div>
            <div style="font-size: 12px; margin-top: 5px;"><span style="font-weight: bold; color: #333;">📧</span> <a href="mailto:{os.getenv('EDUFUN_YAHOO_EMAIL', '[YOUR_EMAIL]')}">{os.getenv('EDUFUN_YAHOO_EMAIL', '[YOUR_EMAIL]')}</a></div>
            <div style="font-size: 12px; margin-top: 5px;"><span style="font-weight: bold; color: #333;">🔗</span> <a href="http://{os.getenv('EDUFUN_WEBSITE_URL', '[YOUR_WEBSITE]')}">{os.getenv('EDUFUN_WEBSITE_URL', '[YOUR_WEBSITE]')}</a></div>
            <div style="font-size: 12px; margin-top: 5px;"><span style="font-weight: bold; color: #333;">📱</span> {os.getenv('EDUFUN_SIGNATURE_MOBILE', '[YOUR_MOBILE]')}</div>
            <div style="font-size: 12px; margin-top: 5px;"><span style="font-weight: bold; color: #333;">📞</span> {os.getenv('EDUFUN_SIGNATURE_PHONE', '[YOUR_PHONE]')}</div>
        </div>
    </div>
</body>
</html>"""

    # Get recipient emails from environment variables
    to_email_env = os.getenv('EDUFUN_TO_EMAIL', '[RECIPIENT_EMAIL]')
    cc_email_env = os.getenv('EDUFUN_CC_EMAIL', '[CC_EMAIL]')

    # Handle multiple recipients if needed
    to_emails = [email.strip() for email in to_email_env.split(',') if email.strip()]
    cc_emails = [email.strip() for email in cc_email_env.split(',') if email.strip()]

    # Send the email with the attachment
    print("📤 Sending email with report attachment...")
    success = send_email_with_attachment(
        file_path=OUTPUT_FILE,
        to_emails=to_emails,
        cc_emails=cc_emails,
        subject=subject,
        body_html=html_body
    )

    if success:
        print("🎉 Complete workflow finished successfully!")
        print(f"   Report: {OUTPUT_FILE}")
        print(f"   Sent to: {len(to_emails + cc_emails)} recipients")
    else:
        print("❌ Workflow failed at email sending stage")

if __name__ == "__main__":
    main()