app.py

#!/usr/bin/env python3
"""
Turbo Air Viewer - Equipment Specification Database Viewer
Enhanced version with product image extraction and display
Modified to work with Excel-generated database structure

Required dependencies (add to requirements.txt):
- streamlit
- pandas
- requests
- Pillow
- PyMuPDF
- reportlab
"""

import streamlit as st
import streamlit.components.v1 as components
import sqlite3
import json
from pathlib import Path
import pandas as pd
from datetime import datetime
import re
import time
import base64
import io
import os
import requests
from urllib.parse import quote
from PIL import Image
import fitz  # type: ignore # PyMuPDF
import tempfile
from reportlab.lib import colors
from reportlab.lib.pagesizes import letter, landscape
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image as RLImage, PageBreak
from reportlab.lib.enums import TA_CENTER
import urllib.parse

# Try to import openpyxl for Excel export
try:
    import openpyxl
    from openpyxl.drawing.image import Image as XLImage
    from openpyxl.styles import Alignment, Font, PatternFill
    from openpyxl.utils import get_column_letter
    EXCEL_AVAILABLE = True
except ImportError:
    EXCEL_AVAILABLE = False

# Streamlit page config MUST be first
st.set_page_config(
    page_title="Turbo Air Equipment Viewer",
    page_icon="❄️",
    layout="wide"
)

# Configuration - MODIFIED FOR HUGGING FACE
DB_FILENAME = "turbo_air_db_online.sqlite"  # Local database in root
PDF_DIR = "pdfs"  # Local PDF directory

# Create PDF directory if it doesn't exist
if not os.path.exists(PDF_DIR):
    os.makedirs(PDF_DIR)
    st.info(f"Created PDF directory: {PDF_DIR}")

# Check if database exists
if not os.path.exists(DB_FILENAME):
    st.error(f"❌ Database file '{DB_FILENAME}' not found!")
    st.info("Please ensure 'turbo_air_db_online.sqlite' is in the same directory as this script.")
    st.write(f"Looking in: {os.path.abspath(DB_FILENAME)}")
    st.stop()

# Product type mappings
PRODUCT_TYPES = {
    'TSR': 'Reach-In Refrigerators',
    'TSF': 'Reach-In Freezers',
    'TGM': 'Glass Door Merchandisers',
    'TOM': 'Open Display Merchandisers',
    'MUR': 'Undercounter Refrigerators',
    'MUF': 'Undercounter Freezers',
    'PRO': 'Prep Tables',
    'M3': 'M3 Series',
    'TBP': 'Back Bar Coolers',
    'CRT': 'Countertop Display',
    'TPR': 'Pizza Prep Tables',
    'MST': 'Sandwich/Salad Units',
    'J': 'J Series',
    'TUF': 'Undercounter Freezers',
    'TUR': 'Undercounter Refrigerators',
    'TGF': 'Glass Door Freezers',
    'TGR': 'Glass Door Refrigerators',
    'JUF': 'J Series Undercounter Freezers',
    'JUR': 'J Series Undercounter Refrigerators',
    'PST': 'Prep Station Tables'
}

# Enhanced dark theme
st.markdown("""
<style>
    .stApp {
        background-color: #1a1a1a;
    }
    
    .search-container {
        padding: 25px;
        margin: 20px 0;
    }
    
    .search-label {
        color: #4CAF50;
        font-size: 1.2em;
        font-weight: bold;
        margin-bottom: 10px;
        display: block;
    }
    
    .stSelectbox > div > div {
        background-color: #3d3d3d !important;
        border: 1px solid #555 !important;
        border-radius: 10px !important;
    }
    
    .specs-table {
        padding: 10px;
    }
    
    .google-search-button {
        width: 100%;
        padding: 0.5rem;
        background-color: #4CAF50;
        color: white;
        border: none;
        border-radius: 5px;
        cursor: pointer;
        font-size: 16px;
    }
    
    .google-search-button:hover {
        background-color: #45a049;
    }
    
    .pdf-container iframe {
        border: 2px solid #444;
        border-radius: 8px;
    }
    
    /* Make PDF viewer take up more vertical space */
    iframe[src*="pdf.js"] {
        min-height: 85vh !important;
        height: 85vh !important;
    }
    
    .main-title {
        color: #4CAF50;
        font-size: 2.5em;
        font-weight: bold;
        margin: 0;
    }
    
    .subtitle {
        color: #888;
        font-size: 1.1em;
        margin-top: 5px;
    }
    
    .quality-badge {
        display: inline-block;
        padding: 2px 8px;
        border-radius: 4px;
        font-size: 0.85em;
        margin-left: 5px;
        font-weight: 600;
    }
    
    .quality-excellent {
        background-color: #4CAF50;
        color: white;
    }
    
    .quality-good {
        background-color: #8BC34A;
        color: white;
    }
    
    .quality-acceptable {
        background-color: #FFC107;
        color: black;
    }
    
    .quality-poor {
        background-color: #FF5722;
        color: white;
    }
    
    .stExpander {
        background-color: #2d2d2d;
        border-radius: 8px;
        border: 1px solid #444;
    }
    
    .cart-image {
        border: 1px solid #444;
        border-radius: 4px;
        margin-bottom: 8px;
        transition: transform 0.2s ease;
    }
    
    .cart-image:hover {
        transform: scale(1.05);
        border-color: #4CAF50;
    }
    
    .product-image {
        border: none;
        border-radius: 8px;
        box-shadow: 0 2px 8px rgba(0,0,0,0.3);
    }
    
    /* Equal height columns */
    [data-testid="column"] {
        display: flex;
        flex-direction: column;
    }
    
    [data-testid="column"] > div {
        flex: 1;
    }
    
    /* Selectbox enhancement */
    .stSelectbox input {
        cursor: text !important;
    }
    
    .email-button {
        width: 100%;
        padding: 0.5rem;
        background-color: #4CAF50;
        color: white;
        border: none;
        border-radius: 5px;
        cursor: pointer;
        font-size: 16px;
        text-decoration: none;
        display: inline-block;
        text-align: center;
    }
    
    .email-button:hover {
        background-color: #45a049;
        color: white;
        text-decoration: none;
    }
    
    /* Left-align text in cart text-only view buttons */
    [data-testid="stButton"][id*="select_text_"] button,
    [data-testid="stButton"][id*="select_text_exp_"] button {
        text-align: left !important;
        justify-content: flex-start !important;
        padding-left: 10px !important;
    }
</style>

<script>
document.addEventListener('DOMContentLoaded', function() {
    // Auto-select text in selectbox when focused
    const observer = new MutationObserver(function(mutations) {
        const selectInput = document.querySelector('[data-baseweb="select"] input');
        if (selectInput) {
            selectInput.addEventListener('focus', function() {
                this.select();
            });
        }
    });
    observer.observe(document.body, { childList: true, subtree: true });
});
</script>
""", unsafe_allow_html=True)

