app.py

from flask import Flask, render_template, request, send_file, jsonify, flash, redirect, url_for
import pandas as pd
import os
from werkzeug.utils import secure_filename
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import letter, A4
from reportlab.lib.colors import black, blue, red
from reportlab.pdfbase import pdfutils
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
from reportlab.lib.units import inch
from PyPDF2 import PdfReader, PdfWriter
from reportlab.pdfgen.canvas import Canvas
from reportlab.lib.utils import ImageReader
import io
import zipfile
from datetime import datetime
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.lib.fonts import addMapping
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import threading
import time

app = Flask(__name__)
app.secret_key = 'your-secret-key-here'

# Configure upload folders
UPLOAD_FOLDER = 'uploads'
GENERATED_FOLDER = 'generated'
ALLOWED_EXTENSIONS = {'pdf', 'csv'}

# Create directories if they don't exist
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
os.makedirs(GENERATED_FOLDER, exist_ok=True)

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['GENERATED_FOLDER'] = GENERATED_FOLDER
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB max file size

# Font configurations with cursive and aesthetic options
AVAILABLE_FONTS = {
    'helvetica_bold': {
        'name': 'Helvetica-Bold',
        'display_name': '🔤 Helvetica Bold (Clean & Professional)',
        'file': None,  # Built-in font
        'style': 'clean'
    },
    'times_bold': {
        'name': 'Times-Bold', 
        'display_name': '📚 Times Bold (Classic & Elegant)',
        'file': None,  # Built-in font
        'style': 'classic'
    },
    'courier_bold': {
        'name': 'Courier-Bold',
        'display_name': '⌨️ Courier Bold (Typewriter Style)',
        'file': None,  # Built-in font
        'style': 'typewriter'
    },
    'script_mt_bold': {
        'name': 'Script-MT-Bold',
        'display_name': '✍️ Script MT (Handwritten Style)',
        'file': None,  # Built-in font if available
        'style': 'script'
    }
}

# Email configuration
EMAIL_CONFIG = {
    'smtp_server': '',
    'smtp_port': 587,
    'email': '',
    'password': '',
    'use_tls': True
}

# Global variable to track email sending progress
email_progress = {
    'total': 0,
    'sent': 0,
    'failed': 0,
    'current': '',
    'status': 'idle',  # idle, sending, completed, error
    'errors': []
}

def register_custom_fonts():
    """Register additional fonts for stylish certificates"""
    try:
        # Try to register some common system fonts that might be available
        import os
        import platform
        
        # Common font paths by OS
        font_paths = []
        system = platform.system()
        
        if system == "Darwin":  # macOS
            font_paths = [
                "/System/Library/Fonts/",
                "/Library/Fonts/",
                "~/Library/Fonts/"
            ]
        elif system == "Windows":
            font_paths = [
                "C:/Windows/Fonts/",
                "C:/WINDOWS/Fonts/"
            ]
        elif system == "Linux":
            font_paths = [
                "/usr/share/fonts/",
                "/usr/local/share/fonts/",
                "~/.fonts/"
            ]
        
        # Try to find and register cursive fonts
        cursive_fonts = {
            'brush_script': ['BrushScriptMT.ttf', 'brush.ttf', 'BrushScript.ttf'],
            'lucida_handwriting': ['LucidaHandwriting.ttf', 'LHANDW.TTF', 'lucida_handwriting.ttf'],
            'edwardian_script': ['ITCEDSCR.TTF', 'EdwardianScript.ttf', 'edwardian.ttf'],
            'freestyle_script': ['FREESCPT.TTF', 'FreestyleScript.ttf', 'freestyle.ttf'],
            'monotype_corsiva': ['MTCORSVA.TTF', 'MonotypeCorsiva.ttf', 'corsiva.ttf']
        }
        
        for font_key, font_files in cursive_fonts.items():
            for font_path_base in font_paths:
                font_path_base = os.path.expanduser(font_path_base)
                if os.path.exists(font_path_base):
                    for font_file in font_files:
                        font_path = os.path.join(font_path_base, font_file)
                        if os.path.exists(font_path):
                            try:
                                font_name = f"CustomFont_{font_key}"
                                pdfmetrics.registerFont(TTFont(font_name, font_path))
                                
                                # Add to available fonts
                                AVAILABLE_FONTS[font_key] = {
                                    'name': font_name,
                                    'display_name': f'✨ {font_key.replace("_", " ").title()} (Cursive)',
                                    'file': font_path,
                                    'style': 'cursive'
                                }
                                print(f"✅ Registered cursive font: {font_key}")
                                break
                            except Exception as e:
                                print(f"⚠️ Could not register {font_file}: {str(e)}")
                                continue
                    else:
                        continue
                    break
        
        # Add some web-safe cursive alternatives using built-in fonts with transformations
        AVAILABLE_FONTS.update({
            'times_italic': {
                'name': 'Times-Italic',
                'display_name': '✍️ Times Italic (Elegant Script)',
                'file': None,
                'style': 'script'
            },
            'helvetica_oblique': {
                'name': 'Helvetica-Oblique', 
                'display_name': '📝 Helvetica Oblique (Modern Script)',
                'file': None,
                'style': 'script'
            }
        })
        
    except Exception as e:
        print(f"Font registration error: {str(e)}")

