from flask import Blueprint, request, session, jsonify, render_template, redirect, url_for, g, current_app, send_from_directory, send_file
from functools import wraps
import uuid
from app import mysql
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
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 app.utils.admin_settings import get_admin_settings, send_email_via_admin_smtp
from app.utils.call_analysis import process_call_outcome
from datetime import datetime, timedelta
import secrets
from app.utils.email_campaign import send_campaign_emails
import imaplib
from app.tasks import process_campaign
from twilio.rest import Client
from twilio.twiml.voice_response import VoiceResponse, Gather, Connect, Stream
from app.utils.llm_reply import generate_call_response, get_quick_response, normalize_email_from_speech
import re
import threading
import random
import os
import time
import pandas as pd
import importlib
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP

stripe_spec = importlib.util.find_spec("stripe")
stripe_available = bool(stripe_spec)
stripe = importlib.import_module("stripe") if stripe_spec else None

if stripe is None:  # pragma: no cover - fallback when stripe isn't installed
    class _StripeError(Exception):
        """Fallback Stripe error."""

    class _StripeFallback:
        class error:  # type: ignore
            StripeError = _StripeError

    stripe = _StripeFallback()

logger = logging.getLogger(__name__)

company_bp = Blueprint('company', __name__)

DEFAULT_FREE_FILE_LIMIT = 5
DEFAULT_FREE_CALL_MINUTES = 5


def get_active_subscription(cursor, company_id):
    cursor.execute("""
        SELECT
            cpp.package_id,
            cpp.expires_at,
            DATEDIFF(cpp.expires_at, NOW()) AS remaining_days,
            p.no_of_files,
            p.title,
            p.no_of_mins
        FROM company_package_payments cpp
        JOIN packages p ON cpp.package_id = p.id
        WHERE cpp.company_id = %s
          AND cpp.status = 'succeeded'
          AND (cpp.expires_at IS NULL OR cpp.expires_at > NOW())
        ORDER BY cpp.completed_at DESC
        LIMIT 1
    """, (company_id,))
    row = cursor.fetchone()
    if not row:
        return None
    try:
        file_limit = int(row[3]) if row[3] is not None else None
    except (TypeError, ValueError):
        file_limit = None
    try:
        call_minutes_limit = int(row[5]) if row[5] is not None else None
    except (TypeError, ValueError):
        call_minutes_limit = None

    return {
        "package_id": row[0],
        "expires_at": row[1],
        "remaining_days": row[2],
        "file_limit": file_limit,
        "package_title": row[4],
        "call_minutes_limit": call_minutes_limit,
    }


def format_currency(value: Decimal, currency_code: str) -> str:
    currency_code = (currency_code or "GBP").upper()
    symbols = {
        "USD": "$",
        "EUR": "€",
        "GBP": "£",
        "PKR": "₨",
        "CAD": "$",
        "AUD": "$",
    }
    symbol = symbols.get(currency_code, currency_code + " ")
    quantized = value.quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
    return f"{symbol}{quantized:,}"


def get_call_minutes_usage(cursor, company_id):
    subscription = get_active_subscription(cursor, company_id)
    if subscription:
        if subscription.get('call_minutes_limit') is not None:
            limit_minutes = subscription.get('call_minutes_limit')
        else:
            limit_minutes = None
    else:
        limit_minutes = DEFAULT_FREE_CALL_MINUTES

    try:
        limit_minutes = int(limit_minutes) if limit_minutes is not None else None
    except (TypeError, ValueError):
        limit_minutes = None

    cursor.execute("""
        SELECT COALESCE(SUM(cc.duration_seconds), 0)
        FROM campaign_calls cc
        JOIN call_campaigns c ON cc.campaign_id = c.id
        WHERE c.company_id = %s
          AND cc.duration_seconds IS NOT NULL
    """, (company_id,))
    used_seconds = cursor.fetchone()[0] or 0

    if limit_minutes is not None:
        total_allowed_seconds = max(0, limit_minutes) * 60
        remaining_seconds = max(0, total_allowed_seconds - used_seconds)
    else:
        remaining_seconds = None

    return {
        "limit_minutes": limit_minutes,
        "used_seconds": used_seconds,
        "remaining_seconds": remaining_seconds,
        "subscription": subscription
    }


def enforce_call_minutes_limit(cursor, company_id, campaign_id=None):
    usage = get_call_minutes_usage(cursor, company_id)
    limit_minutes = usage['limit_minutes']
    if limit_minutes is None:
        return
    total_allowed_seconds = max(0, limit_minutes) * 60
    if usage['used_seconds'] >= total_allowed_seconds:
        if campaign_id:
            cursor.execute("""
                UPDATE campaign_calls
                SET status = 'limit_reached', error_message = 'Call minutes limit exceeded'
                WHERE campaign_id = %s AND status = 'pending'
            """, (campaign_id,))
        else:
            cursor.execute("""
                UPDATE campaign_calls
                SET status = 'limit_reached', error_message = 'Call minutes limit exceeded'
                WHERE campaign_id IN (
                    SELECT id FROM call_campaigns WHERE company_id = %s
                )
                  AND status = 'pending'
            """, (company_id,))

# Define the upload folder for recordings near the top of the file
UPLOAD_FOLDER_CALL_RECORDINGS = os.path.join('app', 'static', 'uploads')

# 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'))
        
        # Check if session has expired (past midnight)
        if 'session_expires_at' in session:
            logger.info(f"Session expires at: {session['session_expires_at']}")
            from datetime import datetime
            try:
                expires_at = datetime.fromisoformat(session['session_expires_at'])
                if datetime.now() >= expires_at:
                    # Session expired, clear it and redirect to login
                    session.clear()
                    return redirect(url_for('company.login'))
            except (ValueError, TypeError):
                # Invalid expiry time, clear session
                session.clear()
                return redirect(url_for('company.login'))
        
        # Check package validity on each request
        company_id = session.get('company_id')
        if company_id:
            cursor = mysql.connection.cursor()
            try:
                # Check if any packages have expired since last check
                cursor.execute("""
                    UPDATE company_package_payments
                    SET status = 'expired',
                        updated_at = NOW()
                    WHERE company_id = %s
                      AND status = 'succeeded'
                      AND expires_at IS NOT NULL
                      AND expires_at <= NOW()
                """, (company_id,))
                mysql.connection.commit()
            except Exception as e:
                logger.error(f"Error checking package expiry: {str(e)}")
            finally:
                cursor.close()
        
        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, status FROM companies WHERE email = %s", (email,))
        existing_company = cursor.fetchone()
        
        if existing_company:
            # existing_company is a tuple: (id, is_verified, status)
            company_id = existing_company[0]
            is_verified = existing_company[1]
            status = existing_company[2]
            if is_verified:
                return jsonify({
                    "success": False,
                    "error": "Email already registered"
                }), 400
            if status == 'rejected':
                cursor.execute(
                    """
                    UPDATE companies
                    SET name = %s,
                        password = %s,
                        status = 'pending',
                        updated_at = NOW()
                    WHERE id = %s
                    """,
                    (name, generate_password_hash(password), company_id)
                )
            # else: resend verification code for unverified company
        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, status) 
                VALUES (%s, %s, %s, %s, FALSE, 'pending')""",
                (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':
        if session.get('company_id'):
            return redirect(url_for('company.dashboard'))
        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, is_verified, status FROM companies WHERE email = %s",
            (email,)
        )
        
        company = cursor.fetchone()
        if not company or not check_password_hash(company[1], password):
            cursor.close()
            return jsonify({
                "success": False,
                "error": "Invalid credentials"
            }), 401
        
        if not company[3]:
            cursor.close()
            return jsonify({
                "success": False,
                "error": "Please verify your email before logging in."
            }), 403
        
        if company[4] != 'approved':
            cursor.close()
            return jsonify({
                "success": False,
                "error": "Your account is awaiting approval from the administrator."
            }), 403
        
        # Check and update expired packages
        cursor.execute("""
            UPDATE company_package_payments
            SET status = 'expired',
                updated_at = NOW()
            WHERE company_id = %s
              AND status = 'succeeded'
              AND expires_at IS NOT NULL
              AND expires_at <= NOW()
        """, (company[0],))
        mysql.connection.commit()
        
        # Calculate next midnight (12:00 AM)
        now = datetime.now()
        # Calculate tomorrow's midnight (next 12:00 AM)
        next_midnight = (now + timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
        
        # Set session with expiry at next midnight
        session.permanent = True
        session['company_id'] = company[0]
        session['company_name'] = company[2]
        session['session_expires_at'] = next_midnight.isoformat()
        
        cursor.close()
        return jsonify({"success": True})
        
    except Exception as e:
        if cursor:
            cursor.close()
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/forgot-password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'GET':
        return render_template('company/forgot_password.html')
    
    try:
        data = request.form
        email = data.get('email')
        
        if not email:
            return jsonify({
                "success": False,
                "error": "Email is required"
            }), 400
        
        cursor = mysql.connection.cursor()
        
        # Check if company exists
        cursor.execute("""
            SELECT id, name FROM companies 
            WHERE email = %s AND is_verified = TRUE AND status = 'approved'
        """, (email,))
        company = cursor.fetchone()
        
        if not company:
            return jsonify({
                "success": False,
                "error": "No verified company found with this email"
            }), 404
        
        company_id = company[0]
        company_name = company[1]
        
        # Generate and store reset code
        email_sender = EmailSender()
        reset_code = email_sender.generate_reset_code()
        expires_at = datetime.now() + timedelta(minutes=15)
        
        cursor.execute(
            """UPDATE companies 
            SET password_reset_code = %s, 
                password_reset_expires_at = %s 
            WHERE id = %s""",
            (reset_code, expires_at, company_id)
        )
        
        mysql.connection.commit()
        
        # Send reset email
        if email_sender.send_password_reset_email(email, company_name, reset_code):
            return jsonify({
                "success": True,
                "message": "Password reset code sent to your email",
                "company_id": company_id
            })
        else:
            return jsonify({
                "success": False,
                "error": "Failed to send reset email"
            }), 500
        
    except Exception as e:
        print(f"Forgot password error: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/reset-password', methods=['GET', 'POST'])
def reset_password():
    if request.method == 'GET':
        company_id = request.args.get('company_id')
        if not company_id:
            return redirect(url_for('company.forgot_password'))
        return render_template('company/reset_password.html', company_id=company_id)
    
    try:
        data = request.form
        company_id = data.get('company_id')
        reset_code = data.get('reset_code')
        new_password = data.get('new_password')
        confirm_password = data.get('confirm_password')
        
        if not all([company_id, reset_code, new_password, confirm_password]):
            return jsonify({
                "success": False,
                "error": "All fields are required"
            }), 400
        
        if new_password != confirm_password:
            return jsonify({
                "success": False,
                "error": "Passwords do not match"
            }), 400
        
        if len(new_password) < 6:
            return jsonify({
                "success": False,
                "error": "Password must be at least 6 characters long"
            }), 400
        
        cursor = mysql.connection.cursor()
        
        # Check reset code
        cursor.execute("""
            SELECT password_reset_code, password_reset_expires_at 
            FROM companies 
            WHERE id = %s AND is_verified = TRUE AND status = 'approved'
        """, (company_id,))
        
        result = cursor.fetchone()
        if not result:
            return jsonify({
                "success": False,
                "error": "Invalid reset attempt"
            }), 400
        
        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": "Reset code has expired"
            }), 400
        
        if reset_code != stored_code:
            return jsonify({
                "success": False,
                "error": "Invalid reset code"
            }), 400
        
        # Update password
        hashed_password = generate_password_hash(new_password)
        cursor.execute("""
            UPDATE companies 
            SET password = %s,
                password_reset_code = NULL,
                password_reset_expires_at = NULL
            WHERE id = %s
        """, (hashed_password, company_id))
        
        mysql.connection.commit()
        
        return jsonify({
            "success": True,
            "message": "Password reset successfully"
        })
        
    except Exception as e:
        print(f"Reset password error: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

@company_bp.route('/company/dashboard')
@company_login_required
def dashboard():
    cursor = None
    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()

        # Aggregate stats for the dashboard
        cursor.execute("""
            SELECT COUNT(DISTINCT session_id)
            FROM chat_messages
            WHERE company_id = %s
        """, (session['company_id'],))
        total_chats = cursor.fetchone()[0] or 0

        cursor.execute("""
            SELECT COUNT(*) 
            FROM meetings 
            WHERE company_id = %s
        """, (session['company_id'],))
        total_meetings = cursor.fetchone()[0] or 0

        cursor.execute("""
            SELECT COUNT(*) 
            FROM contacts 
            WHERE company_id = %s
        """, (session['company_id'],))
        total_contacts = cursor.fetchone()[0] or 0

        cursor.execute("""
            SELECT COUNT(*) 
            FROM company_documents 
            WHERE company_id = %s
        """, (session['company_id'],))
        total_files = cursor.fetchone()[0] or 0
        
        return render_template(
            'company/dashboard.html',
            company=company,
            total_chats=total_chats,
            total_meetings=total_meetings,
            total_contacts=total_contacts,
            total_files=total_files
        )
        
    except Exception as e:
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500
    finally:
        if cursor:
            cursor.close()
    
@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()
        folder_id = request.form.get('folder_id')
        print(f"folder_id start: {folder_id}")
        
        uploaded_documents = []  # Track uploaded documents for vector store update

        active_subscription = get_active_subscription(cursor, session['company_id'])
        if active_subscription:
            file_limit = active_subscription.get('file_limit')
            if file_limit is not None and file_limit <= 0:
                file_limit = None
        else:
            file_limit = DEFAULT_FREE_FILE_LIMIT

        cursor.execute("""
            SELECT COUNT(*) FROM company_documents WHERE company_id = %s
        """, (session['company_id'],))
        current_file_count = cursor.fetchone()[0]

        if file_limit is not None:
            remaining_capacity = file_limit - current_file_count
            if remaining_capacity <= 0:
                return jsonify({
                    "success": False,
                    "error": "You have reached your file upload limit. Please remove files or upgrade your package."
                }), 400
        else:
            remaining_capacity = None

        # Verify folder exists and belongs to company if folder_id is provided
        if folder_id and folder_id != 'null':
            cursor.execute("""
                SELECT id FROM company_folders 
                WHERE id = %s AND company_id = %s
            """, (folder_id, session['company_id']))
            if not cursor.fetchone():
                return jsonify({"success": False, "error": "Folder not found"}), 404

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

        cursor.execute("SELECT file_size_limit FROM admin_settings WHERE id = 1")
        settings_row = cursor.fetchone()
        file_size_limit_mb = settings_row[0] if settings_row and settings_row[0] else 25
        try:
            file_size_limit_mb = int(file_size_limit_mb)
        except (TypeError, ValueError):
            file_size_limit_mb = 25
        max_bytes = file_size_limit_mb * 1024 * 1024

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

        if remaining_capacity is not None and len(files) > remaining_capacity:
            return jsonify({
                "success": False,
                "error": f"You can upload up to {remaining_capacity} more file(s) based on your current plan."
            }), 400

        for file in files:
            print(f"Processing file: {file.filename}")
            if file:
                file.seek(0, os.SEEK_END)
                file_size_bytes = file.tell()
                file.seek(0)
                if file_size_bytes > max_bytes:
                    return jsonify({
                        "success": False,
                        "error": f"File {file.filename} exceeds the maximum allowed size of {file_size_limit_mb} MB."
                    }), 400
                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, folder_id, created_at
                            ) VALUES (%s, %s, %s, %s, %s, %s, NOW())
                        """, (
                            doc_id,
                            session['company_id'],
                            file.filename,
                            file_extension,
                            file_content,
                            folder_id
                        ))
                        
                        # Track uploaded document for vector store update
                        uploaded_documents.append({
                            'doc_id': doc_id,
                            'content': file_content,
                            'filename': file.filename
                        })
                        
                        logger.info(f"Successfully uploaded document: {file.filename}")
                    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()
        
        # Update vector store with new documents
        if uploaded_documents:
            try:
                from app.routes.api import update_vector_store_with_new_documents
                success = update_vector_store_with_new_documents(session['company_id'], uploaded_documents)
                if success:
                    logger.info(f"Vector store updated with {len(uploaded_documents)} new documents")
                else:
                    logger.warning("Failed to update vector store with new documents")
            except Exception as e:
                logger.error(f"Error updating vector store: {str(e)}")
        
        if file_limit is not None:
            new_count = current_file_count + len(uploaded_documents)
            remaining_after = max(0, file_limit - new_count)
        else:
            remaining_after = None

        return jsonify({
            "success": True,
            "message": f"Uploaded {len(uploaded_documents)} document(s) successfully",
            "remaining_files": remaining_after
            ,"file_size_limit_mb": file_size_limit_mb
        })
        
    except Exception as e:
        logger.error(f"Error in upload_document: {str(e)}")
        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/download-document/<document_id>', methods=['GET'])