# Initialize session state
if 'selected_model' not in st.session_state:
    st.session_state.selected_model = None
if 'cart_models' not in st.session_state:
    st.session_state.cart_models = []
if 'text_only_view' not in st.session_state:
    st.session_state.text_only_view = False
if 'product_images' not in st.session_state:
    st.session_state.product_images = {}
if 'db_last_modified' not in st.session_state:
    st.session_state.db_last_modified = None

# Check if database has been modified
def check_db_cache():
    """Check if database has been modified and clear cache if needed"""
    if DB_PATH and os.path.exists(DB_PATH):
        current_mtime = os.path.getmtime(DB_PATH)
        if st.session_state.db_last_modified is None:
            st.session_state.db_last_modified = current_mtime
        elif current_mtime != st.session_state.db_last_modified:
            # Database has been modified, clear cache
            st.session_state.product_images = {}
            st.session_state.db_last_modified = current_mtime
            st.cache_data.clear()
            return True
    return False

# MODIFIED: Check for local database
def check_database():
    """Check if database exists locally"""
    if os.path.exists(DB_FILENAME):
        try:
            conn = sqlite3.connect(DB_FILENAME)
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table' LIMIT 1")
            tables = cursor.fetchall()
            conn.close()
            
            if tables:
                return DB_FILENAME
        except:
            st.error("Database file exists but is invalid")
            return None
    else:
        st.error(f"Database file '{DB_FILENAME}' not found in root directory")
        return None

# Check database
DB_PATH = check_database()

if DB_PATH is None:
    st.error("❌ Unable to load database. Please ensure 'turbo_air_db_online.sqlite' is in the root directory.")
    st.stop()

# Check if database was modified
check_db_cache()

def extract_pdf_thumbnail(pdf_path, model_name, max_width=300, max_height=400):
    """Extract first page of PDF as thumbnail image"""
    cache_key = f"thumb_{model_name}"
    
    # Check if already cached in session state
    if cache_key in st.session_state.product_images:
        return st.session_state.product_images[cache_key]
    
    try:
        # Check if file exists
        if not os.path.exists(pdf_path):
            # Try alternate naming conventions in PDF_DIR
            alt_paths = [
                os.path.join(PDF_DIR, f"{model_name}.pdf"),
                os.path.join(PDF_DIR, f"{model_name.upper()}.pdf"),
                os.path.join(PDF_DIR, f"{model_name.lower()}.pdf"),
                os.path.join(PDF_DIR, f"{model_name.replace('-', '_')}.pdf"),
                os.path.join(PDF_DIR, f"{model_name.replace('-', '')}.pdf"),
                # Also try with parentheses removed
                os.path.join(PDF_DIR, f"{model_name.replace('(', '').replace(')', '')}.pdf"),
                os.path.join(PDF_DIR, f"{model_name.split('(')[0].strip()}.pdf"),
            ]
            
            # Debug: Show what files we're looking for
            print(f"Looking for PDF for model {model_name}")
            print(f"Primary path: {pdf_path}")
            
            for alt_path in alt_paths:
                if os.path.exists(alt_path):
                    print(f"Found PDF at: {alt_path}")
                    pdf_path = alt_path
                    break
            else:
                print(f"No PDF found for {model_name}")
                # List available PDFs in the directory for debugging
                if os.path.exists(PDF_DIR):
                    available_pdfs = [f for f in os.listdir(PDF_DIR) if f.endswith('.pdf')]
                    print(f"Available PDFs in {PDF_DIR}: {available_pdfs[:5]}...")  # Show first 5
                return None
        
        # Open PDF and extract first page
        pdf_document = fitz.open(pdf_path)  # type: ignore
        first_page = pdf_document[0]
        
        # Render page as image (2x resolution for better quality)
        mat = fitz.Matrix(2, 2)
        # Fixed: Use getPixmap for older PyMuPDF versions or get_pixmap for newer
        try:
            # Try newer API first
            pix = first_page.get_pixmap(matrix=mat)  # type: ignore
        except AttributeError:
            try:
                # Try older API with matrix parameter
                pix = first_page.getPixmap(matrix=mat)  # type: ignore
            except:
                try:
                    # Try older API with mat parameter
                    pix = first_page.getPixmap(mat)  # type: ignore
                except:
                    # Fallback to no matrix
                    pix = first_page.getPixmap()  # type: ignore
        
        # Convert to PIL Image
        img_data = pix.tobytes("png")
        img = Image.open(io.BytesIO(img_data))
        
        # Calculate aspect ratio and resize
        width, height = img.size
        aspect_ratio = width / height
        
        if width > max_width:
            new_width = max_width
            new_height = int(new_width / aspect_ratio)
        else:
            new_width = width
            new_height = height
        
        if new_height > max_height:
            new_height = max_height
            new_width = int(new_height * aspect_ratio)
        
        img = img.resize((new_width, new_height), Image.Resampling.LANCZOS)
        
        # Convert to base64 for caching
        buffered = io.BytesIO()
        img.save(buffered, format="PNG")
        img_base64 = base64.b64encode(buffered.getvalue()).decode()
        
        # Cache in session state
        st.session_state.product_images[cache_key] = img_base64
        
        # Cleanup
        pdf_document.close()
        
        return img_base64
        
    except Exception as e:
        print(f"Error extracting thumbnail: {e}")
        return None

# Cache functions
@st.cache_data
def get_all_models():
    """Get all models from database - CACHED"""
    if DB_PATH is None:
        return []
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    all_models = []
    
    try:
        # Try products table from scanner database
        cursor.execute("SELECT model FROM products ORDER BY model")
        models = cursor.fetchall()
        if models:
            all_models = [m[0] for m in models]
        
    except Exception as e:
        st.error(f"Database error: {e}")
        all_models = []
    finally:
        conn.close()
    
    return all_models

