from flask import Blueprint, request, session, jsonify, render_template, redirect, url_for, g
from functools import wraps
import uuid
from app import mysql
from werkzeug.security import generate_password_hash, check_password_hash
from bs4 import BeautifulSoup
import requests
from urllib.parse import urljoin, urlparse
from app.routes.api import find_relevant_urls  # Import the existing function
from app.utils.pdf_processor import process_pdf_content,process_docx_content
from app.config import Config
import logging
from app.utils.email_sender import EmailSender
from datetime import datetime, timedelta
import secrets
from app.utils.company_settings import company_login_required

logger = logging.getLogger(__name__)

company_bp = Blueprint('company', __name__)

# Company login required decorator
def company_login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'company_id' not in session:
            return redirect(url_for('company.login'))
        return f(*args, **kwargs)
    return decorated_function

@company_bp.route('/company/register', methods=['GET', 'POST'])
def register():
    if request.method == 'GET':
        return render_template('company/register.html')
    
    try:
        data = request.form
        name = data.get('name')
        email = data.get('email')
        password = data.get('password')
        
        if not all([name, email, password]):
            return jsonify({
                "success": False,
                "error": "All fields are required"
            }), 400
        
        cursor = mysql.connection.cursor()
        
        # Check if company exists
        cursor.execute("SELECT id, is_verified FROM companies WHERE email = %s", (email,))
        existing_company = cursor.fetchone()
        
        if existing_company:
            # Since we're using DictCursor, access by key instead of index
            if existing_company.get('is_verified'):
                return jsonify({
                    "success": False,
                    "error": "Email already registered"
                }), 400
            else:
                # Resend verification code for unverified company
                company_id = existing_company.get('id')
        else:
            # Create new company
            company_id = str(uuid.uuid4())
            hashed_password = generate_password_hash(password)
            
            cursor.execute(
                """INSERT INTO companies 
                (id, name, email, password, is_verified) 
                VALUES (%s, %s, %s, %s, FALSE)""",
                (company_id, name, email, hashed_password)
            )
        
        # Generate and store verification code
        email_sender = EmailSender()
        verification_code = email_sender.generate_verification_code()
        expires_at = datetime.now() + timedelta(minutes=15)
        
        cursor.execute(
            """UPDATE companies 
            SET verification_code = %s, 
                verification_code_expires_at = %s 
            WHERE id = %s""",
            (verification_code, expires_at, company_id)
        )
        
        mysql.connection.commit()
        
        # Send verification email
        if email_sender.send_verification_email(email, name, verification_code):
            return jsonify({
                "success": True,
                "message": "Please check your email for verification code",
                "company_id": company_id
            })
        else:
            return jsonify({
                "success": False,
                "error": "Failed to send verification email"
            }), 500
        
    except Exception as e:
        print(f"Registration error: {str(e)}")  # Add this for debugging
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/verify', methods=['POST'])
def verify_email():
    try:
        data = request.form
        company_id = data.get('company_id')
        verification_code = data.get('code')
        
        if not all([company_id, verification_code]):
            return jsonify({
                "success": False,
                "error": "Verification code is required"
            }), 400
        
        cursor = mysql.connection.cursor()
        
        # Check verification code
        cursor.execute("""
            SELECT verification_code, verification_code_expires_at 
            FROM companies 
            WHERE id = %s AND is_verified = FALSE
        """, (company_id,))
        
        result = cursor.fetchone()
        if not result:
            return jsonify({
                "success": False,
                "error": "Invalid verification attempt"
            }), 400
        
        # Since we're using DictCursor, access by key
        stored_code = result[0]
        expires_at = result[1]
        
        # Convert expires_at to datetime if it's a string
        if isinstance(expires_at, str):
            expires_at = datetime.strptime(expires_at, '%Y-%m-%d %H:%M:%S')
        
        # Check if code has expired
        if datetime.now() > expires_at:
            return jsonify({
                "success": False,
                "error": "Verification code has expired"
            }), 400
        
        if verification_code != stored_code:
            return jsonify({
                "success": False,
                "error": "Invalid verification code"
            }), 400
        
        # Mark company as verified
        cursor.execute("""
            UPDATE companies 
            SET is_verified = TRUE,
                verification_code = NULL,
                verification_code_expires_at = NULL
            WHERE id = %s
        """, (company_id,))
        
        mysql.connection.commit()
        
        return jsonify({
            "success": True,
            "message": "Email verified successfully"
        })
        
    except Exception as e:
        print(f"Verification error: {str(e)}")  # Add logging
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/login', methods=['GET', 'POST'])
def login():
    if request.method == 'GET':
        return render_template('company/login.html')
    try:
        data = request.form
        email = data.get('email')
        password = data.get('password')
        
        cursor = mysql.connection.cursor()
        cursor.execute(
            "SELECT id, password, name FROM companies WHERE email = %s",
            (email,)
        )
        
        company = cursor.fetchone()
        if not company or not check_password_hash(company[1], password):
            return jsonify({
                "success": False,
                "error": "Invalid credentials"
            }), 401
        
        session['company_id'] = company[0]
        session['company_name'] = company[2]
        
        return jsonify({"success": True})
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/dashboard')
@company_login_required
def dashboard():
    try:
        cursor = mysql.connection.cursor()
        
        # Get company details - update the SELECT statement to match table columns
        cursor.execute("""
            SELECT id, name, email, instructions, website,welcome_message, created_at, updated_at 
            FROM companies 
            WHERE id = %s
        """, (session['company_id'],))
        company = cursor.fetchone()
        
        return render_template('company/dashboard.html', company=company)
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500
    
