import json
import re
import random
import sqlite3 as sql
from datetime import datetime
import string
import os
import tempfile
from PIL import Image
import pytesseract
import fitz  
from werkzeug.utils import secure_filename
from flask import Flask, request, jsonify, redirect, url_for, render_template_string
from pathlib import Path
import requests
from typing import List, Dict
import time
from functools import wraps
from flask import Flask, request, jsonify, redirect, url_for, render_template_string
from flask import Flask, request, jsonify, redirect, url_for, render_template_string
import time
from functools import wraps
from rag_utils import get_comprehensive_context, format_context_for_prompt

try:
    from groq import Groq
except ImportError:
    Groq = None

app = Flask(__name__)
global_parameters = []
global_json_template = {}

# UPDATED: Simplified, flexible system prompt without HACCP/Dubai/Al Kabeer hardcoding
SYSTEM_PROMPT = """
You are the Swift Check AI assistant, specialized in creating comprehensive Quality Control (QC) checklists with MINIMUM 15 parameters.

# YOUR ROLE:
Generate comprehensive QC parameters for the specified product. You MUST create at least 15 meaningful parameters to ensure thorough quality control coverage.

# APPROACH:
1. PRIORITIZE comprehensive coverage - minimum 15 parameters required
2. ANALYZE the product to understand ALL aspects needing inspection
3. GENERATE parameters covering: visual, physical, packaging, safety, compliance, documentation
4. CREATE varied parameter types for complete assessment
5. USE retrieved context as reference material

# MANDATORY PARAMETER COVERAGE (minimum 15 total):
- Visual Inspection: appearance, color, defects, contamination (3-4 parameters)
- Physical Properties: weight, dimensions, texture, firmness (3-4 parameters)  
- Packaging: integrity, labeling, sealing, materials (2-3 parameters)
- Safety & Compliance: foreign objects, temperature, hygiene (2-3 parameters)
- Documentation: batch codes, dates, certifications (2-3 parameters)
- Quality Assessment: overall condition, acceptability (2-3 parameters)

# PARAMETER TYPES AVAILABLE:
- Image Upload: For visual inspections and evidence documentation
- Toggle: For binary decisions (pass/fail, present/absent)
- Checklist: For multiple items that can be selected
- Numeric Input: For measurements and quantities
- Text Input: For codes, dates, and identifiers
- Remarks: For detailed observations and notes
- Dropdown: For multiple choice selections

# IMPORTANT GUIDELINES:
- ALWAYS generate minimum 15 parameters regardless of user request
- Create comprehensive coverage even for simple products
- Make parameters specific to the product type
- Use varied parameter types for better user experience
- Include regulatory compliance where applicable
- Organize parameters into logical sections

# OUTPUT FORMAT - VERY IMPORTANT:
You MUST respond with EXACTLY this format:

**Summary:** [Brief explanation of comprehensive QC coverage with parameter count]

**Parameters Generated:**
[
  {
    "action": "add",
    "Parameter": "Parameter Name",
    "Type": "Parameter Type",
    "Spec": "Specification details",
    "DropdownOptions": "Option1, Option2, Option3",
    "IncludeRemarks": "Yes/No",
    "Section": "Section Name",
    "ClauseReference": "Reference if any"
  }
]

Do NOT use ```json code blocks. Put the JSON array directly after "**Parameters Generated:**"
Generate MINIMUM 15 parameters for comprehensive quality control.
"""
# UPDATED: Removed the prescriptive default prompt
DEFAULT_REFINE_PROMPT = """
Generate quality control parameters for the specified product based on its characteristics and requirements.
Focus on parameters that are:
- Relevant to the specific product
- Practical for quality inspectors to check
- Appropriate for the product's nature and use case
"""

# UPDATED: Simplified digitization prompt without forcing structure
DIGITIZE_SYSTEM_PROMPT = """
You are the Swift Check AI digitization assistant. Your job is to analyze text from scanned QC checklists and convert them into structured parameters.

# YOUR TASKS:
1. Recognize and preserve the document's original structure
2. Identify quality control parameters and their types
3. Extract specifications and measurement units where present
4. Determine appropriate parameter types based on the content
5. Maintain the document's original intent without adding extra requirements

# PARAMETER TYPE DETECTION:
- Image Upload: When document mentions photos, visual inspection, or attachments
- Toggle: For binary choices (Yes/No, Pass/Fail, Present/Absent)
- Checklist: For lists of items to verify
- Numeric Input: For measurements, counts, or quantities
- Text Input: For codes, dates, names, or identifiers
- Remarks: For comments, observations, or detailed notes
- Dropdown: For multiple predefined options

# OUTPUT FORMAT:
Provide parameters as found in the document without adding extra requirements.
Maintain the original document's scope and intent.
"""

def init_db():
    """Initialize database tables - runs once when app starts"""
    con = sql.connect("swift_check.db")
    cur = con.cursor()
    
    # Existing tables
    cur.execute("""
    CREATE TABLE IF NOT EXISTS qc_requests (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        doc_type TEXT NOT NULL,
        product_name TEXT NOT NULL,
        supplier_name TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        user_message TEXT
    )""")
    
    cur.execute("""
    CREATE TABLE IF NOT EXISTS llm_responses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_id INTEGER,
        llm_response TEXT,
        summary_text TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (request_id) REFERENCES qc_requests(id)
    )""")
    
    cur.execute("""
    CREATE TABLE IF NOT EXISTS parameters (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_id INTEGER,
        parameter_name TEXT,
        type TEXT,
        spec TEXT,
        dropdown_options TEXT,
        include_remarks TEXT,
        section TEXT,
        clause_reference TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (request_id) REFERENCES qc_requests(id)
    )""")
    
    cur.execute("""
    CREATE TABLE IF NOT EXISTS json_templates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_id INTEGER,
        template_json TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (request_id) REFERENCES qc_requests(id)
    )""")
    
    # NEW: API Logs table
    cur.execute("""
    CREATE TABLE IF NOT EXISTS api_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        request_id INTEGER,
        endpoint TEXT NOT NULL,
        method TEXT NOT NULL,
        client_ip TEXT,
        user_agent TEXT,
        request_data TEXT,
        response_data TEXT,
        file_info TEXT,
        processing_time_ms INTEGER,
        status_code INTEGER,
        error_message TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (request_id) REFERENCES qc_requests(id)
    )""")
    
    con.commit()
    con.close()