@st.cache_data
def get_model_data(model_name):
    """Get data for specific model - CACHED - MODIFIED FOR EXCEL STRUCTURE"""
    if DB_PATH is None:
        return None
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    try:
        # Get from products table
        cursor.execute("SELECT * FROM products WHERE model = ?", (model_name,))
        row = cursor.fetchone()
        
        if row:
            # Get column names
            columns = [description[0] for description in cursor.description]
            
            # Create dictionary from row data
            data = dict(zip(columns, row))
            
            # Build file path from source_file
            filename = 'Unknown'
            file_path = None
            if data.get('source_file'):
                # Extract just the filename from the full path
                source_path = data['source_file']
                # Handle both Windows and Unix paths
                filename = source_path.replace('\\', '/').split('/')[-1]
                # Remove any file extension and add .pdf if needed
                if not filename.lower().endswith('.pdf'):
                    filename = filename.split('.')[0] + '.pdf'
                file_path = filename  # Store just the filename
            
            # Create specs dictionary
            specs = {
                'voltage': data.get('Voltage', 'N/A'),  # Changed from hardcoded 'N/A'
                'amperage': f"{data.get('amps', 'N/A')} A" if data.get('amps') else 'N/A',
                'phase': data.get('phase', 'N/A'),  # Now available from Excel
                'frequency': data.get('frequency', 'N/A'),  # Now available from Excel
                'dimensions': data.get('Dimensions', 'N/A'),  # Direct from Excel
                'weight': data.get('Weight', 'N/A'),  # Changed from weight_lbs
                'capacity': data.get('Capacity', 'N/A'),  # Changed from capacity_cuft
                'refrigerant': data.get('refrigerant', 'N/A'),
                'temperature_range': data.get('temperature_range', 'N/A'),  # Now available from Excel
                'compressor': data.get('Compressor', 'N/A'),  # Changed from hp
                'btu': 'N/A',  # Not in products table
                'doors': str(data.get('doors', 'N/A')) if data.get('doors') else 'N/A',
                'shelves': str(data.get('shelves', 'N/A')) if data.get('shelves') else 'N/A',
                'pans': str(data.get('pans', 'N/A')) if data.get('pans') else 'N/A',
            }
            
            # Format dimensions properly if available from individual columns
            if data.get('length_in') and data.get('depth_in') and data.get('height_in'):
                specs['dimensions'] = f"{data['length_in']}\" x {data['depth_in']}\" x {data['height_in']}\""
            
            # Add voltage if we have plug_type and Voltage is not available
            if specs['voltage'] == 'N/A' and data.get('plug_type'):
                # Extract voltage from plug type (e.g., "NEMA 5-15P" might be 115V)
                plug = str(data['plug_type'])
                if '5-15' in plug:
                    specs['voltage'] = '115V'
                elif '5-20' in plug:
                    specs['voltage'] = '115V'
                elif '6-20' in plug:
                    specs['voltage'] = '208-230V'
                elif '6-30' in plug:
                    specs['voltage'] = '208-230V'
                elif '6-50' in plug:
                    specs['voltage'] = '208-230V'
                else:
                    specs['voltage'] = 'See specifications'
            
            return {
                'id': model_name,
                'filename': filename,
                'file_path': file_path,
                'data': {
                    'models': [model_name],
                    'specs': specs,
                    'features': data.get('features', '').split(', ') if data.get('features') else [],  # Now available from Excel
                    'certifications': data.get('certifications', '').split(', ') if data.get('certifications') else [],  # Now available from Excel
                    'description': data.get('description', ''),  # Now available from Excel
                    'use_cases': data.get('use_cases', ''),  # Now available from Excel
                },
                'quality': 'good',  # Default quality since no confidence score
                'price': data.get('Price', 'N/A'),  # Single price field from Excel
                'model_no_dashes': model_name.replace('-', '')  # Generate on the fly
            }
            
    except Exception as e:
        st.error(f"Database error: {e}")
    finally:
        conn.close()
    
    return None

def clean_spec_data(specs):
    """Clean and validate specification data"""
    cleaned_specs = {}
    
    for key, value in specs.items():
        if value and isinstance(value, str):
            # Clean amperage values
            if key == 'amperage':
                if value.strip().upper() in ['A', 'AMP', 'AMPS', 'AMPERE', 'AMPERES']:
                    value = 'N/A'
                else:
                    match = re.search(r'(\d+\.?\d*)\s*[Aa]', value)
                    if match:
                        value = f"{match.group(1)} A"
                    elif value.strip().upper() == 'A':
                        value = 'N/A'
            
            # Clean voltage values
            elif key == 'voltage':
                if value.strip().upper() in ['V', 'VOLT', 'VOLTS']:
                    value = 'N/A'
                else:
                    match = re.search(r'(\d+)\s*[Vv]', value)
                    if match:
                        value = f"{match.group(1)}V"
            
            # Clean phase values
            elif key == 'phase':
                if value.strip() in ['1', 'Single', 'single', '1-phase', '1 phase']:
                    value = '1-Phase'
                elif value.strip() in ['3', 'Three', 'three', '3-phase', '3 phase']:
                    value = '3-Phase'
            
            # Clean frequency values
            elif key == 'frequency':
                if value.strip().upper() in ['HZ', 'HERTZ']:
                    value = 'N/A'
                else:
                    match = re.search(r'(\d+)\s*[Hh][Zz]', value)
                    if match:
                        value = f"{match.group(1)} Hz"
        
        cleaned_specs[key] = value
    
    return cleaned_specs

def get_product_type(model):
    """Determine product type from model number"""
    for prefix, type_name in PRODUCT_TYPES.items():
        if model.startswith(prefix):
            return type_name
    
    model_upper = model.upper()
    if 'REFRIGERATOR' in model_upper or 'REF' in model_upper:
        return "Refrigerator"
    elif 'FREEZER' in model_upper or 'FRZ' in model_upper:
        return "Freezer"
    elif 'PREP' in model_upper:
        return "Prep Table"
    elif 'DISPLAY' in model_upper:
        return "Display Case"
    elif 'MERCHANDISER' in model_upper:
        return "Merchandiser"
    
    return "Equipment"

def format_model_option(model):
    """Format model with product type for display"""
    product_type = get_product_type(model)
    return f"{model} - {product_type}"