# Initialize fonts when app starts
register_custom_fonts()

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/upload', methods=['POST'])
def upload_files():
    try:
        pdf_file = request.files.get('pdf_file')
        csv_file = request.files.get('csv_file')
        
        if not pdf_file or not csv_file:
            flash('Please select both PDF template and CSV file')
            return redirect(url_for('index'))
        
        if pdf_file and allowed_file(pdf_file.filename):
            pdf_filename = secure_filename(pdf_file.filename)
            pdf_path = os.path.join(app.config['UPLOAD_FOLDER'], pdf_filename)
            pdf_file.save(pdf_path)
        else:
            flash('Invalid PDF file')
            return redirect(url_for('index'))
        
        if csv_file and allowed_file(csv_file.filename):
            csv_filename = secure_filename(csv_file.filename)
            csv_path = os.path.join(app.config['UPLOAD_FOLDER'], csv_filename)
            csv_file.save(csv_path)
        else:
            flash('Invalid CSV file')
            return redirect(url_for('index'))
        
        # Read CSV to get column names
        df = pd.read_csv(csv_path)
        columns = df.columns.tolist()
        
        return render_template('configure.html', 
                             pdf_file=pdf_filename, 
                             csv_file=csv_filename, 
                             columns=columns)
    
    except Exception as e:
        flash(f'Error uploading files: {str(e)}')
        return redirect(url_for('index'))

@app.route('/configure', methods=['POST'])
def configure_certificate():
    try:
        pdf_file = request.form.get('pdf_file')
        csv_file = request.form.get('csv_file')
        
        # Get form data for text positioning and content
        name_field = request.form.get('name_field')
        name_x = float(request.form.get('name_x', 300))
        name_y = float(request.form.get('name_y', 400))
        name_size = int(request.form.get('name_size', 24))
        name_font = request.form.get('name_font', 'helvetica_bold')  # New font selection
        
        # Additional fields
        additional_fields = {}
        field_count = int(request.form.get('field_count', 0))
        
        for i in range(field_count):
            field_name = request.form.get(f'field_{i}_name')
            field_column = request.form.get(f'field_{i}_column')
            field_x = float(request.form.get(f'field_{i}_x', 300))
            field_y = float(request.form.get(f'field_{i}_y', 350))
            field_size = int(request.form.get(f'field_{i}_size', 14))
            field_font = request.form.get(f'field_{i}_font', 'helvetica_bold')  # New font selection
            
            if field_name and field_column:
                additional_fields[field_name] = {
                    'column': field_column,
                    'x': field_x,
                    'y': field_y,
                    'size': field_size,
                    'font': field_font
                }
        
        # Generate certificates
        pdf_path = os.path.join(app.config['UPLOAD_FOLDER'], pdf_file)
        csv_path = os.path.join(app.config['UPLOAD_FOLDER'], csv_file)
        
        certificates_generated = generate_certificates(
            pdf_path, csv_path, name_field, name_x, name_y, name_size, name_font, additional_fields
        )
        
        flash(f'Successfully generated {certificates_generated} certificates!')
        return redirect(url_for('download_certificates'))
    
    except Exception as e:
        flash(f'Error generating certificates: {str(e)}')
        return redirect(url_for('index'))

def generate_certificates(pdf_path, csv_path, name_field, name_x, name_y, name_size, name_font, additional_fields):
    # Read CSV data
    df = pd.read_csv(csv_path)
    
    # Clean up any existing generated files
    for filename in os.listdir(app.config['GENERATED_FOLDER']):
        if filename.endswith('.pdf'):
            os.remove(os.path.join(app.config['GENERATED_FOLDER'], filename))
    
    certificates_generated = 0
    
    for index, row in df.iterrows():
        try:
            # Skip empty rows
            if pd.isna(row[name_field]) or str(row[name_field]).strip() == '':
                continue
            
            # Create output filename with name_certificate format
            safe_name = secure_filename(str(row[name_field]).replace(' ', '_'))
            output_filename = f"{safe_name}_certificate.pdf"
            output_path = os.path.join(app.config['GENERATED_FOLDER'], output_filename)
            
            # Create certificate with text overlay
            create_certificate_with_overlay(pdf_path, output_path, row, name_field, 
                                          name_x, name_y, name_size, name_font, additional_fields)
            
            certificates_generated += 1
            
        except Exception as e:
            print(f"Error generating certificate for row {index}: {str(e)}")
            continue
    
    return certificates_generated