@company_bp.route('/company/update-profile', methods=['POST'])
@company_login_required
def update_profile():
    try:
        data = request.form
        instructions = data.get('instructions')
        # website = data.get('website')
        message = data.get('message')
        email = data.get('email')
        

        
        cursor = mysql.connection.cursor()
        cursor.execute(
            "UPDATE companies SET instructions = %s, welcome_message = %s,email = %s WHERE id = %s",
            (instructions, message, email, session['company_id'])
        )
        
        mysql.connection.commit()
        
        return jsonify({"success": True})
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/upload-document', methods=['POST'])
@company_login_required
def upload_document():
    try:
        cursor = mysql.connection.cursor()

        # Check if files are provided
        if 'documents[]' not in request.files:
            return jsonify({"success": False, "error": "No files uploaded"}), 400

        files = request.files.getlist('documents[]')
        if not files:
            return jsonify({"success": False, "error": "No files provided"}), 400

        for file in files:
            if file:
                try:
                    doc_id = str(uuid.uuid4())
                    file_extension = file.filename.rsplit('.', 1)[-1].lower()

                    # Process PDF or DOCX file
                    if file_extension == 'pdf':
                        file_content = process_pdf_content(file)
                    elif file_extension == 'docx':
                        file_content = process_docx_content(file)
                    else:
                        logger.warning(f"Unsupported file type: {file.filename}")
                        continue  # Skip unsupported files

                    if file_content:
                        cursor.execute("""
                            INSERT INTO company_documents (
                                id, company_id, file_name, file_type, file_content, created_at
                            ) VALUES (%s, %s, %s, %s, %s, NOW())
                        """, (
                            doc_id,
                            session['company_id'],
                            file.filename,
                            file_extension,
                            file_content
                        ))
                    else:
                        logger.error(f"Failed to process content for file: {file.filename}")
                except Exception as e:
                    logger.error(f"Error processing file {file.filename}: {str(e)}")
                    continue

        mysql.connection.commit()
        return jsonify({"success": True})

    except Exception as e:
        logger.error(f"Error in upload_document: {str(e)}")
        mysql.connection.rollback()
        return jsonify({"success": False, "error": str(e)}), 500


# @company_bp.route('/company/upload-document', methods=['POST'])
# @company_login_required
# def upload_document():
#     try:
#         cursor = mysql.connection.cursor()
        
#         # Check if files are provided
#         if 'documents[]' not in request.files:
#             return jsonify({"success": False, "error": "No files uploaded"}), 400

#         files = request.files.getlist('documents[]')
#         if not files:
#             return jsonify({"success": False, "error": "No files provided"}), 400

#         for pdf_file in files:
#             if pdf_file and pdf_file.filename.endswith('.pdf'):
#                 try:
#                     doc_id = str(uuid.uuid4())
#                     pdf_content = process_pdf_content(pdf_file)