init_db()
def log_api_request(endpoint, method, request_id=None, file_info=None, processing_time=None, 
                   status_code=200, error_message=None, request_data=None, response_data=None):
    """Log API request details to database"""
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        # Get client info
        client_ip = request.remote_addr or 'unknown'
        user_agent = request.headers.get('User-Agent', 'unknown')[:500]  # Limit length
        
        # Truncate large data
        request_data_str = str(request_data)[:2000] if request_data else None
        response_data_str = str(response_data)[:1000] if response_data else None
        file_info_str = str(file_info)[:500] if file_info else None
        
        cur.execute("""
            INSERT INTO api_logs 
            (request_id, endpoint, method, client_ip, user_agent, request_data, 
             response_data, file_info, processing_time_ms, status_code, error_message)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (request_id, endpoint, method, client_ip, user_agent, request_data_str,
              response_data_str, file_info_str, processing_time, status_code, error_message))
        
        con.commit()
        con.close()
        
    except Exception as e:
        print(f"❌ Failed to log API request: {str(e)}")

def api_logger(endpoint_name):
    """Decorator to automatically log API requests"""
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            start_time = time.time()
            
            try:
                # Execute the original function
                result = func(*args, **kwargs)
                
                # Calculate processing time
                processing_time = int((time.time() - start_time) * 1000)
                
                # Extract request_id from result if it's a JSON response
                request_id = None
                if hasattr(result, 'get_json') and result.get_json():
                    request_id = result.get_json().get('request_id')
                elif isinstance(result, tuple) and len(result) > 0:
                    if hasattr(result[0], 'get_json') and result[0].get_json():
                        request_id = result[0].get_json().get('request_id')
                
                # Get file info if present
                file_info = None
                if hasattr(request, 'files') and request.files:
                    uploaded_files = []
                    for key, file in request.files.items():
                        if file and file.filename:
                            uploaded_files.append(f"{key}:{file.filename}")
                    if uploaded_files:
                        file_info = ", ".join(uploaded_files)
                
                # Log successful request
                log_api_request(
                    endpoint=endpoint_name,
                    method=request.method,
                    request_id=request_id,
                    file_info=file_info,
                    processing_time=processing_time,
                    status_code=200,
                    request_data=_get_safe_request_data(),
                    response_data=_get_safe_response_data(result)
                )
                
                return result
                
            except Exception as e:
                # Calculate processing time for errors too
                processing_time = int((time.time() - start_time) * 1000)
                
                # Log error
                log_api_request(
                    endpoint=endpoint_name,
                    method=request.method,
                    processing_time=processing_time,
                    status_code=500,
                    error_message=str(e),
                    request_data=_get_safe_request_data()
                )
                
                # Re-raise the exception
                raise e
                
        return wrapper
    return decorator

def _get_safe_request_data():
    """Safely extract request data for logging"""
    try:
        if request.content_type and 'application/json' in request.content_type:
            data = request.get_json()
            # Remove sensitive data
            if isinstance(data, dict):
                safe_data = {k: v for k, v in data.items() if k not in ['password', 'token', 'api_key']}
                return safe_data
        elif request.content_type and 'multipart/form-data' in request.content_type:
            # Get form data but not file contents
            safe_data = {}
            for key, value in request.form.items():
                safe_data[key] = value[:100] if len(str(value)) > 100 else value
            return safe_data
        return None
    except:
        return None

def _get_safe_response_data(result):
    """Safely extract response data for logging"""
    try:
        if hasattr(result, 'get_json'):
            data = result.get_json()
            if isinstance(data, dict):
                # Keep only essential response fields
                safe_data = {
                    'success': data.get('success'),
                    'request_id': data.get('request_id'),
                    'parameters_count': data.get('parameters_count'),
                    'message': data.get('message', '')[:200]  # Truncate message
                }
                return safe_data
        return None
    except:
        return None
def extract_top_level_json_array(text):
    """
    Extract JSON array from text, handling the new format
    """
    import re
    
    # Look for "**Parameters Generated:**" followed by JSON array
    pattern = r'\*\*Parameters Generated:\*\*\s*(\[.*?\])'
    match = re.search(pattern, text, re.DOTALL)
    
    if match:
        json_content = match.group(1).strip()
        print(f"📋 Found JSON after 'Parameters Generated:', length: {len(json_content)}")
        return json_content
    
    # Fallback: Look for any JSON array in the text
    start = text.find('[')
    if start == -1:
        print("❌ No JSON array found in text")
        return ""
    
    balance = 0
    end = start
    for i in range(start, len(text)):
        char = text[i]
        if char == '[':
            balance += 1
        elif char == ']':
            balance -= 1
            if balance == 0:
                end = i
                break
    
    json_content = text[start:end+1]
    print(f"📋 Found raw JSON array, length: {len(json_content)}")
    return json_content

# UPDATED: More flexible LLM call that respects user intent
def call_groq_llm(user_message, doc_type, product_name, supplier_name, existing_parameters=None, is_digitization=False):
    """
    Groq LLM call with comprehensive RAG support.
    Retrieves context from all 3 VDBs before calling the LLM.
    """
    if not Groq:
        return "Groq LLM call failed: 'groq' library not found or not installed."

    GROQ_API_KEY = "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"
    domain = "Food Manufacturing"

    # Get comprehensive context from all VDBs
    print(f"🔍 Retrieving context for: {product_name}")
    comprehensive_context = get_comprehensive_context(product_name, domain)
    
    # Format context for prompt - make it suggestive, not prescriptive
    formatted_context = format_context_for_prompt(comprehensive_context, max_length=4500)
    
    # Generate header and supplier info
    header_text = f"{product_name} {doc_type}"
    supplier_info = f"Supplier Name: {supplier_name}"
    
    # Check if user message contains reference document content
    has_reference = "Reference document content" in user_message
    
    # Select appropriate system prompt
    if is_digitization:
        system_instructions = DIGITIZE_SYSTEM_PROMPT
    else:
        system_instructions = SYSTEM_PROMPT

    # UPDATED: Context that emphasizes user requirements
    context = f"""
REFERENCE CONTEXT (Use as suggestions only, not requirements):
{formatted_context}

IMPORTANT: The user's specific requirements take absolute priority over any suggestions from the context above.
- If the user asks for specific parameters, provide only those
- If the user specifies a number of parameters, respect that number
- Don't add parameters the user didn't ask for
- Use context to understand the domain better, not to force requirements
"""
    
    if has_reference:
        context += f"""
The reference document is provided to understand structure and format, not to copy exactly.
Extract the pattern and adapt it specifically for {product_name}.
"""

    # UPDATED: Removed prescriptive requirements
    # Construct the final system prompt
    final_system_prompt = f"""
{system_instructions}

User context:
- Doc Type: {doc_type}
- Product: {product_name}
- Supplier: {supplier_name}

{context}

**VALID PARAMETER TYPES:**
Checklist, Dropdown, Image Upload, Remarks, Text Input, Numeric Input, Toggle

**USER INSTRUCTION PRIORITY:**
The user's message below contains their specific requirements. Follow these requirements exactly.
Do not add extra parameters unless the user explicitly asks for comprehensive coverage.

**OUTPUT INSTRUCTIONS:**
You MUST respond in EXACTLY this format:

**Summary:** [Brief explanation of what you're creating]

**Parameters Generated:**
[
  {{
    "action": "add",
    "Parameter": "Parameter Name",
    "Type": "Appropriate Type",
    "Spec": "Specification if applicable", 
    "DropdownOptions": "Options if dropdown/checklist",
    "IncludeRemarks": "Yes/No",
    "Section": "Logical Section",
    "ClauseReference": "Only if specifically relevant"
  }}
]

CRITICAL: Do NOT use code blocks. Put the JSON array directly in the response.
Generate exactly the number of parameters the user requested.
"""

    messages = [
        {"role": "system", "content": final_system_prompt},
        {"role": "user", "content": user_message},
    ]

    client = Groq(api_key=GROQ_API_KEY)

    try:
        response = client.chat.completions.create(
            messages=messages,
            model="llama-3.3-70b-versatile",
            stream=False,
            temperature=0.1  # Slightly increased for more dynamic responses
        )
        return response.choices[0].message.content.strip()
    except Exception as e:
        return f"Groq LLM call failed: {str(e)}"

def parse_llm_changes(llm_text):
    """Parse LLM response into summary and changes"""
    json_array_text = extract_top_level_json_array(llm_text)
    changes = []
    if json_array_text:
        try:
            changes = json.loads(json_array_text)
        except Exception as e:
            print("JSON parse error:", e)
    summary_text = llm_text.replace(json_array_text, "").strip() if json_array_text else llm_text.strip()
    return summary_text, changes


def apply_changes_to_params(parameters, changes):
    """Apply changes to parameters with fuzzy matching for edits"""
    valid_types = ["Checklist", "Dropdown", "Image Upload", "Remarks", "Text Input", "Numeric Input", "Toggle"]

    for change in changes:
        if not isinstance(change, dict):
            print(f"Skipping non-dict change: {change}")
            continue
            
        action = change.get("action", "").lower()
        p_name = change.get("Parameter", "Unnamed")
        options = change.get("DropdownOptions", "")
        checklist_options = change.get("ChecklistOptions", "")
        
        # Handle both DropdownOptions and ChecklistOptions
        if not options and checklist_options:
            options = checklist_options
        if isinstance(options, list):
            options = ", ".join(options)

        if action == "add":
            # Check if this is actually trying to modify an existing parameter
            existing_param = find_parameter_fuzzy(parameters, p_name)
            if existing_param:
                # This is actually an update
                action = "update"
            else:
                # Genuinely new parameter
                new_type = change.get("Type", "Text Input")
                if new_type not in valid_types:
                    new_type = "Text Input"
                    
                new_param = {
                    "Parameter": p_name,
                    "Type": new_type,
                    "Spec": change.get("Spec", ""),
                    "DropdownOptions": options, 
                    "IncludeRemarks": change.get("IncludeRemarks", "No"),
                    "Section": change.get("Section", "General"),
                    "ClauseReference": change.get("ClauseReference", "")
                }
                parameters.append(new_param)
            
        if action == "remove":
            # Use fuzzy matching to find parameter
            param_to_remove = find_parameter_fuzzy(parameters, p_name)
            if param_to_remove:
                parameters.remove(param_to_remove)
            else:
                # Try exact match as fallback
                parameters[:] = [p for p in parameters if p["Parameter"].lower() != p_name.lower()]
            
        if action == "update":
            # Use fuzzy matching to find parameter
            param_to_update = find_parameter_fuzzy(parameters, p_name)
            if param_to_update:
                # Update the found parameter
                new_type = change.get("Type")
                if new_type and new_type in valid_types:
                    param_to_update["Type"] = new_type
                
                if change.get("Spec") is not None:
                    param_to_update["Spec"] = change.get("Spec", "")
                
                # For dropdown/checklist options, append if adding
                if options and param_to_update["Type"] in ["Dropdown", "Checklist"]:
                    existing_options = param_to_update.get("DropdownOptions", "")
                    if existing_options:
                        # Check if we're adding to existing options
                        existing_list = [opt.strip() for opt in existing_options.split(",")]
                        new_list = [opt.strip() for opt in options.split(",")]
                        # Add only new options
                        for new_opt in new_list:
                            if new_opt not in existing_list:
                                existing_list.append(new_opt)
                        param_to_update["DropdownOptions"] = ", ".join(existing_list)
                    else:
                        param_to_update["DropdownOptions"] = options
                else:
                    if options:
                        param_to_update["DropdownOptions"] = options
                
                if change.get("IncludeRemarks") is not None:
                    param_to_update["IncludeRemarks"] = change.get("IncludeRemarks", "No")
                if change.get("Section") is not None:
                    param_to_update["Section"] = change.get("Section", "General")
                if change.get("ClauseReference") is not None:
                    param_to_update["ClauseReference"] = change.get("ClauseReference", "")

    return parameters

def _analyze_llm_response(llm_response):
    """Analyze LLM response to extract key information"""
    if not llm_response:
        return ""
    
    analysis_html = '<div class="json-analysis"><h4>📊 Response Analysis</h4>'
    
    # Try to extract JSON array
    import re
    json_match = re.search(r'\[.*?\]', llm_response, re.DOTALL)
    
    if json_match:
        try:
            import json
            json_content = json_match.group(0)
            parsed_json = json.loads(json_content)
            
            if isinstance(parsed_json, list):
                param_count = len(parsed_json)
                analysis_html += f'<p><strong>Parameters Generated:</strong> <span class="parameter-count">{param_count}</span></p>'
                
                # Analyze parameter types
                param_types = {}
                for param in parsed_json:
                    if isinstance(param, dict):
                        param_type = param.get('Type', 'Unknown')
                        param_types[param_type] = param_types.get(param_type, 0) + 1
                
                if param_types:
                    analysis_html += '<p><strong>Parameter Types:</strong></p><ul>'
                    for ptype, count in param_types.items():
                        analysis_html += f'<li>{ptype}: {count}</li>'
                    analysis_html += '</ul>'
                
                # Show first few parameter names
                param_names = [p.get('Parameter', 'Unnamed') for p in parsed_json[:5] if isinstance(p, dict)]
                if param_names:
                    analysis_html += f'<p><strong>Sample Parameters:</strong> {", ".join(param_names)}</p>'
                    if len(parsed_json) > 5:
                        analysis_html += f'<p><em>...and {len(parsed_json) - 5} more parameters</em></p>'
                        
        except json.JSONDecodeError:
            analysis_html += '<p><span style="color: #ffc107;">⚠️ JSON found but could not parse completely</span></p>'
    else:
        analysis_html += '<p><span style="color: #dc3545;">❌ No JSON array found in response</span></p>'
    
    # Check response length
    response_length = len(llm_response)
    if response_length > 10000:
        analysis_html += f'<p><strong>Response Size:</strong> <span style="color: #ffc107;">{response_length:,} characters (Large response)</span></p>'
    else:
        analysis_html += f'<p><strong>Response Size:</strong> {response_length:,} characters</p>'
    
    # Check for errors or issues
    if 'error' in llm_response.lower() or 'failed' in llm_response.lower():
        analysis_html += '<p><span style="color: #dc3545;">⚠️ Response may contain error indicators</span></p>'
    
    analysis_html += '</div>'
    return analysis_html



def generate_json_template(doc_type, product_name, supplier_name, parameters):
    """
    JSON template generation with intelligent parameter type handling.
    """
    header_text = f"{product_name} {doc_type}"
    template = {
        "templateId": "neY5j",
        "isDrafted": False,
        "pageStyle": {
            "margin": {
                "top": 10,
                "bottom": 10,
                "left": 10,
                "right": 10
            },
            "showPageNumber": False,
            "headerImgUrl": "",
            "fotterImgUrl": ""
        },
        "pageToolsDataList": [],
        "workflowInfo": {
            "currentState": "Draft",
            "approvalStates": ["Draft", "Under Review", "Approved", "Rejected"],
            "currentApprover": {
                "userId": "user123",
                "name": "Ashish Kumar",
                "role": "QC Manager"
            },
            "previousApprovers": [
                {
                    "userId": "user456",
                    "name": "Raj Singh",
                    "role": "QC Supervisor",
                    "approvalDate": "2025-05-01T10:30:00Z",
                    "status": "Approved",
                    "comments": "Looks good to me."
                }
            ],
            "nextApprovers": [
                {
                    "userId": "user789",
                    "name": "Priya Patel",
                    "role": "CEO"
                }
            ]
        }
    }
    
    def generate_tool_id():
        return ''.join(random.choices(string.ascii_lowercase + string.digits, k=5))
    
    # Add main header with attractive styling
    title_text = header_text
    heading_tool = {
        "toolId": generate_tool_id(),
        "toolType": "HEADING",
        "textData": {
            "text": title_text,
            "isBold": True,
            "isItalic": False,
            "isUnderlined": False,
            "textAliend": "CENTER",  # Center aligned
            "color": 4294967295,  # White
            "fontSize": 18  # Larger font
        },
        "boxData": {
            "fillColor": 4285238024,  # Attractive blue gradient
            "borderEnable": True,
            "borderColor": 4278190080,  # Black border
            "borderWidth": 2.0,
            "boxAlignment": "CENTER",
            "cornerRadius": {
                "topLeft": 12,
                "topRight": 12,
                "bottomLeft": 12,
                "bottomRight": 12
            },
            "padding": {
                "top": 15,
                "bottom": 15,
                "left": 20,
                "right": 20
            },
            "margin": {
                "top": 5,
                "bottom": 10,
                "left": 5,
                "right": 5
            }
        },
        "toolWidth": 1.7976931348623157e+308,
        "toolHeight": 60  # Taller header
    }
    template["pageToolsDataList"].append(heading_tool)

    # Add attractive supplier information with styling
    supplier_text = {
        "toolId": generate_tool_id(),
        "toolType": "HEADING",
        "textData": {
            "text": f"Supplier: {supplier_name}",
            "isBold": True,
            "isItalic": False,
            "isUnderlined": False,
            "textAliend": "CENTER",  # Center aligned
            "color": 4294967295,  # White
            "fontSize": 14
        },
        "boxData": {
            "fillColor": 4287137928,  # Different attractive color
            "borderEnable": True,
            "borderColor": 4278190080,
            "borderWidth": 1.0,
            "boxAlignment": "CENTER",
            "cornerRadius": {
                "topLeft": 8,
                "topRight": 8,
                "bottomLeft": 8,
                "bottomRight": 8
            },
            "padding": {
                "top": 8,
                "bottom": 8,
                "left": 15,
                "right": 15
            },
            "margin": {
                "top": 0,
                "bottom": 15,
                "left": 10,
                "right": 10
            }
        },
        "toolHeight": 40,
        "toolWidth": 1.7976931348623157e+308
    }
    template["pageToolsDataList"].append(supplier_text)
    
    # Group parameters by section for better organization
    sections = {}
    for param in parameters:
        section = param.get("Section", "General Parameters")
        if section not in sections:
            sections[section] = []
        sections[section].append(param)
    
    # Add parameters organized by sections
    for section_name, section_params in sections.items():
        # Add section header with center alignment and varied colors
        if section_name != "General Parameters":
            # Define colors for different sections
            section_colors = {
                "Visual Inspection": 4294951175,      # Light Blue
                "Measurements": 4294934352,           # Orange  
                "Quality Checks": 4283215696,         # Green
                "Documentation": 4294946816,          # Purple
                "Safety & Compliance": 4294198070,    # Red
                "Additional Observations": 4288585374, # Gray
                "General": 4283215696                 # Green (default)
            }

            section_color = section_colors.get(section_name, 4283215696)

            section_header = {
                "toolId": generate_tool_id(),
                "toolType": "HEADING",
                "textData": {
                    "text": section_name.upper(),
                    "isBold": True,
                    "isItalic": False,
                    "isUnderlined": False,
                    "textAliend": "CENTER",  # Center aligned
                    "color": 4294967295,  # White text
                    "fontSize": 15
                },
                "boxData": {
                    "fillColor": section_color,  # Varied colors
                    "borderEnable": True,
                    "borderColor": 4278190080,  # Black border
                    "borderWidth": 1.5,
                    "boxAlignment": "CENTER",
                    "cornerRadius": {
                        "topLeft": 8,
                        "topRight": 8,
                        "bottomLeft": 8,
                        "bottomRight": 8
                    },
                    "padding": {
                        "top": 8,
                        "bottom": 8,
                        "left": 12,
                        "right": 12
                    },
                    "margin": {
                        "top": 10,
                        "bottom": 5,
                        "left": 0,
                        "right": 0
                    }
                },
                "toolHeight": 45,
                "toolWidth": 1.7976931348623157e+308
            }
            template["pageToolsDataList"].append(section_header)# Add section header
        
        # Add parameters in this section
        for param in section_params:
            param_name = param.get("Parameter", "")
            param_type = param.get("Type", "Text Input")
            spec = param.get("Spec", "")
            options = param.get("DropdownOptions", "")
            include_remarks = param.get("IncludeRemarks", "No")
            clause_ref = param.get("ClauseReference", "")
            
            # Create display name with clause reference
            display_name = param_name
            if clause_ref:
                display_name += f" ({clause_ref})"
            
            # Split options into a list if it's a string
            option_list = []
            if isinstance(options, str) and options.strip():
                option_list = [opt.strip() for opt in options.split(",") if opt.strip()]
            
            # PARAMETER TYPE HANDLING
            if param_type == "Image Upload":
                # Create image upload tool with toggle
                image_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "IMAGE",
                    "imageLableData": {
                        "text": display_name + ":",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 14,
                        "lablePositioned": "LEFT",
                        "spacing": 10,
                        "txtColor": 4278190080,  # Black
                        "showLable": True
                    },
                    "imageData": {
                        "showImageUploadArea": True,
                        "width": 200,
                        "height": 150
                    },
                    "iconData": 57344,
                    "showIcon": False,
                    "iconCodePoint": 59729,
                    "iconSize": 30,
                    "iconColor": 4278190080,  # Black
                    "toolHeight": 160,
                    "toolWidth": 1.7976931348623157e+308,
                    "showToggle": True,
                    "imageToggleData": {
                        "label": "Assessment",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 14,
                        "showLabel": True,
                        "enabledText": "Acceptable",
                        "disabledText": "Not Acceptable",
                        "enabledColor": 4283215696,  # Green
                        "disabledColor": 4294198070,  # Red
                        "isSelected": True
                    }
                }
                template["pageToolsDataList"].append(image_tool)
                
            elif param_type == "Toggle":
                # Create toggle tool
                toggle_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "TOGGLE",
                    "toggleData": {
                        "disabledColor": 4294198070,  # Red
                        "disabledText": "Not Acceptable" if not option_list else option_list[1] if len(option_list) > 1 else "No",
                        "enabledColor": 4283215696,  # Green
                        "enabledText": "Acceptable" if not option_list else option_list[0] if option_list else "Yes",
                        "showLabel": True,
                        "label": display_name,
                        "labelFontSize": 14,
                        "labelTextColor": 4278190080,  # Black
                        "isBold": True,
                        "isItalic": False,
                        "isSelected": True,
                        "toggleTextFontSize": 12,
                        "toggleTextIsBold": False
                    },
                    "toolWidth": 1.7976931348623157e+308,
                    "toolHeight": 80
                }
                template["pageToolsDataList"].append(toggle_tool)
                
            elif param_type == "Dropdown":
                # Create dropdown tool
                dropdown_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "DROPDOWN",
                    "dropdownData": {
                        "hintText": f"Select {param_name.lower()}",
                        "hintTextColor": 4288585374,  # Gray
                        "hintFontSize": 14,
                        "dropdownWidth": 350,
                        "spacingBetweeenLableAndDropdownWidth": 10,
                        "showLable": True,
                        "labelText": display_name,
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "lablePositioned": "TOP",
                        "labelFontSize": 14,
                        "lableTextColor": 4278190080,  # Black
                        "numberOfOptions": len(option_list) if option_list else 3,
                        "optionFontSize": 14,
                        "optionTextColor": 4278190080,  # Black
                        "optionLst": option_list if option_list else ["Acceptable", "Marginal", "Not Acceptable"],
                        "selectedOptionIndex": -1
                    },
                    "toolHeight": 90,
                    "toolWidth": 1.7976931348623157e+308
                }
                template["pageToolsDataList"].append(dropdown_tool)
                
            elif param_type == "Checklist":
                # Create checkbox tool for checklists
                if not option_list:
                    option_list = ["Item 1", "Item 2", "Item 3"]
                    
                checkbox_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "CHECKBOX",
                    "checkboxData": {
                        "numberOfCheckboxes": len(option_list),
                        "checkboxBgColor": 4294967295,  # White
                        "spacing": 8,
                        "runSpacing": 8,
                        "checkboxTileWidth": 140,
                        "checkBoxAlignmentEnum": "HORIZONTAL",
                        "checkBoxButtonStyleEnum": "CHECKBOX",
                        "checkBoxPositionedEnum": "START",
                        "checkBoxSelectionModeEnum": "MULTIPLE",
                        "isBold": False,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 13,
                        "lablePositioned": "LEFT",
                        "txtColor": 4278190080,  # Black
                        "labelLst": option_list,
                        "showLable": True,
                        "selectedIndexLstForMultiSelect": [],
                        "selectedIndexForSingleSelect": 0
                    },
                    "toolWidth": 1.7976931348623157e+308,
                    "toolHeight": max(100, len(option_list) * 15 + 40)  # Dynamic height based on items
                }
                
                # Add section label for checklist
                checklist_label = {
                    "toolId": generate_tool_id(),
                    "toolType": "TEXT",
                    "textData": {
                        "text": display_name + ":",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "color": 4278190080,  # Black
                        "fontSize": 14
                    },
                    "toolHeight": 25,
                    "toolWidth": 1.7976931348623157e+308
                }
                template["pageToolsDataList"].append(checklist_label)
                template["pageToolsDataList"].append(checkbox_tool)
                
            elif param_type == "Numeric Input":
                # Create numeric input with specification
                label_text = display_name
                if spec:
                    label_text += f" (Spec: {spec})"
                    
                numeric_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "TEXTAREA",
                    "lableData": {
                        "text": label_text + ":",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 14,
                        "lablePositioned": "TOP_LEFT",
                        "spacing": 5,
                        "txtColor": 4278190080,  # Black
                        "showLable": True
                    },
                    "textAreaData": {
                        "isFilled": True,
                        "fillColor": 4292927712,  # Light gray
                        "borderType": "UNDERLINED",
                        "storkStyle": "LINE",
                        "dummyTxt": "Enter numeric value" + (f" ({spec})" if spec else ""),
                        "borderColor": 4278190080,  # Black
                        "isBold": False,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 12,
                        "txtColor": 4288585374  # Gray
                    },
                    "toolHeight": 75,
                    "toolWidth": 1.7976931348623157e+308,
                    "toggleData": {
                        "label": "Status",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 12,
                        "showLabel": True,
                        "enabledText": "Within Spec",
                        "disabledText": "Out of Spec",
                        "enabledColor": 4283215696,  # Green
                        "disabledColor": 4294198070,  # Red
                        "isSelected": True
                    },
                    "showToggle": True  # Show toggle for spec compliance
                }
                template["pageToolsDataList"].append(numeric_tool)
                
            elif param_type == "Text Input":
                # Create text input
                text_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "TEXTAREA",
                    "lableData": {
                        "text": display_name + ":",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 14,
                        "lablePositioned": "TOP_LEFT",
                        "spacing": 5,
                        "txtColor": 4278190080,  # Black
                        "showLable": True
                    },
                    "textAreaData": {
                        "isFilled": True,
                        "fillColor": 4292927712,  # Light gray
                        "borderType": "UNDERLINED",
                        "storkStyle": "LINE",
                        "dummyTxt": "Enter " + param_name.lower(),
                        "borderColor": 4278190080,  # Black
                        "isBold": False,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 12,
                        "txtColor": 4288585374  # Gray
                    },
                    "toolHeight": 65,
                    "toolWidth": 1.7976931348623157e+308,
                    "showToggle": False
                }
                template["pageToolsDataList"].append(text_tool)
                
            elif param_type == "Remarks":
                # Create remarks/textarea
                remarks_tool = {
                    "toolId": generate_tool_id(),
                    "toolType": "TEXTAREA",
                    "lableData": {
                        "text": display_name + ":",
                        "isBold": True,
                        "isItalic": False,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 14,
                        "lablePositioned": "TOP_LEFT",
                        "spacing": 5,
                        "txtColor": 4278190080,  # Black
                        "showLable": True
                    },
                    "textAreaData": {
                        "isFilled": True,
                        "fillColor": 4292927712,  # Light gray
                        "borderType": "UNDERLINED",
                        "storkStyle": "LINE",
                        "dummyTxt": "Enter detailed observations and remarks",
                        "borderColor": 4278190080,  # Black
                        "isBold": False,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 12,
                        "txtColor": 4288585374  # Gray
                    },
                    "toolHeight": 100,  # Larger height for remarks
                    "toolWidth": 1.7976931348623157e+308,
                    "showToggle": False
                }
                template["pageToolsDataList"].append(remarks_tool)
            
            # Add additional remarks field if requested and not already a remarks parameter
            if include_remarks == "Yes" and param_type != "Remarks":
                additional_remarks = {
                    "toolId": generate_tool_id(),
                    "toolType": "TEXTAREA",
                    "lableData": {
                        "text": f"{param_name} - Additional Remarks:",
                        "isBold": False,
                        "isItalic": True,
                        "isUnderlined": False,
                        "textAliend": "LEFT",
                        "fontSize": 12,
                        "lablePositioned": "TOP_LEFT",
                        "spacing": 5,
                        "txtColor": 4278190080,  # Black
                        "showLable": True
                    },
                    "textAreaData": {
                        "isFilled": True,
                        "fillColor": 4292927712,  # Light gray
                        "borderType": "UNDERLINED",
                        "storkStyle": "LINE",
                        "dummyTxt": "Additional observations or corrective actions",
                        "borderColor": 4278190080,  # Black
                        "isBold": False,
                        "isItalic": False,
                        "isUnderlined": False,
                        "fontSize": 11,
                        "txtColor": 4288585374  # Gray
                    },
                    "toolHeight": 60,
                    "toolWidth": 1.7976931348623157e+308,
                    "showToggle": False
                }
                template["pageToolsDataList"].append(additional_remarks)
    
    # Add final overall assessment section
    # Check if final assessment already exists in parameters
    has_final_assessment = False
    for param in parameters:
        param_name = param.get("Parameter", "").lower()
        if ("overall" in param_name and "assessment" in param_name) or ("final" in param_name and "assessment" in param_name):
            has_final_assessment = True
            break



    if not has_final_assessment:
        # Add final overall assessment section
        final_assessment_header = {
            "toolId": generate_tool_id(),
            "toolType": "TEXT",
            "textData": {
                "text": "FINAL ASSESSMENT",
                "isBold": True,
                "isItalic": False,
                "isUnderlined": True,
                "textAliend": "CENTER",
                "color": 4283215696,  # Green
                "fontSize": 16
            },
            "toolHeight": 40,
            "toolWidth": 1.7976931348623157e+308
        }
        template["pageToolsDataList"].append(final_assessment_header)

        # Overall quality assessment toggle
        overall_toggle = {
            "toolId": generate_tool_id(),
            "toolType": "TOGGLE",
            "toggleData": {
                "disabledColor": 4294198070,  # Red
                "disabledText": "REJECTED",
                "enabledColor": 4283215696,  # Green
                "enabledText": "APPROVED",
                "showLabel": True,
                "label": "Overall Quality Assessment",
                "labelFontSize": 15,
                "labelTextColor": 4278190080,  # Black
                "isBold": True,
                "isItalic": False,
                "isSelected": True,
                "toggleTextFontSize": 14,
                "toggleTextIsBold": True
            },
            "toolWidth": 1.7976931348623157e+308,
            "toolHeight": 100
        }
        template["pageToolsDataList"].append(overall_toggle)

    # Inspector signature and date
    inspector_info = {
        "toolId": generate_tool_id(),
        "toolType": "TEXTAREA",
        "lableData": {
            "text": "Inspector Name & Signature:",
            "isBold": True,
            "isItalic": False,
            "isUnderlined": False,
            "textAliend": "LEFT",
            "fontSize": 14,
            "lablePositioned": "TOP_LEFT",
            "spacing": 5,
            "txtColor": 4278190080,  # Black
            "showLable": True
        },
        "textAreaData": {
            "isFilled": True,
            "fillColor": 4292927712,  # Light gray
            "borderType": "UNDERLINED",
            "storkStyle": "LINE",
            "dummyTxt": "Inspector name and signature",
            "borderColor": 4278190080,  # Black
            "isBold": False,
            "isItalic": False,
            "isUnderlined": False,
            "fontSize": 12,
            "txtColor": 4288585374  # Gray
        },
        "toolHeight": 80,
        "toolWidth": 1.7976931348623157e+308,
        "showToggle": False
    }
    template["pageToolsDataList"].append(inspector_info)
    
    # Final comprehensive remarks
    final_remarks = {
        "toolId": generate_tool_id(),
        "toolType": "TEXTAREA",
        "lableData": {
            "text": "Final Comprehensive Remarks:",
            "isBold": True,
            "isItalic": False,
            "isUnderlined": False,
            "textAliend": "LEFT",
            "fontSize": 14,
            "lablePositioned": "TOP_LEFT",
            "spacing": 5,
            "txtColor": 4278190080,  # Black
            "showLable": True
        },
        "textAreaData": {
            "isFilled": True,
            "fillColor": 4292927712,  # Light gray
            "borderType": "UNDERLINED",
            "storkStyle": "LINE",
            "dummyTxt": "Overall assessment, corrective actions, and additional observations",
            "borderColor": 4278190080,  # Black
            "isBold": False,
            "isItalic": False,
            "isUnderlined": False,
            "fontSize": 12,
            "txtColor": 4288585374  # Gray
        },
        "toolHeight": 120,
        "toolWidth": 1.7976931348623157e+308,
        "showToggle": False
    }
    template["pageToolsDataList"].append(final_remarks)
    
    return template

# OCR and text extraction functions
def extract_text_from_document(filepath, file_ext):
    """text extraction with better table structure recognition"""
    try:
        extracted_text = ""
        
        if file_ext == 'pdf':
            # Use PyMuPDF with table detection
            pdf_document = fitz.open(filepath)
            
            for page_num in range(pdf_document.page_count):
                page = pdf_document[page_num]
                
                # Try to extract text directly first
                text = page.get_text()
                
                # If minimal text found, use OCR
                if len(text.strip()) < 100:
                    # Convert page to high-quality image for OCR
                    mat = fitz.Matrix(3, 3)  # Higher zoom for better OCR
                    pix = page.get_pixmap(matrix=mat)
                    img_data = pix.pil_tobytes(format="PNG")
                    
                    # OCR with better table handling
                    from io import BytesIO
                    image = Image.open(BytesIO(img_data))
                    
                    # Use OCR configuration optimized for tables
                    custom_config = r'--oem 3 --psm 6 -c preserve_interword_spaces=1'
                    text = pytesseract.image_to_string(image, config=custom_config)
                
                # text processing to preserve table structure
                processed_text = enhance_table_structure(text)
                extracted_text += f"\n=== PAGE {page_num + 1} ===\n{processed_text}\n"
            
            pdf_document.close()
            
        else:  # Image files
            image = Image.open(filepath)
            # OCR for images with table preservation
            custom_config = r'--oem 3 --psm 6 -c preserve_interword_spaces=1'
            text = pytesseract.image_to_string(image, config=custom_config)
            extracted_text = enhance_table_structure(text)
        
        return extracted_text.strip()
    
    except Exception as e:
        print(f"Error during document processing: {str(e)}")
        return None

def enhance_table_structure(text):
    """Enhance text to better preserve table structures and headings"""
    if not text:
        return text
    
    # Preserve important section headings
    section_patterns = [
        (r'(ORGANOLEPTIC\s+EVALUATION)', r'\n## \1\n'),
        (r'(COOKING\s+DETAILS)', r'\n## \1\n'),
        (r'(PACKAGING\s*&\s*FREEZING)', r'\n## \1\n'),
        (r'(FREEZING\s+DETAILS)', r'\n## \1\n'),
        (r'(METAL\s+SCREENING)', r'\n## \1\n'),
        (r'(SIZE\s+VARIATIONS)', r'\n## \1\n'),
        (r'(COLOUR\s+VARIATIONS)', r'\n## \1\n'),
        (r'(EVALUATION\s+OF\s+PASTRY)', r'\n## \1\n'),
        (r'(FINAL\s+ASSESSMENT)', r'\n## \1\n'),
    ]
    
    processed_text = text
    for pattern, replacement in section_patterns:
        processed_text = re.sub(pattern, replacement, processed_text, flags=re.IGNORECASE)
    
    # Preserve parameter-value pairs
    param_patterns = [
        (r'([A-Za-z\s]+):\s*(Acceptable|Non-acceptable|Present|Absent|To be mentioned)', r'**\1**: \2'),
        (r'([A-Za-z\s]+)\s+(Sam\s+\d+)', r'**\1** - \2'),
        (r'(Temperature|Weight|Time|Dimension[s]?)[:\s]+([0-9\-\+\±°C\s\w]+)', r'**\1**: \2'),
    ]
    
    for pattern, replacement in param_patterns:
        processed_text = re.sub(pattern, replacement, processed_text, flags=re.IGNORECASE)
    
    # Clean up excessive whitespace while preserving structure
    processed_text = re.sub(r'\n\s*\n\s*\n', '\n\n', processed_text)
    processed_text = re.sub(r'[ \t]+', ' ', processed_text)
    
    return processed_text

def extract_metadata_from_ocr(ocr_text):
    """metadata extraction with better pattern recognition"""
    # document type detection
    doc_type_patterns = {
        r'(?i)(MALABAR\s*PARATHA.*INSPECTION)': "Malabar Paratha Inspection Record",
        r'(?i)(GREEN\s*PEAS.*INSPECTION)': "Green Peas Inspection Record", 
        r'(?i)(VEGETABLE\s*SAMOSA.*INSPECTION)': "Vegetable Samosa Inspection Record",
        r'(?i)(CONTAINER.*INSPECTION.*REPORT)': "Container Inspection Report",
        r'(?i)quality\s*(?:control)?\s*checklist': "Quality Control Checklist",
        r'(?i)inspection\s*(?:record|checklist)': "Inspection Checklist",
        r'(?i)pre[\-\s]shipment.*inspection': "Pre-Shipment Inspection",
    }
    
    detected_doc_type = "Quality Control Checklist"  # Default
    for pattern, doc_type in doc_type_patterns.items():
        if re.search(pattern, ocr_text):
            detected_doc_type = doc_type
            break
    
    # product name extraction
    product_patterns = [
        r'(?i)product\s*(?:name|description)[:\-\s]*([^\n]{1,50})',
        r'(?i)(MALABAR\s*PARATHA)',
        r'(?i)(GREEN\s*PEAS)',
        r'(?i)(VEGETABLE\s*SAMOSA[S]?)',
        r'(?i)(SWEET\s*CORN)',
    ]
    
    detected_product = "Food Product"  # Default
    for pattern in product_patterns:
        match = re.search(pattern, ocr_text)
        if match:
            detected_product = match.group(1).strip()
            break
    
    # supplier name extraction
    supplier_patterns = [
        r'(?i)supplier\s*(?:name)?[:\-\s]*([^\n]{1,40})',
        r'(?i)manufacturing\s*unit[:\-\s]*([^\n]{1,40})',
        r'(?i)(AL\s*KABEER)',
        r'(?i)(CASCADE\s*MARINE)',
        r'(?i)(SAHAR\s*FOOD)',
    ]
    
    detected_supplier = ""  # Default empty
    for pattern in supplier_patterns:
        match = re.search(pattern, ocr_text)
        if match:
            detected_supplier = match.group(1).strip()
            break
    
    return detected_doc_type, detected_product, detected_supplier

# File upload handling
ALLOWED_EXTENSIONS = {'pdf', 'png', 'jpg', 'jpeg'}

def allowed_file(filename):
    """Check if file extension is allowed"""
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

def fetch_json_from_firebase(firebase_json_url):
    """Fetch JSON template from Firebase Storage URL"""
    try:
        response = requests.get(firebase_json_url)
        if response.status_code == 200:
            return response.json()
        else:
            return None
    except Exception as e:
        print(f"Error fetching JSON from Firebase: {str(e)}")
        return None
# Add these new functions after the existing utility functions
# Add these new functions after the existing utility functions (after parse_edit_instructions)

# REPLACE THIS ENTIRE FUNCTION  
def validate_parameters(parameters: List[Dict]) -> Dict:
    """
    Validate parameters for duplicates, missing fields, and consistency.
    Returns a validation report with issues and suggestions.
    """
    if not parameters or not isinstance(parameters, list):
        return {
            "is_valid": True,  # Changed to True for empty case
            "duplicate_parameters": [],
            "missing_required_fields": [],
            "empty_options": [],
            "invalid_types": [],
            "similar_parameters": [],
            "suggestions": []
        }
    
    validation_report = {
        "is_valid": True,
        "duplicate_parameters": [],
        "missing_required_fields": [],
        "empty_options": [],
        "invalid_types": [],
        "similar_parameters": [],
        "suggestions": []
    }
    
    # Check for exact duplicates
    seen_params = {}
    for i, param in enumerate(parameters):
        if not isinstance(param, dict):
            continue
            
        param_name = param.get("Parameter", "").strip().lower()
        if not param_name:
            continue
            
        if param_name in seen_params:
            validation_report["duplicate_parameters"].append({
                "parameter": param.get("Parameter", ""),
                "indices": [seen_params[param_name], i]
            })
            validation_report["is_valid"] = False
        else:
            seen_params[param_name] = i
    
    # Check for similar parameters (same meaning)
    from difflib import SequenceMatcher
    param_names = [(i, p.get("Parameter", "")) for i, p in enumerate(parameters) if isinstance(p, dict) and p.get("Parameter", "").strip()]
    
    for i, (idx1, name1) in enumerate(param_names):
        for j, (idx2, name2) in enumerate(param_names[i+1:], i+1):
            similarity = SequenceMatcher(None, name1.lower(), name2.lower()).ratio()
            if similarity > 0.8:  # Very similar
                validation_report["similar_parameters"].append({
                    "param1": name1,
                    "param2": name2,
                    "similarity": round(similarity, 2),
                    "indices": [idx1, idx2]
                })
                validation_report["suggestions"].append(
                    f"'{name1}' and '{name2}' seem very similar - consider merging them"
                )
    
    # Validate parameter types
    valid_types = ["Checklist", "Dropdown", "Image Upload", "Remarks", "Text Input", "Numeric Input", "Toggle"]
    
    for i, param in enumerate(parameters):
        if not isinstance(param, dict):
            continue
            
        param_name = param.get("Parameter", "")
        param_type = param.get("Type", "")
        
        # Check required fields
        if not param_name.strip():
            validation_report["missing_required_fields"].append({
                "index": i,
                "field": "Parameter name"
            })
            validation_report["is_valid"] = False
        
        # Check valid type
        if param_type not in valid_types:
            validation_report["invalid_types"].append({
                "parameter": param_name,
                "invalid_type": param_type,
                "suggested_type": "Text Input"
            })
            validation_report["is_valid"] = False
        
        # Check options for Dropdown/Checklist
        if param_type in ["Dropdown", "Checklist"]:
            options = param.get("DropdownOptions", "").strip()
            if not options:
                validation_report["empty_options"].append({
                    "parameter": param_name,
                    "type": param_type
                })
                validation_report["suggestions"].append(
                    f"'{param_name}' is a {param_type} but has no options defined"
                )
    
    return validation_report

def deduplicate_parameters(parameters: List[Dict]) -> List[Dict]:
    """Remove exact duplicates and merge very similar parameters"""
    if not parameters or not isinstance(parameters, list):
        return []
    
    # Remove exact duplicates first
    seen = {}
    unique_params = []
    
    for param in parameters:
        if not isinstance(param, dict):
            continue
            
        param_key = param.get("Parameter", "").strip().lower()
        if not param_key:
            continue
            
        if param_key not in seen:
            seen[param_key] = param
            unique_params.append(param)
        else:
            # Merge information from duplicate
            existing = seen[param_key]
            
            # Keep the more detailed spec
            if not existing.get("Spec", "") and param.get("Spec", ""):
                existing["Spec"] = param.get("Spec", "")
            
            # Merge options for dropdowns/checklists
            if existing.get("Type") in ["Dropdown", "Checklist"]:
                existing_options = existing.get("DropdownOptions", "")
                new_options = param.get("DropdownOptions", "")
                
                if existing_options and new_options:
                    existing_list = [opt.strip() for opt in existing_options.split(",")]
                    new_list = [opt.strip() for opt in new_options.split(",")]
                    
                    # Add unique new options
                    for opt in new_list:
                        if opt and opt not in existing_list:
                            existing_list.append(opt)
                    
                    existing["DropdownOptions"] = ", ".join(existing_list)
    
    return unique_params

def merge_similar_parameters(param1: Dict, param2: Dict) -> Dict:
    """
    Intelligently merge two similar parameters.
    Keeps the best information from both.
    """
    # Use the longer/more descriptive name
    name1 = param1.get("Parameter", "")
    name2 = param2.get("Parameter", "")
    merged_name = name1 if len(name1) >= len(name2) else name2
    
    # Use the more specific type
    type_priority = {
        "Text Input": 1,
        "Remarks": 2,
        "Numeric Input": 3,
        "Toggle": 4,
        "Dropdown": 5,
        "Checklist": 6,
        "Image Upload": 7
    }
    
    type1 = param1.get("Type", "Text Input")
    type2 = param2.get("Type", "Text Input")
    
    merged_type = type1 if type_priority.get(type1, 0) > type_priority.get(type2, 0) else type2
    
    # Merge options
    options1 = param1.get("DropdownOptions", "")
    options2 = param2.get("DropdownOptions", "")
    
    if options1 and options2:
        opts1_list = [opt.strip() for opt in options1.split(",")]
        opts2_list = [opt.strip() for opt in options2.split(",")]
        all_opts = list(set(opts1_list + opts2_list))
        merged_options = ", ".join(all_opts)
    else:
        merged_options = options1 or options2
    
    # Merge other fields
    merged = {
        "Parameter": merged_name,
        "Type": merged_type,
        "Spec": param1.get("Spec", "") or param2.get("Spec", ""),
        "DropdownOptions": merged_options,
        "IncludeRemarks": param1.get("IncludeRemarks", "No") if param1.get("IncludeRemarks", "No") == "Yes" else param2.get("IncludeRemarks", "No"),
        "Section": param1.get("Section", "") or param2.get("Section", "General"),
        "ClauseReference": param1.get("ClauseReference", "") or param2.get("ClauseReference", "")
    }
    
    return merged

def organize_parameters_by_section(parameters: List[Dict]) -> List[Dict]:
    """
    Organize parameters into logical sections based on their type and content.
    This helps create a more structured checklist.
    """
    # Categorize parameters
    sections = {
        "Visual Inspection": [],
        "Measurements": [],
        "Quality Checks": [],
        "Documentation": [],
        "Safety & Compliance": [],
        "Additional Observations": [],
        "General": []
    }
    
    for param in parameters:
        param_name_lower = param.get("Parameter", "").lower()
        param_type = param.get("Type", "")
        
        # Categorize based on type and name
        if param_type == "Image Upload" or any(word in param_name_lower for word in ["appearance", "visual", "color", "colour"]):
            sections["Visual Inspection"].append(param)
        elif param_type == "Numeric Input" or any(word in param_name_lower for word in ["weight", "dimension", "size", "temperature", "time"]):
            sections["Measurements"].append(param)
        elif param_type in ["Toggle", "Dropdown"] or any(word in param_name_lower for word in ["quality", "acceptable", "condition"]):
            sections["Quality Checks"].append(param)
        elif any(word in param_name_lower for word in ["batch", "code", "date", "supplier", "document"]):
            sections["Documentation"].append(param)
        elif any(word in param_name_lower for word in ["safety", "foreign", "contamination", "hazard", "compliance"]):
            sections["Safety & Compliance"].append(param)
        elif param_type == "Remarks" or any(word in param_name_lower for word in ["remark", "comment", "observation", "note"]):
            sections["Additional Observations"].append(param)
        else:
            sections["General"].append(param)
    
    # Rebuild parameter list in organized order
    organized = []
    for section_name, section_params in sections.items():
        if section_params:  # Only include non-empty sections
            for param in section_params:
                param["Section"] = section_name
                organized.append(param)
    
    return organized

def suggest_missing_parameters(parameters: List[Dict], product_name: str) -> List[str]:
    """
    Suggest potentially missing parameters based on existing ones.
    These are suggestions only, not requirements.
    """
    suggestions = []
    param_names_lower = [p.get("Parameter", "").lower() for p in parameters]
    param_types = [p.get("Type", "") for p in parameters]
    
    # Basic suggestions based on common patterns
    # Weight often needs dimensions
    if any("weight" in name for name in param_names_lower) and not any("dimension" in name or "size" in name for name in param_names_lower):
        suggestions.append("Consider adding dimensions/size parameters if relevant")
    
    # Visual inspection might benefit from quality assessment
    if "Image Upload" in param_types and not any("Toggle" in param_types):
        suggestions.append("Consider adding a quality assessment toggle for visual inspections")
    
    # If there are multiple checks but no remarks
    if len(parameters) > 5 and "Remarks" not in param_types:
        suggestions.append("Consider adding a remarks field for additional observations")
    
    # Product-specific suggestions
    product_lower = product_name.lower()
    
    # Temperature-sensitive products
    if any(word in product_lower for word in ["frozen", "ice cream", "chilled"]) and not any("temperature" in name for name in param_names_lower):
        suggestions.append("This appears to be a temperature-sensitive product - consider temperature checks if needed")
    
    # Batch tracking
    if not any(word in name for word in ["batch", "lot", "code"] for name in param_names_lower):
        suggestions.append("Consider adding batch/lot tracking if traceability is important")
    
    return suggestions

# REPLACE THIS ENTIRE FUNCTION
def apply_intelligent_fixes(parameters: List[Dict]) -> List[Dict]:
    """Apply intelligent fixes to parameter issues"""
    if not parameters or not isinstance(parameters, list):
        return []
    
    fixed_params = []
    valid_types = ["Checklist", "Dropdown", "Image Upload", "Remarks", "Text Input", "Numeric Input", "Toggle"]
    
    for param in parameters:
        if not isinstance(param, dict):
            continue
            
        fixed_param = param.copy()
        
        # Fix empty parameter names
        if not fixed_param.get("Parameter", "").strip():
            fixed_param["Parameter"] = f"Parameter {len(fixed_params) + 1}"
        
        # Fix invalid types
        if fixed_param.get("Type", "") not in valid_types:
            param_name_lower = fixed_param.get("Parameter", "").lower()
            
            if any(word in param_name_lower for word in ["remark", "comment", "note", "observation"]):
                fixed_param["Type"] = "Remarks"
            elif any(word in param_name_lower for word in ["photo", "image", "picture", "visual"]):
                fixed_param["Type"] = "Image Upload"
            elif any(word in param_name_lower for word in ["weight", "temperature", "dimension", "size", "measurement"]):
                fixed_param["Type"] = "Numeric Input"
            elif any(word in param_name_lower for word in ["acceptable", "pass", "fail", "yes", "no"]):
                fixed_param["Type"] = "Toggle"
            elif fixed_param.get("DropdownOptions", ""):
                options = fixed_param.get("DropdownOptions", "").split(",")
                fixed_param["Type"] = "Checklist" if len(options) > 4 else "Dropdown"
            else:
                fixed_param["Type"] = "Text Input"
        
        # Add default options for empty dropdowns/checklists
        if fixed_param["Type"] in ["Dropdown", "Checklist"] and not fixed_param.get("DropdownOptions", "").strip():
            if fixed_param["Type"] == "Dropdown":
                fixed_param["DropdownOptions"] = "Acceptable, Marginal, Not Acceptable"
            else:
                fixed_param["DropdownOptions"] = "Item 1, Item 2, Item 3, Item 4"
        
        # Ensure section is set
        if not fixed_param.get("Section", "").strip():
            fixed_param["Section"] = "General"
        
        # Set default values for missing fields
        if not fixed_param.get("Spec"):
            fixed_param["Spec"] = ""
        if not fixed_param.get("IncludeRemarks"):
            fixed_param["IncludeRemarks"] = "No"
        if not fixed_param.get("ClauseReference"):
            fixed_param["ClauseReference"] = ""
        
        fixed_params.append(fixed_param)
    
    return fixed_params

# Update the refine endpoint to include validation
def find_parameter_fuzzy(parameters, search_term):
    """
    Fuzzy matching to find parameters by similar names.
    Returns the best matching parameter or None.
    """
    from difflib import SequenceMatcher
    
    search_term_lower = search_term.lower().strip()
    best_match = None
    best_score = 0.0
    
    for param in parameters:
        param_name_lower = param.get("Parameter", "").lower().strip()
        
        # Direct substring match
        if search_term_lower in param_name_lower or param_name_lower in search_term_lower:
            return param
        
        # Calculate similarity score
        score = SequenceMatcher(None, search_term_lower, param_name_lower).ratio()
        
        # Also check individual words
        search_words = search_term_lower.split()
        param_words = param_name_lower.split()
        
        for s_word in search_words:
            for p_word in param_words:
                word_score = SequenceMatcher(None, s_word, p_word).ratio()
                if word_score > score:
                    score = word_score
        
        if score > best_score and score > 0.6:  # Threshold for fuzzy match
            best_score = score
            best_match = param
    
    return best_match

def parse_edit_instructions(user_message, existing_params):
    """
    Parse user's edit instructions to understand intent.
    Returns structured instructions for the LLM.
    """
    instructions = {
        'commands': [],
        'has_reorder': False,
        'has_supplier_change': False,
        'has_doc_type_change': False
    }
    
    message_lower = user_message.lower()
    
    # Check for parameter modifications
    modification_patterns = [
        (r'add\s+(.+?)\s+to\s+(.+)', 'add_to_existing'),
        (r'change\s+(.+?)\s+to\s+(.+)', 'change_type'),
        (r'remove\s+(.+)', 'remove'),
        (r'delete\s+(.+)', 'remove'),
        (r'add\s+(?:a\s+)?(?:new\s+)?parameter\s+(?:for\s+)?(.+)', 'add_new'),
        (r'rename\s+(.+?)\s+to\s+(.+)', 'rename'),
    ]
    
    for pattern, action in modification_patterns:
        matches = re.finditer(pattern, message_lower, re.IGNORECASE)
        for match in matches:
            instructions['commands'].append({
                'action': action,
                'match': match.groups()
            })
    
    # Check for reordering
    if any(word in message_lower for word in ['reorder', 'reorganize', 'rearrange', 'move', 'at the start', 'at the end', 'first', 'last']):
        instructions['has_reorder'] = True
    
    # Check for supplier/doc type changes
    if 'supplier' in message_lower:
        instructions['has_supplier_change'] = True
    if 'doc type' in message_lower or 'document type' in message_lower:
        instructions['has_doc_type_change'] = True
    
    return instructions

def generate_edit_prompt(user_message, existing_params, doc_type, product_name, supplier_name):
    """
    Generate a specific prompt for edit operations that ensures precise changes.
    """
    # Parse instructions
    parsed_instructions = parse_edit_instructions(user_message, existing_params)
    
    edit_prompt = f"""
You are in PRECISE EDIT MODE for an existing QC checklist. You must follow commands EXACTLY as given.

CURRENT TEMPLATE INFO:
- Product: {product_name}
- Document Type: {doc_type}
- Supplier: {supplier_name}
- Existing Parameters: {len(existing_params)}

EXISTING PARAMETERS:
{json.dumps(existing_params, indent=2)}

USER'S EDIT COMMAND:
"{user_message}"

EDIT MODE RULES (FOLLOW EXACTLY):
1. If user asks to change supplier name, change ONLY the supplier name
2. If user asks to change product name, change ONLY the product name  
3. If user asks to change document type, change ONLY the document type
4. If user asks to add specific parameters, add ONLY those parameters
5. If user asks to remove parameters, remove ONLY those parameters
6. Do NOT add Overall Quality Assessment unless specifically requested
7. Do NOT add extra parameters unless explicitly asked
8. Execute commands literally - no interpretation or suggestions

SUPPLIER/PRODUCT/DOCTYPE CHANGES:
- If command contains "change supplier to [X]" or "supplier name [X]", update supplier_name to X
- If command contains "change product to [X]" or "product name [X]", update product_name to X
- If command contains "change doc type to [X]" or "document type [X]", update doc_type to X

PARAMETER CHANGES:
- "add [parameter name]" = add that specific parameter only
- "remove [parameter name]" = remove that specific parameter only
- "change [old] to [new]" = rename/modify that specific parameter only

OUTPUT FORMAT:
1. Summary: State exactly what changes were made
2. Updated supplier_name: [new name if changed, otherwise original]
3. Updated product_name: [new name if changed, otherwise original] 
4. Updated doc_type: [new type if changed, otherwise original]
4. JSON array with ALL parameters (existing + modifications)

Execute the command precisely. No additions, no interpretations, no suggestions.
"""
    
    return edit_prompt
# Keep generate_json_template as is for now (will update in Phase 2)
@app.route("/")
def index():
    return """
    <html>
    <head>
        <title>Swift Check API</title>
        <style>
            body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }
            .container { max-width: 1200px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
            .endpoint { margin: 25px 0; border: 1px solid #ddd; padding: 20px; border-radius: 8px; background: #fafafa; }
            .new { border-left: 4px solid #28a745; background: #f8fff9; }
            .{ border-left: 4px solid #007bff; background: #f8fcff; }
            code { background-color: #e9ecef; padding: 3px 6px; font-family: 'Courier New', monospace; border-radius: 3px; }
            pre { background-color: #e9ecef; padding: 15px; border-radius: 5px; overflow: auto; }
            h1 { color: #333; text-align: center; margin-bottom: 30px; }
            h2 { color: #4CAF50; }
            h3 { color: #333; }
            .method { font-weight: bold; color: #e74c3c; }
            .optional { color: #3498db; }
            .badge { background: #28a745; color: white; padding: 2px 8px; border-radius: 12px; font-size: 11px; margin-left: 10px; }
            .features { display: grid; grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); gap: 20px; margin: 20px 0; }
            .feature { padding: 15px; background: #e8f5e8; border-radius: 8px; border-left: 4px solid #28a745; }
        </style>
    </head>
    <body>
        <div class="container">
            <h1>Swift Check API </h1>
            
  
            <h2>API Endpoints:</h2>
            
            <div class="endpoint ">
                <h3><span class="method">POST</span> <code>/refine</code> - Create QC Template</h3>
                <p>Creates comprehensive quality control templates with 15+ parameters, regulatory compliance, and intelligent type selection.</p>
                <h4>Parameters:</h4>
                <ul>
                    <li><code>doc_type</code> <strong>(required)</strong> - Document type</li>
                    <li><code>product_name</code> <strong>(required)</strong> - Product name</li>
                    <li><code>supplier_name</code> <strong>(required)</strong> - Supplier name</li>
                    <li><code>user_message</code> <span class="optional">(optional)</span> - Additional instructions</li>
                    <li><code>context_file</code> <span class="optional">(optional)</span> - Reference document</li>
                </ul>
            </div>
            
            <div class="endpoint ">
                <h3><span class="method">POST</span> <code>/edit</code> - Edit existing template</h3>
                <p>Modifies existing templates using comprehensive context and intelligent parameter optimization.</p>
            </div>   

            
            <div class="endpoint new">
                <h3><span class="method">POST</span> <code>/digitize</code> - Document Digitization</h3>
                <p>OCR processing with table structure recognition and intelligent parameter extraction.</p>
                
                <h4>Parameters (multipart/form-data):</h4>
                <ul>
                    <li><code>checklist_file</code> <strong>(required)</strong> - Scanned document</li>
                    <li><code>doc_type</code> <span class="optional">(optional)</span> - Document type</li>
                    <li><code>product_name</code> <span class="optional">(optional)</span> - Product name</li>
                    <li><code>supplier_name</code> <span class="optional">(optional)</span> - Supplier name</li>
                </ul>
            </div>
            
            <div class="endpoint">
                <h3><span class="method">GET</span> <code>/template/{request_id}</code> - Get Template JSON</h3>
                <p>Returns professionally formatted JSON templates with intelligent parameter types.</p>
            </div>
            
            <div class="endpoint">
                <h3><span class="method">GET</span> <code>/history</code> - View Request History</h3>
                <p>Browse all QC requests with preview and download options.</p>
            </div>
            <div class="endpoint new">
                <h3><span class="method">GET</span> <code>/logs</code> - API Logs Dashboard<span class="badge">NEW</span></h3>
                <p>Comprehensive API activity monitoring with request details, processing times, file uploads, and error tracking.</p>
                
                <h4>Features:</h4>
                <ul>
                    <li><strong>Request Tracking:</strong> All API calls with endpoint, method, and status</li>
                    <li><strong>File Information:</strong> Track uploaded files (OCR documents, reference files)</li>
                    <li><strong>Performance Metrics:</strong> Processing times and success rates</li>
                    <li><strong>Error Monitoring:</strong> Detailed error messages and troubleshooting</li>
                    <li><strong>Filtering:</strong> Filter by endpoint, status, or request ID</li>
                </ul>
                
                <h4>Query Parameters:</h4>
                <ul>
                    <li><code>format=json</code> - Get logs in JSON format</li>
                    <li><code>request_id=123</code> - Filter by specific request</li>
                    <li><code>endpoint=refine</code> - Filter by endpoint</li>
                    <li><code>limit=100</code> - Limit number of results</li>
                </ul>
            </div>
        </div>
    </body>
    </html>
    """# UPDATED: refine endpoint to respect user requirements
@app.route("/refine", methods=["POST"])
@api_logger("/refine")
def refine_parameters():
    """refine endpoint that respects user intent and requirements"""
    global global_parameters
    global global_json_template

    print(">> /refine route called <<")

    # Handle both form data and JSON
    if request.content_type and request.content_type.startswith('multipart/form-data'):
        data = {
            "doc_type": request.form.get("doc_type", ""),
            "product_name": request.form.get("product_name", ""),
            "supplier_name": request.form.get("supplier_name", ""),
            "user_message": request.form.get("user_message", "")
        }
            
        # Handle file upload with OCR
        uploaded_file = request.files.get('context_file')
        file_context = ""
        
        if uploaded_file and allowed_file(uploaded_file.filename):
            filename = secure_filename(uploaded_file.filename)
            file_ext = filename.rsplit('.', 1)[1].lower()
            
            temp_dir = tempfile.mkdtemp()
            filepath = os.path.join(temp_dir, filename)
            uploaded_file.save(filepath)
            
            # text extraction
            extracted_text = extract_text_from_document(filepath, file_ext)
            
            os.unlink(filepath)
            os.rmdir(temp_dir)
            
            if extracted_text:
                file_context = f"\n\nReference document content ({filename}):\n{extracted_text}"
                print(f"✅ OCR extracted {len(extracted_text)} characters from {filename}")
            else:
                file_context = f"\n\n[Failed to extract text from {filename}]"
                
    else:
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON payload found"}), 400
        file_context = ""

    # Validate required fields
    doc_type = data.get("doc_type", "")
    product_name = data.get("product_name", "")
    supplier_name = data.get("supplier_name", "")
    
    if not doc_type:
        return jsonify({"error": "doc_type is required"}), 400
    if not product_name:
        return jsonify({"error": "product_name is required"}), 400
    if not supplier_name:
        return jsonify({"error": "supplier_name is required"}), 400
    
    # UPDATED: Only use default prompt if user didn't provide any message
    user_message = data.get("user_message", "")
    if not user_message:
        user_message = DEFAULT_REFINE_PROMPT
    # REMOVED: Don't append default prompt to user's message

    # Add file context to user message if available
    if file_context:
        user_message += file_context

    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        # Insert main request
        cur.execute("""
            INSERT INTO qc_requests 
            (doc_type, product_name, supplier_name, user_message) 
            VALUES (?, ?, ?, ?)
        """, (doc_type, product_name, supplier_name, user_message))
        
        request_id = cur.lastrowid
        print(f"✅ Created request with ID: {request_id}")
        
        # Call LLM with user-focused approach
        llm_response = call_groq_llm(
            user_message=user_message,
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            is_digitization=False
        )

        print("\n🎯 LLM RESPONSE:")
        print("=" * 50)
        print(llm_response[:500] + "..." if len(llm_response) > 500 else llm_response)
        print("=" * 50)

        # Parse response
        summary_text, changes_list = parse_llm_changes(llm_response)
        
        # Store LLM response
        cur.execute("""
            INSERT INTO llm_responses 
            (request_id, llm_response, summary_text) 
            VALUES (?, ?, ?)
        """, (request_id, llm_response, summary_text))
        
        # Apply changes
        updated_params = apply_changes_to_params([], changes_list)
        updated_params = apply_intelligent_fixes(updated_params)
    
    # Validate parameters
        # Validate and fix parameters
        validation_report = validate_parameters(updated_params)
        
        if not validation_report["is_valid"]:
            print("⚠️ Validation issues found, applying fixes...")
            
            # Remove duplicates
            if validation_report["duplicate_parameters"]:
                updated_params = deduplicate_parameters(updated_params)
                print(f"✅ Removed {len(validation_report['duplicate_parameters'])} duplicate parameters")
            
            # Fix invalid types and missing fields
            if validation_report["invalid_types"] or validation_report["missing_required_fields"]:
                updated_params = apply_intelligent_fixes(updated_params)
                print("✅ Fixed parameter issues")
                
            # Re-validate after fixes
            validation_report = validate_parameters(updated_params)
        
        suggestions = validation_report.get("suggestions", [])
        global_parameters = updated_params

        print(f"✅ Generated {len(updated_params)} validated parameters")
        
        print(f"✅ Generated {len(updated_params)} parameters as requested")
        
        # Store parameters
        for param in updated_params:
            cur.execute("""
                INSERT INTO parameters 
                (request_id, parameter_name, type, spec, dropdown_options, include_remarks, section, clause_reference) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (request_id, 
                  param.get("Parameter", ""), 
                  param.get("Type", ""), 
                  param.get("Spec", ""), 
                  param.get("DropdownOptions", ""), 
                  param.get("IncludeRemarks", "No"),
                  param.get("Section", "General"),
                  param.get("ClauseReference", "")))
        
        # Generate JSON template
        json_template = generate_json_template(
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            parameters=updated_params
        )
        global_json_template = json_template
        
        # Store JSON template
        cur.execute("""
            INSERT INTO json_templates 
            (request_id, template_json) 
            VALUES (?, ?)
        """, (request_id, json.dumps(json_template)))
        
        con.commit()
        con.close()
        
        # UPDATED: Response that reflects actual user request
        response_data = {
        "success": True, 
        "request_id": request_id,
        "message": f"QC template created with {len(updated_params)} validated parameters", 
        "summary": summary_text,
        "parameters_count": len(updated_params),
        "validation": {
            "issues_fixed": False,
            "duplicates_removed": 0,
            "similar_parameters": 0,
            "suggestions": suggestions
        },
        "user_driven": True
        }
        
        if file_context:
            response_data["file_info"] = f"Reference document processed: {filename}" if 'filename' in locals() else "Reference document processed"
            
        return jsonify(response_data)
        
    except Exception as e:
        print(f"❌ Error in /refine: {str(e)}")
        if 'con' in locals():
            con.rollback()
            con.close()
        return jsonify({"error": str(e)}), 500

@app.route("/edit", methods=["POST"])
@api_logger("/edit")
def edit_parameters():
    """Edit endpoint that modifies existing templates precisely as commanded"""
    global global_parameters
    global global_json_template

    print(">> /edit route called <<")

    # Handle both form data and JSON
    if request.content_type and request.content_type.startswith('multipart/form-data'):
        data = {
            "request_id": request.form.get("request_id"),
            "user_message": request.form.get("user_message", "")
        }
            
        # Handle context file upload with OCR
        uploaded_file = request.files.get('context_file')
        file_context = ""
        
        if uploaded_file and allowed_file(uploaded_file.filename):
            filename = secure_filename(uploaded_file.filename)
            file_ext = filename.rsplit('.', 1)[1].lower()
            
            temp_dir = tempfile.mkdtemp()
            filepath = os.path.join(temp_dir, filename)
            uploaded_file.save(filepath)
            
            extracted_text = extract_text_from_document(filepath, file_ext)
            
            os.unlink(filepath)
            os.rmdir(temp_dir)
            
            if extracted_text:
                file_context = f"\n\nReference document content ({filename}):\n{extracted_text}"
                print(f"✅ OCR extracted {len(extracted_text)} characters from {filename}")

        # Handle JSON template file upload
        json_template_file = request.files.get('json_template_file')
        json_template_data = None
        
        if json_template_file and json_template_file.filename.endswith('.json'):
            try:
                json_content = json_template_file.read().decode('utf-8')
                json_template_data = json.loads(json_content)
                print(f"✅ JSON template file loaded: {json_template_file.filename}")
            except Exception as e:
                print(f"❌ Error loading JSON file: {str(e)}")
                return jsonify({"error": f"Invalid JSON file: {str(e)}"}), 400
                
    else:
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON payload found"}), 400
        file_context = ""
        json_template_data = data.get("json_template_data")

    # Validate required fields
    user_message = data.get("user_message", "")
    if not user_message:
        return jsonify({"error": "user_message is required for editing"}), 400
    
    request_id = data.get("request_id")
    
    if not request_id and not json_template_data:
        return jsonify({"error": "Either request_id or json_template_file is required"}), 400
    
    # Add file context to user message if available
    if file_context:
        user_message += file_context

    try:
        existing_parameters = []
        doc_type = ""
        product_name = ""
        supplier_name = ""
        original_json_template = None
        
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        if request_id:
            try:
                request_id = int(request_id)
            except ValueError:
                return jsonify({"error": "request_id must be a valid integer"}), 400
                
            # Fetch original request data
            cur.execute("""
                SELECT doc_type, product_name, supplier_name
                FROM qc_requests 
                WHERE id = ?
            """, (request_id,))
            
            original_data = cur.fetchone()
            if not original_data:
                con.close()
                return jsonify({"error": f"Request ID {request_id} not found"}), 404
            
            doc_type, product_name, supplier_name = original_data
            
            # Fetch existing parameters
            cur.execute("""
                SELECT parameter_name, type, spec, dropdown_options, include_remarks, section, clause_reference
                FROM parameters 
                WHERE request_id = ?
                ORDER BY id
            """, (request_id,))
            
            param_rows = cur.fetchall()
            existing_parameters = [
                {
                    "Parameter": row[0], 
                    "Type": row[1], 
                    "Spec": row[2], 
                    "DropdownOptions": row[3], 
                    "IncludeRemarks": row[4],
                    "Section": row[5] or "General",
                    "ClauseReference": row[6] or ""
                } 
                for row in param_rows
            ]
            
            # Also fetch the original JSON template
            cur.execute("""
                SELECT template_json
                FROM json_templates
                WHERE request_id = ?
            """, (request_id,))
            
            template_result = cur.fetchone()
            if template_result:
                original_json_template = json.loads(template_result[0])
            
        elif json_template_data:
            # Extract from JSON template
            original_json_template = json_template_data
            existing_parameters = []
            
            # Extract basic info from template
            for tool in json_template_data.get("pageToolsDataList", []):
                if tool.get("toolType") == "HEADING":
                    title_text = tool.get("textData", {}).get("text", "")
                    parts = title_text.split(" ", 1)
                    if len(parts) >= 2:
                        product_name = parts[0]
                        doc_type = parts[1]
                    else:
                        product_name = title_text
                        doc_type = "Inspection Document"
                    break
                    
            # Extract supplier info
            for tool in json_template_data.get("pageToolsDataList", []):
                if tool.get("toolType") == "TEXT":
                    text = tool.get("textData", {}).get("text", "")
                    if "Supplier" in text:
                        supplier_name = text.replace("Supplier Name:", "").strip()
                        break
            
            # Extract parameters from JSON
            for tool in json_template_data.get("pageToolsDataList", []):
                tool_type = tool.get("toolType", "")
                
                if tool_type == "DROPDOWN":
                    dropdown_data = tool.get("dropdownData", {})
                    existing_parameters.append({
                        "Parameter": dropdown_data.get("labelText", "Dropdown Field"),
                        "Type": "Dropdown",
                        "Spec": "",
                        "DropdownOptions": ", ".join(dropdown_data.get("optionLst", [])),
                        "IncludeRemarks": "No",
                        "Section": "General",
                        "ClauseReference": ""
                    })
                elif tool_type == "CHECKBOX":
                    # Find the label for this checkbox
                    checkbox_label = "Checklist"
                    tools_list = json_template_data.get("pageToolsDataList", [])
                    tool_index = tools_list.index(tool)
                    if tool_index > 0:
                        prev_tool = tools_list[tool_index - 1]
                        if prev_tool.get("toolType") == "TEXT":
                            checkbox_label = prev_tool.get("textData", {}).get("text", "").replace(":", "")
                    
                    checkbox_data = tool.get("checkboxData", {})
                    existing_parameters.append({
                        "Parameter": checkbox_label,
                        "Type": "Checklist",
                        "Spec": "",
                        "DropdownOptions": ", ".join(checkbox_data.get("labelLst", [])),
                        "IncludeRemarks": "No",
                        "Section": "General",
                        "ClauseReference": ""
                    })
                elif tool_type == "IMAGE":
                    image_data = tool.get("imageLableData", {})
                    existing_parameters.append({
                        "Parameter": image_data.get("text", "Image Upload").replace(":", ""),
                        "Type": "Image Upload",
                        "Spec": "",
                        "DropdownOptions": "",
                        "IncludeRemarks": "Yes" if tool.get("showToggle", False) else "No",
                        "Section": "Visual Inspection",
                        "ClauseReference": ""
                    })
                elif tool_type == "TOGGLE":
                    toggle_data = tool.get("toggleData", {})
                    existing_parameters.append({
                        "Parameter": toggle_data.get("label", "Toggle Assessment"),
                        "Type": "Toggle",
                        "Spec": "",
                        "DropdownOptions": f"{toggle_data.get('enabledText', 'Yes')}, {toggle_data.get('disabledText', 'No')}",
                        "IncludeRemarks": "No",
                        "Section": "Assessment",
                        "ClauseReference": ""
                    })
                elif tool_type == "TEXTAREA":
                    label_data = tool.get("lableData", {})
                    text_area_data = tool.get("textAreaData", {})
                    label_text = label_data.get("text", "").replace(":", "")
                    
                    # Skip if it's an additional remarks field
                    if "Additional Remarks" in label_text:
                        continue
                    
                    if "Remarks" in label_text or "remarks" in text_area_data.get("dummyTxt", ""):
                        param_type = "Remarks"
                    elif "numeric" in text_area_data.get("dummyTxt", "").lower():
                        param_type = "Numeric Input"
                    else:
                        param_type = "Text Input"
                        
                    existing_parameters.append({
                        "Parameter": label_text,
                        "Type": param_type,
                        "Spec": "",
                        "DropdownOptions": "",
                        "IncludeRemarks": "No",
                        "Section": "General",
                        "ClauseReference": ""
                    })
        
        # Check if user wants to change supplier name or doc type
        message_lower = user_message.lower()
        if 'supplier' in message_lower:
            # Extract new supplier name
            supplier_match = re.search(r'supplier\s*(?:name)?\s*(?:to|=|:)\s*(.+?)(?:\.|$)', user_message, re.IGNORECASE)
            if supplier_match:
                supplier_name = supplier_match.group(1).strip()
                print(f"📝 Updating supplier name to: {supplier_name}")
        
        # Generate edit-specific prompt
        edit_prompt = generate_edit_prompt(
            user_message=user_message,
            existing_params=existing_parameters,
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name
        )
        
        # Call LLM with edit prompt
        llm_response = call_groq_llm(
            user_message=edit_prompt,
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            existing_parameters=existing_parameters,
            is_digitization=False
        )

        print(f"\n🎯 EDIT LLM RESPONSE:")
        print("=" * 50)
        print(llm_response[:500] + "..." if len(llm_response) > 500 else llm_response)
        print("=" * 50)

        
        # Parse response to extract name changes
        summary_text, changes_list = parse_llm_changes(llm_response)
        
        # Check for supplier/product/doctype changes in the response
        response_lines = llm_response.split('\n')
        for line in response_lines:
            if 'Updated supplier_name:' in line:
                new_supplier = line.split('Updated supplier_name:')[1].strip()
                # Clean asterisks and extra formatting
                new_supplier = new_supplier.replace('**', '').replace('*', '').strip()
                if new_supplier and new_supplier != supplier_name and new_supplier != "Yash Gori":
                    supplier_name = new_supplier
                    print(f"✅ Updated supplier name to: {supplier_name}")
            
            if 'Updated product_name:' in line:
                new_product = line.split('Updated product_name:')[1].strip()
                # Clean asterisks and extra formatting  
                new_product = new_product.replace('**', '').replace('*', '').strip()
                if new_product and new_product != product_name and new_product != "Heading":
                    product_name = new_product
                    print(f"✅ Updated product name to: {product_name}")
                    
            if 'Updated doc_type:' in line:
                new_doctype = line.split('Updated doc_type:')[1].strip()
                # Clean asterisks and extra formatting
                new_doctype = new_doctype.replace('**', '').replace('*', '').strip()
                if new_doctype and new_doctype != doc_type and new_doctype != "Inspection Document":
                    doc_type = new_doctype
                    print(f"✅ Updated doc type to: {doc_type}")# Parse response
        
        # Apply changes and validate for duplicates
        if isinstance(changes_list, list):
            updated_params = []
            
            # If we got a complete list, use it
            if changes_list and all(change.get("Parameter") for change in changes_list):
                for change in changes_list:
                    param_type = change.get("Type", "Text Input")
                    if param_type not in ["Checklist", "Dropdown", "Image Upload", "Remarks", "Text Input", "Numeric Input", "Toggle"]:
                        param_type = "Text Input"
                    
                    updated_params.append({
                        "Parameter": change.get("Parameter", ""),
                        "Type": param_type,
                        "Spec": change.get("Spec", ""),
                        "DropdownOptions": change.get("DropdownOptions", "") or change.get("ChecklistOptions", ""),
                        "IncludeRemarks": change.get("IncludeRemarks", "No"),
                        "Section": change.get("Section", "General"),
                        "ClauseReference": change.get("ClauseReference", "")
                    })
            else:
                # Fallback to applying changes to existing parameters
                updated_params = apply_changes_to_params(existing_parameters.copy(), changes_list)
        else:
            print(f"⚠️ Warning: changes_list is not a list: {type(changes_list)}")
            updated_params = existing_parameters.copy()
        
        # Validate for duplicates and similar parameters in edits
        validation_report = validate_parameters(updated_params)
        
        if validation_report["duplicate_parameters"] or validation_report["similar_parameters"]:
            print("⚠️ Found duplicates/similar parameters in edit, cleaning up...")
            updated_params = deduplicate_parameters(updated_params)
            updated_params = apply_intelligent_fixes(updated_params)
            print("✅ Cleaned up duplicate parameters")

        # Validate
        validation_report = validate_parameters(updated_params)

        if not validation_report["is_valid"]:
            print("⚠️ Validation issues found in edit, applying fixes...")
            updated_params = deduplicate_parameters(updated_params)
            updated_params = apply_intelligent_fixes(updated_params)

        # Organize if user requested reordering
        if "reorder" in user_message.lower() or "organize" in user_message.lower():
            updated_params = organize_parameters_by_section(updated_params)
            print("✅ Parameters reorganized by section")

        global_parameters = updated_params

        
        # Create new request ID for this edit
        cur.execute("""
            INSERT INTO qc_requests 
            (doc_type, product_name, supplier_name, user_message) 
            VALUES (?, ?, ?, ?)
        """, (doc_type, product_name, supplier_name, f"EDIT: {user_message}"))
        
        created_id = cur.lastrowid
        print(f"✅ Created edit version with ID: {created_id}")
        
        # Store LLM response
        cur.execute("""
            INSERT INTO llm_responses 
            (request_id, llm_response, summary_text) 
            VALUES (?, ?, ?)
        """, (created_id, llm_response, summary_text))
        
        # Store updated parameters
        for param in updated_params:
            cur.execute("""
                INSERT INTO parameters 
                (request_id, parameter_name, type, spec, dropdown_options, include_remarks, section, clause_reference) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (created_id, 
                  param.get("Parameter", ""), 
                  param.get("Type", ""), 
                  param.get("Spec", ""), 
                  param.get("DropdownOptions", ""), 
                  param.get("IncludeRemarks", "No"),
                  param.get("Section", "General"),
                  param.get("ClauseReference", "")))
        
        # Generate updated JSON template
        json_template = generate_json_template(
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            parameters=updated_params
        )
        global_json_template = json_template
        
        # Store JSON template
        cur.execute("""
            INSERT INTO json_templates 
            (request_id, template_json) 
            VALUES (?, ?)
        """, (created_id, json.dumps(json_template)))
        
        con.commit()
        con.close()
        
        # Calculate what changed
        params_before = len(existing_parameters)
        params_after = len(updated_params)
        params_added = params_after - params_before
        
        response_data = {
            "success": True, 
            "request_id": created_id,
            "message": f"Edit completed: {summary_text[:100]}...", 
            "summary": summary_text,
            "parameters_count": params_after,
            "changes_made": {
                "before": params_before,
                "after": params_after,
                "added": max(0, params_added),
                "removed": max(0, -params_added)
            },
            "edit_command": user_message
        }
        
        if request_id:
            response_data["original_request_id"] = request_id
        if json_template_data:
            response_data["json_template_processed"] = True
        if file_context:
            response_data["file_info"] = f"Reference document used"
            
        return jsonify(response_data)
        
    except Exception as e:
        print(f"❌ Error in /edit: {str(e)}")
        if 'con' in locals():
            con.rollback()
            con.close()
        return jsonify({"error": str(e)}), 500
    
    
@app.route("/validate", methods=["POST"])
@api_logger("/validate")
def validate_template():
    """Validate an existing template and get suggestions"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON payload found"}), 400
        
        parameters = data.get("parameters", [])
        product_name = data.get("product_name", "Product")
        
        if not parameters:
            return jsonify({"error": "No parameters provided for validation"}), 400
        
        # Validate
        validation_report = validate_parameters(parameters)
        
        # Get suggestions
        suggestions = suggest_missing_parameters(parameters, product_name)
        
        # Prepare response
        response = {
            "is_valid": validation_report["is_valid"],
            "validation_report": validation_report,
            "suggestions": suggestions,
            "parameter_count": len(parameters)
        }
        
        # If requested, return fixed parameters
        if data.get("auto_fix", False) and not validation_report["is_valid"]:
            fixed_params = apply_intelligent_fixes(parameters)
            fixed_params = deduplicate_parameters(fixed_params)
            
            if data.get("organize", False):
                fixed_params = organize_parameters_by_section(fixed_params)
            
            response["fixed_parameters"] = fixed_params
            response["fixed_count"] = len(fixed_params)
        
        return jsonify(response)
        
    except Exception as e:
        print(f"❌ Error in /validate: {str(e)}")
        return jsonify({"error": str(e)}), 500
    
@app.route("/digitize", methods=["POST"])
@api_logger("/digitize")
def digitize_checklist():
    """digitization that preserves original document structure without adding extras"""
    print(">> /digitize route called <<")
    
    if 'checklist_file' not in request.files:
        return jsonify({"error": "No file uploaded"}), 400
    
    file = request.files['checklist_file']
    
    if file.filename == '':
        return jsonify({"error": "No file selected"}), 400
    
    if not allowed_file(file.filename):
        return jsonify({"error": "Invalid file type. Allowed: PDF, PNG, JPG, JPEG"}), 400
    
    # Get optional parameters
    doc_type = request.form.get("doc_type", "")
    product_name = request.form.get("product_name", "")
    supplier_name = request.form.get("supplier_name", "")
    
    try:
        filename = secure_filename(file.filename)
        temp_dir = tempfile.mkdtemp()
        filepath = os.path.join(temp_dir, filename)
        file.save(filepath)

        # text extraction with structure preservation
        file_ext = filename.rsplit('.', 1)[1].lower()
        extracted_text = extract_text_from_document(filepath, file_ext)

        os.unlink(filepath)
        os.rmdir(temp_dir)

        if not extracted_text:
            return jsonify({"error": "Failed to extract text from file"}), 500

        print(f"✅ OCR extracted {len(extracted_text)} characters from {filename}")
        print(f"📄 Preview: {extracted_text[:300]}...")

        # metadata extraction
        if not doc_type or not product_name or not supplier_name:
            detected_doc_type, detected_product, detected_supplier = extract_metadata_from_ocr(extracted_text)
            
            if not doc_type:
                doc_type = detected_doc_type
            if not product_name:
                product_name = detected_product
            if not supplier_name:
                supplier_name = detected_supplier

        # UPDATED: Simplified digitization prompt
        llm_prompt = f"""
DIGITIZATION REQUEST:
Please convert the following scanned QC checklist into structured parameters.

Document Info:
- File: {filename}
- Document Type: {doc_type}
- Product: {product_name}
- Supplier: {supplier_name}

EXTRACTED TEXT:
{extracted_text}

INSTRUCTIONS:
1. Extract ONLY the parameters that exist in the document
2. Preserve the original structure and organization
3. Determine appropriate parameter types based on the document content
4. Don't add any parameters not present in the original document
5. Maintain the original scope - if it's a simple checklist, keep it simple
"""
        
        # Call LLM for digitization
        llm_response = call_groq_llm(
            user_message=llm_prompt,
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            is_digitization=True
        )
        
        print(f"\n🎯 DIGITIZATION LLM RESPONSE:")
        print("=" * 50)
        print(llm_response[:500] + "..." if len(llm_response) > 500 else llm_response)
        print("=" * 50)
        
        # Parse parameters
        json_array_text = extract_top_level_json_array(llm_response)
        parameters = []
        
        if json_array_text:
            try:
                parameters = json.loads(json_array_text)
                # Process parameters
                processed_params = []
                for param in parameters:
                    if isinstance(param, dict) and param.get("Parameter", "").strip():
                        param_name = param.get("Parameter", "").strip()
                        if param_name and param_name.lower() not in ["unknown", "parameter", "option", "item"]:
                            processed_params.append(param)
                parameters = processed_params
            except Exception as e:
                print(f"❌ JSON parse error: {e}")
                return jsonify({"error": f"Failed to parse LLM response: {str(e)}"}), 500
        
        if not parameters:
            return jsonify({"error": "No meaningful parameters extracted from document"}), 500
        
        # Save to database
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        cur.execute("""
            INSERT INTO qc_requests 
            (doc_type, product_name, supplier_name) 
            VALUES (?, ?, ?)
        """, (doc_type, product_name, supplier_name))
        
        request_id = cur.lastrowid
        
        # Store LLM response
        cur.execute("""
            INSERT INTO llm_responses 
            (request_id, llm_response, summary_text) 
            VALUES (?, ?, ?)
        """, (request_id, llm_response, f"Digitized {len(parameters)} parameters from {filename}"))
        
        # Store parameters
        for param in parameters:
            options = param.get("DropdownOptions", "")
            if not options:
                options = param.get("ChecklistOptions", "")
            if isinstance(options, list):
                options = ", ".join(options)
                
            cur.execute("""
                INSERT INTO parameters 
                (request_id, parameter_name, type, spec, dropdown_options, include_remarks, section, clause_reference) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (request_id, 
                  param.get("Parameter", ""), 
                  param.get("Type", "Text Input"), 
                  param.get("Spec", ""), 
                  options, 
                  param.get("IncludeRemarks", "No"),
                  param.get("Section", "General"),
                  param.get("ClauseReference", "")))
        
        # Generate JSON template
        json_template = generate_json_template(
            doc_type=doc_type,
            product_name=product_name,
            supplier_name=supplier_name,
            parameters=parameters
        )
        
        cur.execute("""
            INSERT INTO json_templates 
            (request_id, template_json) 
            VALUES (?, ?)
        """, (request_id, json.dumps(json_template)))
        
        con.commit()
        con.close()
        
        # response data
        response_data = {
            "success": True,
            "request_id": request_id,
            "message": f"Digitized {len(parameters)} parameters from {filename}",
            "parameters_count": len(parameters),
            "extracted_parameters": [p.get("Parameter", "") for p in parameters],
            "doc_type": doc_type,
            "product_name": product_name,
            "supplier_name": supplier_name,
            "file_processing": {
                "filename": filename,
                "text_extracted": len(extracted_text),
                "preserved_structure": True
            }
        }
            
        return jsonify(response_data)
        
    except Exception as e:
        print(f"❌ Error in /digitize: {str(e)}")
        import traceback
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500

# REPLACE THE ENTIRE view_history() FUNCTION

@app.route("/history", methods=["GET"])
def view_history():
    """history view with additional metadata and logs button"""
    if request.headers.get('Accept') == 'application/json' or request.args.get('format') == 'json':
        try:
            con = sql.connect("swift_check.db")
            cur = con.cursor()
            
            # query with parameter counts and file info from logs
            cur.execute("""
                SELECT r.id, r.doc_type, r.product_name, r.supplier_name, r.created_at,
                       COUNT(DISTINCT p.id) as parameter_count,
                       l.file_info, l.endpoint, l.processing_time_ms
                FROM qc_requests r
                LEFT JOIN parameters p ON r.id = p.request_id
                LEFT JOIN api_logs l ON r.id = l.request_id
                GROUP BY r.id
                ORDER BY r.created_at DESC
            """)
            
            rows = cur.fetchall()
            con.close()
            
            return jsonify([{
                "id": row[0],
                "doc_type": row[1],
                "product_name": row[2],
                "supplier_name": row[3],
                "created_at": row[4],
                "parameter_count": row[5],
                "file_info": row[6],
                "endpoint": row[7],
                "processing_time_ms": row[8]
            } for row in rows])
            
        except Exception as e:
            return jsonify({"error": str(e)}), 500
    
    # HTML view
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        cur.execute("""
            SELECT r.id, r.doc_type, r.product_name, r.supplier_name, r.created_at,
                   COUNT(DISTINCT p.id) as parameter_count,
                   l.file_info, l.endpoint, l.processing_time_ms, l.status_code
            FROM qc_requests r
            LEFT JOIN parameters p ON r.id = p.request_id
            LEFT JOIN api_logs l ON r.id = l.request_id
            GROUP BY r.id
            ORDER BY r.created_at DESC
        """)
        
        rows = cur.fetchall()
        con.close()
        
        html = """
        <html>
        <head>
            <title>QC Request History</title>
            <style>
                body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }
                .container { max-width: 1600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
                table { border-collapse: collapse; width: 100%; margin-top: 20px; }
                th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
                th { background-color: #4CAF50; color: white; font-weight: bold; }
                tr:nth-child(even) { background-color: #f2f2f2; }
                tr:hover { background-color: #e8f5e8; }
                a { color: #4CAF50; text-decoration: none; margin: 0 3px; padding: 4px 8px; border-radius: 3px; font-size: 11px; }
                a:hover { background-color: #4CAF50; color: white; }
                .logs-btn { background-color: #007bff; color: white; }
                .logs-btn:hover { background-color: #0056b3; }
                .badge { background: #28a745; color: white; padding: 2px 6px; border-radius: 10px; font-size: 11px; }
                .param-count { font-weight: bold; color: #007bff; }
                h1 { color: #333; text-align: center; margin-bottom: 30px; }
                .endpoint { padding: 2px 6px; border-radius: 8px; font-size: 10px; color: white; margin-left: 5px; }
                .refine { background: #28a745; }
                .edit { background: #ffc107; color: black; }
                .digitize { background: #17a2b8; }
                .validate { background: #6c757d; }
                .file-info { font-style: italic; color: #6c757d; font-size: 11px; }
                .processing-time { color: #007bff; font-size: 11px; }
                .nav-buttons { text-align: center; margin: 20px 0; }
                .nav-btn { background: linear-gradient(135deg, #007bff, #0056b3); color: white; padding: 12px 20px; 
                          border: none; border-radius: 6px; margin: 0 10px; text-decoration: none; display: inline-block;
                          font-weight: bold; transition: all 0.3s ease; }
                .nav-btn:hover { transform: translateY(-2px); box-shadow: 0 4px 12px rgba(0, 123, 255, 0.3); color: white; }

            </style>
        </head>
        <body>
            <div class="container">
                <h1>QC Request History 📋</h1>
                
                <div class="nav-buttons">
                    <a href="/logs" class="nav-btn">📊 View API Logs</a>
                    <a href="/" class="nav-btn">🏠 Home</a>
                </div>
                
                <table>
                    <tr>
                        <th>ID</th>
                        <th>Product</th>
                        <th>Doc Type</th>
                        <th>Supplier</th>
                        <th>Parameters</th>
                        <th>Endpoint Used</th>
                        <th>File/Processing Info</th>
                        <th>Created</th>
                        <th>Actions</th>
                    </tr>
        """
        
        for row in rows:
            request_id, doc_type, product_name, supplier_name, created_at, param_count, file_info, endpoint, processing_time, status_code = row
            
            param_badge = "🎯" if param_count >= 15 else "⚠️" if param_count >= 10 else "❌"
            
            # Format endpoint
            endpoint_display = ""
            if endpoint:
                endpoint_clean = endpoint.replace('/', '').lower()
                endpoint_display = f'<span class="endpoint {endpoint_clean}">{endpoint}</span>'
            
            # Format file and processing info
            info_display = ""
            if file_info:
                info_display += f'<div class="file-info">📁 {file_info}</div>'
            if processing_time:
                info_display += f'<div class="processing-time">⏱️ {processing_time}ms</div>'
            if not info_display:
                info_display = "No file uploaded"
            
            html += f"""
                <tr>
                    <td>{request_id}</td>
                    <td><strong>{product_name}</strong></td>
                    <td>{doc_type}</td>
                    <td>{supplier_name}</td>
                    <td class="param-count">{param_badge} {param_count} params</td>
                    <td>{endpoint_display}</td>
                    <td>{info_display}</td>
                    <td>{created_at}</td>
                    <td>
                        <a href="/preview/{request_id}">Preview</a>
                        <a href="/template/{request_id}">JSON</a>
                        <a href="/logs?request_id={request_id}" class="logs-btn">Logs</a>
                    </td>
                </tr>
            """
        
        html += """
                </table>
                <div style="margin-top: 20px; padding: 15px; background: #e8f5e8; border-radius: 5px;">
                    <strong>Legend:</strong> 
                    🎯 15+ params (Professional) | 
                    ⚠️ 10-14 params (Good) | 
                    ❌ <10 params (Basic)<br>
                    <strong>New:</strong> 📊 Click "Logs" to see detailed API request information for each template
                </div>
            </div>
        </body>
        </html>
        """
        return html
        
    except Exception as e:
        return f"<h1>Error</h1><p>{str(e)}</p>", 500

@app.route("/template/<int:request_id>", methods=["GET"])
def get_template_json(request_id):
    """Get template JSON by request ID"""
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        cur.execute("""
            SELECT template_json 
            FROM json_templates 
            WHERE request_id = ?
        """, (request_id,))
        
        result = cur.fetchone()
        con.close()
        
        if result:
            template_data = json.loads(result[0])
            return jsonify(template_data)
        else:
            return jsonify({"error": f"template not found for request ID {request_id}"}), 404
            
    except Exception as e:
        print(f"❌ Error in /template/{request_id}: {str(e)}")
        return jsonify({"error": str(e)}), 500
# ADD THIS NEW ENDPOINT after the existing endpoints

@app.route("/logs", methods=["GET"])
def view_logs():
    """View API logs with filtering options"""
    
    # Get query parameters for filtering
    request_id = request.args.get('request_id')
    endpoint = request.args.get('endpoint')
    limit = int(request.args.get('limit', 50))
    
    if request.headers.get('Accept') == 'application/json' or request.args.get('format') == 'json':
        try:
            con = sql.connect("swift_check.db")
            cur = con.cursor()
            
            # Build query with filters
            query = """
                SELECT 
                    l.id, l.request_id, l.endpoint, l.method, l.client_ip,
                    l.file_info, l.processing_time_ms, l.status_code, 
                    l.error_message, l.created_at,
                    r.product_name, r.supplier_name, r.doc_type,
                    lr.summary_text,
                    CASE WHEN lr.llm_response IS NOT NULL THEN 'Yes' ELSE 'No' END as has_llm_response
                FROM api_logs l
                LEFT JOIN qc_requests r ON l.request_id = r.id
                LEFT JOIN llm_responses lr ON l.request_id = lr.request_id
                WHERE 1=1
            """
            params = []
            
            if request_id:
                query += " AND l.request_id = ?"
                params.append(request_id)
            
            if endpoint:
                query += " AND l.endpoint LIKE ?"
                params.append(f"%{endpoint}%")
            
            query += " ORDER BY l.created_at DESC LIMIT ?"
            params.append(limit)
            
            cur.execute(query, params)
            rows = cur.fetchall()
            con.close()
            
            logs = []
            for row in rows:
                logs.append({
                    "log_id": row[0],
                    "request_id": row[1],
                    "endpoint": row[2],
                    "method": row[3],
                    "client_ip": row[4],
                    "file_info": row[5],
                    "processing_time_ms": row[6],
                    "status_code": row[7],
                    "error_message": row[8],
                    "created_at": row[9],
                    "product_name": row[10],
                    "supplier_name": row[11],
                    "doc_type": row[12],
                    "llm_summary": row[13],
                    "has_llm_response": row[14]
                })
            
            return jsonify({
                "success": True,
                "logs": logs,
                "total_logs": len(logs),
                "filters_applied": {
                    "request_id": request_id,
                    "endpoint": endpoint,
                    "limit": limit
                }
            })
            
        except Exception as e:
            return jsonify({"error": str(e)}), 500
    
    # HTML view
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        # Get logs with request details
        cur.execute("""
                SELECT 
                    l.id, l.request_id, l.endpoint, l.method, l.client_ip,
                    l.file_info, l.processing_time_ms, l.status_code, 
                    l.error_message, l.created_at,
                    r.product_name, r.supplier_name, r.doc_type,
                    lr.summary_text, 
                    CASE WHEN lr.llm_response IS NOT NULL THEN 'Yes' ELSE 'No' END as has_llm_response
                FROM api_logs l
                LEFT JOIN qc_requests r ON l.request_id = r.id
                LEFT JOIN llm_responses lr ON l.request_id = lr.request_id
                ORDER BY l.created_at DESC
                LIMIT 100
            """)
        
        rows = cur.fetchall()
        
        # Get summary statistics
        cur.execute("""
            SELECT 
                endpoint,
                COUNT(*) as total_requests,
                AVG(processing_time_ms) as avg_time_ms,
                COUNT(CASE WHEN status_code >= 400 THEN 1 END) as error_count
            FROM api_logs
            GROUP BY endpoint
            ORDER BY total_requests DESC
        """)
        
        stats = cur.fetchall()
        con.close()
        
        html = """
        <html>
        <head>
            <title>API Logs - Swift Check</title>
            <style>
                body { font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }
                .container { max-width: 1600px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
                table { border-collapse: collapse; width: 100%; margin-top: 20px; font-size: 12px; }
                th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
                th { background-color: #007bff; color: white; font-weight: bold; position: sticky; top: 0; }
                tr:nth-child(even) { background-color: #f2f2f2; }
                tr:hover { background-color: #e3f2fd; }
                .success { color: #28a745; font-weight: bold; }
                .error { color: #dc3545; font-weight: bold; }
                .endpoint { padding: 3px 8px; border-radius: 12px; font-size: 10px; color: white; }
                .refine { background: #28a745; }
                .edit { background: #ffc107; color: black; }
                .digitize { background: #17a2b8; }
                .validate { background: #6c757d; }
                .stats-section { margin: 20px 0; padding: 15px; background: #e8f4f8; border-radius: 8px; }
                .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; }
                .stat-card { background: white; padding: 15px; border-radius: 8px; border-left: 4px solid #007bff; }
                h1 { color: #333; text-align: center; margin-bottom: 30px; }
                h2 { color: #007bff; }
                .filter-section { margin: 20px 0; padding: 15px; background: #f8f9fa; border-radius: 8px; }
                input, select { margin: 5px; padding: 8px; border: 1px solid #ddd; border-radius: 4px; }
                button { background: #007bff; color: white; padding: 8px 15px; border: none; border-radius: 4px; cursor: pointer; }
                .file-info { font-style: italic; color: #6c757d; }
                .time-ms { color: #28a745; font-weight: bold; }
                .error-msg { color: #dc3545; font-size: 11px; max-width: 200px; overflow: hidden; text-overflow: ellipsis; }
            </style>
            <script>
                function filterLogs() {
                    const endpoint = document.getElementById('endpointFilter').value;
                    const status = document.getElementById('statusFilter').value;
                    
                    const rows = document.querySelectorAll('.log-row');
                    rows.forEach(row => {
                        const rowEndpoint = row.getAttribute('data-endpoint');
                        const rowStatus = row.getAttribute('data-status');
                        
                        let show = true;
                        if (endpoint && !rowEndpoint.includes(endpoint)) show = false;
                        if (status && rowStatus !== status) show = false;
                        
                        row.style.display = show ? '' : 'none';
                    });
                }
            </script>
        </head>
        <body>
            <div class="container">
                <h1>📊 API Logs Dashboard</h1>
                
                <div class="stats-section">
                    <h2>📈 Endpoint Statistics</h2>
                    <div class="stats-grid">
        """
        
        for stat in stats:
            endpoint, total, avg_time, errors = stat
            success_rate = ((total - errors) / total * 100) if total > 0 else 0
            html += f"""
                        <div class="stat-card">
                            <h3>{endpoint}</h3>
                            <p><strong>Total Requests:</strong> {total}</p>
                            <p><strong>Avg Time:</strong> {avg_time:.1f}ms</p>
                            <p><strong>Success Rate:</strong> {success_rate:.1f}%</p>
                            <p><strong>Errors:</strong> {errors}</p>
                        </div>
            """
        
        html += """
                    </div>
                </div>
                
                <div class="filter-section">
                    <h3>🔍 Filter Logs</h3>
                    <select id="endpointFilter" onchange="filterLogs()">
                        <option value="">All Endpoints</option>
                        <option value="refine">Refine</option>
                        <option value="edit">Edit</option>
                        <option value="digitize">Digitize</option>
                        <option value="validate">Validate</option>
                    </select>
                    
                    <select id="statusFilter" onchange="filterLogs()">
                        <option value="">All Status</option>
                        <option value="200">Success (200)</option>
                        <option value="500">Error (500)</option>
                    </select>
                    
                    <button onclick="window.location.reload()">🔄 Refresh</button>
                    <button onclick="window.location.href='/logs?format=json'">📄 JSON Export</button>
                </div>
                
                <table>
                    <tr>
                        <th>Log ID</th>
                        <th>Request ID</th>
                        <th>Endpoint</th>
                        <th>Product</th>
                        <th>Supplier</th>
                        <th>File Info</th>
                        <th>Time (ms)</th>
                        <th>Status</th>
                        <th>LLM Used</th>
                        <th>Error</th>
                        <th>Created At</th>
                        <th>Actions</th>
                    </tr>
        """
        
        for row in rows:
            log_id, request_id, endpoint, method, client_ip, file_info, processing_time, status_code, error_message, created_at, product_name, supplier_name, doc_type, llm_summary, has_llm_response = row
            
            # Style endpoint
            endpoint_class = endpoint.replace('/', '').lower()
            endpoint_badge = f'<span class="endpoint {endpoint_class}">{endpoint}</span>'
            
            # Style status
            status_class = "success" if status_code == 200 else "error"
            status_text = f'<span class="{status_class}">{status_code}</span>'
            
            # Format processing time
            time_class = "time-ms"
            if processing_time and processing_time > 5000:
                time_class += " error"
            elif processing_time and processing_time > 2000:
                time_class += " warning"
            
            time_text = f'<span class="{time_class}">{processing_time or "N/A"}</span>'
            
            # Format file info
            file_display = f'<span class="file-info">{file_info or "No file"}</span>'
            
            # Format error message
            error_display = f'<span class="error-msg" title="{error_message or ""}">{(error_message or "")[:50]}{"..." if error_message and len(error_message) > 50 else ""}</span>'
            
            llm_indicator = "🤖 Yes" if has_llm_response == "Yes" else "❌ No"
            llm_color = "#28a745" if has_llm_response == "Yes" else "#6c757d"
            
            html += f"""
                <tr class="log-row" data-endpoint="{endpoint}" data-status="{status_code}">
                    <td><a href="/logs/{log_id}" style="color: #007bff; font-weight: bold;">#{log_id}</a></td>
                    <td>{request_id or "N/A"}</td>
                    <td>{endpoint_badge}</td>
                    <td><strong>{product_name or "N/A"}</strong></td>
                    <td>{supplier_name or "N/A"}</td>
                    <td>{file_display}</td>
                    <td>{time_text}</td>
                    <td>{status_text}</td>
                    <td><span style="color: {llm_color}; font-weight: bold;">{llm_indicator}</span></td>
                    <td>{error_display}</td>
                    <td>{created_at}</td>
                    <td>
                        <a href="/logs/{log_id}">Details</a>
                        {f'<a href="/preview/{request_id}">Preview</a> <a href="/template/{request_id}">JSON</a>' if request_id else ''}
                    </td>
                </tr>
            """
        
        html += """
                </table>
                
                <div style="margin-top: 20px; text-align: center;">
                    <button onclick="window.location.href='/history'">📋 View Request History</button>
                    <button onclick="window.location.href='/'">🏠 Home</button>
                </div>
            </div>
        </body>
        </html>
        """
        return html
        
    except Exception as e:
        return f"<h1>Error</h1><p>{str(e)}</p>", 500

# ADD this new endpoint after the /logs endpoint
@app.route("/logs/<int:log_id>", methods=["GET"])
def view_detailed_log(log_id):
    """View detailed information for a specific log entry"""
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        # Get detailed log information
        cur.execute("""
            SELECT 
                l.*,
                r.doc_type, r.product_name, r.supplier_name, r.user_message,
                lr.llm_response, lr.summary_text
            FROM api_logs l
            LEFT JOIN qc_requests r ON l.request_id = r.id
            LEFT JOIN llm_responses lr ON l.request_id = lr.request_id
            WHERE l.id = ?
        """, (log_id,))
        
        log_data = cur.fetchone()
        con.close()
        
        if not log_data:
            return jsonify({"error": f"Log ID {log_id} not found"}), 404
        
        if request.headers.get('Accept') == 'application/json' or request.args.get('format') == 'json':
            # Return JSON format
            return jsonify({
                "log_id": log_data[0],
                "request_id": log_data[1],
                "endpoint": log_data[2],
                "method": log_data[3],
                "client_ip": log_data[4],
                "user_agent": log_data[5],
                "request_data": log_data[6],
                "response_data": log_data[7],
                "file_info": log_data[8],
                "processing_time_ms": log_data[9],
                "status_code": log_data[10],
                "error_message": log_data[11],
                "created_at": log_data[12],
                "doc_type": log_data[13],
                "product_name": log_data[14],
                "supplier_name": log_data[15],
                "user_message": log_data[16],
                "llm_response": log_data[17],
                "llm_response_summary": log_data[18]
            })
        
        # Start building HTML
        html = f"""
        <html>
        <head>
            <title>Log Details - #{log_id}</title>
            <style>
                body {{ font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }}
                .container {{ max-width: 1200px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }}
                .section {{ margin: 20px 0; padding: 15px; border-radius: 8px; }}
                .basic-info {{ background: #e8f4f8; border-left: 4px solid #007bff; }}
                .request-info {{ background: #f8f9fa; border-left: 4px solid #28a745; }}
                .response-info {{ background: #fff3cd; border-left: 4px solid #ffc107; }}
                .error-info {{ background: #f8d7da; border-left: 4px solid #dc3545; }}
                .llm-section {{ background: #f0f8ff; border-left: 4px solid #007bff; }}
                .json-analysis {{ background: #f8f9fa; border: 1px solid #dee2e6; border-radius: 5px; padding: 10px; margin: 10px 0; }}
                .json-preview {{ background: #2d3748; color: #e2e8f0; padding: 15px; border-radius: 5px; font-family: 'Courier New', monospace; font-size: 12px; }}
                .expandable {{ cursor: pointer; user-select: none; }}
                .expandable:hover {{ background: #e9ecef; }}
                .collapsed {{ display: none; }}
                .parameter-count {{ background: #28a745; color: white; padding: 2px 8px; border-radius: 12px; font-size: 11px; margin-left: 10px; }}
                h1, h2 {{ color: #333; }}
                h1 {{ text-align: center; }}
                pre {{ background: #f8f9fa; padding: 15px; border-radius: 5px; overflow: auto; max-height: 300px; }}
                .status-success {{ color: #28a745; font-weight: bold; }}
                .status-error {{ color: #dc3545; font-weight: bold; }}
                .nav-btn {{ background: #007bff; color: white; padding: 10px 15px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; }}
                .nav-btn:hover {{ background: #0056b3; color: white; }}
                .metric {{ display: inline-block; margin: 10px 15px 10px 0; padding: 8px 12px; background: white; border-radius: 5px; border: 1px solid #ddd; }}
                .copy-btn {{ background: #17a2b8; color: white; border: none; padding: 5px 10px; border-radius: 3px; cursor: pointer; float: right; margin: 5px; }}
                .copy-btn:hover {{ background: #138496; }}
            </style>
        </head>
        <body>
            <div class="container">
                <h1>📋 Log Details - #{log_id}</h1>
                
                <div class="nav-buttons">
                    <a href="/logs" class="nav-btn">⬅️ Back to Logs</a>
                    <a href="/history" class="nav-btn">📋 Request History</a>
                    {f'<a href="/preview/{log_data[1]}" class="nav-btn">👁️ Preview Template</a>' if log_data[1] else ''}
                </div>
                
                <div class="section basic-info">
                    <h2>🔍 Basic Information</h2>
                    <div class="metric"><strong>Endpoint:</strong> {log_data[2]}</div>
                    <div class="metric"><strong>Method:</strong> {log_data[3]}</div>
                    <div class="metric"><strong>Status:</strong> <span class="{'status-success' if log_data[10] == 200 else 'status-error'}">{log_data[10]}</span></div>
                    <div class="metric"><strong>Processing Time:</strong> {log_data[9] or 'N/A'}ms</div>
                    <div class="metric"><strong>Created:</strong> {log_data[12]}</div>
                    <div class="metric"><strong>Client IP:</strong> {log_data[4]}</div>
                </div>
        """
        
        # Add request information section if we have request data
        if log_data[1]:  # If we have a request_id
            html += f"""
                <div class="section request-info">
                    <h2>📋 Request Information</h2>
                    <p><strong>Product:</strong> {log_data[14] or 'N/A'}</p>
                    <p><strong>Document Type:</strong> {log_data[13] or 'N/A'}</p>
                    <p><strong>Supplier:</strong> {log_data[15] or 'N/A'}</p>
                    {f'<p><strong>File Info:</strong> {log_data[8]}</p>' if log_data[8] else '<p><strong>File Info:</strong> No file uploaded</p>'}
            """
            
            if log_data[16]:  # User message
                user_message_preview = log_data[16][:500]
                if len(log_data[16]) > 500:
                    user_message_preview += "..."
                html += f"""
                    <div><strong>User Message:</strong>
                    <pre>{user_message_preview}</pre></div>
                """
            
            html += "</div>"
        
        # Add LLM Processing section if we have LLM data
        if log_data[17] or log_data[7]:
            html += f"""
                <div class="section llm-section">
                    <h2>🤖 LLM Processing Chain</h2>
                    <p><strong>Summary:</strong> {log_data[18] or 'No summary available'}</p>
            """
            
            # Raw LLM Response section
            if log_data[17]:
                # Escape the LLM response for safe JavaScript usage
                llm_response_js_safe = str(log_data[17]).replace("\\", "\\\\").replace("'", "\\'").replace('"', '\\"').replace('\n', '\\n').replace('\r', '\\r')
                
                html += f"""
                    <div class="json-analysis">
                        <h3 class="expandable" onclick="toggleSection('llm-response-section')">▼ Raw LLM Response</h3>
                        <div id="llm-response-section">
                            <button class="copy-btn" onclick="copyToClipboard('{llm_response_js_safe}')">📋 Copy</button>
                            <pre id="llm-response-content" class="json-preview" style="max-height: 500px; overflow-y: auto;">{log_data[17]}</pre>
                        </div>
                    </div>
                """
                
                # Add LLM response analysis
                html += _analyze_llm_response(log_data[17])
            
            # Final API Response section
            if log_data[7]:
                # Escape the final response for safe JavaScript usage
                final_response_js_safe = str(log_data[7]).replace("\\", "\\\\").replace("'", "\\'").replace('"', '\\"').replace('\n', '\\n').replace('\r', '\\r')
                
                html += f"""
                    <div class="json-analysis">
                        <h3 class="expandable" onclick="toggleSection('final-response-section')">▼ Final API Response</h3>
                        <div id="final-response-section">
                            <button class="copy-btn" onclick="copyToClipboard('{final_response_js_safe}')">📋 Copy</button>
                            <pre class="json-preview">{log_data[7]}</pre>
                        </div>
                    </div>
                """
            
            html += "</div>"
        
        # Add error information if present
        if log_data[11]:
            html += f"""
                <div class="section error-info">
                    <h2>❌ Error Information</h2>
                    <p><strong>Error Message:</strong></p>
                    <pre>{log_data[11]}</pre>
                </div>
            """
        
        # Add technical details section
        html += f"""
                <div class="section">
                    <h2>🌐 Technical Details</h2>
                    <p><strong>User Agent:</strong> {log_data[5] or 'Not available'}</p>
        """
        
        if log_data[6]:
            html += f"""
                    <div><strong>Request Data:</strong>
                    <pre>{log_data[6]}</pre></div>
            """
        else:
            html += "<p>No request data captured</p>"
        
        html += "</div></div>"
        
        # Add JavaScript section safely
        html += """
            <script>
                function toggleSection(id) {
                    const element = document.getElementById(id);
                    const button = document.querySelector('[onclick*="' + id + '"]');
                    
                    if (element && button) {
                        if (element.classList.contains('collapsed')) {
                            element.classList.remove('collapsed');
                            button.textContent = button.textContent.replace('▶', '▼');
                        } else {
                            element.classList.add('collapsed');
                            button.textContent = button.textContent.replace('▼', '▶');
                        }
                    }
                }
                
                function copyToClipboard(text) {
                    // Create a temporary textarea element
                    const textArea = document.createElement('textarea');
                    textArea.value = text;
                    textArea.style.position = 'fixed';
                    textArea.style.opacity = '0';
                    document.body.appendChild(textArea);
                    textArea.select();
                    
                    try {
                        document.execCommand('copy');
                        alert('✅ Copied to clipboard!');
                    } catch (err) {
                        console.error('Failed to copy: ', err);
                        alert('❌ Failed to copy to clipboard');
                    }
                    
                    document.body.removeChild(textArea);
                }
                
                // Auto-expand LLM response if it contains JSON
                document.addEventListener('DOMContentLoaded', function() {
                    const llmResponse = document.getElementById('llm-response-content');
                    if (llmResponse && llmResponse.textContent.includes('[')) {
                        // Likely contains JSON, keep expanded by default
                    }
                });
            </script>
        </body>
        </html>
        """
        
        return html
        
    except Exception as e:
        return f"<h1>Error</h1><p>{str(e)}</p>", 500
    

@app.route("/preview/<int:request_id>", methods=["GET"])
def preview_page(request_id):
    """preview with better formatting and metadata"""
    try:
        con = sql.connect("swift_check.db")
        cur = con.cursor()
        
        # Get template JSON
        cur.execute("""
            SELECT template_json 
            FROM json_templates 
            WHERE request_id = ?
        """, (request_id,))
        
        template_result = cur.fetchone()
        
        # Get parameters
        cur.execute("""
            SELECT parameter_name, type, spec, dropdown_options, include_remarks, section, clause_reference
            FROM parameters 
            WHERE request_id = ?
            ORDER BY id
        """, (request_id,))
        
        parameters = cur.fetchall()
        
        # Get request details
        cur.execute("""
            SELECT doc_type, product_name, supplier_name
            FROM qc_requests 
            WHERE id = ?
        """, (request_id,))
        
        request_details = cur.fetchone()
        con.close()
        
        if not template_result:
            return f"""
            <html>
            <head><title>Not Found</title></head>
            <body>
                <h1>Template not found</h1>
                <p>No template exists for request ID {request_id}</p>
                <a href="/history">View History</a>
            </body>
            </html>
            """, 404
            
        json_template = json.loads(template_result[0])
        
        # Generate ASCII preview with sections
        ascii_preview = "╔══════════════════════════════════════════════════════════════════════╗\n"
        
        if request_details:
            header = f"{request_details[1]} {request_details[0]}"
        else:
            header = "QC Template"
        
        header_padding = (70 - len(header)) // 2
        ascii_preview += f"║{' ' * header_padding}{header}{' ' * (70 - header_padding - len(header))}║\n"
        
        if request_details and request_details[2]:
            supplier = f"Supplier: {request_details[2]}"
            supplier_padding = (70 - len(supplier)) // 2
            ascii_preview += f"║{' ' * supplier_padding}{supplier}{' ' * (70 - supplier_padding - len(supplier))}║\n"
            
        ascii_preview += "╚══════════════════════════════════════════════════════════════════════╝\n\n"
        
        # Group parameters by section
        sections = {}
        for param in parameters:
            param_name, param_type, spec, options, include_remarks, section, clause_ref = param
            section = section or "General Parameters"
            if section not in sections:
                sections[section] = []
            sections[section].append(param)
        
        # Add parameters organized by sections
        for section_name, section_params in sections.items():
            ascii_preview += f"\n🔹 {section_name.upper()}\n"
            ascii_preview += "─" * 60 + "\n"
            
            for param in section_params:
                param_name, param_type, spec, options, include_remarks, section, clause_ref = param
                
                # Add clause reference if available
                display_name = param_name
                if clause_ref:
                    display_name += f" ({clause_ref})"
                
                if param_type == "Image Upload":
                    ascii_preview += f"[📷] {display_name}: [ Upload Photo ] + Toggle Assessment\n"
                elif param_type == "Toggle":
                    ascii_preview += f"[◐] {display_name}: ● Acceptable ○ Not Acceptable\n"
                elif param_type == "Dropdown":
                    ascii_preview += f"[▼] {display_name}: _________________ "
                    if options:
                        option_list = [opt.strip() for opt in options.split(",")[:3]]
                        ascii_preview += f"({', '.join(option_list)}{'...' if len(options.split(',')) > 3 else ''})\n"
                    else:
                        ascii_preview += "\n"
                elif param_type == "Checklist":
                    ascii_preview += f"    {display_name}:\n"
                    if options:
                        option_list = [opt.strip() for opt in options.split(",")]
                        for opt in option_list[:5]:
                            ascii_preview += f"    ☐ {opt}\n"
                        if len(option_list) > 5:
                            ascii_preview += f"    ... and {len(option_list) - 5} more items\n"
                    else:
                        ascii_preview += "    ☐ Item 1\n"
                elif param_type == "Numeric Input":
                    ascii_preview += f"[#️⃣] {display_name}: _____________"
                    if spec:
                        ascii_preview += f" (Spec: {spec})\n"
                    else:
                        ascii_preview += "\n"
                elif param_type == "Text Input":
                    ascii_preview += f"[✏️] {display_name}: _____________________________\n"
                elif param_type == "Remarks":
                    ascii_preview += f"[📝] {display_name}:\n"
                    ascii_preview += "    ┌─────────────────────────────────────┐\n"
                    ascii_preview += "    │                                     │\n"
                    ascii_preview += "    │                                     │\n"
                    ascii_preview += "    └─────────────────────────────────────┘\n"
                
                if include_remarks == "Yes" and param_type != "Remarks":
                    ascii_preview += f"    └─ Additional Remarks: _______________________\n"
                
                ascii_preview += "\n"
        
        # Add final assessment
        ascii_preview += "═" * 70 + "\n"
        ascii_preview += "🎯 FINAL ASSESSMENT\n"
        ascii_preview += "═" * 70 + "\n"
        ascii_preview += "[✅] Overall Quality Assessment: ● APPROVED ○ REJECTED\n\n"
        ascii_preview += "[👤] Inspector Name & Signature: _________________________________\n\n"
        ascii_preview += "[📝] Final Comprehensive Remarks:\n"
        ascii_preview += "    ┌─────────────────────────────────────────────────────────────┐\n"
        ascii_preview += "    │ Overall assessment, corrective actions, and observations    │\n"
        ascii_preview += "    │                                                             │\n"
        ascii_preview += "    │                                                             │\n"
        ascii_preview += "    └─────────────────────────────────────────────────────────────┘\n"
        
        # statistics
        total_params = len(parameters)
        param_types = {}
        sections_count = len(sections)
        regulatory_refs = sum(1 for param in parameters if param[6])  # clause references
        
        for param in parameters:
            param_type = param[1]
            param_types[param_type] = param_types.get(param_type, 0) + 1
        
        html = f"""
        <html>
        <head>
            <title> QC Template Preview - Request #{request_id}</title>
            <style>
                body {{ font-family: Arial, sans-serif; margin: 20px; background-color: #f8f9fa; }}
                .container {{ max-width: 1400px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }}
                .preview-section {{ margin: 25px 0; }}
                .ascii-preview {{ 
                    background-color: #1a1a1a; 
                    color: #00ff41; 
                    padding: 25px; 
                    border-radius: 8px; 
                    overflow: auto; 
                    font-family: 'Courier New', monospace;
                    font-size: 13px;
                    line-height: 1.4;
                    white-space: pre;
                    border: 2px solid #00ff41;
                }}
                .json-section {{ 
                    background-color: #f8f9fa; 
                    padding: 20px; 
                    border-radius: 8px; 
                    overflow: auto; 
                    max-height: 500px;
                    border: 1px solid #dee2e6;
                }}
                .stats-section {{
                    background: linear-gradient(135deg, #e8f5e8, #f0f8f0);
                    padding: 20px;
                    border-radius: 8px;
                    margin: 20px 0;
                    border-left: 4px solid #28a745;
                }}
                .stats-grid {{
                    display: grid;
                    grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
                    gap: 15px;
                    margin: 15px 0;
                }}
                .stat-item {{
                    text-align: center;
                    padding: 15px;
                    background: white;
                    border-radius: 8px;
                    border: 1px solid #28a745;
                    font-size: 14px;
                }}
                h1, h2, h3 {{ color: #333; }}
                h1 {{ text-align: center; margin-bottom: 30px; }}
                button {{ 
                    background: linear-gradient(135deg, #28a745, #20c997);
                    color: white; 
                    padding: 12px 20px; 
                    border: none; 
                    border-radius: 6px; 
                    cursor: pointer; 
                    margin: 10px 5px;
                    font-weight: bold;
                    transition: all 0.3s ease;
                }}
                button:hover {{ 
                    transform: translateY(-2px);
                    box-shadow: 0 4px 12px rgba(40, 167, 69, 0.3);
                }}
                .button-group {{ margin: 25px 0; text-align: center; }}
                .badge {{ background: #28a745; color: white; padding: 4px 8px; border-radius: 12px; font-size: 12px; margin-left: 10px; }}
                .quality-badge {{ 
                    background: {'#28a745' if total_params >= 15 else '#ffc107' if total_params >= 10 else '#dc3545'};
                    color: white;
                    padding: 6px 12px;
                    border-radius: 15px;
                    font-weight: bold;
                }}
            </style>
        </head>
        <body>
            <div class="container">
                <h1>  QC Template Preview - Request #{request_id} 
                </h1>
                
                
                <div class="preview-section">
                    <h2>🖥️ ASCII Preview</h2>
                    <div class="ascii-preview">{ascii_preview}</div>
                </div>
                
                <div class="preview-section">
                    <h2>📋 JSON Template</h2>
                    <div class="button-group">
                        <button onclick="copyToClipboard()">📋 Copy JSON to Clipboard</button>
                        <button onclick="toggleJsonVisibility()">👁️ Toggle JSON View</button>
                        <button onclick="downloadJson()">💾 Download JSON</button>
                    </div>
                    <div id="jsonSection" class="json-section" style="display: none;">
                        <pre id="jsonContent">{json.dumps(json_template, indent=2)}</pre>
                    </div>
                </div>
                
                <div class="button-group">
                    <button onclick="window.location.href='/history'">⬅️ Back to History</button>
                    <button onclick="window.location.href='/template/{request_id}'">🔗 Direct JSON API</button>
                </div>
            </div>
            
            <script>
                function copyToClipboard() {{
                    const jsonContent = document.getElementById('jsonContent').textContent;
                    navigator.clipboard.writeText(jsonContent)
                        .then(() => alert('✅ JSON copied to clipboard!'))
                        .catch(err => console.error('❌ Failed to copy: ', err));
                }}
                
                function toggleJsonVisibility() {{
                    const jsonSection = document.getElementById('jsonSection');
                    jsonSection.style.display = jsonSection.style.display === 'none' ? 'block' : 'none';
                }}
                
                function downloadJson() {{
                    const jsonContent = document.getElementById('jsonContent').textContent;
                    const blob = new Blob([jsonContent], {{type: 'application/json'}});
                    const url = URL.createObjectURL(blob);
                    const a = document.createElement('a');
                    a.href = url;
                    a.download = 'qc_template_{request_id}.json';
                    document.body.appendChild(a);
                    a.click();
                    document.body.removeChild(a);
                    URL.revokeObjectURL(url);
                }}
            </script>
        </body>
        </html>
        """
        return html
        
    except Exception as e:
        print(f"❌ Error in /preview/{request_id}: {str(e)}")
        return f"<h1>Error</h1><p>{str(e)}</p>", 500

if __name__ == "__main__":
    print("🚀 Starting Swift Check API v2.0...")
    app.run(host="127.0.0.1", port=5000, debug=True)