def export_cart_models_excel():
    """Export cart models to Excel with thumbnail images"""
    if not st.session_state.cart_models:
        return None
    
    if not EXCEL_AVAILABLE:
        st.error("Excel export requires openpyxl. Please ensure it's installed.")
        return None
    
    # Create workbook and worksheet
    wb = openpyxl.Workbook()  # type: ignore
    ws = wb.active
    if ws is None:  # Fixed: Check if worksheet is None
        st.error("Failed to create Excel worksheet")
        return None
        
    ws.title = "Turbo Air Equipment"
    
    # Set up headers
    headers = [
        'Image', 'Model', 'Product Type', 'Voltage', 'Amperage', 
        'Dimensions', 'Weight', 'Capacity', 'Refrigerant', 'Compressor',
        'Doors', 'Shelves', 'Pans', 'Price'
    ]
    
    # Style for headers
    header_font = Font(bold=True, color="FFFFFF")  # type: ignore
    header_fill = PatternFill(start_color="4CAF50", end_color="4CAF50", fill_type="solid")  # type: ignore
    
    # Write headers
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center', vertical='center')  # type: ignore
    
    # Process each cart model
    progress_bar = st.progress(0)
    status_text = st.empty()
    
    for idx, model in enumerate(st.session_state.cart_models):
        # Update progress
        progress = (idx + 1) / len(st.session_state.cart_models)
        progress_bar.progress(progress)
        status_text.text(f"Processing {model}... ({idx + 1}/{len(st.session_state.cart_models)})")
        
        model_data = get_model_data(model)
        if not model_data:
            continue
            
        row = idx + 2  # Start from row 2 (after headers)
        specs = model_data['data'].get('specs', {})
        specs = clean_spec_data(specs)
        
        # Column A: Image
        if model_data.get('file_path'):
            pdf_filename = model_data['file_path']
            pdf_path = os.path.join(PDF_DIR, pdf_filename)
            
            # Get or extract thumbnail
            cache_key = f"thumb_{model}"
            img_base64 = st.session_state.product_images.get(cache_key)
            
            if not img_base64:
                img_base64 = extract_pdf_thumbnail(pdf_path, model, max_width=150, max_height=200)
            
            if img_base64:
                # Convert base64 to image file for Excel
                img_data = base64.b64decode(img_base64)
                img = Image.open(io.BytesIO(img_data))
                
                # Save to temporary file
                temp_img = io.BytesIO()
                img.save(temp_img, format='PNG')
                temp_img.seek(0)
                
                # Add to Excel
                xl_img = XLImage(temp_img)  # type: ignore
                xl_img.width = 150
                xl_img.height = 200
                ws.add_image(xl_img, f'A{row}')
                
                # Set row height to accommodate image
                ws.row_dimensions[row].height = 150
        
        # Column B onwards: Data
        ws.cell(row=row, column=2, value=model)
        ws.cell(row=row, column=3, value=get_product_type(model))
        ws.cell(row=row, column=4, value=specs.get('voltage', 'N/A'))
        ws.cell(row=row, column=5, value=specs.get('amperage', 'N/A'))
        ws.cell(row=row, column=6, value=specs.get('dimensions', 'N/A'))
        ws.cell(row=row, column=7, value=specs.get('weight', 'N/A'))
        ws.cell(row=row, column=8, value=specs.get('capacity', 'N/A'))
        ws.cell(row=row, column=9, value=specs.get('refrigerant', 'N/A'))
        ws.cell(row=row, column=10, value=specs.get('compressor', 'N/A'))
        ws.cell(row=row, column=11, value=specs.get('doors', 'N/A'))
        ws.cell(row=row, column=12, value=specs.get('shelves', 'N/A'))
        ws.cell(row=row, column=13, value=specs.get('pans', 'N/A'))
        
        # Price information - MODIFIED FOR SINGLE PRICE
        price = model_data.get('price', 'N/A')
        ws.cell(row=row, column=14, value=price)
        
        # Center align all cells
        for col in range(2, 15):
            ws.cell(row=row, column=col).alignment = Alignment(vertical='center')  # type: ignore
    
    # Adjust column widths
    ws.column_dimensions['A'].width = 25  # Image column
    for col in range(2, 15):
        ws.column_dimensions[get_column_letter(col)].width = 15  # type: ignore
    
    # Save to BytesIO
    output = io.BytesIO()
    wb.save(output)
    output.seek(0)
    
    # Clear progress
    progress_bar.empty()
    status_text.empty()
    
    return output.getvalue()

