import os
import re
import mysql.connector

def process_success_pages_and_update_totals():
    # Database configuration from environment variables
    db_config = {
        'user': os.getenv('DB_USER'),
        'password': os.getenv('DB_PASSWORD'),
        'host': os.getenv('DB_HOST'),
        'database': os.getenv('DB_NAME'),
        'port': int(os.getenv('DB_PORT', 3306))
    }
    
    # Set up directories
    success_directory = "/var/www/html/success_pages"
    totals_directory = "/var/www/html/total_paid"

    # Ensure totals directory exists
    if not os.path.exists(totals_directory):
        os.makedirs(totals_directory)

    # Regex pattern to find numbers associated with "Total"
    total_pattern = r'Total[^0-9]*([0-9]+(?:\.[0-9]{2})?)'
    
    # Track campaign totals
    campaign_totals = {}

    try:
        print("Starting to process success pages...")
        
        # Get all campaign directories in success_pages
        campaign_dirs = [d for d in os.listdir(success_directory) 
                       if os.path.isdir(os.path.join(success_directory, d))]
        
        print(f"Found {len(campaign_dirs)} campaign directories to process")

        # Process each campaign directory
        for campaign_code in campaign_dirs:
            campaign_success_dir = os.path.join(success_directory, campaign_code)
            campaign_totals_dir = os.path.join(totals_directory, campaign_code)
            
            # Create campaign directory in totals if it doesn't exist
            if not os.path.exists(campaign_totals_dir):
                os.makedirs(campaign_totals_dir)
                
            # Process HTML files in this campaign's success directory
            html_files = [f for f in os.listdir(campaign_success_dir) if f.endswith(".html")]
            print(f"Processing {len(html_files)} success pages for campaign {campaign_code}")
            
            for file_name in html_files:
                file_path = os.path.join(campaign_success_dir, file_name)
                uuid = file_name.replace('.html', '')
                
                try:
                    # Read the file content
                    with open(file_path, 'r', encoding='utf-8', errors='replace') as file:
                        content = file.read()
                    
                    # Extract totals
                    matches = re.findall(total_pattern, content, re.IGNORECASE)
                    print(f"Found {len(matches)} totals in {file_name}: {matches}")
                    
                    if matches:
                        # Write totals to file in campaign totals directory
                        totals_file_path = os.path.join(campaign_totals_dir, file_name)
                        with open(totals_file_path, 'w', encoding='utf-8') as totals_file:
                            for match in matches:
                                totals_file.write(f"{match}\n")
                except Exception as e:
                    print(f"Error processing file {file_name}: {e}")
        
        # Connect to database to update totals
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        print("Connected to database successfully")
        
        # Process each campaign's totals directory
        for campaign_code in os.listdir(totals_directory):
            campaign_totals_dir = os.path.join(totals_directory, campaign_code)
            
            if not os.path.isdir(campaign_totals_dir):
                continue
                
            print(f"Calculating totals for campaign {campaign_code}")
            campaign_total = 0
            
            # Process each total file
            for file_name in os.listdir(campaign_totals_dir):
                if file_name.endswith(".html"):
                    file_path = os.path.join(campaign_totals_dir, file_name)
                    uuid = file_name.replace('.html', '')
                    
                    with open(file_path, 'r', encoding='utf-8') as file:
                        # Extract decimal numbers
                        totals = [float(line.strip()) for line in file.readlines() 
                                 if re.match(r'\d+\.\d{2}', line.strip())]
                    
                    if totals:
                        highest_total = max(totals)
                        print(f"Highest total for {uuid}: {highest_total}")
                        campaign_total += highest_total
            
            # Update tbl_campaigns with the total
            if campaign_total > 0:
                cursor.execute("""
                UPDATE tbl_campaigns SET sales_total_paid = %s WHERE code = %s
                """, (campaign_total, campaign_code))
                print(f"Updated campaign {campaign_code} with total paid: {campaign_total}")
        
        # Commit changes
        conn.commit()
        print("All database updates committed successfully")
        
    except mysql.connector.Error as err:
        print(f"Database error: {err}")
        if 'conn' in locals() and conn:
            conn.rollback()
    except Exception as e:
        print(f"Error: {e}")
    finally:
        if 'conn' in locals() and conn and conn.is_connected():
            cursor.close()
            conn.close()
            print("Database connection closed")

if __name__ == "__main__":
    process_success_pages_and_update_totals()