#                     if pdf_content:
#                         cursor.execute("""
#                             INSERT INTO company_documents (
#                                 id, company_id, file_name, file_type, file_content, created_at
#                             ) VALUES (%s, %s, %s, %s, %s, NOW())
#                         """, (
#                             doc_id,
#                             session['company_id'],
#                             pdf_file.filename,
#                             'pdf',
#                             pdf_content
#                         ))
#                     else:
#                         logger.error(f"Failed to process PDF content for file: {pdf_file.filename}")
#                 except Exception as e:
#                     logger.error(f"Error processing PDF file {pdf_file.filename}: {str(e)}")
#                     continue
        
#         mysql.connection.commit()
#         return jsonify({"success": True})
        
#     except Exception as e:
#         logger.error(f"Error in upload_document: {str(e)}")
#         mysql.connection.rollback()
#         return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/faq', methods=['POST'])
@company_login_required
def add_faq():
    try:
        data = request.json
        question = data.get('question')
        answer = data.get('answer')
        
        if not all([question, answer]):
            return jsonify({
                "success": False,
                "error": "Question and answer are required"
            }), 400
        
        faq_id = str(uuid.uuid4())
        cursor = mysql.connection.cursor()
        cursor.execute(
            "INSERT INTO company_faqs (id, company_id, question, answer) VALUES (%s, %s, %s, %s)",
            (faq_id, session['company_id'], question, answer)
        )
        
        mysql.connection.commit()
        
        return jsonify({
            "success": True,
            "faq_id": faq_id
        })
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/faq/<faq_id>', methods=['PUT', 'DELETE'])
@company_login_required
def manage_faq(faq_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Verify FAQ belongs to company
        cursor.execute(
            "SELECT id FROM company_faqs WHERE id = %s AND company_id = %s",
            (faq_id, session['company_id'])
        )
        
        if not cursor.fetchone():
            return jsonify({
                "success": False,
                "error": "FAQ not found"
            }), 404
        
        if request.method == 'DELETE':
            cursor.execute("DELETE FROM company_faqs WHERE id = %s", (faq_id,))
            mysql.connection.commit()
            return jsonify({"success": True})
        
        elif request.method == 'PUT':
            data = request.json
            question = data.get('question')
            answer = data.get('answer')
            
            if not all([question, answer]):
                return jsonify({
                    "success": False,
                    "error": "Question and answer are required"
                }), 400
            
            cursor.execute(
                "UPDATE company_faqs SET question = %s, answer = %s WHERE id = %s",
                (question, answer, faq_id)
            )
            
            mysql.connection.commit()
            return jsonify({"success": True})
            
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/logout')
def logout():
    # Clear company session data
    session.pop('company_id', None)
    session.pop('company_name', None)
    
    # Redirect to login page
    return redirect(url_for('company.login'))

def init_company_tables():
    try:
        cursor = mysql.connection.cursor()
        
        # Modify companies table
        cursor.execute("""
            CREATE TABLE companies (
                id VARCHAR(36) PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                website VARCHAR(255),
                welcome_message TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                verification_code VARCHAR(4) NULL,
                verification_code_expires_at DATETIME NULL,
                is_verified BOOLEAN DEFAULT FALSE,
                whatsapp_verify_token VARCHAR(64) NULL,
                whatsapp_phone_number VARCHAR(32) NULL,
                whatsapp_phone_number_id VARCHAR(128) NULL,
                whatsapp_access_token TEXT NULL
            )
        """)
        
        # Create new table for company documents
        cursor.execute("""
            CREATE TABLE company_documents (
                id VARCHAR(36) PRIMARY KEY,
                company_id VARCHAR(36) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                file_content LONGTEXT NOT NULL,
                file_type VARCHAR(50) NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (company_id) REFERENCES companies(id)
            )
        """)
        
        mysql.connection.commit()
    except Exception as e:
        print(f"Company tables initialization error: {str(e)}")

def get_website_content(url):
    try:
        # Clean up the URL if it starts with @
        if url.startswith('@'):
            url = url[1:]
        
        print(f"Starting to crawl: {url}")  # Debug log
        
        # Add https:// if not present
        if not url.startswith(('http://', 'https://')):
            url = 'https://' + url
            
        headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
        }
        
        response = requests.get(url, headers=headers, timeout=15)
        soup = BeautifulSoup(response.text, 'html.parser')
        
        # Get all links
        links = []
        seen_urls = set()
        base_domain = urlparse(url).netloc
        
        print(f"Base domain: {base_domain}")  # Debug log
        
        # Find all <a> tags
        for a in soup.find_all('a', href=True):
            href = a['href']
            # Convert relative URLs to absolute
            full_url = urljoin(url, href)
            parsed_url = urlparse(full_url)
            
            # Only include links from the same domain and not already seen
            if (parsed_url.netloc == base_domain and 
                full_url not in seen_urls and 
                not href.startswith(('#', 'javascript:', 'mailto:', 'tel:'))):
                
                seen_urls.add(full_url)
                text = a.get_text().strip()
                
                if text:  # Only include links with text
                    link_data = {
                        'url': full_url,
                        'text': text[:255]  # Limit title length for database
                    }
                    links.append(link_data)
                    print(f"Found link: {text} -> {full_url}")  # Debug log
        
        print(f"Total links found: {len(links)}")  # Debug log
        return links
        
    except Exception as e:
        print(f"Error in get_website_content: {str(e)}")
        return []