def export_cart_models_pdf():
    """Export cart models to PDF with images and specifications"""
    if not st.session_state.cart_models:
        return None
    
    # Create PDF in memory
    buffer = io.BytesIO()
    doc = SimpleDocTemplate(buffer, pagesize=landscape(letter), 
                           topMargin=0.5*inch, bottomMargin=0.5*inch,
                           leftMargin=0.5*inch, rightMargin=0.5*inch)
    
    # Container for the 'Flowable' objects
    elements = []
    
    # Styles
    styles = getSampleStyleSheet()
    title_style = ParagraphStyle(
        'CustomTitle',
        parent=styles['Heading1'],
        fontSize=24,
        textColor=colors.HexColor('#4CAF50'),
        spaceAfter=30,
        alignment=TA_CENTER
    )
    
    model_title_style = ParagraphStyle(
        'ModelTitle',
        parent=styles['Heading2'],
        fontSize=16,
        textColor=colors.HexColor('#333333'),
        spaceAfter=12
    )
    
    # Title
    elements.append(Paragraph("Turbo Air Equipment Selection Report", title_style))
    elements.append(Paragraph(f"Generated: {datetime.now().strftime('%B %d, %Y at %I:%M %p')}", 
                             styles['Normal']))
    elements.append(Spacer(1, 0.5*inch))
    
    # Process each cart model
    for idx, model in enumerate(st.session_state.cart_models):
        if idx > 0:
            elements.append(PageBreak())
        
        model_data = get_model_data(model)
        if not model_data:
            continue
        
        # Model header
        elements.append(Paragraph(f"{model} - {get_product_type(model)}", model_title_style))
        
        # Try to get product image
        if model_data.get('file_path'):
            pdf_filename = model_data['file_path']
            pdf_path = os.path.join(PDF_DIR, pdf_filename)
            
            # Get cached image or extract it
            cache_key = f"thumb_{model}"
            img_base64 = st.session_state.product_images.get(cache_key)
            
            if not img_base64:
                img_base64 = extract_pdf_thumbnail(pdf_path, model, max_width=200, max_height=250)
            
            if img_base64:
                # Convert base64 to image for PDF
                img_data = base64.b64decode(img_base64)
                img = RLImage(io.BytesIO(img_data), width=2*inch, height=2.5*inch)
                elements.append(img)
                elements.append(Spacer(1, 0.2*inch))
        
        # Get specifications for table
        specs = model_data['data'].get('specs', {})
        specs = clean_spec_data(specs)
        
        # Create specifications data for table
        spec_data = [['Specification', 'Value']]
        
        if specs.get('voltage') and specs.get('voltage') != 'N/A':
            spec_data.append(['Voltage', specs['voltage']])
        if specs.get('amperage') and specs.get('amperage') != 'N/A':
            spec_data.append(['Amperage', specs['amperage']])
        if specs.get('phase') and specs.get('phase') != 'N/A':
            spec_data.append(['Phase', specs['phase']])
        if specs.get('frequency') and specs.get('frequency') != 'N/A':
            spec_data.append(['Frequency', specs['frequency']])
        if specs.get('dimensions') and specs.get('dimensions') != 'N/A':
            spec_data.append(['Dimensions', specs['dimensions']])
        if specs.get('weight') and specs.get('weight') != 'N/A':
            spec_data.append(['Weight', specs['weight']])
        if specs.get('capacity') and specs.get('capacity') != 'N/A':
            spec_data.append(['Capacity', specs['capacity']])
        if specs.get('refrigerant') and specs.get('refrigerant') != 'N/A':
            spec_data.append(['Refrigerant', specs['refrigerant']])
        if specs.get('temperature_range') and specs.get('temperature_range') != 'N/A':
            spec_data.append(['Temperature Range', specs['temperature_range']])
        if specs.get('compressor') and specs.get('compressor') != 'N/A':
            spec_data.append(['Compressor', specs['compressor']])
        if specs.get('btu') and specs.get('btu') != 'N/A':
            spec_data.append(['BTU', specs['btu']])
        
        # Add price information - MODIFIED FOR SINGLE PRICE
        price = model_data.get('price', 'N/A')
        if price and price != 'N/A':
            spec_data.append(['Price', price])
        
        if len(spec_data) > 1:
            # Create table
            spec_table = Table(spec_data, colWidths=[2.5*inch, 4*inch])
            spec_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4CAF50')),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 12),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 10),
                ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f0f0f0')]),
            ]))
            elements.append(spec_table)
            elements.append(Spacer(1, 0.3*inch))
        
        # Features
        features = model_data['data'].get('features', [])
        if features:
            elements.append(Paragraph("<b>Features:</b>", styles['Heading3']))
            for feature in features:
                elements.append(Paragraph(f"• {feature}", styles['Normal']))
            elements.append(Spacer(1, 0.2*inch))
        
        # Source info
        elements.append(Paragraph(f"<i>Source: {model_data.get('filename', 'Unknown')}</i>", styles['Normal']))
    
    # Build PDF
    doc.build(elements)
    buffer.seek(0)
    return buffer.getvalue()

def display_pdf_preview(file_path, model_name):
    """Display PDF inline in Streamlit app - optimized for HuggingFace Spaces"""
    
    # Extract filename from path
    pdf_filename = file_path.replace('\\', '/').split('/')[-1]
    
    # Create the HuggingFace Space URL for the PDF
    pdf_url = f"https://huggingface.co/spaces/redxican/TurboAirViewer2.0/resolve/main/pdfs/{pdf_filename}"
    
    # Control buttons row
    col1, col2, col3 = st.columns([2, 1, 1])
    
    with col1:
        # Download button with actual file download
        try:
            response = requests.get(pdf_url, timeout=10)
            if response.status_code == 200:
                st.download_button(
                    label="📥 Download PDF",
                    data=response.content,
                    file_name=pdf_filename,
                    mime="application/pdf",
                    use_container_width=True,
                    type="primary"
                )
            else:
                # Fallback to link if download fails
                st.markdown(f"[📥 Download PDF]({pdf_url})")
        except:
            # Fallback to simple link
            st.markdown(f"[📥 Download PDF]({pdf_url})")
    
    with col2:
        st.success("✅ PDF ready")
    
    with col3:
        if st.button("❌ Close Preview", use_container_width=True):
            st.session_state[f'show_pdf_{model_name}'] = False
            st.rerun()
    
    st.markdown("---")
    
    # Show backup viewer with PDF.js
    st.markdown("🔍 **Backup viewer** (if PDF doesn't display above):")
    
    # Use PDF.js viewer directly (most reliable for HuggingFace Spaces)
    components.iframe(
        src=f"https://mozilla.github.io/pdf.js/web/viewer.html?file={quote(pdf_url, safe='')}",
        height=1200,
        scrolling=True
    )

