import os
import sys
import json
import uuid
import threading
from concurrent.futures import ThreadPoolExecutor, as_completed
from flask import Flask, request, jsonify, send_file
from flask_cors import CORS
import fitz  # PyMuPDF
from PIL import Image
import io
import tempfile
import shutil
from datetime import datetime
import traceback
import re
import mysql.connector
from mysql.connector import Error
import time
import atexit
import signal

app = Flask(__name__)
CORS(app)

# REMOVE ALL UPLOAD LIMITS
app.config['MAX_CONTENT_LENGTH'] = None  # No limit

class DatabaseManager:
    def __init__(self):
        self.config = {
            'host': 'localhost',
            'user': 'root',
            'password': '',
            'database': 'go_productions'
        }
    
    def get_connection(self):
        try:
            conn = mysql.connector.connect(**self.config)
            return conn
        except Error as e:
            print(f"❌ Database connection error: {e}")
            return None

    def save_to_database(self, table, data, update=False):
        """Generic method to save data to database"""
        conn = self.get_connection()
        if not conn:
            print("❌ Database connection failed")
            return False
            
        try:
            cursor = conn.cursor()
            
            if update and 'id' in data:
                # Update existing record
                set_clause = ', '.join([f"{key} = %s" for key in data.keys() if key != 'id'])
                values = [value for key, value in data.items() if key != 'id']
                values.append(data['id'])
                sql = f"UPDATE {table} SET {set_clause} WHERE id = %s"
            else:
                # Insert new record
                placeholders = ', '.join(['%s'] * len(data))
                columns = ', '.join(data.keys())
                sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
                values = list(data.values())
            
            print(f"🗄️ Executing SQL: {sql}")
            print(f"🗄️ Values: {values}")
            cursor.execute(sql, values)
            conn.commit()
            
            # Get the last inserted ID - FIXED THIS PART
            if not update:
                # For projects table, we're using custom ID, so return True for success
                if table == 'projects':
                    print(f"✅ Project created with custom ID: {data.get('id', 'unknown')}")
                    return True
                else:
                    # For other tables with auto-increment, get the last ID
                    cursor.execute("SELECT LAST_INSERT_ID()")
                    last_id = cursor.fetchone()[0]
                    print(f"✅ Inserted record with ID: {last_id}")
                    return last_id
            else:
                print(f"✅ Updated {cursor.rowcount} rows")
                return True
            
        except Error as e:
            print(f"❌ Database error: {e}")
            print(f"❌ Full error: {traceback.format_exc()}")
            return False
        finally:
            if conn:
                cursor.close()
                conn.close()