@company_bp.route('/save_company', methods=['POST'])
def save_company():
    try:
        data = request.form
        company_id = str(uuid.uuid4())
        
        # Get form data
        name = data.get('name')
        password = data.get('password')
        description = data.get('description')
        website = data.get('website')
        instructions = data.get('instructions')
        
        print(f"Processing company registration for {name} with website: {website}")
        
        # Hash the password
        hashed_password = generate_password_hash(password)
        
        cursor = mysql.connection.cursor()
        
        # Save company details
        cursor.execute("""
            INSERT INTO companies (id, name, password, description, website, instructions)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (company_id, name, hashed_password, description, website, instructions))
        
        # After saving company, crawl and store website URLs using existing function
        if website:
            print(f"Starting website crawl for {website}")
            # Use empty query string since we just want to trigger the crawl
            find_relevant_urls(website, "", company_id)
            
        mysql.connection.commit()
        
        return jsonify({
            "success": True,
            "message": "Company saved successfully and website URLs have been crawled",
            "company_id": company_id
        })
        
    except Exception as e:
        print(f"Error saving company: {str(e)}")
        mysql.connection.rollback()
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/update_website', methods=['POST'])
def update_website():
    try:
        company_id = request.form.get('company_id')
        website = request.form.get('website')
        
        if not company_id or not website:
            return jsonify({
                "success": False,
                "error": "Company ID and website URL are required"
            }), 400
            
        cursor = mysql.connection.cursor()
        
        # Update company website
        cursor.execute("""
            UPDATE companies 
            SET website = %s 
            WHERE id = %s
        """, (website, company_id))
        
        mysql.connection.commit()
        
        # Trigger URL refresh using the existing endpoint
        refresh_url = f"{Config.url}/api/{company_id}/refresh-urls"
        response = requests.post(refresh_url)
        
        if response.status_code == 200:
            return jsonify({
                "success": True,
                "message": "Website updated and URLs crawled successfully"
            })
        else:
            return jsonify({
                "success": False,
                "error": "Website updated but URL crawling failed"
            }), 500
            
    except Exception as e:
        print(f"Error updating website: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/chat-history')
@company_login_required
def chat_history():
    try:
        cursor = mysql.connection.cursor()
        
        # First get all unique sessions with their first user message
        cursor.execute("""
            SELECT 
                cm.session_id,
                MIN(CASE WHEN cm.role = 'user' THEN cm.content END) as first_message,
                MAX(cm.created_at) as last_message_time,
                COUNT(*) as message_count
            FROM chat_messages cm
            WHERE cm.company_id = %s
            GROUP BY cm.session_id
            ORDER BY last_message_time DESC
        """, (session['company_id'],))
        
        chat_sessions = cursor.fetchall()
        
        # Convert to list of dicts for easier template handling
        formatted_sessions = []
        for session_data in chat_sessions:
            formatted_sessions.append({
                'session_id': session_data[0],
                'first_message': session_data[1],
                'last_message_time': session_data[2],
                'message_count': session_data[3]
            })
        
        return render_template('company/chat_history.html', chat_sessions=formatted_sessions)
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/chat-session/<session_id>')
@company_login_required
def chat_session_details(session_id):
    try:
        cursor = mysql.connection.cursor()
        
        cursor.execute("""
            SELECT 
                id,
                role,
                content,
                created_at,  # Remove DATE_FORMAT and handle in Python
                is_voice_input
            FROM chat_messages
            WHERE company_id = %s AND session_id = %s
            ORDER BY created_at ASC
        """, (session['company_id'], session_id))
        
        # Convert tuple results to properly formatted dictionaries
        messages = []
        for row in cursor.fetchall():
            messages.append({
                'id': row[0],
                'role': row[1],
                'content': row[2],
                'created_at': row[3].strftime('%Y-%m-%d %H:%M:%S') if row[3] else None,  # Format date in Python
                'is_voice_input': bool(row[4])
            })
        
        return jsonify({
            "success": True,
            "messages": messages
        })
        
    except Exception as e:
        print(f"Error in chat_session_details: {str(e)}")  # Debug log
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

# Add this context processor to make company data available to all templates
@company_bp.context_processor
def inject_company():
    if 'company_id' in session:
        try:
            cursor = mysql.connection.cursor()
            cursor.execute(
                "SELECT * FROM companies WHERE id = %s",
                (session['company_id'],)
            )
            company = cursor.fetchone()
            cursor.close()
            return {'company': company}
        except Exception:
            return {'company': None}
    return {'company': None}

@company_bp.route('/api/<company_id>/welcome-message', methods=['GET'])
def get_welcome_message(company_id):
    try:
        # Query your database to get the welcome message for the company
        cursor = mysql.connection.cursor()
        cursor.execute("SELECT welcome_message FROM companies WHERE id = %s", (company_id,))
        result = cursor.fetchone()
        cursor.close()
        
        if result and result[0]:
            return jsonify({
                'success': True,
                'welcome_message': result[0]
            })
        else:
            return jsonify({
                'success': True,
                'welcome_message': 'Hello! How can I help you today?'  # Default message
            })
    except Exception as e:
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@company_bp.route('/company/documents', methods=['GET'])
@company_login_required
def get_documents():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT id, file_name, created_at 
            FROM company_documents 
            WHERE company_id = %s 
            ORDER BY created_at DESC
        """, (session['company_id'],))
        
        documents = cursor.fetchall()
        return jsonify({
            "success": True,
            "documents": [
                {
                    "id": doc[0],
                    "name": doc[1],
                    "date": doc[2].strftime("%Y-%m-%d %H:%M:%S")
                }
                for doc in documents
            ]
        })
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/files')
@company_login_required
def files():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT 
                id,
                file_name,
                created_at,
                file_type
            FROM company_documents 
            WHERE company_id = %s 
            ORDER BY created_at DESC
        """, (session['company_id'],))
        
        documents = cursor.fetchall()
        
        return render_template('company/files.html', documents=documents)
        
    except Exception as e:
        print(f"Error fetching files: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/delete-document/<doc_id>', methods=['DELETE'])
@company_login_required
def delete_document(doc_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Verify document belongs to company
        cursor.execute("""
            SELECT id FROM company_documents 
            WHERE id = %s AND company_id = %s
        """, (doc_id, session['company_id']))
        
        if not cursor.fetchone():
            return jsonify({
                "success": False,
                "error": "Document not found"
            }), 404
        
        # Delete document
        cursor.execute("DELETE FROM company_documents WHERE id = %s", (doc_id,))
        mysql.connection.commit()
        
        return jsonify({"success": True})
        
    except Exception as e:
        print(f"Error deleting document: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/meetings')
@company_login_required
def meetings():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT 
                id,
                customer_email,
                meeting_date,
                meeting_time
            FROM meetings 
            WHERE company_id = %s 
            ORDER BY meeting_date DESC
        """, (session['company_id'],))
        
        meetings = cursor.fetchall()
        
        return render_template('company/meeting.html', meetings=meetings)
        
    except Exception as e:
        print(f"Error fetching meetings: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/settings', methods=['GET'])
@company_login_required
def show_settings():
    cursor = mysql.connection.cursor()
    
    # Fetch existing settings for display
    cursor.execute("""
        SELECT available_days, start_time, end_time, widget_color, voice_speed
        FROM company_settings 
        WHERE company_id = %s
    """, (session['company_id'],))
    settings_data = cursor.fetchone()
    
    # Convert tuple to dict for easier template access
    settings = {}
    if settings_data:
        settings = {
            'available_days': settings_data[0],
            'start_time': settings_data[1],
            'end_time': settings_data[2],
            'widget_color': settings_data[3],
            'voice_speed': settings_data[4],
        }
    
    cursor.close()
    return render_template('company/setting.html', settings=settings)

@company_bp.route('/company/settings/update', methods=['POST'])
@company_login_required
def update_settings():
    cursor = mysql.connection.cursor()
    
    available_days = ','.join(request.form.getlist('available_days'))
    start_time = request.form.get('start_time')
    end_time = request.form.get('end_time')
    widget_color = request.form.get('widget_color', '#F42941')
    voice_speed = request.form.get('voice_speed', '1.0')
    
    # Check if settings already exist for this company
    cursor.execute("SELECT id FROM company_settings WHERE company_id = %s", (session['company_id'],))
    existing_settings = cursor.fetchone()
    
    if existing_settings:
        # Update existing settings
        cursor.execute("""
            UPDATE company_settings 
            SET available_days = %s, start_time = %s, end_time = %s,
                widget_color = %s, voice_speed = %s
            WHERE company_id = %s
        """, (available_days, start_time, end_time, widget_color, voice_speed, session['company_id']))
    else:
        # Insert new settings
        cursor.execute("""
            INSERT INTO company_settings 
            (company_id, available_days, start_time, end_time, widget_color, voice_speed)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (session['company_id'], available_days, start_time, end_time, widget_color, voice_speed))
        
    mysql.connection.commit()
    cursor.close()
    return jsonify({"success": True})

@company_bp.route('/company/whatsapp-settings')
@company_login_required
def whatsapp_settings():
    try:
        cursor = mysql.connection.cursor()
        
        # First, check if company already has a verify token
        cursor.execute("""
            SELECT id, name, email, whatsapp_phone_number, whatsapp_phone_number_id, 
                   whatsapp_access_token, whatsapp_verify_token,whatsapp_app_id,whatsapp_app_secret
            FROM companies 
            WHERE id = %s
        """, (session['company_id'],))
        company = cursor.fetchone()
        
        # If no verify token exists, generate one
        if not company[6]:  # if whatsapp_verify_token is None
            verify_token = secrets.token_hex(16)  # Generate a 32-character random token
            
            # Save the verify token
            cursor.execute("""
                UPDATE companies 
                SET whatsapp_verify_token = %s 
                WHERE id = %s
            """, (verify_token, session['company_id']))
            mysql.connection.commit()
            
            # Get updated company data
            cursor.execute("""
                SELECT id, name, email, whatsapp_phone_number, whatsapp_phone_number_id, 
                       whatsapp_access_token, whatsapp_verify_token,whatsapp_app_id,whatsapp_app_secret
                FROM companies 
                WHERE id = %s
            """, (session['company_id'],))
            company = cursor.fetchone()
        
        # Convert tuple to dict for easier template access
        company_data = {
            'id': company[0],
            'name': company[1],
            'email': company[2],
            'whatsapp_phone_number': company[3],
            'whatsapp_phone_number_id': company[4],
            'whatsapp_access_token': company[5],
            'whatsapp_verify_token': company[6],
            'app_id': company[7],
            'app_secret': company[8]
        }
        
        return render_template('company/whatsapp_settings.html', 
                             company=company_data,
                             base_url=Config.BASE_URL)
        
    except Exception as e:
        logger.error(f"Error loading WhatsApp settings: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/update-whatsapp-settings', methods=['POST'])
@company_login_required
def update_whatsapp_settings():
    try:
        cursor = mysql.connection.cursor()
        
        # Get form data
        whatsapp_app_id = request.form.get('whatsapp_app_id')
        whatsapp_app_secret = request.form.get('whatsapp_app_secret')
        whatsapp_phone_number = request.form.get('whatsapp_phone_number')
        whatsapp_phone_number_id = request.form.get('whatsapp_phone_number_id')
        whatsapp_access_token = request.form.get('whatsapp_access_token')
        
        # Check if phone number already exists for another company
        cursor.execute("""
            SELECT id, name 
            FROM companies 
            WHERE whatsapp_phone_number = %s 
            AND id != %s
        """, (whatsapp_phone_number, session['company_id']))
        
        existing_company = cursor.fetchone()
        if existing_company:
            return jsonify({
                "success": False,
                "error": "This WhatsApp phone number is already registered with another company"
            }), 400
        
        # If phone number is unique, proceed with update
        cursor.execute("""
            UPDATE companies 
            SET whatsapp_app_id = %s,
                whatsapp_app_secret = %s,
                whatsapp_phone_number = %s,
                whatsapp_phone_number_id = %s,
                whatsapp_access_token = %s,
                whatsapp_token_updated_at = NOW()
            WHERE id = %s
        """, (
            whatsapp_app_id,
            whatsapp_app_secret,
            whatsapp_phone_number,
            whatsapp_phone_number_id,
            whatsapp_access_token,
            session['company_id']
        ))
        
        mysql.connection.commit()
        
        return jsonify({
            "success": True,
            "message": "WhatsApp settings updated successfully"
        })
        
    except Exception as e:
        logger.error(f"Error updating WhatsApp settings: {str(e)}")
        mysql.connection.rollback()  # Added rollback in case of error
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/whatsapp-history')
@company_login_required
def whatsapp_history():
    try:
        cursor = mysql.connection.cursor()
        
        # Get all unique WhatsApp conversations with their latest message
        cursor.execute("""
            SELECT 
                wc.customer_number,
                wc.session_id,
                MIN(CASE WHEN wm.is_from_customer = TRUE THEN wm.content END) as first_message,
                MAX(wm.created_at) as last_message_time,
                COUNT(*) as message_count
            FROM whatsapp_conversations wc
            LEFT JOIN whatsapp_messages wm ON wc.session_id = wm.session_id
            WHERE wc.company_id = %s
            GROUP BY wc.customer_number, wc.session_id
            ORDER BY last_message_time DESC
        """, (session['company_id'],))
        
        whatsapp_sessions = cursor.fetchall()
        
        # Convert to list of dicts for easier template handling
        formatted_sessions = []
        for session_data in whatsapp_sessions:
            formatted_sessions.append({
                'customer_number': session_data[0],
                'session_id': session_data[1],
                'first_message': session_data[2],
                'last_message_time': session_data[3],
                'message_count': session_data[4]
            })
        
        return render_template('company/whatsapp_history.html', whatsapp_sessions=formatted_sessions)
        
    except Exception as e:
        logger.error(f"Error fetching WhatsApp history: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/whatsapp-session/<session_id>')
@company_login_required
def whatsapp_session_details(session_id):
    try:
        cursor = mysql.connection.cursor()
        
        cursor.execute("""
            SELECT 
                id,
                role,
                content,
                created_at,
                is_from_customer
            FROM whatsapp_messages
            WHERE company_id = %s AND session_id = %s
            ORDER BY created_at ASC
        """, (session['company_id'], session_id))
        
        messages = []
        for row in cursor.fetchall():
            messages.append({
                'id': row[0],
                'role': row[1],
                'content': row[2],
                'created_at': row[3].strftime('%Y-%m-%d %H:%M:%S'),
                'is_from_customer': bool(row[4])
            })
        
        return jsonify({
            "success": True,
            "messages": messages
        })
        
    except Exception as e:
        logger.error(f"Error in whatsapp_session_details: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/campaigns')
@company_login_required
def campaigns():
    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT id, name, subject, status, scheduled_at, created_at
        FROM email_campaigns
        WHERE company_id = %s
        ORDER BY created_at DESC
    """, (session['company_id'],))
    campaigns = cursor.fetchall()
    cursor.close()
    return render_template('company/campaigns.html', campaigns=campaigns)

@company_bp.route('/company/contacts')
@company_login_required
def contacts():
    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT id, name, email, created_at
        FROM contacts
        WHERE company_id = %s
        ORDER BY created_at DESC
    """, (session['company_id'],))
    contacts = cursor.fetchall()
    cursor.close()
    return render_template('company/contacts.html', contacts=contacts)

@company_bp.route('/company/campaigns/<campaign_id>/show')
@company_login_required
def show_campaign(campaign_id):
    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT id, name, subject, content, status, scheduled_at, created_at
        FROM email_campaigns
        WHERE id = %s
    """, (campaign_id,))
    campaign = cursor.fetchone()
    cursor.execute("""
        SELECT c.id, c.name, c.email, cr.status, cr.error_message, cr.message_id
        FROM campaign_recipients cr
        JOIN contacts c ON cr.contact_id = c.id
        WHERE cr.campaign_id = %s
    """, (campaign_id,))
    recipients = cursor.fetchall()
    cursor.execute("""
        SELECT er.contact_id, c.name, c.email, er.reply_content, er.reply_date, er.is_ai_response
        FROM email_replies er
        JOIN contacts c ON er.contact_id = c.id
        WHERE er.campaign_id = %s
        ORDER BY er.reply_date ASC
    """, (campaign_id,))
    replies = cursor.fetchall()
    cursor.close()
    return render_template('company/campaign_show.html', campaign=campaign, recipients=recipients, replies=replies)