def display_cart_models():
    """Display cart models section with optimized toggle"""
    if not st.session_state.cart_models:
        st.info("🛒 Your cart is empty. Add models to create your custom quote!")
        return
    
    # Collapsible header
    with st.expander(f"🛒 Shopping Cart ({len(st.session_state.cart_models)} items)", expanded=True):
        view_col1, view_col2, view_col3 = st.columns([2, 1, 1])
        
        with view_col3:
            # Toggle for text-only view - optimized to avoid loading
            toggle_label = "📷 Show Images" if st.session_state.text_only_view else "📝 Text Only"
            if st.button(toggle_label, key="toggle_view", use_container_width=True):
                st.session_state.text_only_view = not st.session_state.text_only_view
        
        # Display cart models with or without images
        if st.session_state.text_only_view:
            # Text-only view (original compact list)
            display_limit = 5
            for idx, model in enumerate(st.session_state.cart_models[:display_limit]):
                col_select, col_remove = st.columns([5, 1])
                with col_select:
                    if st.button(f"• {model}", key=f"select_text_{idx}", use_container_width=True, help=f"Click to view {model}"):
                        st.session_state.selected_model = model
                        st.rerun()
                with col_remove:
                    if st.button("❌", key=f"remove_cart_list_{idx}", help=f"Remove {model} from cart"):
                        st.session_state.cart_models.remove(model)
                        st.rerun()
            
            if len(st.session_state.cart_models) > display_limit:
                with st.expander(f"Show all {len(st.session_state.cart_models)} items"):
                    for idx, model in enumerate(st.session_state.cart_models[display_limit:], display_limit):
                        col_select, col_remove = st.columns([5, 1])
                        with col_select:
                            if st.button(f"• {model}", key=f"select_text_exp_{idx}", use_container_width=True, help=f"Click to view {model}"):
                                st.session_state.selected_model = model
                                st.rerun()
                        with col_remove:
                            if st.button("❌", key=f"remove_cart_exp_{idx}", help=f"Remove {model} from cart"):
                                st.session_state.cart_models.remove(model)
                                st.rerun()

        else:
            # Image view
            # Display in grid layout
            cols_per_row = 6  # Changed from 4 to 6 columns for narrower items
            for i in range(0, len(st.session_state.cart_models), cols_per_row):
                cols = st.columns(cols_per_row)
                
                for j, col in enumerate(cols):
                    if i + j < len(st.session_state.cart_models):
                        model = st.session_state.cart_models[i + j]
                        model_data = get_model_data(model)
                        
                        with col:
                            # Container for each cart item
                            st.markdown('<div class="cart-item">', unsafe_allow_html=True)
                            
                            # Container for each cart item
                            with st.container():
                                # Try to get and display thumbnail
                                if model_data and model_data.get('file_path'):
                                    pdf_filename = model_data['file_path']
                                    pdf_path = os.path.join(PDF_DIR, pdf_filename)
                                    
                                    # Check if image is already cached
                                    cache_key = f"thumb_{model}"
                                    img_base64 = st.session_state.product_images.get(cache_key)
                                    
                                    if img_base64:
                                        # Use cached image
                                        st.markdown(
                                            f'<img src="data:image/png;base64,{img_base64}" '
                                            f'style="width:100%; max-height:150px; object-fit:contain; cursor:pointer;" '
                                            f'class="cart-image">',
                                            unsafe_allow_html=True
                                        )
                                    else:
                                        # Extract image with minimal loading indication
                                        img_base64 = extract_pdf_thumbnail(pdf_path, model, max_width=200, max_height=250)
                                        
                                        if img_base64:
                                            st.markdown(
                                                f'<img src="data:image/png;base64,{img_base64}" '
                                                f'style="width:100%; max-height:200px; object-fit:contain; cursor:pointer;" '
                                                f'class="cart-image">',
                                                unsafe_allow_html=True
                                            )
                                        else:
                                            st.info("📄 No preview")
                                
                                # Model name button
                                if st.button(f"{model}", key=f"select_model_{i}_{j}", use_container_width=True, type="secondary"):
                                    st.session_state.selected_model = model
                                    st.rerun()
                                
                                # Product type caption
                                st.caption(get_product_type(model))
                                
                                # Action buttons row
                                col_view, col_remove = st.columns(2)
                                with col_view:
                                    if st.button("👁️ View", key=f"view_{i}_{j}", use_container_width=True):
                                        st.session_state.selected_model = model
                                        st.rerun()
                                with col_remove:
                                    if st.button("❌", key=f"remove_img_{i}_{j}", use_container_width=True, type="secondary"):
                                        st.session_state.cart_models.remove(model)
                                        st.rerun()
                            
                            st.markdown('</div>', unsafe_allow_html=True)
    
    # Export section
    st.markdown("---")
    st.markdown("### 📊 Export & Share")
    
    # Initialize email visibility state
    if 'show_email_form' not in st.session_state:
        st.session_state.show_email_form = False
    
    # Export buttons row - all in one line
    button_col1, button_col2, button_col3, button_col4 = st.columns([1.2, 1.2, 1.2, 1])
    
    with button_col1:
        # Email toggle button (moved to first position)
        email_btn_text = "📧 Email Excel ▲" if st.session_state.show_email_form else "📧 Email Excel ▼"
        if st.button(email_btn_text, use_container_width=True, key="toggle_email"):
            st.session_state.show_email_form = not st.session_state.show_email_form
    
    with button_col2:
        # Excel export button
        if EXCEL_AVAILABLE:
            excel_data = export_cart_models_excel()
            if excel_data:
                st.download_button(
                    "📊 Download Excel",
                    data=excel_data,
                    file_name=f"turbo_air_cart_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    use_container_width=True,
                    type="primary",
                    key="download_excel_btn"
                )
        else:
            st.error("Excel export unavailable - openpyxl not installed")
    
    with button_col3:
        # PDF export button
        pdf_data = export_cart_models_pdf()
        if pdf_data:
            st.download_button(
                "📄 Download PDF",
                data=pdf_data,
                file_name=f"turbo_air_report_{datetime.now().strftime('%Y%m%d_%H%M')}.pdf",
                mime="application/pdf",
                use_container_width=True,
                key="download_pdf_btn"
            )
    
    with button_col4:
        # Clear cart button (moved to last position)
        if st.button("🗑️ Clear Cart", use_container_width=True):
            st.session_state.cart_models = []
            st.session_state.product_images = {}  # Clear image cache too
            st.rerun()
    
    # Email form (only shown when toggled)
    if st.session_state.show_email_form:
        st.markdown("---")
        
        # Initialize session state for download tracking
        if 'excel_downloaded' not in st.session_state:
            st.session_state.excel_downloaded = False
        
        # Email form
        email_col1, email_col2, email_col3 = st.columns([3, 1, 1])
        
        with email_col1:
            receiver_email = st.text_input("To:", placeholder="Customer email address", key="receiver_email", label_visibility="collapsed")
        
        if receiver_email and EXCEL_AVAILABLE:
            excel_data = export_cart_models_excel()
            if excel_data:
                filename = f"turbo_air_cart_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
                
                with email_col2:
                    # Step 1: Download Excel
                    downloaded = st.download_button(
                        label="📥 Step 1: Download",
                        data=excel_data,
                        file_name=filename,
                        mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                        use_container_width=True,
                        key="download_excel_for_email"
                    )
                    if downloaded:
                        st.session_state.excel_downloaded = True
                
                with email_col3:
                    # Step 2: Open Email (only enabled after download)
                    mailto_link = f"mailto:{receiver_email}"
                    
                    if st.session_state.excel_downloaded:
                        st.markdown(f'''
                        <a href="{mailto_link}" class="email-button" target="_blank" style="width: 100%; padding: 0.5rem; display: inline-block; text-align: center;">
                            📧 Step 2: Email
                        </a>
                        ''', unsafe_allow_html=True)
                    else:
                        st.button("📧 Step 2: Email", use_container_width=True, disabled=True, key="email_disabled")
                
                # Instructions
                if st.session_state.excel_downloaded:
                    st.info(f"✅ File downloaded: **{filename}** → Now click 'Step 2: Email' to compose your message and attach the file from your Downloads folder.")
        else:
            with email_col2:
                st.button("📥 Step 1: Download", use_container_width=True, disabled=True)
            with email_col3:
                st.button("📧 Step 2: Email", use_container_width=True, disabled=True)

# MAIN UI
st.title("❄️ Turbo Air Equipment Viewer")
st.caption("Professional Equipment Specification Database")