@company_login_required
def download_document(document_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Get document info
        cursor.execute("""
            SELECT file_name, file_path, company_id
            FROM company_documents 
            WHERE id = %s
        """, (document_id,))
        
        document = cursor.fetchone()
        if not document:
            return jsonify({"error": "Document not found"}), 404
            
        file_name, file_path, company_id = document
        
        # Check if file exists
        if not os.path.exists(file_path):
            return jsonify({"error": "File not found on server"}), 404
            
        # Return file for download
        return send_file(
            file_path,
            as_attachment=True,
            download_name=file_name,
            mimetype='application/octet-stream'
        )
        
    except Exception as e:
        logger.error(f"Error downloading document {document_id}: {str(e)}")
        return jsonify({"error": "Error downloading file"}), 500

@company_bp.route('/company/files', methods=['GET'])
@company_login_required
def files():
    try:
        current_folder_id = request.args.get('folder')
        page = request.args.get('page', 1, type=int)
        per_page = 12  # Items per page
        
        cursor = mysql.connection.cursor()

        active_subscription = get_active_subscription(cursor, session['company_id'])
        if active_subscription:
            file_limit = active_subscription.get('file_limit')
            if file_limit is not None and file_limit <= 0:
                file_limit = None
            active_package_title = active_subscription.get('package_title')
            remaining_days = active_subscription.get('remaining_days')
        else:
            file_limit = 5
            active_package_title = None
            remaining_days = None

        cursor.execute("""
            SELECT COUNT(*) FROM company_documents WHERE company_id = %s
        """, (session['company_id'],))
        total_company_documents = cursor.fetchone()[0]
        print(f"total_company_documents: {total_company_documents}")
        if file_limit is not None:
            remaining_files_count = max(0, file_limit - total_company_documents)
        else:
            remaining_files_count = None
        
        # Get folders (only show on first page to avoid duplication across pages)
        folders = []
        folder_count = 0
        if page == 1:  # Only show folders on the first page
            if current_folder_id:
                cursor.execute("""
                    SELECT id, name 
                    FROM company_folders 
                    WHERE company_id = %s AND parent_id = %s
                    ORDER BY name
                """, (session['company_id'], current_folder_id))
            else:
                cursor.execute("""
                    SELECT id, name 
                    FROM company_folders 
                    WHERE company_id = %s AND parent_id IS NULL
                    ORDER BY name
                """, (session['company_id'],))
            
            folders = cursor.fetchall()
            folder_count = len(folders)
        
        # Get documents with pagination
        if current_folder_id:
            cursor.execute("""
                SELECT COUNT(*) 
                FROM company_documents 
                WHERE company_id = %s AND folder_id = %s
            """, (session['company_id'], current_folder_id))
        else:
            cursor.execute("""
                SELECT COUNT(*) 
                FROM company_documents 
                WHERE company_id = %s AND (folder_id IS NULL OR folder_id = 'null')
            """, (session['company_id'],))
        
        total_documents = cursor.fetchone()[0]
        
        # Get paginated documents
        if page == 1:
            # On first page, account for folders taking up space
            available_slots_for_files = max(0, per_page - folder_count)
            file_offset = 0
        else:
            # On subsequent pages, use full per_page since no folders
            available_slots_for_files = per_page
            # Calculate offset accounting for files shown on first page
            files_on_first_page = max(0, per_page - folder_count)
            file_offset = files_on_first_page + (page - 2) * per_page
        
        if available_slots_for_files > 0:
            if current_folder_id:
                cursor.execute("""
                    SELECT id, file_name, created_at 
                    FROM company_documents 
                    WHERE company_id = %s AND folder_id = %s
                    ORDER BY created_at DESC
                    LIMIT %s OFFSET %s
                """, (session['company_id'], current_folder_id, available_slots_for_files, file_offset))
            else:
                cursor.execute("""
                    SELECT id, file_name, created_at 
                    FROM company_documents 
                    WHERE company_id = %s AND (folder_id IS NULL OR folder_id = 'null')
                    ORDER BY created_at DESC
                    LIMIT %s OFFSET %s
                """, (session['company_id'], available_slots_for_files, file_offset))
            
            documents = cursor.fetchall()
        else:
            # If folders take up all slots, no files to show
            documents = []
        
        # Create pagination object with iter_pages method
        # Calculate total pages based on mixed pagination logic
        if total_documents == 0:
            # No files, only folders (if any)
            total_pages = 1 if folder_count > 0 else 0
        else:
            # Calculate total pages considering mixed pagination
            files_on_first_page = max(0, per_page - folder_count)
            remaining_files = total_documents - files_on_first_page
            
            if remaining_files <= 0:
                # All files fit on first page
                total_pages = 1
            else:
                # Additional pages needed for remaining files
                additional_pages = (remaining_files + per_page - 1) // per_page
                total_pages = 1 + additional_pages
        
        class Pagination:
            def __init__(self, page, per_page, total, pages):
                self.page = page
                self.per_page = per_page
                self.total = total
                self.pages = pages
                self.has_prev = page > 1
                self.has_next = page < pages
                self.prev_num = page - 1 if page > 1 else None
                self.next_num = page + 1 if page < pages else None
                # Calculate start_item and end_item accounting for mixed pagination
                if page == 1:
                    # First page: folders + files
                    self.start_item = 1
                    self.end_item = min(folder_count + len(documents), total + folder_count)
                else:
                    # Subsequent pages: only files
                    files_on_first_page = max(0, per_page - folder_count)
                    start_file = files_on_first_page + (page - 2) * per_page + 1
                    end_file = min(start_file + len(documents) - 1, total)
                    self.start_item = start_file
                    self.end_item = end_file
                self.total_items = total
                
            def iter_pages(self, left_edge=2, right_edge=2, left_current=2, right_current=3):
                last = self.pages
                for num in range(1, last + 1):
                    if num <= left_edge or \
                       (num > self.page - left_current - 1 and num < self.page + right_current) or \
                       num > last - right_edge:
                        yield num
                    elif num == left_edge + 1 or num == last - right_edge:
                        yield None
        
        pagination = Pagination(page, per_page, total_documents, total_pages)
        
        # Get breadcrumb
        breadcrumb = []
        if current_folder_id:
            folder_id = current_folder_id
            while folder_id:
                cursor.execute("""
                    SELECT id, name, parent_id 
                    FROM company_folders 
                    WHERE id = %s
                """, (folder_id,))
                folder = cursor.fetchone()
                if folder:
                    breadcrumb.insert(0, {'id': folder[0], 'name': folder[1]})
                    folder_id = folder[2]
                else:
                    break
     
        return render_template('company/files.html', 
                             folders=folders, 
                             documents=documents,
                             breadcrumb=breadcrumb,
                             current_folder_id=current_folder_id,
                             pagination=pagination,
                             file_limit=file_limit,
                             remaining_files=remaining_files_count,
                             active_package_title=active_package_title,
                             remaining_days=remaining_days)
        
    except Exception as e:
        logger.error(f"Error in files route: {str(e)}")
        return render_template('company/files.html', 
                             folders=[], 
                             documents=[],
                             breadcrumb=[],
                             current_folder_id=None,
                             pagination=None,
                             file_limit=None,
                             remaining_files=None,
                             active_package_title=None,
                             remaining_days=None)

@company_bp.route('/company/create-folder', methods=['POST'])
@company_login_required
def create_folder():
    try:
        data = request.json
        folder_name = data.get('name')
        parent_id = data.get('parent_id')
        print(f"parent_id: {parent_id}")
        if not folder_name:
            return jsonify({"success": False, "error": "Folder name is required"}), 400
            
        cursor = mysql.connection.cursor()
        
        # Only verify parent folder if parent_id is provided and not null
        if parent_id and parent_id != 'null':
            cursor.execute("""
                SELECT id FROM company_folders 
                WHERE id = %s AND company_id = %s
            """, (parent_id, session['company_id']))
            if not cursor.fetchone():
                return jsonify({"success": False, "error": "Parent folder not found"}), 404
        
        folder_id = str(uuid.uuid4())
        cursor.execute("""
            INSERT INTO company_folders (id, company_id, name, parent_id)
            VALUES (%s, %s, %s, %s)
        """, (folder_id, session['company_id'], folder_name, parent_id if parent_id and parent_id != 'null' else None))
        
        mysql.connection.commit()
        return jsonify({"success": True, "folder_id": folder_id})
        
    except Exception as e:
        mysql.connection.rollback()
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/edit-folder/<folder_id>', methods=['PUT'])
@company_login_required
def edit_folder(folder_id):
    try:
        data = request.json
        new_name = data.get('name')
        
        if not new_name:
            return jsonify({"success": False, "error": "Folder name is required"}), 400
            
        cursor = mysql.connection.cursor()
        
        # Verify folder exists and belongs to company
        cursor.execute("""
            SELECT id FROM company_folders 
            WHERE id = %s AND company_id = %s
        """, (folder_id, session['company_id']))
        
        if not cursor.fetchone():
            return jsonify({"success": False, "error": "Folder not found"}), 404
        
        # Update folder name
        cursor.execute("""
            UPDATE company_folders 
            SET name = %s, updated_at = NOW()
            WHERE id = %s AND company_id = %s
        """, (new_name, folder_id, session['company_id']))
        
        mysql.connection.commit()
        return jsonify({"success": True, "message": "Folder updated successfully"})
        
    except Exception as e:
        mysql.connection.rollback()
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/delete-folder/<folder_id>', methods=['DELETE'])
@company_login_required
def delete_folder(folder_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Verify folder belongs to company
        cursor.execute("""
            SELECT id FROM company_folders 
            WHERE id = %s AND company_id = %s
        """, (folder_id, session['company_id']))
        
        if not cursor.fetchone():
            return jsonify({"success": False, "error": "Folder not found"}), 404
        
        # Recursively delete contents: files and subfolders.
        # First, delete files in the current folder
        cursor.execute("""
            DELETE FROM company_documents WHERE folder_id = %s AND company_id = %s
        """, (folder_id, session['company_id']))
        
        # Then, recursively delete subfolders and their contents
        # Note: This assumes your database supports recursive deletion or you handle it in a loop
        # A simple approach is to delete files and then folders in a bottom-up manner or rely on CASCADE DELETE for subfolders if set up.
        # The current schema has ON DELETE CASCADE for parent_id in company_folders, which will delete subfolders.
        
        # Finally, delete the folder itself
        cursor.execute("DELETE FROM company_folders WHERE id = %s AND company_id = %s", (folder_id, session['company_id']))
        
        mysql.connection.commit()
        
        return jsonify({"success": True})
        
    except Exception as e:
        mysql.connection.rollback()
        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, mail_mailer, mail_host, mail_port, mail_username, mail_password, mail_encryption, mail_from_address, mail_from_name, idle_reminder_seconds,imap_host,imap_port,twilio_account_sid,twilio_auth_token,twilio_phone_number,ai_voice
        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],
            'mail_mailer': settings_data[5],
            'mail_host': settings_data[6],
            'mail_port': settings_data[7],
            'mail_username': settings_data[8],
            'mail_password': settings_data[9],
            'mail_encryption': settings_data[10],
            'mail_from_address': settings_data[11],
            'mail_from_name': settings_data[12],
            'idle_reminder_seconds': settings_data[13],
            'imap_host': settings_data[14],
            'imap_port': settings_data[15],
            'twilio_account_sid': settings_data[16],
            'twilio_auth_token': settings_data[17],
            'twilio_phone_number': settings_data[18],
            'ai_voice': settings_data[19],

        }
    
    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()
    
    # Get existing settings
    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')
    ai_voice = request.form.get('ai_voice', 'en-GB-female')
    
    # Get SMTP settings
    mail_mailer = request.form.get('mail_mailer')
    mail_host = request.form.get('mail_host')
    mail_port = request.form.get('mail_port')
    mail_username = request.form.get('mail_username')
    mail_password = request.form.get('mail_password')
    mail_encryption = request.form.get('mail_encryption')
    mail_from_address = request.form.get('mail_from_address')
    mail_from_name = request.form.get('mail_from_name')
    imap_host = request.form.get('imap_host')
    imap_port = request.form.get('imap_port')
    idle_reminder_seconds = int(request.form.get('idle_reminder_seconds', 4))
    print(f"idle_reminder_seconds: {idle_reminder_seconds}")

    # Get Twilio settings
    twilio_account_sid = request.form.get('twilio_account_sid')
    twilio_auth_token = request.form.get('twilio_auth_token')
    twilio_phone_number = request.form.get('twilio_phone_number')
    
    # 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,
                ai_voice = %s,
                mail_mailer = %s,
                mail_host = %s,
                mail_port = %s,
                mail_username = %s,
                mail_password = %s,
                mail_encryption = %s,
                mail_from_address = %s,
                mail_from_name = %s,
                imap_host = %s,
                imap_port = %s,
                idle_reminder_seconds = %s,
                twilio_account_sid = %s,
                twilio_auth_token = %s,
                twilio_phone_number = %s
            WHERE company_id = %s
        """, (
            available_days, start_time, end_time, widget_color, voice_speed,
            ai_voice,
            mail_mailer, mail_host, mail_port, mail_username, mail_password,
            mail_encryption, mail_from_address, mail_from_name, imap_host, imap_port,
            idle_reminder_seconds, twilio_account_sid, twilio_auth_token, twilio_phone_number,
            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,
             ai_voice,
             mail_mailer, mail_host, mail_port, mail_username, mail_password,
             mail_encryption, mail_from_address, mail_from_name, imap_host, imap_port, 
             idle_reminder_seconds, twilio_account_sid, twilio_auth_token, twilio_phone_number)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            session['company_id'], available_days, start_time, end_time, widget_color, voice_speed,
            ai_voice,
            mail_mailer, mail_host, mail_port, mail_username, mail_password,
            mail_encryption, mail_from_address, mail_from_name, imap_host, imap_port,
            idle_reminder_seconds, twilio_account_sid, twilio_auth_token, twilio_phone_number
        ))
        
    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/contacts')
@company_login_required
def contacts():
    return render_template('company/contacts.html')

@company_bp.route('/company/contacts/list')
@company_login_required
def list_contacts():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT id, name, phone_number, email, created_at
            FROM contacts
            WHERE company_id = %s
            ORDER BY name
        """, (session['company_id'],))
        
        contacts = cursor.fetchall()
        return jsonify({
            "success": True,
            "contacts": contacts
        })
    except Exception as e:
        logger.error(f"Error fetching contacts: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contacts/import', methods=['POST'])
@company_login_required
def import_contacts():
    try:
        if 'file' not in request.files:
            return jsonify({
                "success": False,
                "error": "No file provided"
            }), 400

        file = request.files['file']
        if not file.filename:
            return jsonify({
                "success": False,
                "error": "No file selected"
            }), 400

        filename = secure_filename(file.filename)
        file_ext = filename.rsplit('.', 1)[1].lower()

        if file_ext not in ['csv', 'xlsx']:
            return jsonify({
                "success": False,
                "error": "Invalid file format. Please upload CSV or Excel file."
            }), 400

        # Read file
        if file_ext == 'csv':
            df = pd.read_csv(file)
        else:
            df = pd.read_excel(file)

        # Validate required columns
        required_columns = ['name', 'phone_number']
        if not all(col in df.columns for col in required_columns):
            return jsonify({
                "success": False,
                "error": "File must contain 'name' and 'phone_number' columns"
            }), 400

        cursor = mysql.connection.cursor()
        imported_count = 0
        error_count = 0
        
        for _, row in df.iterrows():
            try:
                # Clean phone number
                phone = str(row['phone_number']).strip()
                if not phone.startswith('+'):
                    phone = '+' + phone

                cursor.execute("""
                    INSERT INTO contacts (
                        id, company_id, name, phone_number, email
                    ) VALUES (%s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                        name = VALUES(name),
                        email = VALUES(email)
                """, (
                    str(uuid.uuid4()),
                    session['company_id'],
                    row['name'],
                    phone,
                    row.get('email')
                ))
                imported_count += 1
            except Exception as e:
                error_count += 1
                logger.error(f"Error importing contact {row['name']}: {str(e)}")
                continue

        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": f"Successfully imported {imported_count} contacts. {error_count} failed."
        })

    except Exception as e:
        logger.error(f"Error importing contacts: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contacts/add', methods=['POST'])
@company_login_required
def add_contact():
    try:
        data = request.json
        name = data.get('name')
        phone_number = data.get('phone_number')
        email = data.get('email')

        if not all([name, phone_number]):
            return jsonify({
                "success": False,
                "error": "Name and phone number are required"
            }), 400

        cursor = mysql.connection.cursor()
        cursor.execute("""
            INSERT INTO contacts (
                id, company_id, name, phone_number, email
            ) VALUES (%s, %s, %s, %s, %s)
        """, (
            str(uuid.uuid4()),
            session['company_id'],
            name,
            phone_number,
            email
        ))

        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": "Contact added successfully"
        })

    except Exception as e:
        logger.error(f"Error adding contact: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contacts/<contact_id>', methods=['DELETE'])
@company_login_required
def delete_contact(contact_id):
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            DELETE FROM contacts 
            WHERE id = %s AND company_id = %s
        """, (contact_id, session['company_id']))
        
        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": "Contact deleted successfully"
        })

    except Exception as e:
        logger.error(f"Error deleting contact: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contacts/<contact_id>', methods=['PUT'])
@company_login_required
def update_contact(contact_id):
    try:
        data = request.json
        name = data.get('name')
        phone_number = data.get('phone_number')
        email = data.get('email')

        if not all([name, phone_number]):
            return jsonify({
                "success": False,
                "error": "Name and phone number are required"
            }), 400

        cursor = mysql.connection.cursor()
        cursor.execute("""
            UPDATE contacts 
            SET name = %s, phone_number = %s, email = %s
            WHERE id = %s AND company_id = %s
        """, (name, phone_number, email, contact_id, session['company_id']))

        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": "Contact updated successfully"
        })

    except Exception as e:
        logger.error(f"Error updating contact: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@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

@company_bp.route('/company/campaigns/list')
@company_login_required
def list_campaigns():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT * FROM email_campaigns
            WHERE company_id = %s
            ORDER BY created_at DESC
        """, (session['company_id'],))
        
        campaigns = cursor.fetchall()
        return jsonify({"success": True, "campaigns": campaigns})
        
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/campaigns/<campaign_id>', methods=['GET'])
@company_login_required
def get_campaign_detail(campaign_id):
    cursor = mysql.connection.cursor()
    # Get campaign details
    cursor.execute("""
        SELECT id, name, subject, content, status, scheduled_at, created_at
        FROM email_campaigns
        WHERE id = %s
    """, (campaign_id,))
    campaign = cursor.fetchone()
    if not campaign:
        return jsonify({"success": False, "error": "Campaign not found"}), 404

    # Get contacts for this campaign
    cursor.execute("""
        SELECT c.id, c.name, c.email, cr.status, cr.error_message
        FROM campaign_recipients cr
        JOIN contacts c ON cr.contact_id = c.id
        WHERE cr.campaign_id = %s
    """, (campaign_id,))
    contacts = cursor.fetchall()
    cursor.close()

    return jsonify({
        "success": True,
        "campaign": {
            "id": campaign[0],
            "name": campaign[1],
            "subject": campaign[2],
            "content": campaign[3],
            "status": campaign[4],
            "scheduled_at": campaign[5].strftime('%Y-%m-%d %H:%M') if campaign[5] else None,
            "created_at": campaign[6].strftime('%Y-%m-%d %H:%M') if campaign[6] else None,
        },
        "contacts": [
            {
                "id": c[0],
                "name": c[1],
                "email": c[2],
                "status": c[3],
                "error_message": c[4]
            } for c in contacts
        ]
    })