@company_bp.route('/company/campaigns/recipient/<recipient_id>/thread')
@company_login_required
def get_recipient_thread(recipient_id):
    campaign_id = request.args.get('campaign_id')
    cursor = mysql.connection.cursor()
    cursor.execute("""
        SELECT ec.subject, ec.content, ec.created_at, c.email, c.name
        FROM campaign_recipients cr
        JOIN email_campaigns ec ON cr.campaign_id = ec.id
        JOIN contacts c ON cr.contact_id = c.id
        WHERE cr.campaign_id = %s AND cr.contact_id = %s
        LIMIT 1
    """, (campaign_id, recipient_id))
    sent = cursor.fetchone()
    cursor.execute("""
        SELECT er.reply_content, er.reply_date, c.email, er.is_ai_response
        FROM email_replies er
        JOIN contacts c ON er.contact_id = c.id
        WHERE er.campaign_id = %s AND er.contact_id = %s
        ORDER BY er.reply_date ASC
    """, (campaign_id, recipient_id))
    replies = cursor.fetchall()
    cursor.close()
    return jsonify({
        "success": True,
        "sent": {
            "subject": sent[0],
            "content": sent[1],
            "date": sent[2].strftime('%Y-%m-%d %H:%M:%S'),
            "to_email": sent[3],
            "to_name": sent[4]
        } if sent else None,
        "replies": [
            {
                "content": r[0],
                "date": r[1].strftime('%Y-%m-%d %H:%M:%S'),
                "contact_email": r[2],
                "is_ai_response": r[3]
            } for r in replies
        ]
    })