def create_certificate_with_overlay(template_path, output_path, data_row, name_field, 
                                   name_x, name_y, name_size, name_font, additional_fields):
    # Read the template PDF
    with open(template_path, 'rb') as template_file:
        pdf_reader = PdfReader(template_file)
        pdf_writer = PdfWriter()
        
        # Get the first page (assuming single page certificate)
        page = pdf_reader.pages[0]
        page_width = float(page.mediabox.width)
        page_height = float(page.mediabox.height)
        
        # Create overlay with text
        packet = io.BytesIO()
        can = canvas.Canvas(packet, pagesize=(page_width, page_height))
        
        # Set up the selected font for names
        font_config = AVAILABLE_FONTS.get(name_font, AVAILABLE_FONTS['helvetica_bold'])
        font_name = font_config['name']
        
        try:
            can.setFont(font_name, name_size)
        except:
            # Fallback to Helvetica-Bold if font not available
            can.setFont("Helvetica-Bold", name_size)
            font_name = "Helvetica-Bold"
        
        # Add name with selected font
        name_text = str(data_row[name_field])
        text_width = can.stringWidth(name_text, font_name, name_size)
        can.drawString(name_x - text_width/2, page_height - name_y, name_text)
        
        # Add additional fields with their selected fonts
        for field_name, field_config in additional_fields.items():
            try:
                field_value = str(data_row[field_config['column']])
                field_font_key = field_config.get('font', 'helvetica_bold')
                field_font_config = AVAILABLE_FONTS.get(field_font_key, AVAILABLE_FONTS['helvetica_bold'])
                field_font_name = field_font_config['name']
                
                try:
                    can.setFont(field_font_name, field_config['size'])
                except:
                    # Fallback to Helvetica if font not available
                    can.setFont("Helvetica", field_config['size'])
                    field_font_name = "Helvetica"
                
                text_width = can.stringWidth(field_value, field_font_name, field_config['size'])
                can.drawString(field_config['x'] - text_width/2, page_height - field_config['y'], field_value)
            except Exception as e:
                print(f"Error adding field {field_name}: {str(e)}")
                continue
        
        can.save()
        
        # Create overlay PDF
        packet.seek(0)
        overlay_pdf = PdfReader(packet)
        overlay_page = overlay_pdf.pages[0]
        
        # Merge template with overlay
        page.merge_page(overlay_page)
        pdf_writer.add_page(page)
        
        # Write the output
        with open(output_path, 'wb') as output_file:
            pdf_writer.write(output_file)

@app.route('/download')
def download_certificates():
    try:
        # Create a zip file with all generated certificates
        zip_filename = f"certificates_{datetime.now().strftime('%Y%m%d_%H%M%S')}.zip"
        zip_path = os.path.join(app.config['GENERATED_FOLDER'], zip_filename)
        
        with zipfile.ZipFile(zip_path, 'w') as zipf:
            for filename in os.listdir(app.config['GENERATED_FOLDER']):
                if filename.endswith('.pdf'):
                    file_path = os.path.join(app.config['GENERATED_FOLDER'], filename)
                    zipf.write(file_path, filename)
        
        return send_file(zip_path, as_attachment=True, download_name=zip_filename)
    
    except Exception as e:
        flash(f'Error creating download: {str(e)}')
        return redirect(url_for('index'))

@app.route('/preview')
def preview():
    return render_template('preview.html')

@app.route('/fonts')
def get_available_fonts():
    """API endpoint to get available fonts for AJAX calls"""
    return jsonify(AVAILABLE_FONTS)

@app.route('/font-showcase')
def font_showcase():
    """Display font showcase page"""
    return render_template('font_showcase.html')

@app.route('/email-setup')
def email_setup():
    """Email configuration and sending page"""
    return render_template('email_setup.html')

