import os
import re
import mysql.connector
import uuid

def scrape_products_and_update_database():
    # 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))
    }
    
    # Base directories
    success_directory = "/var/www/html/success_pages"
    products_directory = "/var/www/html/products"
    
    # Ensure products directory exists
    if not os.path.exists(products_directory):
        os.makedirs(products_directory)
        print(f"Created base products directory: {products_directory}")
    
    # Connect to database
    try:
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        print("Connected to database successfully")
        
        # 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")
        
        for campaign_code in campaign_dirs:
            campaign_success_dir = os.path.join(success_directory, campaign_code)
            campaign_products_dir = os.path.join(products_directory, campaign_code)
            
            # Create campaign directory in products if needed
            if not os.path.exists(campaign_products_dir):
                os.makedirs(campaign_products_dir)
                print(f"Created directory for campaign: {campaign_code}")
            
            # 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}")
            
            # Get influencer_id and brand_id for this campaign
            cursor.execute("""
                SELECT influencer_id, brand_id FROM tbl_campaigns WHERE code = %s
            """, (campaign_code,))
            campaign_data = cursor.fetchone()
            
            if not campaign_data:
                print(f"Campaign {campaign_code} not found in tbl_campaigns, skipping")
                continue
                
            influencer_id = campaign_data['influencer_id']
            brand_id = campaign_data['brand_id']
            
            for file_name in html_files:
                file_path = os.path.join(campaign_success_dir, file_name)
                order_uuid = file_name.replace('.html', '')
                products_file_path = os.path.join(campaign_products_dir, file_name)
                
                try:
                    # Get timestamp from tbl_tracker
                    cursor.execute("""
                        SELECT processed_at FROM tbl_tracker WHERE uuid = %s
                    """, (order_uuid,))
                    tracker_data = cursor.fetchone()
                    
                    if not tracker_data:
                        print(f"UUID {order_uuid} not found in tbl_tracker, skipping")
                        continue
                        
                    time_payment = tracker_data['processed_at']
                    
                    # Read the HTML file
                    with open(file_path, 'r', encoding='utf-8', errors='replace') as file:
                        html_content = file.read()
                    
                    # Look for product names in woocommerce-table__product-name cells
                    product_pattern = r'<td class="[^"]*woocommerce-table__product-name[^"]*">(.*?)</td>'
                    product_cells = re.findall(product_pattern, html_content, re.DOTALL)
                    
                    with open(products_file_path, 'w', encoding='utf-8') as product_file:
                        product_file.write(f"Products for Order ID: {order_uuid}\n\n")
                        
                        if product_cells:
                            for cell in product_cells:
                                # Extract product name (content of the first link)
                                product_link_pattern = r'<a[^>]*>([^<]+)</a>'
                                product_match = re.search(product_link_pattern, cell)
                                
                                if product_match:
                                    product_name = product_match.group(1).strip()
                                    product_file.write(f"{product_name}\n")
                                    
                                    # Insert product into database
                                    new_uuid = str(uuid.uuid4())
                                    cursor.execute("""
                                        INSERT INTO tbl_sales_products 
                                        (uuid, campaign_code, product_name, time_payment, influencer_id, brand_id)
                                        VALUES (%s, %s, %s, %s, %s, %s)
                                    """, (new_uuid, campaign_code, product_name, time_payment, influencer_id, brand_id))
                                    print(f"Added product '{product_name}' to database")
                                else:
                                    # Fallback - just clean the HTML tags if no link found
                                    cleaned_text = re.sub(r'<[^>]+>', ' ', cell)
                                    cleaned_text = re.sub(r'\s+', ' ', cleaned_text).strip()
                                    product_file.write(f"{cleaned_text}\n")
                                    
                                    # Insert product into database
                                    new_uuid = str(uuid.uuid4())
                                    cursor.execute("""
                                        INSERT INTO tbl_sales_products 
                                        (uuid, campaign_code, product_name, time_payment, influencer_id, brand_id)
                                        VALUES (%s, %s, %s, %s, %s, %s)
                                    """, (new_uuid, campaign_code, cleaned_text, time_payment, influencer_id, brand_id))
                                    print(f"Added product '{cleaned_text}' to database")
                        else:
                            product_file.write("No product information found in this page.\n")
                    
                    print(f"Processed file: {file_name}")
                
                except Exception as e:
                    print(f"Error processing file {file_name}: {e}")
            
        # Commit all 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")
    
    print("Product scraping and database update completed")

if __name__ == "__main__":
    scrape_products_and_update_database()