class PDFProcessor:
    def __init__(self, max_workers=5):
        self.supported_formats = ['.pdf']
        self.temp_base = "temp_pdf_processing"
        self.max_workers = max_workers
        self.executor = ThreadPoolExecutor(max_workers=max_workers)
        self.db = DatabaseManager()
        
    def create_project_structure(self, project_id, project_name):
        """Create folder structure for project"""
        project_folder = os.path.join("projects", project_id)
        pdfs_folder = os.path.join(project_folder, "pdfs")
        images_folder = os.path.join(project_folder, "images")
        additional_folder = os.path.join(project_folder, "additional")
        model_images_folder = os.path.join(project_folder, "model_images")
        
        for folder in [project_folder, pdfs_folder, images_folder, additional_folder, model_images_folder]:
            os.makedirs(folder, exist_ok=True)
            
        return {
            'project_folder': project_folder,
            'pdfs_folder': pdfs_folder,
            'images_folder': images_folder,
            'additional_folder': additional_folder,
            'model_images_folder': model_images_folder
        }
    
    def update_processing_status(self, project_id, status):
        """Update project processing status"""
        conn = self.db.get_connection()
        if not conn:
            return False
            
        try:
            cursor = conn.cursor()
            sql = "UPDATE projects SET status = %s WHERE id = %s"
            cursor.execute(sql, (status, project_id))
            conn.commit()
            return True
        except Error as e:
            print(f"❌ Database error: {e}")
            return False
        finally:
            cursor.close()
            conn.close()
    
    def clean_filename(self, filename):
        """Clean filename to create proper model name"""
        # Remove file extension
        name = os.path.splitext(filename)[0]
        # Remove UUID prefixes and special characters
        name = re.sub(r'^temp_[a-f0-9]+_', '', name)
        name = re.sub(r'^[a-f0-9]+_', '', name)
        # Replace underscores and hyphens with spaces
        name = name.replace('_', ' ').replace('-', ' ')
        # Title case and clean up
        name = ' '.join(word.capitalize() for word in name.split())
        return name
    
    def extract_model_info(self, text, pdf_filename):
        """Enhanced model information extraction with better Instagram detection"""
        # Use cleaned PDF filename as model name
        model_name = self.clean_filename(pdf_filename)
        
        model_info = {
            'model_name': model_name,
            'model_height': '',
            'model_weight': '',
            'model_waist': '',
            'instagram_handle': '',
            'instagram_url': '',
            'contact_info': '',
            'specialties': [],
            'extracted_details': ''
        }
        
        text_lower = (text or "").lower()
        lines = (text or "").split('\n')
        
        # Store all extracted text
        model_info['extracted_details'] = text.strip() if text else ''
        
        # ENHANCED INSTAGRAM EXTRACTION - Multiple patterns
        insta_patterns = [
            r'https?://(?:www\.)?instagram\.com/([a-zA-Z0-9._]+)/?',
            r'www\.instagram\.com/([a-zA-Z0-9._]+)/?',
            r'instagram\.com/([a-zA-Z0-9._]+)/?',
            r'instagram[:\s]*@?([a-zA-Z0-9._]+)',
            r'ig[:\s]*@?([a-zA-Z0-9._]+)',
            r'insta[:\s]*@?([a-zA-Z0-9._]+)',
            r'@([a-zA-Z0-9._]{2,30})',
            r'\b([a-zA-Z0-9._]{3,30})\b(?=\s*(?:on instagram|on ig|instagram))',
        ]
        
        instagram_handle = ''
        instagram_url = ''
        
        for pattern in insta_patterns:
            matches = re.findall(pattern, text, re.IGNORECASE)
            if matches:
                handle = matches[0].strip()
                
                # If it's a full URL, extract handle
                if 'instagram.com' in handle.lower():
                    url_match = re.search(r'instagram\.com/([a-zA-Z0-9._]+)', handle, re.IGNORECASE)
                    if url_match:
                        handle = url_match.group(1)
                
                # Remove @ if present for URL generation
                clean_handle = handle.lstrip('@')
                
                # Basic validation: reasonable length, no spaces, not a domain
                if (3 <= len(clean_handle) <= 30 and
                    ' ' not in clean_handle and
                    '/' not in clean_handle and
                    not clean_handle.lower().endswith(('.com', '.net', '.org'))):
                    
                    instagram_handle = f"@{clean_handle}"
                    instagram_url = f"https://www.instagram.com/{clean_handle}/"
                    break
        
        model_info['instagram_handle'] = instagram_handle
        model_info['instagram_url'] = instagram_url
        
        # Extract contact information
        email_pattern = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b'
        phone_pattern = r'(\+?\d{1,3}[-.\s]?)?\(?\d{2,4}\)?[-.\s]?\d{3,4}[-.\s]?\d{3,4}'
        
        emails = re.findall(email_pattern, text)
        if emails:
            model_info['contact_info'] = emails[0]
        else:
            phones = re.findall(phone_pattern, text)
            if phones:
                phone = phones[0]
                if isinstance(phone, tuple):
                    phone = ''.join(phone)
                model_info['contact_info'] = phone.strip()
        
        print(f"🔍 Final extracted model info: {model_info}")
        return model_info
    
    def extract_content(self, pdf_path, project_folders, file_index=0, total_files=1, project_id=None):
        """Extract text, images, and model information from PDF"""
        pdf_document = None
        try:
            print(f"🔄 Processing PDF {file_index + 1}/{total_files}: {os.path.basename(pdf_path)}")
            
            pdf_document = fitz.open(pdf_path)
            original_filename = os.path.basename(pdf_path)
            
            # Save PDF to project folder first
            pdf_filename = f"{uuid.uuid4().hex}_{original_filename}"
            pdf_save_path = os.path.join(project_folders['pdfs_folder'], pdf_filename)
            shutil.copy2(pdf_path, pdf_save_path)
            
            print(f"💾 Saved PDF to: {pdf_save_path}")
            
            # Save PDF record to database FIRST to get the ID
            pdf_data = {
                'project_id': project_id,
                'original_name': original_filename,
                'file_path': pdf_save_path,
                'file_size': os.path.getsize(pdf_path),
                'page_count': len(pdf_document),
                'processing_status': 'processing'
            }
            
            pdf_id = self.db.save_to_database('project_pdfs', pdf_data)
            
            if not pdf_id:
                raise Exception("Failed to save PDF to database")
            
            print(f"🗄️ Saved PDF record with ID: {pdf_id}")
            
            extraction_result = {
                'text_content': [],
                'images': [],
                'metadata': {},
                'model_info': {},
                'file_name': original_filename,
                'file_index': file_index,
                'total_files': total_files,
                'pdf_id': pdf_id
            }
            
            # Extract metadata
            metadata = pdf_document.metadata
            extraction_result['metadata'] = {
                'title': metadata.get('title', ''),
                'author': metadata.get('author', ''),
                'subject': metadata.get('subject', ''),
                'pages': len(pdf_document),
                'file_size': os.path.getsize(pdf_path),
            }
            
            all_text = ""
            total_images = 0
            
            # Process each page
            for page_num in range(len(pdf_document)):
                page = pdf_document[page_num]
                
                # Extract text
                text = page.get_text()
                all_text += text + "\n"
                
                # Extract images
                image_list = page.get_images(full=True)
                print(f"🖼️ Found {len(image_list)} images on page {page_num + 1}")
                
                for img_index, img in enumerate(image_list):
                    try:
                        # Extract image
                        xref = img[0]
                        base_image = pdf_document.extract_image(xref)
                        image_bytes = base_image["image"]
                        image_ext = base_image["ext"]
                        
                        # Convert to PIL Image
                        pil_image = Image.open(io.BytesIO(image_bytes))
                        
                        # Convert to WebP
                        image_filename = f"{uuid.uuid4().hex}.webp"
                        image_save_path = os.path.join(project_folders['images_folder'], image_filename)
                        
                        # Save as WebP
                        pil_image.save(image_save_path, 'WEBP', quality=85, optimize=True)
                        
                        # Save image record to database with correct PDF ID
                        image_data = {
                            'pdf_id': pdf_id,
                            'project_id': project_id,
                            'image_name': image_filename,
                            'image_path': image_save_path,
                            'image_size': os.path.getsize(image_save_path),
                            'width': pil_image.width,
                            'height': pil_image.height,
                            'page_number': page_num + 1
                        }
                        
                        image_id = self.db.save_to_database('extracted_images', image_data)
                        
                        if image_id:
                            image_info = {
                                'page': page_num + 1,
                                'image_index': img_index + 1,
                                'filename': image_filename,
                                'filepath': image_save_path,
                                'width': pil_image.width,
                                'height': pil_image.height,
                            }
                            
                            extraction_result['images'].append(image_info)
                            total_images += 1
                            print(f"✅ Extracted image: {image_filename}")
                        else:
                            print(f"❌ Failed to save image record")
                        
                    except Exception as img_error:
                        print(f"❌ Error processing image: {img_error}")
                        continue
            
            # Extract model information with cleaned PDF filename as model name
            print(f"🔍 Extracting model information from text")
            model_info = self.extract_model_info(all_text, original_filename)
            extraction_result['model_info'] = model_info
            
            # Save extracted data to database with correct PDF ID
            extracted_data = {
                'pdf_id': pdf_id,
                'project_id': project_id,
                'model_name': model_info['model_name'],
                'model_height': model_info['model_height'],
                'model_weight': model_info['model_weight'],
                'model_waist': model_info['model_waist'],
                'instagram_handle': model_info['instagram_handle'],
                'instagram_url': model_info['instagram_url'],
                'contact_info': model_info['contact_info'],
                'specialties': json.dumps(model_info['specialties']),
                'extracted_text': all_text,
                'extracted_details': model_info['extracted_details']
            }
            
            data_id = self.db.save_to_database('extracted_data', extracted_data)
            
            if data_id:
                print(f"✅ Saved extracted data with ID: {data_id}")
            else:
                print(f"❌ Failed to save extracted data")
            
            # Update PDF status to completed
            self.db.save_to_database('project_pdfs', {
                'id': pdf_id,
                'processing_status': 'completed'
            }, update=True)
            
            print(f"✅ Completed PDF: {original_filename} - {total_images} images extracted")
            return extraction_result
            
        except Exception as e:
            error_msg = f"❌ PDF processing error: {str(e)}"
            print(error_msg)
            print(traceback.format_exc())
            
            # Update PDF status to failed
            if 'pdf_id' in locals():
                self.db.save_to_database('project_pdfs', {
                    'id': pdf_id,
                    'processing_status': 'failed'
                }, update=True)
            
            raise Exception(error_msg)
        finally:
            if pdf_document:
                pdf_document.close()
    
    def process_single_pdf(self, file_data, project_folders, file_index, total_files, project_id):
        """Process a single PDF file"""
        try:
            print(f"🔍 Processing: {file_data.filename}")
            
            if not file_data or file_data.filename == '':
                return {'error': 'Invalid file data', 'file_name': 'unknown'}
            
            # Save file temporarily
            temp_filename = f"temp_{uuid.uuid4().hex}_{file_data.filename}"
            temp_pdf_path = os.path.join(project_folders['pdfs_folder'], temp_filename)
            file_data.save(temp_pdf_path)
            
            if not os.path.exists(temp_pdf_path):
                return {'error': 'Failed to save file', 'file_name': file_data.filename}
            
            # Process PDF
            result = self.extract_content(temp_pdf_path, project_folders, file_index, total_files, project_id)
            
            # Cleanup
            if os.path.exists(temp_pdf_path):
                os.remove(temp_pdf_path)
                
            return result
            
        except Exception as e:
            error_msg = f"Error processing {file_data.filename}: {str(e)}"
            print(f"❌ {error_msg}")
            return {'error': error_msg, 'file_name': file_data.filename}
    
    def process_project_pdfs(self, files, project_id, project_name):
        """Process multiple PDFs for a project"""
        total_files = len(files)
        results = []
        errors = []
        
        print(f"🚀 Processing {total_files} PDFs for project {project_name}")
        
        # Create project structure
        project_folders = self.create_project_structure(project_id, project_name)
        self.update_processing_status(project_id, 'processing')
        
        # Process files
        future_to_index = {}
        for i, file_data in enumerate(files):
            if file_data and file_data.filename:
                future = self.executor.submit(
                    self.process_single_pdf, 
                    file_data, project_folders, i, total_files, project_id
                )
                future_to_index[future] = i
        
        # Collect results
        for future in as_completed(future_to_index):
            i = future_to_index[future]
            try:
                result = future.result()
                if 'error' in result:
                    errors.append({
                        'file_index': i,
                        'file_name': files[i].filename,
                        'error': result['error']
                    })
                    print(f"❌ Failed: {files[i].filename}")
                else:
                    results.append(result)
                    print(f"✅ Success: {files[i].filename}")
            except Exception as e:
                errors.append({
                    'file_index': i,
                    'file_name': files[i].filename,
                    'error': str(e)
                })
                print(f"❌ Exception: {files[i].filename}")
        
        # Update project status
        status = 'completed' if results else 'failed'
        self.update_processing_status(project_id, status)
        
        return {
            'success': len(results),
            'failed': len(errors),
            'total': total_files,
            'results': results,
            'errors': errors,
            'project_id': project_id,
            'project_name': project_name,
            'processing_time': datetime.now().isoformat()
        }