# Get all models
all_models = get_all_models()

if not all_models:
    st.error("⚠️ No data found in database. Please ensure turbo_air_db_online.sqlite is available.")
    st.stop()

# Cart section
if st.session_state.cart_models:
    display_cart_models()
else:
    st.info("🛒 Your cart is empty. Add models to create your custom quote!")

# Main content area
col1, col2 = st.columns([1, 3])

with col1:
    st.markdown("### 💡 Quick Tips")
    st.write("• View PDF spec sheets")
    st.write("• Add models to cart")
    st.write("• Toggle image/text view")
    st.write("• Export to Excel with images")
    st.write("• Export to PDF report")
    st.write("• Email quotes to customers")
    st.write("• Google search finds prices")

with col2:
    st.markdown('### 🔍 Model Search')
    st.caption("Start typing the model number or browse all models")
    
    # Create formatted options with empty first option for easy typing
    formatted_options = ['']  # Empty first option
    # Add all models sorted alphabetically
    for model in sorted(all_models):
        formatted_options.append(model)
    
    # Search selectbox with clear typing experience
    if st.session_state.selected_model and st.session_state.selected_model in formatted_options:
        default_index = formatted_options.index(st.session_state.selected_model)
    else:
        default_index = 0
    
    selected = st.selectbox(
        "Select or type a model number:",
        options=formatted_options,
        format_func=lambda x: x if x else "↓ Click here and start typing model number...",
        key="model_search",
        index=default_index,
        help="Click and start typing to search models"
    )
    
    if selected:
        st.session_state.selected_model = selected