@company_bp.route('/company/campaigns/create', methods=['POST'])
@company_login_required
def create_campaign():
    try:
        data = request.json
        campaign_id = str(uuid.uuid4())
        
        cursor = mysql.connection.cursor()
        
        # Convert scheduled_at to proper datetime or None
        scheduled_at = None
        if data['scheduled_at'] and data['scheduled_at'].strip():
            scheduled_at = datetime.strptime(data['scheduled_at'], '%Y-%m-%dT%H:%M')
            status = 'scheduled'
        else:
            status = 'queued'
        
        # Create campaign
        cursor.execute("""
            INSERT INTO email_campaigns (
                id, company_id, name, subject, content, 
                status, scheduled_at, total_recipients,
                created_at
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW())
        """, (
            campaign_id,
            session['company_id'],
            data['name'],
            data['subject'],
            data['content'],
            status,  # Use the status we determined above
            scheduled_at,
            len(data['contact_ids'])
        ))
        
        # Add recipients
        for contact_id in data['contact_ids']:
            cursor.execute("""
                INSERT INTO campaign_recipients (campaign_id, contact_id)
                VALUES (%s, %s)
            """, (campaign_id, contact_id))
        
        mysql.connection.commit()
        
        # If not scheduled, send immediately
        if not scheduled_at:
            try:
                # Start the Celery task for sending emails
                send_campaign_emails.delay(campaign_id)
            except Exception as e:
                print(f"Error queuing email task: {str(e)}")
                # Update campaign status to error if task queuing fails
                cursor.execute("""
                    UPDATE email_campaigns 
                    SET status = 'failed',
                        error_message = %s
                    WHERE id = %s
                """, (str(e), campaign_id))
                mysql.connection.commit()
                raise e
        
        return jsonify({"success": True, "campaign_id": campaign_id})
        
    except Exception as e:
        print(f"Error creating campaign: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500