import smtplib
import os
import sqlite3
import logging
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from threading import Lock

# Configure logging for robust exception tracking
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# Thread-safe lock for database operations
db_lock = Lock()
DATABASE_PATH = 'leads.db' # Assume a default database name based on prior context

def send_email(recipient_email, subject, body):
    """Handles the SMTP dispatch logic using TLS on port 465."""
    sender_email = os.environ.get('SMTP_SENDER')
    app_password = os.environ.get('SMTP_APP_PASSWORD')

    if not sender_email or not app_password:
        logger.error("SMTP credentials not found in environment variables. Please set SMTP_SENDER and SMTP_APP_PASSWORD.")
        return False

    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = recipient_email
    msg['Subject'] = subject
    msg.attach(MIMEText(body, 'plain'))

    try:
        # Configure secure transit using TLS connection mechanisms
        with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
            server.login(sender_email, app_password)
            server.send_message(msg)
        logger.info(f"Email successfully sent to {recipient_email}")
        return True
    
    except smtplib.SMTPException as e:
        logger.error(f"SMTP error occurred while sending email to {recipient_email}: {e}")
        return False
    except Exception as e:
        # Handles transient network drops or unexpected errors gracefully
        logger.error(f"An unexpected error occurred while sending email to {recipient_email}: {e}")
        return False

def update_lead_status(lead_id):
    """State Synchronization: Updates the lead status to SENT in the database."""
    with db_lock:
        conn = None
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            # Using parameterized query to prevent SQL injection and update state
            cursor.execute('''
                UPDATE leads 
                SET status = 'SENT' 
                WHERE id = ? AND status = 'PENDING_REVIEW'
            ''', (lead_id,))
            
            if cursor.rowcount > 0:
                conn.commit()
                logger.info(f"Successfully synchronized state: status updated to SENT for lead ID {lead_id}")
            else:
                logger.warning(f"No rows updated for lead ID {lead_id}. Lead might not exist or status is not PENDING_REVIEW.")
                
        except sqlite3.Error as e:
            logger.error(f"Database error updating lead {lead_id}: {e}")
            if conn:
                conn.rollback() # Ensure database integrity is maintained
        finally:
            if conn:
                conn.close()

def dispatch_and_sync(lead_id, recipient_email, subject, body):
    """Main thread-safe dispatch routine that executes email delivery and syncs state."""
    logger.info(f"Starting dispatch sequence for lead ID {lead_id} ({recipient_email})")
    
    # 1. Attempt dispatch
    delivery_successful = send_email(recipient_email, subject, body)
    
    # 2. Synchronize state on success
    if delivery_successful:
        update_lead_status(lead_id)
    else:
        logger.error(f"Failed to deliver email for lead {lead_id}. Database synchronization skipped.")