@company_bp.route('/company/campaigns/<campaign_id>', methods=['DELETE'])
@company_login_required
def delete_campaign(campaign_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Check if campaign belongs to company and is in draft status
        cursor.execute("""
            SELECT status FROM email_campaigns
            WHERE id = %s AND company_id = %s
        """, (campaign_id, session['company_id']))
        
        campaign = cursor.fetchone()
        if not campaign:
            return jsonify({"success": False, "error": "Campaign not found"}), 404
            
        if campaign[0] != 'draft':
            return jsonify({
                "success": False,
                "error": "Only draft campaigns can be deleted"
            }), 400
            
        # Delete recipients first
        cursor.execute("DELETE FROM campaign_recipients WHERE campaign_id = %s", (campaign_id,))
        
        # Delete campaign
        cursor.execute("DELETE FROM email_campaigns WHERE id = %s", (campaign_id,))
        
        mysql.connection.commit()
        return jsonify({"success": True})
        
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

# @company_bp.route('/company/campaigns')
# @company_login_required
# def campaigns():
#     try:
#         cursor = mysql.connection.cursor()
        
#         # Get campaigns list
#         cursor.execute("""
#             SELECT 
#                 ec.*,
#                 COUNT(cr.contact_id) as total_recipients,
#                 SUM(CASE WHEN cr.status = 'sent' THEN 1 ELSE 0 END) as sent_count
#             FROM email_campaigns ec
#             LEFT JOIN campaign_recipients cr ON ec.id = cr.campaign_id
#             WHERE ec.company_id = %s
#             GROUP BY ec.id
#             ORDER BY ec.created_at DESC
#         """, (session['company_id'],))
        
#         campaigns = cursor.fetchall()
        
#         # Get contacts for selection
#         cursor.execute("""
#             SELECT id, name, email, phone_number 
#             FROM contacts 
#             WHERE company_id = %s
#             ORDER BY name
#         """, (session['company_id'],))
        
#         contacts = cursor.fetchall()
        
#         return render_template('company/campaigns.html', 
#                              campaigns=campaigns,
#                              contacts=contacts)
                             
#     except Exception as e:
#         print(f"Error loading campaigns: {str(e)}")
#         return render_template('company/campaigns.html', 
#                              campaigns=[],
#                              contacts=[],
#                              error="Error loading campaigns")

@company_bp.route('/company/campaigns/<campaign_id>/replies')
@company_login_required
def get_campaign_replies(campaign_id):
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT er.*, c.name, c.email
            FROM email_replies er
            JOIN contacts c ON er.contact_id = c.id
            WHERE er.campaign_id = %s
            ORDER BY er.reply_date DESC
        """, (campaign_id,))
        
        replies = cursor.fetchall()
        cursor.close()
        
        return jsonify({
            "success": True,
            "replies": [{
                "id": r[0],
                "contact_name": r[7],
                "contact_email": r[8],
                "content": r[4],
                "reply_date": r[5].strftime('%Y-%m-%d %H:%M:%S')
            } for r in replies]
        })
        
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/test-reply-scanner')
@company_login_required
def test_reply_scanner():
    try:
        from app.utils.email_reply_scanner import scan_email_replies
        scan_email_replies.delay()
        return jsonify({
            "success": True, 
            "message": "Reply scanner triggered. Check Celery logs for results."
        })
    except Exception as e:
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/test-imap-connection')
@company_login_required
def test_imap_connection():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT smtp_username, smtp_password, smtp_host
            FROM company_settings
            WHERE company_id = %s
        """, (session['company_id'],))
        
        settings = cursor.fetchone()
        if not settings:
            return jsonify({"success": False, "error": "SMTP settings not found"}), 400
            
        email_username = settings[0]
        email_password = settings[1]
        smtp_host = settings[2]
        imap_host = smtp_host.replace('smtp', 'imap')
        
        # Test IMAP connection
        mail = imaplib.IMAP4_SSL(imap_host, 993)
        mail.login(email_username, email_password)
        mail.select('INBOX')
        
        # Get unread count
        _, messages = mail.search(None, 'UNSEEN')
        unread_count = len(messages[0].split())
        
        mail.close()
        mail.logout()
        
        return jsonify({
            "success": True,
            "message": f"IMAP connection successful. Found {unread_count} unread messages.",
            "settings": {
                "imap_host": imap_host,
                "username": email_username
            }
        })
        
    except Exception as e:
        return jsonify({
            "success": False, 
            "error": str(e),
            "settings": {
                "imap_host": imap_host,
                "username": email_username
            }
        }), 500