@app.route('/api/csv-files')
def get_csv_files():
    """API endpoint to get available CSV files"""
    try:
        csv_files = []
        upload_folder = app.config['UPLOAD_FOLDER']
        
        if os.path.exists(upload_folder):
            for filename in os.listdir(upload_folder):
                if filename.endswith('.csv'):
                    file_path = os.path.join(upload_folder, filename)
                    file_size = os.path.getsize(file_path)
                    csv_files.append({
                        'filename': filename,
                        'size': file_size,
                        'size_mb': round(file_size / (1024 * 1024), 2)
                    })
        
        return jsonify(csv_files)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/csv-columns/<path:filename>')
def get_csv_columns(filename):
    """API endpoint to get columns from a specific CSV file"""
    try:
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        
        if not os.path.exists(file_path):
            # List available files for better error reporting
            available_files = [f for f in os.listdir(app.config['UPLOAD_FOLDER']) if f.endswith('.csv')]
            return jsonify({
                'error': f'File "{filename}" not found',
                'requested_file': filename,
                'available_files': available_files
            }), 404
        
        # Read just the first few rows to get column info
        df = pd.read_csv(file_path, nrows=5)
        columns = df.columns.tolist()
        
        # Get sample data for preview
        sample_data = []
        for _, row in df.iterrows():
            # Convert row to dict and handle NaN values
            row_dict = row.to_dict()
            # Replace NaN values with None (which becomes null in JSON)
            for key, value in row_dict.items():
                if pd.isna(value):
                    row_dict[key] = None
                elif isinstance(value, float) and str(value) == 'nan':
                    row_dict[key] = None
            sample_data.append(row_dict)
        
        return jsonify({
            'columns': columns,
            'sample_data': sample_data,
            'total_rows': len(pd.read_csv(file_path))
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/upload-csv', methods=['POST'])
def upload_csv():
    """Upload a new CSV file for email sending"""
    try:
        if 'csv_file' not in request.files:
            return jsonify({'success': False, 'error': 'No file provided'}), 400
        
        file = request.files['csv_file']
        if file.filename == '':
            return jsonify({'success': False, 'error': 'No file selected'}), 400
        
        if not file.filename.endswith('.csv'):
            return jsonify({'success': False, 'error': 'Only CSV files are allowed'}), 400
        
        # Save the file
        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        
        # If file exists, add timestamp to make it unique
        if os.path.exists(file_path):
            name, ext = os.path.splitext(filename)
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"{name}_{timestamp}{ext}"
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        
        file.save(file_path)
        
        # Validate CSV structure
        try:
            df = pd.read_csv(file_path, nrows=1)
            if len(df.columns) < 2:
                os.remove(file_path)  # Remove invalid file
                return jsonify({'success': False, 'error': 'CSV must have at least 2 columns (name and email)'}), 400
        except Exception as e:
            if os.path.exists(file_path):
                os.remove(file_path)  # Remove invalid file
            return jsonify({'success': False, 'error': f'Invalid CSV format: {str(e)}'}), 400
        
        return jsonify({
            'success': True, 
            'filename': filename,
            'message': f'CSV file "{filename}" uploaded successfully'
        })
        
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/test-smtp', methods=['POST'])
def test_smtp():
    """Test SMTP connection without sending emails"""
    try:
        data = request.get_json()
        
        smtp_server = data.get('smtp_server')
        smtp_port = data.get('smtp_port', 587)
        sender_email = data.get('sender_email')
        sender_password = data.get('sender_password')
        use_tls = data.get('use_tls', True)
        
        if not all([smtp_server, sender_email, sender_password]):
            return jsonify({'success': False, 'error': 'Missing required fields'}), 400
        
        # Test SMTP connection
        server = smtplib.SMTP(smtp_server, smtp_port)
        if use_tls:
            server.starttls()
        
        # Test login
        server.login(sender_email, sender_password)
        server.quit()
        
        return jsonify({
            'success': True, 
            'message': 'SMTP connection successful'
        })
        
    except smtplib.SMTPAuthenticationError as e:
        error_msg = str(e)
        if 'BadCredentials' in error_msg or '535' in error_msg:
            return jsonify({
                'success': False, 
                'error': 'Authentication failed. For Gmail: Use App Password, not regular password. Enable 2FA first.'
            })
        else:
            return jsonify({'success': False, 'error': f'Authentication error: {error_msg}'})
    
    except smtplib.SMTPException as e:
        return jsonify({'success': False, 'error': f'SMTP error: {str(e)}'})
    
    except Exception as e:
        return jsonify({'success': False, 'error': f'Connection error: {str(e)}'})
    
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/send-emails', methods=['POST'])
def send_emails():
    """Configure and start sending emails"""
    global email_progress
    
    try:
        # Get email configuration
        smtp_server = request.form.get('smtp_server')
        smtp_port = int(request.form.get('smtp_port', 587))
        sender_email = request.form.get('sender_email')
        sender_password = request.form.get('sender_password')
        use_tls = request.form.get('use_tls') == 'on'
        
        # Get email content
        subject = request.form.get('subject', 'Your Certificate')
        email_body = request.form.get('email_body', 'Please find your certificate attached.')
        
        # Get CSV file path and name field
        csv_file = request.form.get('csv_file')
        name_field = request.form.get('name_field', 'Name')
        email_field = request.form.get('email_field', 'Email')
        
        if not all([smtp_server, sender_email, sender_password, csv_file]):
            flash('Please fill in all required email configuration fields')
            return redirect(url_for('email_setup'))
        
        # Update email configuration
        EMAIL_CONFIG.update({
            'smtp_server': smtp_server,
            'smtp_port': smtp_port,
            'email': sender_email,
            'password': sender_password,
            'use_tls': use_tls
        })
        
        # Start email sending in background thread
        csv_path = os.path.join(app.config['UPLOAD_FOLDER'], csv_file)
        
        email_thread = threading.Thread(
            target=send_bulk_emails,
            args=(csv_path, name_field, email_field, subject, email_body)
        )
        email_thread.daemon = True
        email_thread.start()
        
        flash('Email sending started! Check progress on the email status page.')
        return redirect(url_for('email_status'))
        
    except Exception as e:
        flash(f'Error starting email process: {str(e)}')
        return redirect(url_for('email_setup'))

def send_bulk_emails(csv_path, name_field, email_field, subject, email_body):
    """Send emails to all recipients in background"""
    global email_progress
    
    try:
        # Read CSV data
        df = pd.read_csv(csv_path)
        
        # Initialize progress
        email_progress = {
            'total': len(df),
            'sent': 0,
            'failed': 0,
            'current': '',
            'status': 'sending',
            'errors': []
        }
        
        # Connect to SMTP server
        server = smtplib.SMTP(EMAIL_CONFIG['smtp_server'], EMAIL_CONFIG['smtp_port'])
        if EMAIL_CONFIG['use_tls']:
            server.starttls()
        server.login(EMAIL_CONFIG['email'], EMAIL_CONFIG['password'])
        
        for index, row in df.iterrows():
            try:
                # Skip rows without name or email
                if pd.isna(row[name_field]) or pd.isna(row[email_field]):
                    continue
                
                name = str(row[name_field]).strip()
                email = str(row[email_field]).strip()
                
                if not name or not email:
                    continue
                
                email_progress['current'] = f"{name} ({email})"
                
                # Find certificate file
                safe_name = secure_filename(name.replace(' ', '_'))
                cert_filename = f"{safe_name}_certificate.pdf"
                cert_path = os.path.join(app.config['GENERATED_FOLDER'], cert_filename)
                
                if not os.path.exists(cert_path):
                    email_progress['errors'].append(f"Certificate not found for {name}")
                    email_progress['failed'] += 1
                    continue
                
                # Create email
                msg = MIMEMultipart()
                msg['From'] = EMAIL_CONFIG['email']
                msg['To'] = email
                msg['Subject'] = subject
                
                # Personalize email body
                personalized_body = email_body.replace('[NAME]', name)
                personalized_body = personalized_body.replace('[CERTIFICATE]', cert_filename)
                
                msg.attach(MIMEText(personalized_body, 'plain'))
                
                # Attach certificate PDF
                with open(cert_path, "rb") as attachment:
                    part = MIMEBase('application', 'octet-stream')
                    part.set_payload(attachment.read())
                
                encoders.encode_base64(part)
                part.add_header(
                    'Content-Disposition',
                    f'attachment; filename= {cert_filename}'
                )
                msg.attach(part)
                
                # Send email
                server.send_message(msg)
                email_progress['sent'] += 1
                
                # Small delay to avoid overwhelming the server
                time.sleep(0.5)
                
            except Exception as e:
                email_progress['errors'].append(f"Failed to send to {name} ({email}): {str(e)}")
                email_progress['failed'] += 1
                continue
        
        server.quit()
        email_progress['status'] = 'completed'
        
    except Exception as e:
        email_progress['status'] = 'error'
        email_progress['errors'].append(f"Email server error: {str(e)}")

@app.route('/email-status')
def email_status():
    """Show email sending progress"""
    return render_template('email_status.html', progress=email_progress)

@app.route('/email-progress-api')
def email_progress_api():
    """API endpoint for email progress (for AJAX updates)"""
    return jsonify(email_progress)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5001)