# Initialize processor
processor = PDFProcessor(max_workers=5)

# Graceful shutdown
def graceful_shutdown():
    print("🛑 Shutting down...")
    processor.executor.shutdown(wait=True)

atexit.register(graceful_shutdown)

@app.route('/health', methods=['GET'])
def health_check():
    return jsonify({'status': 'healthy', 'timestamp': datetime.now().isoformat()})

@app.route('/create-project', methods=['POST'])
def create_project():
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No JSON data received'}), 400
            
        project_name = data.get('project_name')
        project_description = data.get('project_description', '')
        
        if not project_name:
            return jsonify({'error': 'Project name required'}), 400
        
        project_id = f"proj_{int(time.time())}_{uuid.uuid4().hex[:8]}"
        
        print(f"🆕 Creating project: {project_name} with ID: {project_id}")
        
        db = DatabaseManager()
        project_data = {
            'id': project_id,
            'name': project_name,
            'description': project_description,
            'status': 'created'
        }
        
        success = db.save_to_database('projects', project_data)
        
        if not success:
            return jsonify({'error': 'Failed to save project to database'}), 500
        
        processor.create_project_structure(project_id, project_name)
        
        return jsonify({
            'success': True,
            'project_id': project_id,
            'project_name': project_name
        })
        
    except Exception as e:
        print(f"💥 Create project error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/process-project-pdfs/<project_id>', methods=['POST'])
def process_project_pdfs(project_id):
    try:
        print(f"📨 Processing PDFs for project {project_id}")
        
        # Get project info
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM projects WHERE id = %s", (project_id,))
        project = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if not project:
            return jsonify({'error': 'Project not found'}), 404
        
        # Get files
        files = []
        if 'pdf_files' in request.files:
            files.extend(request.files.getlist('pdf_files'))
        
        file_keys = [key for key in request.files.keys() if key.startswith('pdf_files')]
        for key in file_keys:
            if key != 'pdf_files':
                files.extend(request.files.getlist(key))
        
        pdf_files = [f for f in files if f and f.filename and f.filename.lower().endswith('.pdf')]
        
        if not pdf_files:
            return jsonify({'error': 'No PDF files found'}), 400
        
        print(f"📄 Processing {len(pdf_files)} PDFs")
        
        # Process PDFs
        batch_result = processor.process_project_pdfs(pdf_files, project_id, project['name'])
        
        return jsonify({
            'success': True,
            'batch_result': batch_result
        })
        
    except Exception as e:
        print(f"💥 Error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/project-data/<project_id>', methods=['GET'])
def get_project_data(project_id):
    try:
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Get PDFs with their data
        cursor.execute("""
            SELECT 
                pp.*,
                ed.id as data_id,
                ed.model_name,
                ed.model_height,
                ed.model_weight,
                ed.model_waist,
                ed.instagram_handle,
                ed.instagram_url,
                ed.contact_info,
                ed.specialties,
                ed.extracted_text,
                ed.extracted_details
            FROM project_pdfs pp
            LEFT JOIN extracted_data ed ON pp.id = ed.pdf_id
            WHERE pp.project_id = %s
            ORDER BY pp.created_at DESC
        """, (project_id,))
        pdfs = cursor.fetchall()
        
        # Get images for each PDF
        for pdf in pdfs:
            cursor.execute("SELECT * FROM extracted_images WHERE pdf_id = %s ORDER BY page_number, id", (pdf['id'],))
            pdf['images'] = cursor.fetchall()
            pdf['image_count'] = len(pdf['images'])
        
        # Get additional photos
        cursor.execute("SELECT * FROM additional_photos WHERE project_id = %s ORDER BY uploaded_at DESC", (project_id,))
        additional_photos = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'success': True,
            'pdfs': pdfs,
            'additional_photos': additional_photos
        })
        
    except Exception as e:
        print(f"💥 Get project data error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/upload-additional-photo/<project_id>', methods=['POST'])
def upload_additional_photo(project_id):
    try:
        if 'photo' not in request.files:
            return jsonify({'error': 'No photo file received'}), 400
        
        photo = request.files['photo']
        if not photo or photo.filename == '':
            return jsonify({'error': 'No file selected'}), 400
        
        project_folders = processor.create_project_structure(project_id, "")
        photo_filename = f"additional_{uuid.uuid4().hex}.webp"
        photo_path = os.path.join(project_folders['additional_folder'], photo_filename)
        
        pil_image = Image.open(photo)
        pil_image.save(photo_path, 'WEBP', quality=85)
        
        db = DatabaseManager()
        success = db.save_to_database('additional_photos', {
            'project_id': project_id,
            'image_name': photo_filename,
            'image_path': photo_path,
            'image_size': os.path.getsize(photo_path)
        })
        
        if not success:
            return jsonify({'error': 'Failed to save photo to database'}), 500
        
        return jsonify({'success': True, 'message': 'Photo uploaded successfully'})
        
    except Exception as e:
        print(f"💥 Upload additional photo error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/upload-model-image/<pdf_id>', methods=['POST'])
def upload_model_image(pdf_id):
    try:
        if 'model_image' not in request.files:
            return jsonify({'error': 'No image file received'}), 400
        
        image_file = request.files['model_image']
        if not image_file or image_file.filename == '':
            return jsonify({'error': 'No file selected'}), 400
        
        # Get project_id from pdf_id
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT project_id FROM project_pdfs WHERE id = %s", (pdf_id,))
        pdf_record = cursor.fetchone()
        cursor.close()
        conn.close()
        
        if not pdf_record:
            return jsonify({'error': 'PDF not found'}), 404
        
        project_id = pdf_record['project_id']
        project_folders = processor.create_project_structure(project_id, "")
        
        # Save image
        image_filename = f"model_{uuid.uuid4().hex}.webp"
        image_path = os.path.join(project_folders['model_images_folder'], image_filename)
        
        pil_image = Image.open(image_file)
        pil_image.save(image_path, 'WEBP', quality=85)
        
        # Save to extracted_images table with pdf_id
        success = db.save_to_database('extracted_images', {
            'pdf_id': pdf_id,
            'project_id': project_id,
            'image_name': image_filename,
            'image_path': image_path,
            'image_size': os.path.getsize(image_path),
            'width': pil_image.width,
            'height': pil_image.height,
            'page_number': 0  # 0 indicates manually added image
        })
        
        if not success:
            return jsonify({'error': 'Failed to save image to database'}), 500
        
        return jsonify({'success': True, 'message': 'Model image uploaded successfully'})
        
    except Exception as e:
        print(f"💥 Upload model image error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/update-model-info/<pdf_id>', methods=['PUT'])
def update_model_info(pdf_id):
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No JSON data received'}), 400
        
        print(f"🔄 Updating model info for PDF ID: {pdf_id}")
        print(f"📝 Data: {data}")
        
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Check if record exists
        cursor.execute("SELECT id FROM extracted_data WHERE pdf_id = %s", (pdf_id,))
        existing_record = cursor.fetchone()
        cursor.close()
        conn.close()
        
        update_data = {
            'model_name': data.get('model_name', ''),
            'model_height': data.get('model_height', ''),
            'model_weight': data.get('model_weight', ''),
            'model_waist': data.get('model_waist', ''),
            'instagram_handle': data.get('instagram_handle', ''),
            'instagram_url': data.get('instagram_url', ''),
            'contact_info': data.get('contact_info', ''),
            'specialties': json.dumps(data.get('specialties', [])),
            'extracted_details': data.get('extracted_details', '')
        }
        
        if existing_record:
            # Update existing record
            update_data['id'] = existing_record['id']
            success = db.save_to_database('extracted_data', update_data, update=True)
        else:
            # Create new record
            update_data['pdf_id'] = pdf_id
            update_data['project_id'] = data.get('project_id')
            success = db.save_to_database('extracted_data', update_data)
        
        if not success:
            return jsonify({'error': 'Database update failed'}), 500
        
        return jsonify({'success': True, 'message': 'Model information updated successfully'})
        
    except Exception as e:
        print(f"💥 Update model info error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/delete-image/<image_id>', methods=['DELETE'])
def delete_image(image_id):
    try:
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Get image info
        cursor.execute("SELECT * FROM extracted_images WHERE id = %s", (image_id,))
        image = cursor.fetchone()
        
        if not image:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Image not found'}), 404
        
        # Delete from database
        cursor.execute("DELETE FROM extracted_images WHERE id = %s", (image_id,))
        conn.commit()
        cursor.close()
        conn.close()
        
        # Delete physical file
        if os.path.exists(image['image_path']):
            os.remove(image['image_path'])
        
        return jsonify({'success': True, 'message': 'Image deleted successfully'})
        
    except Exception as e:
        print(f"💥 Delete image error: {str(e)}")
        return jsonify({'error': str(e)}), 500
    





@app.route('/test-pdf', methods=['GET'])
def test_pdf():
    """Test endpoint to verify PDF generation works"""
    try:
        from reportlab.lib.pagesizes import A4
        from reportlab.pdfgen import canvas
        import io
        
        # Create simple test PDF
        buffer = io.BytesIO()
        c = canvas.Canvas(buffer, pagesize=A4)
        
        # Add content
        c.setFont("Helvetica-Bold", 20)
        c.drawString(100, 700, "GO PRODUCTIONS - TEST PDF")
        c.setFont("Helvetica", 12)
        c.drawString(100, 650, "This is a test PDF generated by the system")
        c.drawString(100, 630, f"Generated at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        c.drawString(100, 610, "If you can see this, PDF generation is working!")
        
        c.save()
        
        pdf_content = buffer.getvalue()
        buffer.close()
        
        from flask import Response
        return Response(
            pdf_content,
            mimetype='application/pdf',
            headers={'Content-Disposition': 'attachment; filename=test.pdf'}
        )
        
    except Exception as e:
        return jsonify({'error': f'Test PDF failed: {str(e)}'}), 500



    
        
@app.route('/generate-portfolio-pdf', methods=['POST'])

def generate_portfolio_pdf():
    try:
        print("🔄 Starting PDF generation...")
        
        # Import inside function to avoid issues
        from reportlab.lib.pagesizes import letter, A4
        from reportlab.pdfgen import canvas
        from reportlab.lib.utils import ImageReader
        from reportlab.lib.units import inch
        from reportlab.lib.colors import black, blue
        from reportlab.pdfbase import pdfmetrics
        from reportlab.pdfbase.ttfonts import TTFont
        import io
        import os
        
        # Get request data
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No JSON data received'}), 400
        
        images_data = data.get('images_data', [])
        project_id = data.get('project_id', 'unknown')
        company_name = data.get('company_name', 'GO Productions')
        
        print(f"📄 PDF Request - Images: {len(images_data)}, Project: {project_id}")
        
        if not images_data:
            return jsonify({'error': 'No images data provided'}), 400
        
        # Create PDF buffer
        buffer = io.BytesIO()
        
        # Create canvas with larger page size
        c = canvas.Canvas(buffer, pagesize=A4)
        width, height = A4
        
        # Set initial position
        y_position = height - 1 * inch
        page_number = 1
        
        print("📝 Adding content to PDF...")
        
        # Add header to first page
        c.setFont("Helvetica-Bold", 20)
        c.drawString(1 * inch, y_position, "GO PRODUCTIONS")
        y_position -= 0.5 * inch
        
        c.setFont("Helvetica-Bold", 16)
        c.drawString(1 * inch, y_position, "MODEL PORTFOLIO")
        y_position -= 0.3 * inch
        
        c.setFont("Helvetica", 12)
        c.drawString(1 * inch, y_position, f"Project: {project_id}")
        y_position -= 0.3 * inch
        c.drawString(1 * inch, y_position, f"Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
        y_position -= 0.3 * inch
        c.drawString(1 * inch, y_position, f"Total Images: {len(images_data)}")
        y_position -= 0.8 * inch
        
        # Process each image
        for i, img_data in enumerate(images_data):
            print(f"🖼️ Processing image {i+1}/{len(images_data)}: {img_data.get('image_name', 'unknown')}")
            
            # Check if we need a new page
            if y_position < 3 * inch:
                print("📄 Adding new page...")
                c.showPage()
                page_number += 1
                y_position = height - 1 * inch
                
                # Add footer to each page
                c.setFont("Helvetica-Bold", 10)
                c.drawString(1 * inch, 0.5 * inch, f"GO Productions - Page {page_number}")
            
            # Add model information
            model_name = img_data.get('model_name', 'Unknown Model')
            instagram = img_data.get('instagram', '')
            instagram_url = img_data.get('instagram_url', '')
            details = img_data.get('details', '')[:200] + "..." if len(img_data.get('details', '')) > 200 else img_data.get('details', '')
            
            # Model name
            c.setFont("Helvetica-Bold", 14)
            c.drawString(1 * inch, y_position, f"Image {i+1}: {model_name}")
            y_position -= 0.25 * inch
            
            # Instagram handle (bold)
            if instagram:
                c.setFont("Helvetica-Bold", 10)
                c.drawString(1 * inch, y_position, f"Instagram: {instagram}")
                y_position -= 0.2 * inch
            
            # Instagram URL (blue color)
            if instagram_url:
                c.setFont("Helvetica", 10)
                c.setFillColor(blue)
                c.drawString(1 * inch, y_position, f"Profile: {instagram_url}")
                y_position -= 0.2 * inch
                c.setFillColor(black)  # Reset color
            
            # Details
            if details:
                c.setFont("Helvetica", 9)
                # Simple text wrapping for details
                lines = []
                words = details.split()
                current_line = ""
                
                for word in words:
                    test_line = f"{current_line} {word}".strip()
                    if c.stringWidth(test_line, "Helvetica", 9) < 6 * inch:
                        current_line = test_line
                    else:
                        if current_line:
                            lines.append(current_line)
                        current_line = word
                
                if current_line:
                    lines.append(current_line)
                
                for line in lines[:3]:  # Limit to 3 lines
                    c.drawString(1 * inch, y_position, line)
                    y_position -= 0.15 * inch
            
            y_position -= 0.2 * inch
            
            # Try to add the image
            image_path = img_data.get('image_path', '')
            if image_path and os.path.exists(image_path):
                try:
                    print(f"📸 Adding image: {image_path}")
                    
                    # Calculate image dimensions
                    img = Image.open(image_path)
                    img_width, img_height = img.size
                    
                    # Calculate display size (max width: 4 inches)
                    max_width = 4 * inch
                    if img_width > max_width:
                        ratio = max_width / img_width
                        display_width = max_width
                        display_height = img_height * ratio
                    else:
                        display_width = img_width
                        display_height = img_height
                    
                    # Check if we have space for the image
                    if y_position - display_height - 0.2 * inch < 1 * inch:
                        c.showPage()
                        page_number += 1
                        y_position = height - 1 * inch
                        c.setFont("Helvetica-Bold", 10)
                        c.drawString(1 * inch, 0.5 * inch, f"GO Productions - Page {page_number}")
                    
                    # Add image to PDF
                    img_reader = ImageReader(image_path)
                    c.drawImage(img_reader, 1 * inch, y_position - display_height, 
                               width=display_width, height=display_height, 
                               preserveAspectRatio=True, mask='auto')
                    
                    y_position -= display_height + 0.3 * inch
                    print(f"✅ Image added successfully")
                    
                except Exception as img_error:
                    print(f"❌ Error adding image {image_path}: {img_error}")
                    c.setFont("Helvetica", 10)
                    c.drawString(1 * inch, y_position, "[Image not available]")
                    y_position -= 0.3 * inch
            else:
                print(f"❌ Image file not found: {image_path}")
                c.setFont("Helvetica", 10)
                c.drawString(1 * inch, y_position, "[Image file not found]")
                y_position -= 0.3 * inch
            
            # Add some space between images
            y_position -= 0.3 * inch
        
        # Final footer
        c.setFont("Helvetica-Bold", 10)
        c.drawString(1 * inch, 0.5 * inch, f"GO Productions - Page {page_number}")
        
        print("💾 Saving PDF...")
        c.save()
        
        # Get PDF content
        pdf_content = buffer.getvalue()
        buffer.close()
        
        print(f"✅ PDF generated successfully! Size: {len(pdf_content)} bytes")
        
        # Return PDF
        from flask import Response
        response = Response(
            pdf_content,
            mimetype='application/pdf',
            headers={
                'Content-Disposition': f'attachment; filename=model-portfolio-{project_id}.pdf',
                'Content-Length': str(len(pdf_content))
            }
        )
        
        return response
        
    except Exception as e:
        print(f"💥 PDF generation error: {str(e)}")
        print(f"💥 Traceback: {traceback.format_exc()}")
        return jsonify({'error': f'PDF generation failed: {str(e)}'}), 500

@app.route('/delete-additional-photo/<photo_id>', methods=['DELETE'])
def delete_additional_photo(photo_id):
    try:
        db = DatabaseManager()
        conn = db.get_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500
            
        cursor = conn.cursor(dictionary=True)
        
        # Get photo info
        cursor.execute("SELECT * FROM additional_photos WHERE id = %s", (photo_id,))
        photo = cursor.fetchone()
        
        if not photo:
            cursor.close()
            conn.close()
            return jsonify({'error': 'Photo not found'}), 404
        
        # Delete from database
        cursor.execute("DELETE FROM additional_photos WHERE id = %s", (photo_id,))
        conn.commit()
        cursor.close()
        conn.close()
        
        # Delete physical file
        if os.path.exists(photo['image_path']):
            os.remove(photo['image_path'])
        
        return jsonify({'success': True, 'message': 'Photo deleted successfully'})
        
    except Exception as e:
        print(f"💥 Delete additional photo error: {str(e)}")
        return jsonify({'error': str(e)}), 500




if __name__ == '__main__':
    os.makedirs("projects", exist_ok=True)
    print("=== ENHANCED PDF PROCESSOR STARTED ===")
    app.run(host='127.0.0.1', port=5000, debug=True, threaded=True)