@company_bp.route('/company/campaigns/<campaign_id>/show')
@company_login_required
def show_campaign(campaign_id):
    cursor = mysql.connection.cursor()
    # Get campaign details
    cursor.execute("""
        SELECT id, name, subject, content, status, scheduled_at, created_at
        FROM email_campaigns
        WHERE id = %s
    """, (campaign_id,))
    campaign = cursor.fetchone()

    # Get recipients
    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()

    # Get all replies for this campaign
    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 DESC
    """, (campaign_id,))
    replies = cursor.fetchall()
    cursor.close()

    calls_with_transcript = []
    for call in replies:
        call_id = call[0]
        cursor.execute("""
            SELECT user_input, ai_response, call_status, created_at
            FROM call_conversation_history
            WHERE campaign_call_id = %s
            ORDER BY created_at ASC
        """, (call_id,))
        transcript_rows = cursor.fetchall()
        transcript = ""
        for row in transcript_rows:
            if row[0]:
                transcript += f"User: {row[0]}\n"
            if row[1]:
                transcript += f"AI: {row[1]}\n"
        calls_with_transcript.append(call + (transcript,))

    return render_template(
        'company/campaign_show.html',
        campaign=campaign,
        recipients=recipients,
        replies=calls_with_transcript
    )

@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()
    # Get the sent email (from campaign_recipients + campaign)
    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()
    # Get all replies from this recipient for this campaign
    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'),
                "from_email": r[2],
                'is_ai_response': r[3]
            } for r in replies
        ]
    })

@company_bp.route('/company/call-campaign', methods=['GET'])
@company_login_required
def call_campaign():
    cursor = mysql.connection.cursor()
    
    # Fetch contacts for the modal form
    cursor.execute("SELECT id, name, phone_number FROM contacts WHERE company_id = %s", (session['company_id'],))
    contacts = cursor.fetchall()

    # Fetch contact groups with counts
    cursor.execute("""
        SELECT g.id, g.name, COUNT(gc.contact_id) AS contact_count
        FROM contact_groups g
        LEFT JOIN group_contacts gc ON g.id = gc.group_id
        WHERE g.company_id = %s
        GROUP BY g.id, g.name
        ORDER BY g.name ASC
    """, (session['company_id'],))
    contact_groups = cursor.fetchall()

    # Check and update call statuses for in-progress calls
    check_vapi_call_statuses(session['company_id'])

    # Fetch existing call campaigns
    cursor.execute("""
        SELECT 
            cc.id, 
            cc.name, 
            cc.status, 
            cc.created_at,
            (SELECT COUNT(*) FROM campaign_calls WHERE campaign_id = cc.id) AS total_calls,
            (SELECT COUNT(*) FROM campaign_calls WHERE campaign_id = cc.id AND status != 'failed') AS completed_calls,
            (SELECT COUNT(*) FROM campaign_calls WHERE campaign_id = cc.id AND status = 'failed') AS failed_calls
        FROM call_campaigns cc
        WHERE cc.company_id = %s
        ORDER BY cc.created_at DESC
    """, (session['company_id'],))
    campaigns = cursor.fetchall()

    usage = get_call_minutes_usage(cursor, session['company_id'])
    active_subscription = usage['subscription']
    minute_limit = usage['limit_minutes']
    remaining_days = active_subscription['remaining_days'] if active_subscription else None

    def format_duration(seconds):
        minutes, secs = divmod(int(seconds), 60)
        parts = []
        if minutes:
            parts.append(f"{minutes} min{'s' if minutes != 1 else ''}")
        if secs or not parts:
            parts.append(f"{secs} sec{'s' if secs != 1 else ''}")
        return ' '.join(parts)

    used_readable = format_duration(usage['used_seconds'])
    remaining_readable = None
    if minute_limit is not None:
        remaining_readable = format_duration(usage['remaining_seconds'])

    campaigns_data = []
    for row in campaigns:
        row_list = list(row)
        if row_list[3] is not None:
            row_list[3] = row_list[3].isoformat()
        campaigns_data.append(row_list)

    cursor.close()
    
    return render_template(
        'company/call_campaign.html',
        contacts=contacts,
        campaigns=campaigns,
        campaigns_data=campaigns_data,
        call_minute_limit=minute_limit,
        used_minutes_readable=used_readable,
        remaining_minutes_readable=remaining_readable,
        remaining_days=remaining_days,
        contact_groups=contact_groups
    )

def check_vapi_call_statuses(company_id):
    """Check and update Vapi call statuses for a company"""
    cursor = mysql.connection.cursor()
    
    try:
        # Get all in-progress calls for this company
        cursor.execute("""
            SELECT cc.id, cc.call_sid, cc.campaign_id
            FROM campaign_calls cc
            JOIN call_campaigns c ON cc.campaign_id = c.id
            WHERE c.company_id = %s AND cc.status = 'in_progress' AND cc.call_sid IS NOT NULL
        """, (company_id,))
        in_progress_calls = cursor.fetchall()
        print(f"calls: {in_progress_calls}")
        if not in_progress_calls:
            return
        
        logger.info(f"Checking status for {len(in_progress_calls)} in-progress calls")
        VAPI_API_KEY = "c9988b99-66a9-416f-a994-af674238826d"
        
        headers = {
            "Authorization": f"Bearer {VAPI_API_KEY}",
            "Content-Type": "application/json"
        }
        
        for call in in_progress_calls:
            campaign_call_id, vapi_call_id, campaign_id = call
            
            try:
                # Get call status from Vapi API
                response = requests.get(
                    f"https://api.vapi.ai/call/{vapi_call_id}",
                    headers=headers
                )
                
                if response.status_code == 200:
                    call_data = response.json()
                    call_status = call_data.get('status')
                    ended_reason = (
                        call_data.get('endedReason')
                        or call_data.get('endReason')
                        or call_data.get('ended_reason')
                    )
                    logger.info(f"status: {call_status}, endedReason: {ended_reason}")
                    
                    # Human-friendly text for UI
                    human_reason_map = {
                        'customer-did-not-answer': 'Customer Did Not Answer',
                        'customer-ended-call': 'Customer Ended Call',
                        'assistant-completed': 'Assistant Completed',
                        'assistant-ended-call': 'Assistant Ended Call',
                        'busy': 'Busy',
                        'no-answer': 'Customer Did Not Answer',
                        'rejected': 'Rejected',
                        'canceled': 'Canceled',
                        'timeout': 'Timeout',
                        'error': 'Error',
                    }
                    
                    # Map to our DB status with special handling for "ended"
                    if call_status in ('initiated', 'ringing', 'answered', 'in-progress'):
                        db_status = 'in_progress'
                    elif call_status == 'completed':
                        db_status = 'completed'
                    elif call_status == 'ended':
                        # Decide based on endedReason instead of forcing completed
                        if ended_reason in ('assistant-completed', 'assistant-ended-call'):
                            db_status = 'completed'
                        elif ended_reason in ('customer-did-not-answer', 'no-answer', 'busy', 'rejected', 'canceled', 'timeout', 'error', 'customer-ended-call'):
                            db_status = 'failed'
                        else:
                            db_status = 'failed'
                    else:
                        db_status = 'failed'
                    
                    # Update call status
                    cursor.execute("""
                        UPDATE campaign_calls
                        SET status = %s
                        WHERE id = %s
                    """, (db_status, campaign_call_id))
                    
                    # If failed, persist the human-readable reason
                    if db_status == 'failed':
                        error_reason = human_reason_map.get(ended_reason, call_data.get('error') or ended_reason or call_status)
                        cursor.execute("""
                            UPDATE campaign_calls
                            SET error_message = %s
                            WHERE id = %s
                        """, (error_reason, campaign_call_id))
                    
                    # Handle completed calls only when truly completed
                    if db_status == 'completed':
                        handle_completed_call(call_data, campaign_call_id, vapi_call_id, cursor)
                    
                    # Update campaign status
                    update_campaign_status(campaign_id, cursor)
                    
            except Exception as e:
                logger.error(f"Error checking call {vapi_call_id}: {str(e)}")
        
        mysql.connection.commit()
        
    except Exception as e:
        logger.error(f"Error checking Vapi call statuses: {str(e)}")
        mysql.connection.rollback()
    finally:
        cursor.close()

def handle_completed_call(call_data, campaign_call_id, vapi_call_id, cursor):
    """Handle completed call - save recording and transcript"""
    transcript = call_data.get('transcript')
    recording_url = call_data.get('recordingUrl')
    duration_seconds = call_data.get('durationSeconds') or call_data.get('duration')
    try:
        duration_value = int(duration_seconds) if duration_seconds is not None else None
    except (ValueError, TypeError):
        duration_value = None
    
    if not recording_url and 'recording' in call_data:
        recording = call_data['recording']
        recording_url = recording.get('mono', {}).get('combinedUrl') or recording.get('stereoUrl')
    
    # Save recording
    relative_path = None
    if recording_url:
        # Generate a unique number for this recording file to prevent collisions
        import random
        unique_number = random.randint(100000, 999999)
        filename = f"recording_{campaign_call_id}_{unique_number}.mp3"
        upload_folder = os.path.join('app', 'static', 'uploads')
        filepath = os.path.join(upload_folder, filename)
        os.makedirs(upload_folder, exist_ok=True)
        try:
            recording_response = requests.get(recording_url, stream=True)
            if recording_response.status_code == 200:
                with open(filepath, 'wb') as f:
                    for chunk in recording_response.iter_content(chunk_size=8192):
                        f.write(chunk)
                relative_path = f"static/uploads/{filename}"
        except Exception as e:
            logger.error(f"Error downloading recording: {str(e)}")
    
    # Update with recording and transcript
    cursor.execute("""
        UPDATE campaign_calls
        SET recording_file_path = %s,
            response = %s,
            duration_seconds = CASE
                WHEN %s IS NOT NULL THEN COALESCE(duration_seconds, %s)
                ELSE duration_seconds
            END
        WHERE id = %s
    """, (relative_path, transcript, duration_value, duration_value, campaign_call_id))
    
    # Store transcript in conversation history
    if transcript:
        cursor.execute("SELECT contact_id FROM campaign_calls WHERE id = %s", (campaign_call_id,))
        row = cursor.fetchone()
        contact_id = row[0] if row else None
        
        if contact_id:
            lines = transcript.strip().split('\n')
            user_input = None
            ai_response = None
            for line in lines:
                if line.startswith("AI:"):
                    ai_response = line[3:].strip()
                    if user_input is not None:
                        cursor.execute("""
                            INSERT INTO call_conversation_history
                            (contact_id, call_sid, user_input, ai_response, call_status, created_at, campaign_call_id)
                            VALUES (%s, %s, %s, %s, %s, NOW(), %s)
                        """, (
                            contact_id,
                            vapi_call_id,
                            user_input,
                            ai_response,
                            'completed',
                            campaign_call_id
                        ))
                        user_input = None
                        ai_response = None
                elif line.startswith("User:"):
                    user_input = line[5:].strip()

        process_call_outcome(cursor, campaign_call_id, transcript)

    cursor.execute("""
        SELECT c.company_id, cc.campaign_id
        FROM campaign_calls cc
        JOIN call_campaigns c ON cc.campaign_id = c.id
        WHERE cc.id = %s
    """, (campaign_call_id,))
    company_row = cursor.fetchone()
    if company_row:
        enforce_call_minutes_limit(cursor, company_row[0], company_row[1])

def create_call_campaign(company_id, name, script, contacts, retry_attempts=1, retry_delay=30):
    """Create a new call campaign"""
    if not contacts:
        raise ValueError("At least one contact is required to create a campaign.")

    # Ensure unique order-preserving contacts
    seen = set()
    deduped_contacts = []
    for contact_id in contacts:
        if contact_id and contact_id not in seen:
            seen.add(contact_id)
            deduped_contacts.append(contact_id)

    cursor = mysql.connection.cursor()
    try:
        # Create campaign
        cursor.execute("""
            INSERT INTO call_campaigns 
            (company_id, name, script, retry_attempts, retry_delay, status)
            VALUES (%s, %s, %s, %s, %s, 'pending')
        """, (company_id, name, script, retry_attempts, retry_delay))
        
        # Get the auto-generated campaign ID
        campaign_id = cursor.lastrowid
        
        # Add contacts to campaign
        for contact_id in deduped_contacts:
            cursor.execute("""
                INSERT INTO campaign_calls 
                (campaign_id, contact_id, status)
                VALUES (%s, %s, 'pending')
            """, (campaign_id, contact_id))
        
        mysql.connection.commit()
        return campaign_id
    except Exception as e:
        mysql.connection.rollback()
        raise e
    finally:
        cursor.close()

@company_bp.route('/company/call-campaign/start', methods=['POST'])
@company_login_required
def start_call_campaign():
    data = request.get_json()
    
    cursor = mysql.connection.cursor()
    try:
        usage = get_call_minutes_usage(cursor, session['company_id'])
    finally:
        cursor.close()

    limit_minutes = usage['limit_minutes']
    remaining_seconds = usage['remaining_seconds']
    if limit_minutes is not None:
        if limit_minutes <= 0 or (remaining_seconds is not None and remaining_seconds <= 0):
            return jsonify({"error": "You have reached your call minutes limit. Please upgrade your package to launch more campaigns."}), 400

    selected_contacts = data.get('contacts') or []
    selected_groups = data.get('contact_groups') or []

    if not isinstance(selected_contacts, list) or not isinstance(selected_groups, list):
        return jsonify({"error": "Invalid contacts or groups selection."}), 400

    if not selected_contacts and not selected_groups:
        return jsonify({"error": "Please select at least one contact or group."}), 400

    cursor = mysql.connection.cursor()
    try:
        contact_ids = set()

        if selected_contacts:
            placeholders = ','.join(['%s'] * len(selected_contacts))
            cursor.execute(f"""
                SELECT id
                FROM contacts
                WHERE company_id = %s AND id IN ({placeholders})
            """, tuple([session['company_id'], *selected_contacts]))
            contact_ids.update(row[0] for row in cursor.fetchall())

        if selected_groups:
            placeholders = ','.join(['%s'] * len(selected_groups))
            cursor.execute(f"""
                SELECT DISTINCT gc.contact_id
                FROM group_contacts gc
                JOIN contact_groups g ON gc.group_id = g.id
                JOIN contacts c ON gc.contact_id = c.id
                WHERE g.company_id = %s
                  AND c.company_id = %s
                  AND gc.group_id IN ({placeholders})
            """, tuple([session['company_id'], session['company_id'], *selected_groups]))
            contact_ids.update(row[0] for row in cursor.fetchall())

        contact_ids = [cid for cid in contact_ids if cid]

        if not contact_ids:
            return jsonify({"error": "No valid contacts found for the selected options."}), 400

        campaign_id = create_call_campaign(
            company_id=session['company_id'],
            name=data['name'],
            script=data['script'],
            contacts=contact_ids,
            retry_attempts=int(data['retry_attempts']),
            retry_delay=int(data['retry_delay'])
        )
        # print(f"Campaign ID from start_call_campaign: {campaign_id}")
        # Start processing calls asynchronously
        process_campaign.delay(campaign_id)
        
        return jsonify({"success": True, "campaign_id": campaign_id})
    except Exception as e:
        return jsonify({"error": str(e)}), 400
    finally:
        cursor.close()
 
def update_campaign_status(campaign_id, cursor):
    """
    Update the overall status of a call campaign based on its calls.
    A campaign is 'completed' once there are no calls left in active states.
    Active states are only 'pending' and 'in_progress'. Everything else is terminal,
    including human-friendly reasons like 'Customer Ended Call'.
    """
    try:
        # Count total and how many are still active
        cursor.execute("""
            SELECT
                COUNT(*),
                SUM(CASE WHEN status IN ('pending','in_progress') THEN 1 ELSE 0 END)
            FROM campaign_calls
            WHERE campaign_id = %s
        """, (campaign_id,))
        
        result = cursor.fetchone()
        total_calls, active_calls = result if result else (0, 0)

        # Default to in_progress if any call is still active
        new_status = 'in_progress'
        if total_calls > 0 and active_calls == 0:
            new_status = 'completed'

        # Update only if changed
        cursor.execute("""
            UPDATE call_campaigns
            SET status = %s
            WHERE id = %s AND status != %s
        """, (new_status, campaign_id, new_status))
        
        logger.info(f"Updated campaign {campaign_id} status to {new_status}")

    except Exception as e:
        logger.error(f"Error updating campaign status for campaign {campaign_id}: {str(e)}")
VAPI_WS_URL = "wss://api.vapi.ai/audio-stream/4e29c224-4696-499f-8563-e8ae2270ef5a?apiKey=c9988b99-66a9-416f-a994-af674238826d"

@company_bp.route('/company/call-campaign/webhook', methods=['POST'])
def call_webhook():
    logger.info("Received webhook call from Twilio")
    response = VoiceResponse()
    connect = Connect()
    connect.stream(url=VAPI_WS_URL)
    response.append(connect)
    return str(response)

@company_bp.route('/company/call-campaign/status', methods=['POST'])
def handle_call_status():
    """Handle call status updates"""
    call_sid = request.values.get('CallSid')
    call_status = request.values.get('CallStatus')
    answered_by = request.values.get('AnsweredBy')
    call_duration = (
        request.values.get('CallDuration')
        or request.values.get('callDuration')
    )
    duration_value = None
    if call_duration:
        try:
            duration_value = int(float(call_duration))
        except (ValueError, TypeError):
            duration_value = None
    
    logger.info(f"Call status update - SID: {call_sid}, Status: {call_status}, AnsweredBy: {answered_by}")
    
    cursor = mysql.connection.cursor()
    try:
        # First, get the campaign_id for this call
        cursor.execute("SELECT campaign_id FROM campaign_calls WHERE call_sid = %s", (call_sid,))
        result = cursor.fetchone()
        
        if not result:
            logger.warning(f"No campaign_id found for call SID {call_sid}")
            return '', 200 # Nothing to do
        
        campaign_id = result[0]
        
        # Update call status
        cursor.execute("""
            UPDATE campaign_calls
            SET status = %s,
                duration_seconds = CASE
                    WHEN %s IS NOT NULL THEN %s
                    ELSE duration_seconds
                END
            WHERE call_sid = %s
        """, (call_status, duration_value, duration_value, call_sid))
        
        # If call was answered by a human, trigger the webhook again
        if call_status == 'in-progress' and answered_by == 'human':
            # Make a request to the webhook URL to start the conversation
            webhook_url = f"{Config.BASE_URL}/company/call-campaign/webhook"
            requests.post(webhook_url, data={'CallSid': call_sid, 'CallStatus': 'in-progress'})
        
        # Update the overall campaign status
        update_campaign_status(campaign_id, cursor)
        
        mysql.connection.commit()
    except Exception as e:
        logger.error(f"Error updating call status: {str(e)}")
    finally:
        cursor.close()
    
    return '', 200

@company_bp.route('/company/contact-groups/list')
@company_login_required
def list_contact_groups():
    try:
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT g.id, g.name, g.created_at, COUNT(gc.contact_id) as contact_count
            FROM contact_groups g
            LEFT JOIN group_contacts gc ON g.id = gc.group_id
            WHERE g.company_id = %s
            GROUP BY g.id, g.name, g.created_at
            ORDER BY g.created_at DESC
        """, (session['company_id'],))
        
        groups = cursor.fetchall()
        return jsonify({
            "success": True,
            "groups": [{
                "id": group[0],
                "name": group[1],
                "created_at": group[2],
                "contact_count": group[3]
            } for group in groups]
        })
    except Exception as e:
        logger.error(f"Error listing contact groups: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contact-groups/add', methods=['POST'])
@company_login_required
def add_contact_group():
    try:
        data = request.json
        name = data.get('name')
        contact_ids = data.get('contact_ids', [])

        if not name:
            return jsonify({
                "success": False,
                "error": "Group name is required"
            }), 400

        if not contact_ids:
            return jsonify({
                "success": False,
                "error": "At least one contact must be selected"
            }), 400

        cursor = mysql.connection.cursor()
        group_id = str(uuid.uuid4())
        
        # Create the group
        cursor.execute("""
            INSERT INTO contact_groups (id, company_id, name)
            VALUES (%s, %s, %s)
        """, (group_id, session['company_id'], name))
        id = str(uuid.uuid4())
        for contact_id in contact_ids:
            cursor.execute("""
                INSERT INTO group_contacts (id, group_id, contact_id)
                VALUES (%s, %s, %s)
            """, (str(uuid.uuid4()), group_id, contact_id))  # Generate new UUID for each contact

        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": "Contact group created successfully"
        })

    except Exception as e:
        logger.error(f"Error creating contact group: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contact-groups/<group_id>')
@company_login_required
def get_contact_group(group_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Get group details
        cursor.execute("""
            SELECT g.name, g.created_at
            FROM contact_groups g
            WHERE g.id = %s AND g.company_id = %s
        """, (group_id, session['company_id']))
        
        group = cursor.fetchone()
        if not group:
            return jsonify({
                "success": False,
                "error": "Group not found"
            }), 404

        # Get contacts in the group
        cursor.execute("""
            SELECT c.name, c.phone_number
            FROM contacts c
            JOIN group_contacts gc ON c.id = gc.contact_id
            WHERE gc.group_id = %s
        """, (group_id,))
        
        contacts = cursor.fetchall()
        
        return jsonify({
            "success": True,
            "group": {
                "name": group[0],
                "created_at": group[1],
                "contacts": [{
                    "name": contact[0],
                    "phone_number": contact[1]
                } for contact in contacts]
            }
        })

    except Exception as e:
        logger.error(f"Error getting contact group: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contact-groups/<group_id>', methods=['DELETE'])
@company_login_required
def delete_contact_group(group_id):
    try:
        cursor = mysql.connection.cursor()
        
        # Delete group contacts first
        cursor.execute("""
            DELETE FROM group_contacts
            WHERE group_id = %s
        """, (group_id,))
        
        # Delete the group
        cursor.execute("""
            DELETE FROM contact_groups
            WHERE id = %s AND company_id = %s
        """, (group_id, session['company_id']))
        
        mysql.connection.commit()
        return jsonify({
            "success": True,
            "message": "Contact group deleted successfully"
        })

    except Exception as e:
        logger.error(f"Error deleting contact group: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/contact-groups')
@company_login_required
def contact_groups():
    return render_template('company/groups.html')

@company_bp.route('/company/call-campaign/recording-complete', methods=['POST'])
def handle_recording_complete():
    """
    Handle the callback from Twilio when a call recording is ready.
    This endpoint downloads the recording and saves the file path to the database.
    """
    call_sid = request.values.get('CallSid')
    recording_url = request.values.get('RecordingUrl')

    logger.info(f"Recording complete for Call SID: {call_sid}. URL: {recording_url}")

    if not recording_url:
        logger.warning(f"No RecordingUrl received in callback for call SID: {call_sid}.")
        return '', 200

    cursor = mysql.connection.cursor()
    try:
        # 1. Find the campaign/company for this call_sid
        cursor.execute("""
            SELECT c.company_id, cs.twilio_account_sid, cs.twilio_auth_token
            FROM campaign_calls cc
            JOIN call_campaigns c ON cc.campaign_id = c.id
            JOIN company_settings cs ON c.company_id = cs.company_id
            WHERE cc.call_sid = %s
        """, (call_sid,))
        result = cursor.fetchone()

        if not result:
            logger.error(f"Could not find Twilio credentials for call_sid {call_sid}")
            return '', 200

        company_id, account_sid, auth_token = result

        # 2. Download the recording with the correct credentials
        if not recording_url.endswith('.mp3'):
            recording_url += '.mp3'

        recording_response = requests.get(recording_url, stream=True, auth=(account_sid, auth_token))

        if recording_response.status_code == 200:
            # Create a unique filename based on the call SID
            filename = f"{call_sid}.mp3"
            filepath = os.path.join(UPLOAD_FOLDER_CALL_RECORDINGS, filename)

            # Ensure the directory exists
            os.makedirs(UPLOAD_FOLDER_CALL_RECORDINGS, exist_ok=True)

            # Save the file
            with open(filepath, 'wb') as f:
                for chunk in recording_response.iter_content(chunk_size=8192):
                    f.write(chunk)

            logger.info(f"Call recording for SID {call_sid} saved to: {filepath}")

            # Store the relative path for easier serving
            relative_path = f"static/uploads/{filename}"

            recording_duration = request.values.get('RecordingDuration')
            duration_value = None
            if recording_duration:
                try:
                    duration_value = int(float(recording_duration))
                except (ValueError, TypeError):
                    duration_value = None

            cursor.execute("""
                UPDATE campaign_calls
                SET recording_file_path = %s,
                    duration_seconds = CASE
                        WHEN %s IS NOT NULL THEN COALESCE(duration_seconds, %s)
                        ELSE duration_seconds
                    END
                WHERE call_sid = %s
            """, (relative_path, duration_value, duration_value, call_sid))
            mysql.connection.commit()
        else:
            logger.error(f"Failed to download recording from {recording_url}. Status: {recording_response.status_code}")

    except Exception as e:
        logger.error(f"Error handling recording callback for call SID {call_sid}: {str(e)}")
    finally:
        cursor.close()

    return '', 200

@company_bp.route('/company/call-campaign/<campaign_id>/view')
@company_login_required
def view_call_campaign(campaign_id):
    cursor = mysql.connection.cursor()
    try:
        # Get campaign info
        cursor.execute("""
            SELECT name, created_at FROM call_campaigns WHERE id = %s
        """, (campaign_id,))
        campaign = cursor.fetchone()

        # Get all calls for this campaign
        cursor.execute("""
            SELECT cc.id, ct.name, ct.phone_number, cc.status, cc.attempts, cc.error_message, cc.response, cc.recording_file_path, cc.outcome, cc.outcome_details
            FROM campaign_calls cc
            JOIN contacts ct ON cc.contact_id = ct.id
            WHERE cc.campaign_id = %s
            ORDER BY cc.created_at ASC
        """, (campaign_id,))
        calls = cursor.fetchall()
        print(f"calls: {calls}")
    finally:
        cursor.close()
    return render_template('company/call_campaign_view.html', campaign=campaign, calls=calls)

@company_bp.route('/company/call-campaign/<call_id>/transcript')
@company_login_required
def view_call_transcript(call_id):
    print(f"call_id: {call_id}")
    cursor = mysql.connection.cursor()
    # Fetch call info (optional, for header)
    cursor.execute("""
        SELECT cc.id, ct.name, ct.phone_number, cc.status, cc.campaign_id
        FROM campaign_calls cc
        JOIN contacts ct ON cc.contact_id = ct.id
        WHERE cc.id = %s
    """, (call_id,))
    call = cursor.fetchone()
    print(f"call: {call}")

    # Fetch full conversation history
    cursor.execute("""
        SELECT user_input, ai_response, created_at
        FROM call_conversation_history
        WHERE campaign_call_id = %s
        ORDER BY created_at ASC
    """, (call_id,))
    transcript = cursor.fetchall()
    cursor.close()
    return render_template('company/call_transcript.html', call=call, transcript=transcript, campaign_id=call[4])

@company_bp.route('/company/bookings', methods=['GET'])
@company_login_required
def bookings():
    cursor = mysql.connection.cursor()
    
    # Fetch bookings with contact and campaign information
    cursor.execute("""
        SELECT 
            b.id,
            b.customer_email,
            b.meeting_date_raw,
            b.meeting_time_raw,
            b.additional_details,
            b.outcome,
            b.created_at,
            ct.name AS contact_name,
            ct.phone_number AS contact_phone,
            ct.email AS contact_email,
            cc.name AS campaign_name,
            cc_call.status AS call_status
        FROM bookings b
        JOIN contacts ct ON b.contact_id = ct.id
        LEFT JOIN campaign_calls cc_call ON b.campaign_call_id = cc_call.id
        LEFT JOIN call_campaigns cc ON cc_call.campaign_id = cc.id
        WHERE b.company_id = %s
        ORDER BY b.created_at DESC
    """, (session['company_id'],))
    
    bookings_data = cursor.fetchall()
    cursor.close()
    
    return render_template('company/bookings.html', bookings=bookings_data)

@company_bp.route('/company/support-tickets', methods=['GET'])
@company_login_required
def support_tickets():
    cursor = mysql.connection.cursor()
    
    # Fetch tickets for the company with unread count
    cursor.execute("""
        SELECT 
            id, 
            title, 
            reason, 
            priority, 
            status, 
            created_at, 
            updated_at,
            COALESCE((
                SELECT COUNT(*) 
                FROM support_ticket_messages stm
                LEFT JOIN support_ticket_read_status str ON str.ticket_id = stm.ticket_id 
                    AND str.reader_type = 'company' 
                    AND str.reader_id = %s
                WHERE stm.ticket_id = support_tickets.id 
                    AND stm.sender_type = 'admin'
                    AND (str.last_read_at IS NULL OR stm.created_at > str.last_read_at)
            ), 0) AS unread_count
        FROM support_tickets
        WHERE company_id = %s
        ORDER BY created_at DESC
    """, (session['company_id'], session['company_id']))
    
    tickets = cursor.fetchall()
    cursor.close()
    
    return render_template('company/support_tickets.html', tickets=tickets)

@company_bp.route('/company/support-tickets/create', methods=['POST'])
@company_login_required
def create_support_ticket():
    try:
        data = request.json
        title = data.get('title', '').strip()
        reason = data.get('reason', '').strip()
        priority = data.get('priority', 'medium')
        
        if not title or not reason:
            return jsonify({
                "success": False,
                "error": "Title and reason are required"
            }), 400
        
        if priority not in ['low', 'medium', 'high', 'urgent']:
            priority = 'medium'
        
        ticket_id = str(uuid.uuid4())
        cursor = mysql.connection.cursor()
        cursor.execute("""
            INSERT INTO support_tickets (id, company_id, title, reason, priority, status)
            VALUES (%s, %s, %s, %s, %s, 'open')
        """, (ticket_id, session['company_id'], title, reason, priority))
        
        mysql.connection.commit()
        cursor.close()
        
        return jsonify({
            "success": True,
            "message": "Ticket created successfully",
            "ticket_id": ticket_id
        })
    except Exception as e:
        logger.error(f"Error creating support ticket: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

@company_bp.route('/company/support-tickets/<ticket_id>', methods=['GET'])
@company_login_required
def view_support_ticket(ticket_id):
    cursor = mysql.connection.cursor()
    
    # Fetch ticket
    cursor.execute("""
        SELECT id, title, reason, priority, status, created_at, updated_at
        FROM support_tickets
        WHERE id = %s AND company_id = %s
    """, (ticket_id, session['company_id']))
    
    ticket = cursor.fetchone()
    
    if not ticket:
        cursor.close()
        return redirect(url_for('company.support_tickets'))
    
    # Mark messages as read for company
    read_status_id = str(uuid.uuid4())
    cursor.execute("""
        INSERT INTO support_ticket_read_status (id, ticket_id, reader_type, reader_id, last_read_at)
        VALUES (%s, %s, 'company', %s, NOW())
        ON DUPLICATE KEY UPDATE last_read_at = NOW(), updated_at = NOW()
    """, (read_status_id, ticket_id, session['company_id']))
    mysql.connection.commit()
    
    # Fetch messages
    cursor.execute("""
        SELECT id, sender_type, sender_id, message, created_at
        FROM support_ticket_messages
        WHERE ticket_id = %s
        ORDER BY created_at ASC
    """, (ticket_id,))
    
    messages = cursor.fetchall()
    
    # Get company name
    cursor.execute("SELECT name FROM companies WHERE id = %s", (session['company_id'],))
    company = cursor.fetchone()
    company_name = company[0] if company else "Company"
    
    cursor.close()
    
    return render_template('company/support_ticket_detail.html', 
                         ticket=ticket, 
                         messages=messages,
                         company_name=company_name)

@company_bp.route('/company/support-tickets/<ticket_id>/message', methods=['POST'])
@company_login_required
def add_ticket_message(ticket_id):
    try:
        data = request.json
        message = data.get('message', '').strip()
        
        if not message:
            return jsonify({
                "success": False,
                "error": "Message is required"
            }), 400
        
        # Verify ticket belongs to company and is not closed
        cursor = mysql.connection.cursor()
        cursor.execute("""
            SELECT id, status FROM support_tickets
            WHERE id = %s AND company_id = %s
        """, (ticket_id, session['company_id']))
        
        ticket = cursor.fetchone()
        if not ticket:
            cursor.close()
            return jsonify({
                "success": False,
                "error": "Ticket not found"
            }), 404
        
        if ticket[1] == 'closed':
            cursor.close()
            return jsonify({
                "success": False,
                "error": "Cannot send messages to a closed ticket"
            }), 400
        
        message_id = str(uuid.uuid4())
        cursor.execute("""
            INSERT INTO support_ticket_messages (id, ticket_id, sender_type, sender_id, message)
            VALUES (%s, %s, 'company', %s, %s)
        """, (message_id, ticket_id, session['company_id'], message))
        
        # Update ticket updated_at
        cursor.execute("""
            UPDATE support_tickets
            SET updated_at = NOW()
            WHERE id = %s
        """, (ticket_id,))
        
        mysql.connection.commit()
        cursor.close()
        
        return jsonify({
            "success": True,
            "message": "Message sent successfully",
            "message_id": message_id
        })
    except Exception as e:
        logger.error(f"Error adding ticket message: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

def get_company_info_and_documents(company_id):
    from app import mysql
    cursor = mysql.connection.cursor()
    try:
        # Fetch company info
        cursor.execute("""
            SELECT name, instructions, website, welcome_message
            FROM companies WHERE id = %s
        """, (company_id,))
        company = cursor.fetchone()
        if not company:
            return None

        # Fetch company documents
        cursor.execute("""
            SELECT file_content FROM company_documents WHERE company_id = %s
        """, (company_id,))
        documents = cursor.fetchall()
        doc_content = "\n---\n".join([doc[0] for doc in documents]) if documents else ""

        # Format info for Vapi
        company_info = {
            "name": company[0],
            "instructions": company[1],
            "website": company[2],
            "welcome_message": company[3],
            "documents": doc_content
        }
        return company_info
    finally:
        cursor.close()

@company_bp.route('/vapi/webhook', methods=['POST'])
def vapi_webhook():
    data = request.json
    # logger.info(f"Received Vapi webhook: {data}")
    
    event = data.get('message', data)

    # Try to get metadata from top-level, then from call object
    metadata = event.get('metadata', {})
    if not metadata and 'call' in event and 'metadata' in event['call']:
        metadata = event['call']['metadata']

    campaign_call_id = metadata.get('campaign_call_id')
    vapi_call_id = event.get('id') or event.get('callId')

    if not campaign_call_id:
        logger.warning("No campaign_call_id in Vapi webhook metadata.")
        return '', 200

    # Get event type and status
    event_type = event.get('type') or event.get('eventType')
    call_status = event.get('status') or event.get('callStatus')
    ended_reason = (
        event.get('endedReason')
        or event.get('endReason')
        or (event.get('call') or {}).get('endedReason')
    )
    
    logger.info(f"Vapi webhook - Event: {event_type}, Status: {call_status}, EndedReason: {ended_reason}, Campaign Call ID: {campaign_call_id}")

    cursor = mysql.connection.cursor()
    try:
        # Human-friendly text for UI
        human_reason_map = {
            'customer-did-not-answer': 'Customer Did Not Answer',
            'customer-ended-call': 'Customer Ended Call',
            'assistant-completed': 'Assistant Completed',
            'assistant-ended-call': 'Assistant Ended Call',
            'busy': 'Busy',
            'no-answer': 'Customer Did Not Answer',
            'rejected': 'Rejected',
            'canceled': 'Canceled',
            'timeout': 'Timeout',
            'error': 'Error',
        }
        human_label = human_reason_map.get(ended_reason, call_status or event_type or 'ended')

        # Decide what to store in status
        # Special case: if customer ended the call, reflect that as the status and clear error
        if ended_reason == 'customer-ended-call':
            db_status = human_label
            is_error = False
        # Completed by assistant also should not be error
        elif call_status == 'completed' or ended_reason in ('assistant-completed', 'assistant-ended-call'):
            db_status = human_reason_map.get(ended_reason, 'completed')
            is_error = False
        # Other end reasons are considered non-success but we still show human label if available
        elif call_status == 'ended' and ended_reason:
            db_status = human_label
            is_error = ended_reason not in ('customer-did-not-answer',)
        else:
            # default failure
            db_status = 'failed'
            is_error = True
        
        # Update the individual call status and save the Vapi id
        cursor.execute("""
            UPDATE campaign_calls
            SET status = %s, call_sid = %s
            WHERE id = %s
        """, (db_status, vapi_call_id, campaign_call_id))
        
        # Manage error_message field
        if is_error and db_status == 'failed':
            error_reason = human_reason_map.get(ended_reason, event.get('error') or event.get('reason') or call_status)
            cursor.execute("""
                UPDATE campaign_calls
                SET error_message = %s
                WHERE id = %s
            """, (error_reason, campaign_call_id))
        else:
            # Clear any previous error when the call actually connected and ended normally
            cursor.execute("""
                UPDATE campaign_calls
                SET error_message = NULL
                WHERE id = %s
            """, (campaign_call_id,))
        
        # Always attempt to save transcript/recording on end-of-call reports
        if event_type in ('end-of-call-report', 'ended', 'completed') or call_status in ('completed', 'ended'):
            transcript = event.get('transcript')
            recording_url = event.get('recordingUrl')
            if not recording_url and 'recording' in event:
                recording = event['recording']
                recording_url = recording.get('mono', {}).get('combinedUrl') or recording.get('stereoUrl')

            # Save recording
            relative_path = None
            if recording_url:
                # Generate a unique number for this recording file to prevent collisions
                import random
                unique_number = random.randint(100000, 999999)
                filename = f"recording_{campaign_call_id}_{unique_number}.mp3"
                upload_folder = os.path.join('app', 'static', 'uploads')
                filepath = os.path.join(upload_folder, filename)
                os.makedirs(upload_folder, exist_ok=True)
                try:
                    recording_response = requests.get(recording_url, stream=True)
                    if recording_response.status_code == 200:
                        with open(filepath, 'wb') as f:
                            for chunk in recording_response.iter_content(chunk_size=8192):
                                f.write(chunk)
                        logger.info(f"Vapi call recording saved to: {filepath}")
                        relative_path = f"static/uploads/{filename}"
                    else:
                        logger.error(f"Failed to download Vapi recording from {recording_url}. Status: {recording_response.status_code}")
                except Exception as e:
                    logger.error(f"Error downloading Vapi recording: {str(e)}")

            duration_seconds = event.get('durationSeconds') or event.get('duration') or (event.get('call') or {}).get('duration')
            logger.info(f"Duration seconds: {duration_seconds}")
            logger.info(f"Event: {event}")
            try:
                duration_value = int(float(duration_seconds)) if duration_seconds is not None else None
            except (ValueError, TypeError):
                duration_value = None

            # Update with recording, transcript, and duration if present
            if relative_path or transcript or duration_value is not None:
                cursor.execute("""
                    UPDATE campaign_calls
                    SET recording_file_path = %s,
                        response = %s,
                        duration_seconds = CASE
                            WHEN %s IS NOT NULL THEN COALESCE(duration_seconds, %s)
                            ELSE duration_seconds
                        END
                    WHERE id = %s
                """, (relative_path, transcript, duration_value, duration_value, campaign_call_id))

            # Store transcript in call_conversation_history if available
            if transcript:
                cursor.execute("SELECT contact_id FROM campaign_calls WHERE id = %s", (campaign_call_id,))
                row = cursor.fetchone()
                contact_id = row[0] if row else None

                if contact_id:
                    lines = transcript.strip().split('\n')
                    user_input = None
                    ai_response = None
                    for line in lines:
                        if line.startswith("AI:"):
                            ai_response = line[3:].strip()
                            if user_input is not None:
                                cursor.execute("""
                                    INSERT INTO call_conversation_history
                                    (contact_id, call_sid, user_input, ai_response, call_status, created_at, campaign_call_id)
                                    VALUES (%s, %s, %s, %s, %s, NOW(), %s)
                                """, (
                                    contact_id,
                                    vapi_call_id,
                                    user_input,
                                    ai_response,
                                    call_status,
                                    campaign_call_id
                                ))
                                user_input = None
                                ai_response = None
                        elif line.startswith("User:"):
                            user_input = line[5:].strip()

                process_call_outcome(cursor, campaign_call_id, transcript)

        # Update the overall campaign status
        cursor.execute("SELECT campaign_id FROM campaign_calls WHERE id = %s", (campaign_call_id,))
        row = cursor.fetchone()
        campaign_id = row[0] if row else None
        
        if campaign_id:
            update_campaign_status(campaign_id, cursor)

        mysql.connection.commit()
        logger.info(f"Successfully processed Vapi webhook for campaign_call_id {campaign_call_id}")
        
    except Exception as e:
        logger.error(f"Error processing Vapi webhook: {str(e)}")
        mysql.connection.rollback()
    finally:
        cursor.close()
    
    return '', 200

@company_bp.route('/company/refresh-vector-store', methods=['POST'])
@company_login_required
def refresh_company_vector_store():
    """
    IMPROVED: Manually refresh the vector store for the logged-in company.
    This ensures all documents are up-to-date in the RAG system.
    """
    try:
        company_id = session['company_id']
        logger.info(f"Manual vector store refresh requested for company {company_id}")
        
        # Get company name for logging
        cursor = mysql.connection.cursor()
        cursor.execute("SELECT name FROM companies WHERE id = %s", (company_id,))
        company_result = cursor.fetchone()
        company_name = company_result[0] if company_result else "Unknown"
        
        # Check if company has documents with detailed info
        cursor.execute("""
            SELECT COUNT(*), 
                   COUNT(CASE WHEN file_content IS NOT NULL AND LENGTH(file_content) > 10 THEN 1 END) as valid_docs,
                   SUM(LENGTH(file_content)) as total_content_length
            FROM company_documents 
            WHERE company_id = %s
        """, (company_id,))
        
        doc_stats = cursor.fetchone()
        total_docs, valid_docs, total_content_length = doc_stats
        
        logger.info(f"Document stats - Total: {total_docs}, Valid: {valid_docs}, Content length: {total_content_length}")
        
        if total_docs == 0:
            return jsonify({
                "success": False, 
                "error": "No documents found for this company. Please upload some documents first."
            }), 400
        
        if valid_docs == 0:
            return jsonify({
                "success": False, 
                "error": "No valid documents found. All documents appear to be empty or corrupted."
            }), 400
        
        # Delete existing vector store
        cursor.execute("""
            DELETE FROM company_vector_stores 
            WHERE company_id = %s
        """, (company_id,))
        mysql.connection.commit()
        
        # Create new vector store with improved processing
        from app.routes.api import create_or_get_vector_store, execute_query
        vector_store = create_or_get_vector_store(company_id, execute_query)
        
        if vector_store:
            # Get final document count from vector store
            cursor.execute("""
                SELECT COUNT(*) 
                FROM company_documents 
                WHERE company_id = %s AND file_content IS NOT NULL AND LENGTH(file_content) > 10
            """, (company_id,))
            processed_docs = cursor.fetchone()[0]
            
            logger.info(f"Vector store refreshed successfully for {company_name} (ID: {company_id})")
            logger.info(f"Processed {processed_docs} documents with total content length: {total_content_length}")
            
            return jsonify({
                "success": True,
                "message": f"Vector store refreshed successfully for {company_name}",
                "details": {
                    "company_name": company_name,
                    "total_documents": total_docs,
                    "valid_documents": valid_docs,
                    "documents_processed": processed_docs,
                    "total_content_length": total_content_length,
                    "timestamp": datetime.now().isoformat()
                }
            })
        else:
            logger.error(f"Failed to create vector store for company {company_id}")
            return jsonify({
                "success": False,
                "error": "Failed to create vector store. Please check your documents and try again."
            }), 500
            
    except Exception as e:
        logger.error(f"Error refreshing vector store for company {company_id}: {str(e)}")
        return jsonify({
            "success": False,
            "error": f"An error occurred while refreshing the vector store: {str(e)}"
        }), 500


@company_bp.route('/company/packages')
@company_login_required
def packages():
    cursor = mysql.connection.cursor()
    try:
        cursor.execute("""
            SELECT id, title, description, no_of_contacts, no_of_mins, price, no_of_files
            FROM packages
            ORDER BY id ASC
        """)
        rows = cursor.fetchall()
        currency_code = Config.STRIPE_CURRENCY
        cursor.execute("""
            UPDATE company_package_payments
            SET status = 'expired',
                updated_at = NOW()
            WHERE company_id = %s
              AND status = 'succeeded'
              AND expires_at IS NOT NULL
              AND expires_at <= NOW()
        """, (session['company_id'],))
        mysql.connection.commit()

        active_subscription = get_active_subscription(cursor, session['company_id'])
        active_package_id = active_subscription['package_id'] if active_subscription else None
        remaining_days = active_subscription['remaining_days'] if active_subscription else None
        active_expires_at = active_subscription['expires_at'] if active_subscription else None
        packages = []
        for row in rows:
            price_value = row[5] or 0
            try:
                price_decimal = Decimal(str(price_value))
            except (InvalidOperation, TypeError):
                price_decimal = Decimal("0")

            try:
                file_limit_value = int(row[6]) if row[6] is not None else None
            except (TypeError, ValueError):
                file_limit_value = None

            packages.append({
                "id": row[0],
                "title": row[1],
                "description": row[2],
                "no_of_contacts": row[3],
                "no_of_mins": row[4],
                "price": price_decimal,
                "price_display": format_currency(price_decimal, currency_code),
                "is_active": active_package_id == row[0],
                "remaining_days": remaining_days if active_package_id == row[0] else None,
                "file_limit": file_limit_value,
            })
        return render_template(
            'company/packages.html',
            packages=packages,
            stripe_publishable_key=Config.STRIPE_PUBLISHABLE_KEY,
            currency=currency_code.upper(),
            active_package_id=active_package_id,
            remaining_days=remaining_days,
            active_expires_at=active_expires_at
        )
    except Exception as exc:
        logger.error(f"Error loading company packages: {str(exc)}")
        return render_template('company/packages.html', packages=[], error="Unable to load packages."), 500
    finally:
        cursor.close()


@company_bp.route('/company/packages/<int:package_id>/subscribe', methods=['POST'])
@company_login_required
def subscribe_package(package_id):
    if not Config.STRIPE_SECRET_KEY or not stripe_available:
        return jsonify({"success": False, "error": "Stripe configuration is missing. Please contact support."}), 500

    cursor = mysql.connection.cursor()
    try:
        cursor.execute("""
            SELECT id, expires_at
            FROM company_package_payments
            WHERE company_id = %s
              AND status IN ('succeeded', 'pending')
              AND (expires_at IS NULL OR expires_at > NOW() OR status = 'pending')
            ORDER BY created_at DESC
            LIMIT 1
        """, (session['company_id'],))
        existing_active = cursor.fetchone()
        if existing_active:
            return jsonify({
                "success": False,
                "error": "You already have an active subscription. Please wait until it expires before purchasing another package."
            }), 400

        cursor.execute("""
            SELECT title, price FROM packages WHERE id = %s
        """, (package_id,))
        package = cursor.fetchone()
        if not package:
            return jsonify({"success": False, "error": "Package not found."}), 404
        package_title = package[0]
        try:
            package_price = Decimal(str(package[1]))
        except (InvalidOperation, TypeError):
            return jsonify({"success": False, "error": "Invalid package price."}), 400

        if package_price <= 0:
            return jsonify({"success": False, "error": "Package price must be greater than zero."}), 400

        cursor.execute("SELECT email FROM companies WHERE id = %s", (session['company_id'],))
        company_row = cursor.fetchone()
        customer_email = company_row[0] if company_row else None

        stripe.api_key = Config.STRIPE_SECRET_KEY
        success_url = Config.STRIPE_SUCCESS_URL or url_for('company.package_checkout_success', package_id=package_id, _external=True)
        cancel_url = Config.STRIPE_CANCEL_URL or url_for('company.package_checkout_cancel', package_id=package_id, _external=True)

        amount_cents = int((package_price * Decimal('100')).quantize(Decimal('1'), rounding=ROUND_HALF_UP))

        checkout_session = stripe.checkout.Session.create(
            mode='payment',
            line_items=[
                {
                    'price_data': {
                        'currency': Config.STRIPE_CURRENCY.lower(),
                        'product_data': {
                            'name': package_title,
                            'metadata': {
                                'package_id': str(package_id)
                            }
                        },
                        'unit_amount': amount_cents
                    },
                    'quantity': 1
                }
            ],
            success_url=success_url + '?session_id={CHECKOUT_SESSION_ID}',
            cancel_url=cancel_url,
            customer_email=customer_email
        )

        payment_record_id = str(uuid.uuid4())
        cursor.execute("""
            INSERT INTO company_package_payments (
                id,
                company_id,
                package_id,
                stripe_session_id,
                amount,
                currency,
                status,
                created_at,
                updated_at
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), NOW())
        """, (
            payment_record_id,
            session['company_id'],
            package_id,
            checkout_session.id,
            float(package_price),
            Config.STRIPE_CURRENCY.upper(),
            'pending'
        ))
        mysql.connection.commit()

        return jsonify({
            "success": True,
            "checkout_url": checkout_session.url
        })
    except stripe.error.StripeError as stripe_error:
        logger.error(f"Stripe error subscribing package {package_id}: {stripe_error}")
        return jsonify({"success": False, "error": "Unable to initiate payment. Please try again later."}), 500
    except Exception as exc:
        logger.error(f"Error creating Stripe checkout session: {str(exc)}")
        return jsonify({"success": False, "error": "Unexpected error while processing payment."}), 500
    finally:
        cursor.close()


@company_bp.route('/company/packages/<int:package_id>/success')
@company_login_required
def package_checkout_success(package_id):
    session_id = request.args.get('session_id')
    cursor = None
    if session_id and stripe_available and Config.STRIPE_SECRET_KEY:
        try:
            stripe.api_key = Config.STRIPE_SECRET_KEY
            checkout_session = stripe.checkout.Session.retrieve(session_id)
            payment_intent_id = checkout_session.get('payment_intent')
            cursor = mysql.connection.cursor()
            cursor.execute("""
                UPDATE company_package_payments
                SET status = %s,
                    stripe_payment_intent_id = %s,
                    completed_at = NOW(),
                    started_at = NOW(),
                    expires_at = DATE_ADD(NOW(), INTERVAL 30 DAY),
                    updated_at = NOW()
                WHERE stripe_session_id = %s AND company_id = %s
            """, (
                'succeeded',
                payment_intent_id,
                session_id,
                session['company_id']
            ))
            mysql.connection.commit()

            cursor.execute("""
                UPDATE company_package_payments
                SET status = 'expired',
                    updated_at = NOW()
                WHERE company_id = %s
                  AND status = 'succeeded'
                  AND (expires_at IS NULL OR expires_at > NOW())
                  AND stripe_session_id != %s
            """, (session['company_id'], session_id))
            mysql.connection.commit()

            cursor.execute("""
                SELECT
                    c.name,
                    c.email,
                    p.title,
                    p.description,
                    p.no_of_contacts,
                    p.no_of_mins,
                    p.no_of_files,
                    p.price,
                    cpp.amount,
                    cpp.currency,
                    cpp.status,
                    cpp.started_at,
                    cpp.expires_at,
                    cpp.created_at,
                    cpp.stripe_payment_intent_id,
                    cpp.stripe_session_id
                FROM company_package_payments cpp
                JOIN companies c ON cpp.company_id = c.id
                JOIN packages p ON cpp.package_id = p.id
                WHERE cpp.stripe_session_id = %s
                  AND cpp.company_id = %s
                LIMIT 1
            """, (session_id, session['company_id']))
            purchase_details = cursor.fetchone()

            if purchase_details:
                admin_settings = get_admin_settings()
                recipients = []
                company_email = purchase_details[1]
                if company_email:
                    recipients.append(company_email)
                if admin_settings and admin_settings.get("admin_email"):
                    recipients.append(admin_settings["admin_email"])

                if recipients:
                    currency_code = (purchase_details[9] or Config.STRIPE_CURRENCY or "GBP").upper()

                    def _safe_decimal(value, default="0"):
                        try:
                            return Decimal(str(value))
                        except (InvalidOperation, TypeError):
                            return Decimal(default)

                    package_contacts_val = purchase_details[4]
                    if package_contacts_val is None:
                        package_contacts = "Unlimited"
                    else:
                        try:
                            package_contacts = f"{int(package_contacts_val):,}"
                        except (TypeError, ValueError):
                            package_contacts = str(package_contacts_val)

                    package_minutes_val = purchase_details[5]
                    if package_minutes_val is None:
                        package_minutes = "Unlimited"
                    else:
                        try:
                            package_minutes = f"{int(package_minutes_val):,}"
                        except (TypeError, ValueError):
                            package_minutes = str(package_minutes_val)

                    package_files_val = purchase_details[6]
                    if package_files_val is None:
                        package_file_limit = "Unlimited"
                    else:
                        try:
                            package_file_limit = f"{int(package_files_val):,} files"
                        except (TypeError, ValueError):
                            package_file_limit = f"{package_files_val} files"

                    amount_decimal = _safe_decimal(purchase_details[8], "0")
                    billing_amount_display = format_currency(amount_decimal, currency_code)

                    package_price_value = purchase_details[7] if purchase_details[7] is not None else amount_decimal
                    package_price_display = format_currency(_safe_decimal(package_price_value, amount_decimal), currency_code)

                    billing_status = (purchase_details[10] or "succeeded").replace("_", " ").title()

                    started_at = purchase_details[11]
                    expires_at = purchase_details[12]
                    created_at = purchase_details[13]

                    def _format_dt(value):
                        if not value:
                            return "Not available"
                        try:
                            return value.strftime("%B %d, %Y %I:%M %p")
                        except Exception:
                            return str(value)

                    purchase_date = _format_dt(created_at)
                    if started_at and expires_at:
                        billing_period = f"{_format_dt(started_at)} – {_format_dt(expires_at)}"
                    elif started_at:
                        billing_period = f"Starting {_format_dt(started_at)}"
                    else:
                        billing_period = "Not available"

                    payment_reference = purchase_details[14] or purchase_details[15] or session_id

                    brand_name = (
                        (admin_settings.get("mail_from_name") if admin_settings else None)
                        or Config.SENDER_NAME
                        or "Support Team"
                    )
                    support_email = (
                        (admin_settings.get("admin_email") if admin_settings else None)
                        or (admin_settings.get("mail_from_address") if admin_settings else None)
                        or Config.SENDER_EMAIL
                    )

                    html_body = render_template(
                        "email_template/package_purchase.html",
                        company_name=purchase_details[0],
                        package_title=purchase_details[2],
                        package_description=purchase_details[3],
                        package_contacts=package_contacts,
                        package_minutes=package_minutes,
                        package_file_limit=package_file_limit,
                        package_price_display=package_price_display,
                        billing_amount_display=billing_amount_display,
                        billing_status=billing_status,
                        purchase_date=purchase_date,
                        billing_period=billing_period,
                        payment_reference=payment_reference,
                        support_email=support_email,
                        current_year=datetime.utcnow().year,
                        brand_name=brand_name,
                    )

                    subject = f"Subscription Confirmed: {purchase_details[2]}"
                    success = send_email_via_admin_smtp(
                        subject,
                        html_body,
                        recipients,
                        admin_settings=admin_settings,
                    )
                    if not success:
                        logger.warning("Failed to send package purchase email for session %s", session_id)
        except Exception as exc:
            logger.error(f"Error finalizing package payment for session {session_id}: {str(exc)}")
        finally:
            if cursor:
                cursor.close()
                cursor = None
    if cursor:
        cursor.close()
    return render_template(
        'company/package_checkout_success.html',
        package_id=package_id,
        session_id=session_id
    )


@company_bp.route('/company/packages/<int:package_id>/cancel')
@company_login_required
def package_checkout_cancel(package_id):
    session_id = request.args.get('session_id')
    if session_id:
        try:
            cursor = mysql.connection.cursor()
            cursor.execute("""
                UPDATE company_package_payments
                SET status = %s,
                    updated_at = NOW()
                WHERE stripe_session_id = %s AND company_id = %s AND status = %s
            """, (
                'cancelled',
                session_id,
                session['company_id'],
                'pending'
            ))
            mysql.connection.commit()
        except Exception as exc:
            logger.error(f"Error cancelling package payment for session {session_id}: {str(exc)}")
    return render_template(
        'company/package_checkout_cancel.html',
        package_id=package_id
    )


@company_bp.route('/company/billing')
@company_login_required
def company_billing():
    cursor = mysql.connection.cursor()
    try:
        cursor.execute("""
            SELECT
                cpp.id,
                p.title,
                cpp.amount,
                cpp.currency,
                cpp.status,
                cpp.started_at,
                cpp.expires_at,
                cpp.created_at,
                cpp.stripe_session_id
            FROM company_package_payments cpp
            JOIN packages p ON cpp.package_id = p.id
            WHERE cpp.company_id = %s
            ORDER BY cpp.created_at DESC
        """, (session['company_id'],))
        payments = cursor.fetchall()

        def remaining_days_calc(expires):
            if not expires:
                return None
            delta = expires - datetime.now()
            return max(delta.days, 0)

        formatted = []
        for row in payments:
            expires_at = row[6]
            formatted.append({
                "id": row[0],
                "package_title": row[1],
                "amount": row[2],
                "currency": row[3],
                "status": row[4],
                "started_at": row[5],
                "expires_at": expires_at,
                "created_at": row[7],
                "session_id": row[8],
                "remaining_days": remaining_days_calc(expires_at) if row[4] == 'succeeded' else None
            })

        return render_template('company/billing.html', payments=formatted)
    except Exception as exc:
        logger.error(f"Error loading company billing: {str(exc)}")
        return render_template('company/billing.html', payments=[], error="Unable to load billing history."), 500
    finally:
        cursor.close()