# Display selected model
if st.session_state.selected_model and st.session_state.selected_model != '':
    st.markdown("---")
    
    model_data = get_model_data(st.session_state.selected_model)
    
    if model_data:
        # Model header with cart button
        col1, col2 = st.columns([4, 1])
        with col1:
            st.markdown(f"## {st.session_state.selected_model}")
            st.caption(f"Product Type: {get_product_type(st.session_state.selected_model)}")
            # Removed quality badge display since no confidence score
        
        with col2:
            is_in_cart = st.session_state.selected_model in st.session_state.cart_models
            cart_label = "❌ Remove from Cart" if is_in_cart else "🛒 Add to Cart"
            if st.button(cart_label, key=f"cart_{st.session_state.selected_model}", use_container_width=True):
                if is_in_cart:
                    st.session_state.cart_models.remove(st.session_state.selected_model)
                    st.success("Removed from cart!")
                else:
                    st.session_state.cart_models.append(st.session_state.selected_model)
                    st.success("Added to cart!")
                time.sleep(0.5)
                st.rerun()
        
        # Display product image if available
        if model_data.get('file_path'):
            pdf_filename = model_data['file_path']
            pdf_path = os.path.join(PDF_DIR, pdf_filename)
            
            # Create columns for image and specifications
            img_col, _, spec_col = st.columns([1, 0.1, 2])
            
            with img_col:
                st.markdown("### Product Image")
                # Check if image is already cached
                cache_key = f"thumb_{st.session_state.selected_model}"
                img_base64 = st.session_state.product_images.get(cache_key)
                
                if not img_base64:
                    # Extract image if not cached
                    with st.spinner("Loading product image..."):
                        img_base64 = extract_pdf_thumbnail(pdf_path, st.session_state.selected_model, max_width=400, max_height=500)
                
                if img_base64:
                    st.markdown(
                        f'<img src="data:image/png;base64,{img_base64}" '
                        f'class="product-image" '
                        f'style="width:100%; max-width:400px;" />',
                        unsafe_allow_html=True
                    )
                else:
                    st.info("📄 No preview available")
                    # Show debug info in expander
                    with st.expander("Debug Info"):
                        st.write(f"PDF filename: {pdf_filename}")
                        st.write(f"Looking for: {pdf_path}")
                        st.write(f"File exists: {os.path.exists(pdf_path)}")
            
            with spec_col:
                # Specifications
                specs = model_data['data'].get('specs', {})
                specs = clean_spec_data(specs)
                
                if specs:
                    st.markdown("### Technical Specifications")
                    
                    st.markdown("**Electrical Specifications:**")
                    if specs.get('voltage') and specs.get('voltage') != 'N/A':
                        st.write(f"Voltage: {specs['voltage']}")
                    if specs.get('amperage') and specs.get('amperage') != 'N/A':
                        st.write(f"Amperage: {specs['amperage']}")
                    if specs.get('phase') and specs.get('phase') != 'N/A':
                        st.write(f"Phase: {specs['phase']}")
                    if specs.get('frequency') and specs.get('frequency') != 'N/A':
                        st.write(f"Frequency: {specs['frequency']}")
                    
                    st.markdown("**Physical Specifications:**")
                    if specs.get('dimensions') and specs.get('dimensions') != 'N/A':
                        st.write(f"Dimensions: {specs['dimensions']}")
                    if specs.get('weight') and specs.get('weight') != 'N/A':
                        st.write(f"Weight: {specs['weight']}")
                    
                    st.markdown("**Performance Specifications:**")
                    if specs.get('refrigerant') and specs.get('refrigerant') != 'N/A':
                        st.write(f"Refrigerant: {specs['refrigerant']}")
                    if specs.get('temperature_range') and specs.get('temperature_range') != 'N/A':
                        st.write(f"Temperature: {specs['temperature_range']}")
                    if specs.get('compressor') and specs.get('compressor') != 'N/A':
                        st.write(f"Compressor: {specs['compressor']}")
                    if specs.get('btu') and specs.get('btu') != 'N/A':
                        st.write(f"BTU: {specs['btu']}")
                    if specs.get('capacity') and specs.get('capacity') != 'N/A':
                        st.write(f"Capacity: {specs['capacity']}")
                    
                    # Configuration details
                    config_items = []
                    if specs.get('doors') and specs.get('doors') != 'N/A':
                        config_items.append(f"Doors: {specs['doors']}")
                    if specs.get('shelves') and specs.get('shelves') != 'N/A':
                        config_items.append(f"Shelves: {specs['shelves']}")
                    if specs.get('pans') and specs.get('pans') != 'N/A':
                        config_items.append(f"Pans: {specs['pans']}")
                    
                    if config_items:
                        st.markdown("**Configuration:**")
                        for item in config_items:
                            st.write(f"{item}")
                    
                    # Price information - MODIFIED FOR SINGLE PRICE
                    price = model_data.get('price', 'N/A')
                    if price and price != 'N/A':
                        st.markdown("### Price")
                        st.markdown(f'<p style="font-size: 1.2em; color: #ff0000; font-weight: bold; margin: 0;">{price}</p>', unsafe_allow_html=True)
        else:
            # No file path - show specifications in original two-column layout
            specs = model_data['data'].get('specs', {})
            specs = clean_spec_data(specs)
            
            if specs:
                st.markdown("### Technical Specifications")
                
                spec_col1, spec_col2 = st.columns(2)
                
                with spec_col1:
                    st.markdown("**Electrical Specifications:**")
                    if specs.get('voltage') and specs.get('voltage') != 'N/A':
                        st.write(f"Voltage: {specs['voltage']}")
                    if specs.get('amperage') and specs.get('amperage') != 'N/A':
                        st.write(f"Amperage: {specs['amperage']}")
                    if specs.get('phase') and specs.get('phase') != 'N/A':
                        st.write(f"Phase: {specs['phase']}")
                    if specs.get('frequency') and specs.get('frequency') != 'N/A':
                        st.write(f"Frequency: {specs['frequency']}")
                    
                    st.markdown("**Physical Specifications:**")
                    if specs.get('dimensions') and specs.get('dimensions') != 'N/A':
                        st.write(f"Dimensions: {specs['dimensions']}")
                    if specs.get('weight') and specs.get('weight') != 'N/A':
                        st.write(f"Weight: {specs['weight']}")
                
                with spec_col2:
                    st.markdown("**Performance Specifications:**")
                    if specs.get('refrigerant') and specs.get('refrigerant') != 'N/A':
                        st.write(f"Refrigerant: {specs['refrigerant']}")
                    if specs.get('temperature_range') and specs.get('temperature_range') != 'N/A':
                        st.write(f"Temperature: {specs['temperature_range']}")
                    if specs.get('compressor') and specs.get('compressor') != 'N/A':
                        st.write(f"Compressor: {specs['compressor']}")
                    if specs.get('btu') and specs.get('btu') != 'N/A':
                        st.write(f"BTU: {specs['btu']}")
                    if specs.get('capacity') and specs.get('capacity') != 'N/A':
                        st.write(f"Capacity: {specs['capacity']}")
                    
                    # Price information - MODIFIED FOR SINGLE PRICE
                    price = model_data.get('price', 'N/A')
                    if price and price != 'N/A':
                        st.markdown("### Price")
                        st.markdown(f'<p style="font-size: 1.2em; color: #ff0000; font-weight: bold; margin: 0;">{price}</p>', unsafe_allow_html=True)
        
        # Features
        features = model_data['data'].get('features', [])
        if features:
            st.markdown("### Features")
            feature_cols = st.columns(3)
            for idx, feature in enumerate(features):
                with feature_cols[idx % 3]:
                    st.write(f"• {feature}")
        
        # Certifications
        certifications = model_data['data'].get('certifications', [])
        if certifications:
            st.markdown("### Certifications")
            st.write(" • ".join(certifications))
        
        # Description
        description = model_data['data'].get('description', '')
        if description:
            st.markdown("### Description")
            st.write(description)
        
        # Use cases
        use_cases = model_data['data'].get('use_cases', '')
        if use_cases:
            st.markdown("### Use Cases")
            st.write(use_cases)
        
        # Action buttons - View PDF and Search in Google
        st.markdown("### Actions")
        action_col1, action_col2 = st.columns(2)
        
        with action_col1:
            # PDF toggle button
            pdf_key = f'show_pdf_{st.session_state.selected_model}'
            button_text = "📄 Hide PDF" if st.session_state.get(pdf_key, False) else "📄 View PDF"
            if st.button(button_text, use_container_width=True, key=f"view_pdf_{st.session_state.selected_model}"):
                st.session_state[pdf_key] = not st.session_state.get(pdf_key, False)
        
        with action_col2:
            # Google search button - use model_no_dashes from database
            search_model = model_data.get('model_no_dashes', st.session_state.selected_model.replace(' ', '+'))
            google_search = f"https://www.google.com/search?q=turboair+{search_model}+price"
            st.markdown(f'''
            <a href="{google_search}" target="_blank" style="text-decoration: none;">
                <button class="google-search-button">
                🔍 Search in Google
                </button>
            </a>
            ''', unsafe_allow_html=True)
        
        # Display PDF preview if requested
        pdf_key = f'show_pdf_{st.session_state.selected_model}'
        if st.session_state.get(pdf_key, False):
            st.markdown("---")
            st.markdown("### 📄 PDF Specification Sheet")
            
            if 'file_path' in model_data and model_data['file_path']:
                display_pdf_preview(model_data['file_path'], st.session_state.selected_model)
            else:
                st.error("❌ No PDF file path found for this model.")
                st.info("PDF file may not be available.")
        
        # Source info
        st.markdown("---")
        st.caption(f"Source: {model_data['filename']}")
    else:
        st.error(f"No data found for model {st.session_state.selected_model}")

# Stats at bottom
st.markdown("---")
col1, col2 = st.columns(2)

with col1:
    st.markdown("### Models by Product Type")
    type_counts = {}
    for model in all_models:
        ptype = get_product_type(model)
        type_counts[ptype] = type_counts.get(ptype, 0) + 1
    
    sorted_types = sorted(type_counts.items(), key=lambda x: x[1], reverse=True)
    for ptype, count in sorted_types[:8]:
        if ptype == "Equipment" and count < 20:
            continue
        st.write(f"{ptype}: {count}")

with col2:
    st.markdown("### Database Info")
    if DB_PATH:
        try:
            conn = sqlite3.connect(DB_PATH)
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM products")
            product_count = cursor.fetchone()[0]
            
            # Get products with prices - MODIFIED FOR SINGLE PRICE FIELD
            cursor.execute("SELECT COUNT(*) FROM products WHERE Price IS NOT NULL AND Price != 'N/A'")
            priced_count = cursor.fetchone()[0]
            
            conn.close()
            
            st.write(f"• Total Products: {product_count}")
            st.write(f"• Products with Prices: {priced_count}")
            st.write(f"• Database Size: {Path(DB_PATH).stat().st_size/1024/1024:.1f} MB")
        except:
            st.write("• Database info unavailable")
    else:
        st.write("• Database not loaded")

# Footer
st.markdown("---")
st.caption("Turbo Air Equipment Viewer - Professional Specification Database")
st.caption("💡 Tip: Use Google search button to find current prices and availability")