#!/usr/bin/env python3
"""
Vera Medical CRM Application
Flask-based CRM for managing medical assessment cases
"""

from flask import Flask, render_template, request, redirect, url_for, flash, jsonify, session, abort, send_from_directory, send_file, Response
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
import sqlite3
from datetime import datetime
import os
import functools
import json
import sys
from utils import (
    sanitize_phone, sanitize_postal_code, sanitize_email, sanitize_name,
    sanitize_company_name, sanitize_form_data, validate_form_data,
    fuzzy_match_score, normalize_for_comparison
)
import smtplib
import ssl
from email.message import EmailMessage

app = Flask(__name__)
app.secret_key = os.urandom(24)
app.config['DATABASE'] = 'database/vera_medical.db'
app.config['UPLOAD_FOLDER'] = 'uploads/reports'
app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024  # 50MB max file size
app.config['ALLOWED_EXTENSIONS'] = {'pdf', 'doc', 'docx', 'txt', 'jpg', 'jpeg', 'png'}

# ============================================================================
# File Upload Helper Functions
# ============================================================================

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

def get_file_size_mb(size_bytes):
    """Convert bytes to MB string"""
    return f"{size_bytes / (1024 * 1024):.2f} MB"

def save_form_template_report(case_id, form_name, html_content, report_title=None, uploaded_by='System', appointment_id=None):
    """Persist a generated form template as a report record."""
    if not html_content:
        return None
    
    # Unescape HTML content to ensure clean storage
    html_content = unescape_html(html_content)
    
    form_name = form_name or 'template.html'
    safe_template_name = secure_filename(form_name) or 'template.html'
    report_title = report_title or form_name.replace('.html', '').replace('_', ' ').title()
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    filename = f"{timestamp}_{safe_template_name}"
    filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    
    os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
    # wrap_html_document will also unescape, but that's OK - unescaping clean HTML doesn't change it
    rendered_html = wrap_html_document(html_content)
    with open(filepath, 'w', encoding='utf-8') as f:
        f.write(rendered_html)
    
    file_size = os.path.getsize(filepath)
    
    result = execute_db("""
        INSERT INTO reports (
            report_title, report_type, report_date, file_name,
            file_path, file_size, file_type, case_id, doctor_id,
            status, description, notes, uploaded_by, appointment_id,
            form_template, form_html_content
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        report_title,
        'Form Template',
        datetime.now().date(),
        form_name,
        filename,
        file_size,
        'html',
        case_id,
        None,
        'draft',
        f'Form generated from template: {form_name}',
        f'Generated from form template',
        uploaded_by or 'System',
        appointment_id,
        form_name,
        html_content
    ))
    return result


def resolve_report_full_path(stored_path):
    """Return absolute path for a stored report file path."""
    if not stored_path:
        return None
    if os.path.isabs(stored_path):
        return stored_path
    if stored_path.startswith(app.config['UPLOAD_FOLDER']):
        return os.path.join(app.root_path, stored_path)
    return os.path.join(app.root_path, app.config['UPLOAD_FOLDER'], stored_path)

def unescape_html(content):
    """Unescape HTML entities so they render as HTML instead of text."""
    if not content:
        return ''
    import html
    # Unescape HTML entities (e.g., &lt;div&gt; becomes <div>)
    # Keep unescaping until no more changes occur (handles nested escaping)
    prev_content = None
    unescaped = content
    while unescaped != prev_content:
        prev_content = unescaped
        unescaped = html.unescape(unescaped)
    return unescaped

def wrap_html_document(content):
    """Ensure saved HTML files render cleanly when downloaded or printed."""
    if not content:
        return ''
    # Unescape HTML entities first - handle multiple levels of escaping
    import html
    content = unescape_html(content)
    # Additional pass to ensure all entities are unescaped
    if '&lt;' in content or '&gt;' in content:
        content = html.unescape(content)
    if '</html>' in content.lower():
        return content
    base_styles = (
        "body{font-family:'Segoe UI','Inter',sans-serif;padding:40px;background:#fff;color:#111;line-height:1.7;}"
        "h1,h2,h3{color:#1f2937;margin-top:1.5rem;}"
        "table{width:100%;border-collapse:collapse;margin-top:1rem;}"
        "th,td{border:1px solid #e5e7eb;padding:8px;text-align:left;}"
        ".signature{margin-top:2rem;}"
    )
    return f"<!DOCTYPE html><html><head><meta charset='utf-8'><style>{base_styles}</style></head><body>{content}</body></html>"

def send_appointment_email(appointment_id, email_to, email_cc=None, extra_note=None):
    """Send a confirmation email for an appointment if SMTP settings are configured."""
    if not email_to:
        return True, 'Appointment saved without sending email (no recipient provided).'
    
    appointment = query_db("""
        SELECT a.id, a.appointment_date, a.appointment_time, a.location,
               a.assessment_type, a.service_code, a.notes,
               c.file_case_id, cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name, d.name AS doctor_name
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN doctors d ON a.doctor_id = d.id
        WHERE a.id = ?
    """, (appointment_id,), one=True)
    
    if not appointment:
        return False, 'Appointment email not sent: appointment not found.'
    
    appointment = dict(appointment)
    subject = f"Appointment Confirmation - Case #{appointment.get('file_case_id') or appointment_id}"
    date_str = appointment.get('appointment_date') or 'TBD'
    time_str = appointment.get('appointment_time') or 'TBD'
    doctor_str = appointment.get('doctor_name') or appointment.get('notes') or 'Assigned Provider'
    location_str = appointment.get('location') or 'Location TBD'
    
    body_lines = [
        f"Case: #{appointment.get('file_case_id') or 'N/A'}",
        f"Claimant: {(appointment.get('claimant_first_name') or '').strip()} {(appointment.get('claimant_last_name') or '').strip()}".strip(),
        f"Date: {date_str}",
        f"Time: {time_str}",
        f"Provider: {doctor_str}",
        f"Location: {location_str}",
        "",
        f"Assessment Type: {appointment.get('assessment_type') or 'N/A'}",
        f"Service Code: {appointment.get('service_code') or 'N/A'}",
    ]
    if extra_note:
        body_lines.append("")
        body_lines.append(f"Additional Notes:\n{extra_note}")
    
    smtp_host = os.getenv('SMTP_HOST')
    smtp_user = os.getenv('SMTP_USERNAME')
    smtp_pass = os.getenv('SMTP_PASSWORD')
    smtp_port = int(os.getenv('SMTP_PORT', '465'))
    smtp_from = os.getenv('SMTP_FROM', smtp_user or 'no-reply@vera-medical.local')
    
    if not (smtp_host and smtp_user and smtp_pass):
        print('[send_appointment_email] SMTP settings are not configured; skipping send.')
        return True, 'Email skipped: SMTP settings are not configured.'
    
    message = EmailMessage()
    message['Subject'] = subject
    message['From'] = smtp_from
    message['To'] = email_to
    if email_cc:
        message['Cc'] = email_cc
    message.set_content('\n'.join(body_lines))
    
    try:
        context = ssl.create_default_context()
        with smtplib.SMTP_SSL(smtp_host, smtp_port, context=context) as server:
            server.login(smtp_user, smtp_pass)
            server.send_message(message)
        return True, 'Appointment confirmation email sent successfully.'
    except Exception as exc:
        print(f'[send_appointment_email] Failed to send email: {exc}')
        return False, f'Email not sent: {exc}'

# ============================================================================
# Database Helper Functions
# ============================================================================

def ensure_appointments_view():
    """Ensure v_appointments_full view exists - no-op, kept for compatibility"""
    pass

def get_db():
    """Get database connection"""
    conn = sqlite3.connect(app.config['DATABASE'], timeout=30.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=30000")  # 30 second timeout
    return conn

def query_db(query, args=(), one=False):
    """Query database helper"""
    conn = get_db()
    cur = conn.execute(query, args)
    rv = cur.fetchall()
    conn.close()
    return (rv[0] if rv else None) if one else rv

def execute_db(query, args=()):
    """Execute database command (INSERT, UPDATE, DELETE)"""
    conn = get_db()
    cur = conn.execute(query, args)
    conn.commit()
    lastrowid = cur.lastrowid
    conn.close()
    return lastrowid

# ============================================================================
# Authentication Helper Functions
# ============================================================================

def login_required(f):
    """Decorator to require login for routes"""
    @functools.wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated_function

def init_users_table():
    """Initialize users table if it doesn't exist"""
    conn = get_db()
    conn.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # Check if admin user exists
    admin = conn.execute("SELECT id FROM users WHERE username = ?", ('admin',)).fetchone()
    if not admin:
        # Create default admin user with password: admin123
        password_hash = generate_password_hash('admin123')
        conn.execute("""
            INSERT INTO users (username, password_hash) VALUES (?, ?)
        """, ('admin', password_hash))
        conn.commit()
        print("=" * 60)
        print("DEFAULT LOGIN CREDENTIALS CREATED")
        print("=" * 60)
        print("Username: admin")
        print("Password: [Check documentation or change in database]")
        print("⚠️  IMPORTANT: Change default password in production!")
        print("=" * 60)
    
    conn.close()

# ============================================================================
# Authentication Routes
# ============================================================================

@app.route('/login', methods=['GET', 'POST'])
def login():
    """Login page"""
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        conn = get_db()
        user = conn.execute(
            'SELECT * FROM users WHERE username = ?', (username,)
        ).fetchone()
        conn.close()
        
        if user and check_password_hash(user['password_hash'], password):
            session['user_id'] = user['id']
            session['username'] = user['username']
            flash('Login successful!', 'success')
            return redirect(url_for('index'))
        else:
            flash('Invalid username or password', 'error')
    
    return render_template('login.html')

@app.route('/logout')
def logout():
    """Logout"""
    session.clear()
    flash('You have been logged out', 'success')
    return redirect(url_for('login'))

@app.route('/favicon.ico')
def favicon():
    """Handle favicon requests - return 204 No Content to suppress 404 errors"""
    return Response(status=204)

# ============================================================================
# Dashboard & Overview Routes
# ============================================================================

@app.route('/')
@login_required
def index():
    """Dashboard home page"""
    conn = get_db()
    
    # Get statistics
    stats = {}
    
    # Total cases
    stats['total_cases'] = conn.execute('SELECT COUNT(*) FROM cases').fetchone()[0]
    
    # Active cases (not closed)
    stats['active_cases'] = conn.execute(
        "SELECT COUNT(*) FROM cases WHERE file_close_date IS NULL OR file_close_date = ''"
    ).fetchone()[0]
    
    # Closed cases
    stats['closed_cases'] = stats['total_cases'] - stats['active_cases']
    
    # Total clients
    stats['total_clients'] = conn.execute('SELECT COUNT(*) FROM clients').fetchone()[0]
    
    # Total claimants
    stats['total_claimants'] = conn.execute('SELECT COUNT(*) FROM claimants').fetchone()[0]
    
    # Recent cases (last 10)
    recent_cases = conn.execute("""
        SELECT * FROM v_cases_full
        ORDER BY file_open_date DESC
        LIMIT 10
    """).fetchall()
    
    # Top clients by case count
    top_clients = conn.execute("""
        SELECT * FROM v_cases_by_client
        LIMIT 5
    """).fetchall()
    
    conn.close()
    
    return render_template('dashboard.html', 
                         stats=stats, 
                         recent_cases=recent_cases,
                         top_clients=top_clients)

# ============================================================================
# Cases Routes (CRUD)
# ============================================================================

@app.route('/cases')
@login_required
def cases_list():
    """List all cases with filtering and search"""
    page = request.args.get('page', 1, type=int)
    search = request.args.get('search', '')
    status = request.args.get('status', 'all')
    per_page = 50
    offset = (page - 1) * per_page
    
    conn = get_db()
    
    # Build query
    query = "SELECT * FROM v_cases_full WHERE 1=1"
    params = []
    
    if search:
        query += """ AND (
            claimant_first_name LIKE ? OR 
            claimant_last_name LIKE ? OR
            file_case_id LIKE ? OR
            record_id LIKE ?
        )"""
        search_term = f'%{search}%'
        params.extend([search_term, search_term, search_term, search_term])
    
    if status == 'active':
        query += " AND (file_close_date IS NULL OR file_close_date = '')"
    elif status == 'closed':
        query += " AND file_close_date IS NOT NULL AND file_close_date != ''"
    
    # Get total count
    count_query = f"SELECT COUNT(*) FROM ({query})"
    total = conn.execute(count_query, params).fetchone()[0]
    
    # Get paginated results
    query += " ORDER BY file_open_date DESC LIMIT ? OFFSET ?"
    params.extend([per_page, offset])
    
    cases = conn.execute(query, params).fetchall()
    conn.close()
    
    total_pages = (total + per_page - 1) // per_page
    
    return render_template('cases_list.html',
                         cases=cases,
                         page=page,
                         total_pages=total_pages,
                         total=total,
                         search=search,
                         status=status)

@app.route('/cases/<int:case_id>')
@login_required
def case_view(case_id):
    """View single case details"""
    # Get return URL from query parameter or use referrer
    return_to = request.args.get('return_to') or request.referrer or url_for('cases_list')
    
    conn = get_db()
    
    case = conn.execute("""
        SELECT * FROM v_cases_full WHERE id = ?
    """, (case_id,)).fetchone()
    
    if not case:
        flash('Case not found', 'error')
        conn.close()
        return redirect(url_for('cases_list'))
    
    # Get full details from individual tables
    case_details = conn.execute('SELECT * FROM cases WHERE id = ?', (case_id,)).fetchone()
    
    claimant = None
    if case_details['claimant_id']:
        claimant = conn.execute('SELECT * FROM claimants WHERE id = ?', 
                               (case_details['claimant_id'],)).fetchone()
    
    lawyer_firm = None
    if case_details['lawyer_firm_id']:
        lawyer_firm = conn.execute('SELECT * FROM lawyer_firms WHERE id = ?',
                                   (case_details['lawyer_firm_id'],)).fetchone()
    
    lawyer_contact = None
    if case_details['lawyer_contact_id']:
        lawyer_contact = conn.execute('SELECT * FROM lawyer_contacts WHERE id = ?',
                                      (case_details['lawyer_contact_id'],)).fetchone()
    
    client = None
    if case_details['client_id']:
        client = conn.execute('SELECT * FROM clients WHERE id = ?',
                             (case_details['client_id'],)).fetchone()
    
    client_contact = None
    if case_details['client_contact_id']:
        client_contact = conn.execute('SELECT * FROM client_contacts WHERE id = ?',
                                      (case_details['client_contact_id'],)).fetchone()
    
    # Get appointments for this case
    appointments = conn.execute("""
        SELECT * FROM appointments 
        WHERE case_id = ?
        ORDER BY appointment_date DESC, appointment_time DESC
    """, (case_id,)).fetchall()
    
    # Convert appointments to dicts so templates can safely use .get()
    appointments = [dict(apt) for apt in appointments]
    
    # Get invoices for this case
    invoices = conn.execute("""
        SELECT * FROM v_invoices_full
        WHERE case_id = ?
        ORDER BY invoice_date DESC, invoice_number DESC
    """, (case_id,)).fetchall()
    
    # Convert invoices to dicts
    invoices = [dict(inv) for inv in invoices]
    
    # Get reports for this case (from reports table)
    reports = conn.execute("""
        SELECT * FROM reports
        WHERE case_id = ?
        ORDER BY report_date DESC, uploaded_at DESC
    """, (case_id,)).fetchall()
    
    # Get case-specific reports (from case_reports table)
    case_reports = conn.execute("""
        SELECT * FROM case_reports
        WHERE case_id = ?
        ORDER BY report_date DESC, uploaded_at DESC
    """, (case_id,)).fetchall()
    
    # Convert to dicts
    reports = [dict(r) for r in reports]
    case_reports = [dict(cr) for cr in case_reports]
    
    # Get name history for claimant if exists
    name_history = []
    if claimant:
        name_history = conn.execute("""
            SELECT * FROM claimant_name_history
            WHERE claimant_id = ?
            ORDER BY changed_date DESC, id DESC
        """, (claimant['id'],)).fetchall()
        name_history = [dict(nh) for nh in name_history]
    
    conn.close()
    
    return render_template('case_view.html',
                         case=case_details,
                         claimant=claimant,
                         lawyer_firm=lawyer_firm,
                         lawyer_contact=lawyer_contact,
                         client=client,
                         client_contact=client_contact,
                         invoices=invoices,
                         appointments=appointments,
                         reports=reports,
                         case_reports=case_reports,
                         name_history=name_history,
                         return_to=return_to)

@app.route('/cases/new', methods=['GET', 'POST'])
@login_required
def case_new():
    """Create new case"""
    if request.method == 'POST':
        # Get form data and sanitize
        data = sanitize_form_data(request.form.to_dict())
        
        # Validate form data
        is_valid, errors = validate_form_data(data)
        if not is_valid:
            for error in errors:
                flash(error, 'error')
            return redirect(request.url)
        
        try:
            conn = get_db()
            
            # Generate next case ID if not provided
            file_case_id = data.get('file_case_id')
            if not file_case_id:
                result = conn.execute("""
                    SELECT MAX(CAST(file_case_id AS INTEGER)) as max_case 
                    FROM cases 
                    WHERE file_case_id IS NOT NULL 
                    AND file_case_id != '' 
                    AND file_case_id GLOB '[0-9]*'
                """).fetchone()
                max_case = result['max_case'] if result['max_case'] else 68928
                file_case_id = str(max_case + 1)
            
            # Generate record_id (internal tracking) - must be unique
            # Find the maximum existing record_id and increment
            result = conn.execute("""
                SELECT MAX(CAST(record_id AS INTEGER)) as max_record_id 
                FROM cases 
                WHERE record_id IS NOT NULL 
                AND record_id != '' 
                AND record_id GLOB '[0-9]*'
            """).fetchone()
            max_record_id = result['max_record_id'] if result['max_record_id'] else 68928
            record_id = str(max_record_id + 1)
            
            # Ensure record_id is truly unique (in case of gaps or conflicts)
            while True:
                existing = conn.execute('SELECT id FROM cases WHERE record_id = ?', (record_id,)).fetchone()
                if not existing:
                    break
                record_id = str(int(record_id) + 1)
            
            # Create or get claimant - check for exact matches only (no fuzzy matching)
            claimant_id = None
            if data.get('claimant_first_name') and data.get('claimant_last_name'):
                first_name = sanitize_name(data.get('claimant_first_name'))
                last_name = sanitize_name(data.get('claimant_last_name'))
                
                # Check if user explicitly chose to use existing claimant or create new
                use_existing_claimant = data.get('use_existing_claimant_id')
                if use_existing_claimant:
                    # User confirmed to use existing claimant
                    claimant_id = int(use_existing_claimant)
                    
                    # Update existing claimant with any new information provided in the form
                    # This ensures the claimant record has the latest information
                    full_name = f"{first_name} {last_name}".strip()
                    execute_db("""
                        UPDATE claimants SET
                            first_name = ?, last_name = ?, full_name = ?,
                            date_of_birth = COALESCE(?, date_of_birth),
                            email = COALESCE(?, email),
                            address = COALESCE(?, address),
                            city = COALESCE(?, city),
                            province = COALESCE(?, province),
                            postal_code = COALESCE(?, postal_code),
                            home_phone = COALESCE(?, home_phone),
                            work_phone = COALESCE(?, work_phone),
                            cel_phone = COALESCE(?, cel_phone)
                        WHERE id = ?
                    """, (
                        first_name,
                        last_name,
                        full_name,
                        data.get('claimant_date_of_birth') or None,
                        data.get('claimant_email') or None,
                        data.get('claimant_address') or None,
                        data.get('claimant_city') or None,
                        data.get('claimant_province') or None,
                        data.get('claimant_postal_code') or None,
                        data.get('claimant_home_phone') or None,
                        data.get('claimant_work_phone') or None,
                        data.get('claimant_cel_phone') or None,
                        claimant_id
                    ))
                    
                    # Check if there's already a case for this claimant
                    existing_case = conn.execute("""
                        SELECT id, file_case_id FROM cases 
                        WHERE claimant_id = ? 
                        ORDER BY file_open_date DESC, id DESC
                        LIMIT 1
                    """, (claimant_id,)).fetchone()
                    
                    if existing_case:
                        # Case already exists for this claimant - redirect to it
                        conn.close()
                        flash(f'A case already exists for {first_name} {last_name} (Case #{existing_case["file_case_id"]}). Redirecting to existing case.', 'info')
                        return redirect(url_for('case_view', case_id=existing_case['id']))
                    
                    flash(f'Using existing claimant: {first_name} {last_name}. Updated claimant information.', 'info')
                else:
                    # Check for exact match only
                    existing_claimant = conn.execute("""
                        SELECT id FROM claimants 
                        WHERE LOWER(TRIM(first_name)) = LOWER(?) 
                        AND LOWER(TRIM(last_name)) = LOWER(?)
                    """, (first_name, last_name)).fetchone()
                    
                    if existing_claimant:
                        # This should not happen if frontend validation worked, but handle it
                        claimant_id = existing_claimant['id']
                        flash(f'Using existing claimant: {first_name} {last_name}', 'info')
                    else:
                        # Create new claimant
                        full_name = f"{first_name} {last_name}".strip()
                        claimant_id = execute_db("""
                            INSERT INTO claimants (
                                first_name, last_name, full_name, date_of_birth, email,
                                address, city, province, postal_code,
                                home_phone, work_phone, cel_phone
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """, (
                            first_name,
                            last_name,
                            full_name,
                            data.get('claimant_date_of_birth') or None,
                            data.get('claimant_email') or None,
                            data.get('claimant_address') or None,
                            data.get('claimant_city') or None,
                            data.get('claimant_province') or None,
                            data.get('claimant_postal_code') or None,
                            data.get('claimant_home_phone') or None,
                            data.get('claimant_work_phone') or None,
                            data.get('claimant_cel_phone') or None
                        ))
                        flash('New claimant created', 'success')
            
            # Create or get client with fuzzy matching
            client_id = None
            if data.get('client_company_name'):
                company_name = sanitize_company_name(data.get('client_company_name'))
                
                # Check for exact match first
                existing_client = conn.execute("""
                    SELECT id, company_name FROM clients 
                    WHERE LOWER(TRIM(company_name)) = LOWER(?)
                """, (company_name,)).fetchone()
                
                if existing_client:
                    client_id = existing_client['id']
                    flash(f'Using existing client: {existing_client["company_name"]}', 'info')
                else:
                    # Check fuzzy match for similar company names
                    all_clients = conn.execute("""
                        SELECT id, company_name FROM clients
                    """).fetchall()
                    
                    best_match = None
                    best_score = 0.80  # Threshold for fuzzy match on company names
                    
                    for client in all_clients:
                        score = fuzzy_match_score(company_name, client['company_name'])
                        if score > best_score:
                            best_score = score
                            best_match = client
                    
                    if best_match:
                        client_id = best_match['id']
                        flash(f'Using similar existing client: {best_match["company_name"]}', 'info')
                    else:
                        # Create new client
                        client_id = execute_db("""
                            INSERT INTO clients (
                                company_name, address, city, province, postal_code,
                                assistant_phone
                            ) VALUES (?, ?, ?, ?, ?, ?)
                        """, (
                            company_name,
                            data.get('client_address') or None,
                            data.get('client_city') or None,
                            data.get('client_province') or None,
                            data.get('client_postal_code') or None,
                            data.get('client_assistant_phone') or None
                        ))
                        flash('New client created', 'success')
            
            # Create or get lawyer firm with fuzzy matching
            lawyer_firm_id = None
            if data.get('lawyer_firm_name'):
                firm_name = sanitize_company_name(data.get('lawyer_firm_name'))
                
                # Check for exact match first
                existing_firm = conn.execute("""
                    SELECT id, firm_name FROM lawyer_firms 
                    WHERE LOWER(TRIM(firm_name)) = LOWER(?)
                """, (firm_name,)).fetchone()
                
                if existing_firm:
                    lawyer_firm_id = existing_firm['id']
                    flash(f'Using existing law firm: {existing_firm["firm_name"]}', 'info')
                else:
                    # Check fuzzy match for similar firm names
                    all_firms = conn.execute("""
                        SELECT id, firm_name FROM lawyer_firms
                    """).fetchall()
                    
                    best_match = None
                    best_score = 0.80  # Threshold for fuzzy match
                    
                    for firm in all_firms:
                        score = fuzzy_match_score(firm_name, firm['firm_name'])
                        if score > best_score:
                            best_score = score
                            best_match = firm
                    
                    if best_match:
                        lawyer_firm_id = best_match['id']
                        flash(f'Using similar existing law firm: {best_match["firm_name"]}', 'info')
                    else:
                        # Create new lawyer firm
                        lawyer_firm_id = execute_db("""
                            INSERT INTO lawyer_firms (
                                firm_name, firm_alias, address, city, province, postal_code, phone, email
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        """, (
                            firm_name,
                            data.get('firm_alias') or None,
                            data.get('firm_address') or None,
                            data.get('firm_city') or None,
                            data.get('firm_province') or None,
                            data.get('firm_postal_code') or None,
                            data.get('firm_phone') or None,
                            data.get('firm_email') or None
                        ))
            
            # Create or get lawyer contact
            lawyer_contact_id = None
            if data.get('lawyer_contact_first_name') or data.get('lawyer_contact_last_name'):
                # Create new lawyer contact
                lawyer_contact_id = execute_db("""
                    INSERT INTO lawyer_contacts (
                        first_name, last_name, title, lawyer_firm_id, status
                    ) VALUES (?, ?, ?, ?, ?)
                """, (
                    data.get('lawyer_contact_first_name') or None,
                    data.get('lawyer_contact_last_name') or None,
                    data.get('lawyer_contact_title') or None,
                    lawyer_firm_id,
                    data.get('status') or None
                ))
            
            # Create case
            case_id = execute_db("""
                INSERT INTO cases (
                    record_id, file_case_id, assessment_type, case_notes,
                    file_open_date, file_close_date, date_of_loss,
                    service_location,
                    claimant_id, lawyer_firm_id, lawyer_contact_id,
                    client_id, client_file_number, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                record_id,
                file_case_id,
                data.get('assessment_type') or None,
                data.get('case_notes') or None,
                data.get('file_open_date'),
                data.get('file_close_date') or None,
                data.get('date_of_loss') or None,
                data.get('service_location') or None,
                claimant_id,
                lawyer_firm_id,
                lawyer_contact_id,
                client_id,
                data.get('client_file_number') or None,
                'System'
            ))
            
            # Handle appointments with all Phase 2 fields
            appointment_dates = request.form.getlist('appointment_date[]')
            if appointment_dates and appointment_dates[0]:  # Check if first appointment has data
                appointment_times = request.form.getlist('appointment_time[]')
                appointment_timezones = request.form.getlist('appointment_timezone[]')
                appointment_doctors = request.form.getlist('appointment_doctor[]')
                appointment_provider_specialties = request.form.getlist('appointment_provider_specialty[]')
                appointment_specialist_numbers = request.form.getlist('appointment_specialist_number[]')
                appointment_clinic_locations = request.form.getlist('appointment_clinic_location[]')
                appointment_locations = request.form.getlist('appointment_location[]')
                appointment_service_codes = request.form.getlist('appointment_service_code[]')
                appointment_assessment_types = request.form.getlist('appointment_assessment_type[]')
                appointment_descriptions = request.form.getlist('appointment_description[]')
                appointment_statuses = request.form.getlist('appointment_status[]')
                appointment_date_confirmed = request.form.getlist('appointment_date_confirmed[]')
                appointment_date_received = request.form.getlist('appointment_date_received[]')
                appointment_pil_instructions_received = request.form.getlist('appointment_pil_instructions_received[]')
                appointment_pil_instruction_types = request.form.getlist('appointment_pil_instruction_type[]')
                appointment_loi_received = request.form.getlist('appointment_loi_received[]')
                appointment_loi_received_dates = request.form.getlist('appointment_loi_received_date[]')
                appointment_report_deadline_dates = request.form.getlist('appointment_report_deadline_date[]')
                appointment_report_due_dates = request.form.getlist('appointment_report_due_date[]')
                appointment_confirmation_deposit_letter_sent = request.form.getlist('appointment_confirmation_deposit_letter_sent[]')
                appointment_confirmation_deposit_letter_dates = request.form.getlist('appointment_confirmation_deposit_letter_date[]')
                appointment_confirmation_sent_to_doctor = request.form.getlist('appointment_confirmation_sent_to_doctor[]')
                appointment_confirmation_sent_to_doctor_dates = request.form.getlist('appointment_confirmation_sent_to_doctor_date[]')
                appointment_in_calendar = request.form.getlist('appointment_in_calendar[]')
                appointment_retainer_required = request.form.getlist('appointment_retainer_required[]')
                appointment_retainer_requested_on = request.form.getlist('appointment_retainer_requested_on[]')
                appointment_retainer_received_on = request.form.getlist('appointment_retainer_received_on[]')
                appointment_retainer_amounts = request.form.getlist('appointment_retainer_amount[]')
                appointment_atp_requested_on = request.form.getlist('appointment_atp_requested_on[]')
                appointment_atp_received_on = request.form.getlist('appointment_atp_received_on[]')
                appointment_notes_list = request.form.getlist('appointment_notes[]')
                
                for i, appointment_date in enumerate(appointment_dates):
                    if appointment_date:
                        # Generate reference number for each appointment
                        reference = generate_invoice_number()
                        
                        execute_db("""
                            INSERT INTO appointments (
                                case_id, appointment_date, appointment_time, doctor_name,
                                specialist_number, location, service_code, assessment_type,
                                description, date_confirmed, date_received, date_released,
                                reference, notes, status, invoiced,
                                timezone, provider_specialty, clinic_location,
                                pil_instructions_received, pil_instruction_type,
                                report_deadline_date, report_due_date,
                                confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                                confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                                in_calendar, retainer_required, retainer_requested_on,
                                retainer_received_on, retainer_amount,
                                atp_requested_on, atp_received_on,
                                loi_received, loi_received_date
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """, (
                            case_id,
                            appointment_date,
                            appointment_times[i] if i < len(appointment_times) else None,
                            appointment_doctors[i] if i < len(appointment_doctors) else None,
                            appointment_specialist_numbers[i] if i < len(appointment_specialist_numbers) else None,
                            appointment_locations[i] if i < len(appointment_locations) else None,
                            appointment_service_codes[i] if i < len(appointment_service_codes) else None,
                            appointment_assessment_types[i] if i < len(appointment_assessment_types) else None,
                            appointment_descriptions[i] if i < len(appointment_descriptions) else None,
                            appointment_date_confirmed[i] if i < len(appointment_date_confirmed) else None,
                            appointment_date_received[i] if i < len(appointment_date_received) else None,
                            None,  # date_released - moved to accounting section per to-do list
                            reference,
                            appointment_notes_list[i] if i < len(appointment_notes_list) else None,
                            appointment_statuses[i] if i < len(appointment_statuses) else 'scheduled',
                            0,  # invoiced - default to 0
                            appointment_timezones[i] if i < len(appointment_timezones) else 'America/Edmonton',
                            appointment_provider_specialties[i] if i < len(appointment_provider_specialties) else None,
                            appointment_clinic_locations[i] if i < len(appointment_clinic_locations) else None,
                            1 if (i < len(appointment_pil_instructions_received) and appointment_pil_instructions_received[i] == '1') else 0,
                            appointment_pil_instruction_types[i] if i < len(appointment_pil_instruction_types) else None,
                            appointment_report_deadline_dates[i] if i < len(appointment_report_deadline_dates) else None,
                            appointment_report_due_dates[i] if i < len(appointment_report_due_dates) else None,
                            1 if (i < len(appointment_confirmation_deposit_letter_sent) and appointment_confirmation_deposit_letter_sent[i] == '1') else 0,
                            appointment_confirmation_deposit_letter_dates[i] if i < len(appointment_confirmation_deposit_letter_dates) else None,
                            1 if (i < len(appointment_confirmation_sent_to_doctor) and appointment_confirmation_sent_to_doctor[i] == '1') else 0,
                            appointment_confirmation_sent_to_doctor_dates[i] if i < len(appointment_confirmation_sent_to_doctor_dates) else None,
                            1 if (i < len(appointment_in_calendar) and appointment_in_calendar[i] == '1') else 0,
                            1 if (i < len(appointment_retainer_required) and appointment_retainer_required[i] == '1') else 0,
                            appointment_retainer_requested_on[i] if i < len(appointment_retainer_requested_on) else None,
                            appointment_retainer_received_on[i] if i < len(appointment_retainer_received_on) else None,
                            float(appointment_retainer_amounts[i]) if (i < len(appointment_retainer_amounts) and appointment_retainer_amounts[i]) else None,
                            appointment_atp_requested_on[i] if i < len(appointment_atp_requested_on) else None,
                            appointment_atp_received_on[i] if i < len(appointment_atp_received_on) else None,
                            1 if (i < len(appointment_loi_received) and appointment_loi_received[i] == '1') else 0,
                            appointment_loi_received_dates[i] if i < len(appointment_loi_received_dates) else None
                        ))
            
            conn.close()
            
            flash('Case created successfully!', 'success')
            # Redirect to case view with anchor to appointments section
            return redirect(url_for('case_view', case_id=case_id) + '#appointments-section')
            
        except Exception as e:
            flash(f'Error creating case: {str(e)}', 'error')
            try:
                import traceback
                traceback.print_exc()
            except Exception:
                pass
            conn.close() if 'conn' in locals() else None
    
    # Get dropdown data (only needed for edit mode, but keeping for compatibility)
    conn = get_db()
    claimants = conn.execute('SELECT id, first_name, last_name FROM claimants ORDER BY last_name').fetchall()
    clients = conn.execute('SELECT id, company_name FROM clients ORDER BY company_name').fetchall()
    
    # Get next case ID
    result = conn.execute("""
        SELECT MAX(CAST(file_case_id AS INTEGER)) as max_case 
        FROM cases 
        WHERE file_case_id IS NOT NULL 
        AND file_case_id != '' 
        AND file_case_id GLOB '[0-9]*'
    """).fetchone()
    max_case = result['max_case'] if result['max_case'] else 68928
    next_case_id = str(max_case + 1)
    
    conn.close()
    
    # Load service codes from CSV for assessment type dropdown
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    from datetime import date
    today_date = date.today().isoformat()
    
    # Appointment reference number - will be generated when appointment is created
    appointment_reference_number = None
    appointment_case_data = None  # No case data for new cases
    
    return render_template('case_form.html',
                         case=None,
                         case_id=None,
                         claimants=claimants,
                         clients=clients,
                         next_case_id=next_case_id,
                         service_codes=service_codes,
                         today_date=today_date,
                         appointment_reference_number=appointment_reference_number,
                         appointment_case_data=appointment_case_data,
                         lawyer_firm=None,
                         lawyer_contact=None)

@app.route('/cases/<int:case_id>/edit', methods=['GET', 'POST'])
@login_required
def case_edit(case_id):
    """Edit existing case"""
    conn = get_db()
    
    try:
        if request.method == 'POST':
            # Get form data and sanitize
            data = request.form.to_dict()
            data = sanitize_form_data(data)
            
            # Validate form data
            is_valid, errors = validate_form_data(data)
            if not is_valid:
                for error in errors:
                    flash(error, 'error')
                conn.close()
                return redirect(request.url)
            
            try:
                # Get existing case data first to preserve associations
                existing_case = conn.execute('SELECT * FROM cases WHERE id = ?', (case_id,)).fetchone()
                if not existing_case:
                    conn.close()
                    flash('Case not found', 'error')
                    return redirect(url_for('cases_list'))
                
                # Update or create claimant
                # Preserve existing claimant_id if no claimant data provided
                claimant_id = existing_case['claimant_id'] if existing_case else None
                if data.get('claimant_first_name') and data.get('claimant_last_name'):
                    # Check if user explicitly chose to use existing claimant
                    use_existing_claimant = data.get('use_existing_claimant_id')
                    if use_existing_claimant:
                        # User confirmed to use a different existing claimant
                        claimant_id = int(use_existing_claimant)
                        flash(f'Using existing claimant: {data.get("claimant_first_name")} {data.get("claimant_last_name")}', 'info')
                    elif existing_case and existing_case['claimant_id']:
                        # Update existing claimant linked to this case
                        # Get current last name before update
                        current_claimant = conn.execute('SELECT last_name FROM claimants WHERE id = ?', 
                                                       (existing_case['claimant_id'],)).fetchone()
                        old_last_name = current_claimant['last_name'] if current_claimant else None
                        new_last_name = data.get('claimant_last_name')
                        
                        # Update existing claimant
                        full_name = f"{data.get('claimant_first_name')} {data.get('claimant_last_name')}".strip()
                        execute_db("""
                            UPDATE claimants SET
                                first_name = ?, last_name = ?, full_name = ?, date_of_birth = ?, email = ?,
                                address = ?, city = ?, province = ?, postal_code = ?,
                                home_phone = ?, work_phone = ?, cel_phone = ?
                            WHERE id = ?
                        """, (
                            data.get('claimant_first_name'),
                            data.get('claimant_last_name'),
                            full_name,
                            data.get('claimant_date_of_birth') or None,
                            data.get('claimant_email') or None,
                            data.get('claimant_address') or None,
                            data.get('claimant_city') or None,
                            data.get('claimant_province') or None,
                            data.get('claimant_postal_code') or None,
                            data.get('claimant_home_phone') or None,
                            data.get('claimant_work_phone') or None,
                            data.get('claimant_cel_phone') or None,
                            existing_case['claimant_id']
                        ))
                        claimant_id = existing_case['claimant_id']
                        
                        # Track name change if last name changed
                        if old_last_name and new_last_name and old_last_name.strip() != new_last_name.strip():
                            execute_db("""
                                INSERT INTO claimant_name_history (
                                    claimant_id, old_last_name, new_last_name, changed_by, notes
                                ) VALUES (?, ?, ?, ?, ?)
                            """, (
                                claimant_id,
                                old_last_name,
                                new_last_name,
                                session.get('username', 'System'),
                                f"Name changed via case edit (Case #{case_id})"
                            ))
                    else:
                        # Create new claimant
                        full_name = f"{data.get('claimant_first_name')} {data.get('claimant_last_name')}".strip()
                        claimant_id = execute_db("""
                            INSERT INTO claimants (
                                first_name, last_name, full_name, date_of_birth, email,
                                address, city, province, postal_code,
                                home_phone, work_phone, cel_phone
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        """, (
                            data.get('claimant_first_name'),
                            data.get('claimant_last_name'),
                            full_name,
                            data.get('claimant_date_of_birth') or None,
                            data.get('claimant_email') or None,
                            data.get('claimant_address') or None,
                            data.get('claimant_city') or None,
                            data.get('claimant_province') or None,
                            data.get('claimant_postal_code') or None,
                            data.get('claimant_home_phone') or None,
                            data.get('claimant_work_phone') or None,
                            data.get('claimant_cel_phone') or None
                        ))
                
                # Update or create client
                # Preserve existing client_id if no client data provided
                client_id = existing_case['client_id'] if existing_case else None
                if data.get('client_company_name'):
                    # Update existing client if we have a client_id
                    if existing_case and existing_case['client_id']:
                        # Update existing client
                        execute_db("""
                            UPDATE clients SET
                                company_name = ?, address = ?, city = ?, province = ?,
                                postal_code = ?, assistant_phone = ?
                            WHERE id = ?
                        """, (
                            data.get('client_company_name'),
                            data.get('client_address') or None,
                            data.get('client_city') or None,
                            data.get('client_province') or None,
                            data.get('client_postal_code') or None,
                            data.get('client_assistant_phone') or None,
                            existing_case['client_id']
                        ))
                        client_id = existing_case['client_id']
                    else:
                        # Create new client
                        client_id = execute_db("""
                            INSERT INTO clients (
                                company_name, address, city, province, postal_code,
                                assistant_phone
                            ) VALUES (?, ?, ?, ?, ?, ?)
                        """, (
                            data.get('client_company_name'),
                            data.get('client_address') or None,
                            data.get('client_city') or None,
                            data.get('client_province') or None,
                            data.get('client_postal_code') or None,
                            data.get('client_assistant_phone') or None
                        ))
                
                # Update case (note: record_id is never updated - it's immutable)
                # Ensure file_case_id is not overwritten with record_id value
                file_case_id_value = data.get('file_case_id')
                # If file_case_id is empty or None, preserve existing value
                if not file_case_id_value:
                    file_case_id_value = existing_case['file_case_id'] if existing_case else None
                
                execute_db("""
                    UPDATE cases SET
                        file_case_id = ?,
                        assessment_type = ?,
                        case_notes = ?,
                        file_open_date = ?,
                        file_close_date = ?,
                        date_of_loss = ?,
                        service_location = ?,
                        claimant_id = ?,
                        client_id = ?,
                        client_file_number = ?,
                        updated_by = ?,
                        update_date = CURRENT_TIMESTAMP
                    WHERE id = ?
                """, (
                    file_case_id_value,
                    data.get('assessment_type'),
                    data.get('case_notes'),
                    data.get('file_open_date'),
                    data.get('file_close_date') or None,
                    data.get('date_of_loss') or None,
                    data.get('service_location') or None,
                    claimant_id,
                    client_id,
                    data.get('client_file_number') or None,
                    'System',
                    case_id
                ))
                
                # Handle appointments (add new appointments to existing case) with all Phase 2 fields
                appointment_dates = request.form.getlist('appointment_date[]')
                if appointment_dates and appointment_dates[0]:  # Check if first appointment has data
                    appointment_times = request.form.getlist('appointment_time[]')
                    appointment_timezones = request.form.getlist('appointment_timezone[]')
                    appointment_doctors = request.form.getlist('appointment_doctor[]')
                    appointment_provider_specialties = request.form.getlist('appointment_provider_specialty[]')
                    appointment_specialist_numbers = request.form.getlist('appointment_specialist_number[]')
                    appointment_clinic_locations = request.form.getlist('appointment_clinic_location[]')
                    appointment_locations = request.form.getlist('appointment_location[]')
                    appointment_service_codes = request.form.getlist('appointment_service_code[]')
                    appointment_assessment_types = request.form.getlist('appointment_assessment_type[]')
                    appointment_descriptions = request.form.getlist('appointment_description[]')
                    appointment_statuses = request.form.getlist('appointment_status[]')
                    appointment_date_confirmed = request.form.getlist('appointment_date_confirmed[]')
                    appointment_date_received = request.form.getlist('appointment_date_received[]')
                    appointment_pil_instructions_received = request.form.getlist('appointment_pil_instructions_received[]')
                    appointment_pil_instruction_types = request.form.getlist('appointment_pil_instruction_type[]')
                    appointment_loi_received = request.form.getlist('appointment_loi_received[]')
                    appointment_loi_received_dates = request.form.getlist('appointment_loi_received_date[]')
                    appointment_report_deadline_dates = request.form.getlist('appointment_report_deadline_date[]')
                    appointment_report_due_dates = request.form.getlist('appointment_report_due_date[]')
                    appointment_confirmation_deposit_letter_sent = request.form.getlist('appointment_confirmation_deposit_letter_sent[]')
                    appointment_confirmation_deposit_letter_dates = request.form.getlist('appointment_confirmation_deposit_letter_date[]')
                    appointment_confirmation_sent_to_doctor = request.form.getlist('appointment_confirmation_sent_to_doctor[]')
                    appointment_confirmation_sent_to_doctor_dates = request.form.getlist('appointment_confirmation_sent_to_doctor_date[]')
                    appointment_in_calendar = request.form.getlist('appointment_in_calendar[]')
                    appointment_retainer_required = request.form.getlist('appointment_retainer_required[]')
                    appointment_retainer_requested_on = request.form.getlist('appointment_retainer_requested_on[]')
                    appointment_retainer_received_on = request.form.getlist('appointment_retainer_received_on[]')
                    appointment_retainer_amounts = request.form.getlist('appointment_retainer_amount[]')
                    appointment_atp_requested_on = request.form.getlist('appointment_atp_requested_on[]')
                    appointment_atp_received_on = request.form.getlist('appointment_atp_received_on[]')
                    appointment_notes_list = request.form.getlist('appointment_notes[]')
                    
                    appointments_added = 0
                    for i, appointment_date in enumerate(appointment_dates):
                        if appointment_date:
                            # Generate reference number for each appointment
                            reference = generate_invoice_number()
                            
                            execute_db("""
                                INSERT INTO appointments (
                                    case_id, appointment_date, appointment_time, doctor_name,
                                    specialist_number, location, service_code, assessment_type,
                                    description, date_confirmed, date_received, date_released,
                                    reference, notes, status, invoiced,
                                    timezone, provider_specialty, clinic_location,
                                    pil_instructions_received, pil_instruction_type,
                                    report_deadline_date, report_due_date,
                                    confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                                    confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                                    in_calendar, retainer_required, retainer_requested_on,
                                    retainer_received_on, retainer_amount,
                                    atp_requested_on, atp_received_on,
                                    loi_received, loi_received_date
                                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                            """, (
                                case_id,
                                appointment_date,
                                appointment_times[i] if i < len(appointment_times) else None,
                                appointment_doctors[i] if i < len(appointment_doctors) else None,
                                appointment_specialist_numbers[i] if i < len(appointment_specialist_numbers) else None,
                                appointment_locations[i] if i < len(appointment_locations) else None,
                                appointment_service_codes[i] if i < len(appointment_service_codes) else None,
                                appointment_assessment_types[i] if i < len(appointment_assessment_types) else None,
                                appointment_descriptions[i] if i < len(appointment_descriptions) else None,
                                appointment_date_confirmed[i] if i < len(appointment_date_confirmed) else None,
                                appointment_date_received[i] if i < len(appointment_date_received) else None,
                                None,  # date_released - moved to accounting section per to-do list
                                reference,
                                appointment_notes_list[i] if i < len(appointment_notes_list) else None,
                                appointment_statuses[i] if i < len(appointment_statuses) else 'scheduled',
                                0,  # invoiced - default to 0
                                appointment_timezones[i] if i < len(appointment_timezones) else 'America/Edmonton',
                                appointment_provider_specialties[i] if i < len(appointment_provider_specialties) else None,
                                appointment_clinic_locations[i] if i < len(appointment_clinic_locations) else None,
                                1 if (i < len(appointment_pil_instructions_received) and appointment_pil_instructions_received[i] == '1') else 0,
                                appointment_pil_instruction_types[i] if i < len(appointment_pil_instruction_types) else None,
                                appointment_report_deadline_dates[i] if i < len(appointment_report_deadline_dates) else None,
                                appointment_report_due_dates[i] if i < len(appointment_report_due_dates) else None,
                                1 if (i < len(appointment_confirmation_deposit_letter_sent) and appointment_confirmation_deposit_letter_sent[i] == '1') else 0,
                                appointment_confirmation_deposit_letter_dates[i] if i < len(appointment_confirmation_deposit_letter_dates) else None,
                                1 if (i < len(appointment_confirmation_sent_to_doctor) and appointment_confirmation_sent_to_doctor[i] == '1') else 0,
                                appointment_confirmation_sent_to_doctor_dates[i] if i < len(appointment_confirmation_sent_to_doctor_dates) else None,
                                1 if (i < len(appointment_in_calendar) and appointment_in_calendar[i] == '1') else 0,
                                1 if (i < len(appointment_retainer_required) and appointment_retainer_required[i] == '1') else 0,
                                appointment_retainer_requested_on[i] if i < len(appointment_retainer_requested_on) else None,
                                appointment_retainer_received_on[i] if i < len(appointment_retainer_received_on) else None,
                                float(appointment_retainer_amounts[i]) if (i < len(appointment_retainer_amounts) and appointment_retainer_amounts[i]) else None,
                                appointment_atp_requested_on[i] if i < len(appointment_atp_requested_on) else None,
                                appointment_atp_received_on[i] if i < len(appointment_atp_received_on) else None,
                                1 if (i < len(appointment_loi_received) and appointment_loi_received[i] == '1') else 0,
                                appointment_loi_received_dates[i] if i < len(appointment_loi_received_dates) else None
                            ))
                            appointments_added += 1
                    
                    if appointments_added > 0:
                        flash(f'Case updated successfully! {appointments_added} appointment(s) added.', 'success')
                    else:
                        flash('Case updated successfully!', 'success')
                else:
                    flash('Case updated successfully!', 'success')
            
                # Redirect to case view with anchor to appointments section
                conn.close()
                return redirect(url_for('case_view', case_id=case_id) + '#appointments-section')
                
            except Exception as e:
                import traceback
                import sys
                # Log full error to server logs only (no sensitive data exposed)
                traceback.print_exc(file=sys.stderr)
                # Show generic error message to user
                flash('An error occurred while updating the case. Please try again.', 'error')
                conn.close()
                return redirect(url_for('case_edit', case_id=case_id))
    
    except Exception as e:
        import traceback
        import sys
        # Log full error to server logs only (no sensitive data exposed)
        traceback.print_exc(file=sys.stderr)
        # Show generic error message to user
        flash('An error occurred while loading the case. Please try again.', 'error')
        conn.close()
        return redirect(url_for('cases_list'))
    
    # Verify case exists before proceeding
    case = conn.execute('SELECT * FROM cases WHERE id = ?', (case_id,)).fetchone()
    
    if not case:
        flash('Case not found', 'error')
        conn.close()
        return redirect(url_for('cases_list'))
    
    # Convert case to dict for template compatibility
    case = dict(case)
    
    # Get claimant data
    claimant = None
    if case.get('claimant_id'):
        claimant_row = conn.execute('SELECT * FROM claimants WHERE id = ?', (case['claimant_id'],)).fetchone()
        if claimant_row:
            claimant = dict(claimant_row)
    
    # Get client data
    client = None
    if case.get('client_id'):
        client_row = conn.execute('SELECT * FROM clients WHERE id = ?', (case['client_id'],)).fetchone()
        if client_row:
            client = dict(client_row)
    
    # Get lawyer firm data (needed for template fields)
    lawyer_firm = None
    if case.get('lawyer_firm_id'):
        lawyer_firm_row = conn.execute('SELECT * FROM lawyer_firms WHERE id = ?', (case['lawyer_firm_id'],)).fetchone()
        if lawyer_firm_row:
            lawyer_firm = dict(lawyer_firm_row)
    
    # Get lawyer contact data
    lawyer_contact = None
    if case.get('lawyer_contact_id'):
        lawyer_contact_row = conn.execute('SELECT * FROM lawyer_contacts WHERE id = ?', (case['lawyer_contact_id'],)).fetchone()
        if lawyer_contact_row:
            lawyer_contact = dict(lawyer_contact_row)
    
    # Appointment reference number - will be generated when appointment is created
    appointment_reference_number = None
    appointment_case_data = {
        'file_case_id': case.get('file_case_id', ''),
        'claimant_first_name': claimant.get('first_name', '') if claimant else '',
        'claimant_last_name': claimant.get('last_name', '') if claimant else '',
        'client_company_name': client.get('company_name', '') if client else ''
    }
    
    # Get dropdown data (for compatibility)
    claimants = conn.execute('SELECT id, first_name, last_name FROM claimants ORDER BY last_name').fetchall()
    clients = conn.execute('SELECT id, company_name FROM clients ORDER BY company_name').fetchall()
    conn.close()
    
    # Load service codes from CSV for assessment type dropdown
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    from datetime import date
    today_date = date.today().isoformat()
    
    return render_template('case_form.html',
                         case=case,
                         case_id=case['id'],
                         claimant=claimant,
                         client=client,
                         lawyer_firm=lawyer_firm,
                         lawyer_contact=lawyer_contact,
                         claimants=claimants,
                         clients=clients,
                         service_codes=service_codes,
                         today_date=today_date,
                         appointment_reference_number=appointment_reference_number,
                         appointment_case_data=appointment_case_data)

@app.route('/cases/<int:case_id>/delete', methods=['POST'])
@login_required
def case_delete(case_id):
    """Delete a case"""
    try:
        execute_db('DELETE FROM cases WHERE id = ?', (case_id,))
        flash('Case deleted successfully!', 'success')
    except Exception as e:
        flash(f'Error deleting case: {str(e)}', 'error')
    
    return redirect(url_for('cases_list'))

# ============================================================================
# Clients Routes (CRUD)
# ============================================================================

@app.route('/clients')
@login_required
def clients_list():
    """List all clients (insurance companies)"""
    conn = get_db()
    
    clients = conn.execute("""
        SELECT c.*, 
               COUNT(cs.id) as case_count,
               COALESCE(SUM(CASE 
                   WHEN cs.file_close_date IS NULL OR 
                        cs.file_close_date = '' OR 
                        TRIM(cs.file_close_date) = '' 
                   THEN 1 
                   ELSE 0 
               END), 0) as active_cases
        FROM clients c
        LEFT JOIN cases cs ON c.id = cs.client_id
        GROUP BY c.id
        ORDER BY case_count DESC, c.company_name
    """).fetchall()
    
    conn.close()
    
    return render_template('clients_list.html', clients=clients)

@app.route('/clients/<int:client_id>')
@login_required
def client_view(client_id):
    """View client details"""
    # Get filter parameter
    case_filter = request.args.get('filter', 'all')  # all, active, closed
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    conn = get_db()
    
    client = conn.execute('SELECT * FROM clients WHERE id = ?', (client_id,)).fetchone()
    
    if not client:
        flash('Client not found', 'error')
        conn.close()
        return redirect(url_for('clients_list'))
    
    # Get client contacts with pagination
    contact_page = request.args.get('contact_page', 1, type=int)
    contacts_per_page = 10
    
    total_contacts_result = conn.execute("""
        SELECT COUNT(*) as count FROM client_contacts WHERE client_id = ?
    """, (client_id,)).fetchone()
    total_contacts = total_contacts_result['count'] if total_contacts_result else 0
    
    contact_offset = (contact_page - 1) * contacts_per_page
    contacts = conn.execute("""
        SELECT * FROM client_contacts WHERE client_id = ?
        ORDER BY last_name, first_name
        LIMIT ? OFFSET ?
    """, (client_id, contacts_per_page, contact_offset)).fetchall()
    
    total_contact_pages = (total_contacts + contacts_per_page - 1) // contacts_per_page if total_contacts > 0 else 1
    
    # Build cases query with filter
    if case_filter == 'active':
        cases_query = """
            SELECT * FROM v_cases_full 
            WHERE client_id = ? AND (file_close_date IS NULL OR file_close_date = '')
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        """
    elif case_filter == 'closed':
        cases_query = """
            SELECT * FROM v_cases_full 
            WHERE client_id = ? AND file_close_date IS NOT NULL AND file_close_date != ''
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        """
    else:
        cases_query = """
            SELECT * FROM v_cases_full 
            WHERE client_id = ?
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        """
    
    # Get cases with pagination
    offset = (page - 1) * per_page
    cases = conn.execute(cases_query, (client_id, per_page, offset)).fetchall()
    
    # Get total count for pagination
    if case_filter == 'active':
        total_cases = conn.execute("""
            SELECT COUNT(*) FROM v_cases_full 
            WHERE client_id = ? AND (file_close_date IS NULL OR file_close_date = '')
        """, (client_id,)).fetchone()[0]
    elif case_filter == 'closed':
        total_cases = conn.execute("""
            SELECT COUNT(*) FROM v_cases_full 
            WHERE client_id = ? AND file_close_date IS NOT NULL AND file_close_date != ''
        """, (client_id,)).fetchone()[0]
    else:
        total_cases = conn.execute("""
            SELECT COUNT(*) FROM v_cases_full WHERE client_id = ?
        """, (client_id,)).fetchone()[0]
    
    # Get actual counts for all cases (not filtered)
    all_cases = conn.execute("""
        SELECT * FROM v_cases_full WHERE client_id = ? ORDER BY file_open_date DESC
    """, (client_id,)).fetchall()
    
    total_pages = (total_cases + per_page - 1) // per_page
    
    conn.close()
    
    return render_template('client_view.html', 
                         client=client, contacts=contacts, cases=cases, all_cases=all_cases,
                         case_filter=case_filter, page=page, total_pages=total_pages,
                         contact_page=contact_page, total_contact_pages=total_contact_pages,
                         total_contacts=total_contacts)

@app.route('/clients/<int:client_id>/edit', methods=['GET', 'POST'])
@login_required
def client_edit(client_id):
    """Edit client information"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            execute_db("""
                UPDATE clients SET
                    company_name = ?, company_alias = ?, address = ?,
                    city = ?, province = ?, postal_code = ?, assistant_phone = ?
                WHERE id = ?
            """, (
                data.get('company_name'),
                data.get('company_alias') or None,
                data.get('address') or None,
                data.get('city') or None,
                data.get('province') or None,
                data.get('postal_code') or None,
                data.get('assistant_phone') or None,
                client_id
            ))
            
            conn.close()
            flash('Client updated successfully!', 'success')
            return redirect(url_for('client_view', client_id=client_id))
            
        except Exception as e:
            flash(f'Error updating client: {str(e)}', 'error')
            conn.close()
    
    client = conn.execute('SELECT * FROM clients WHERE id = ?', (client_id,)).fetchone()
    conn.close()
    
    return render_template('client_form.html', client=client)

@app.route('/clients/<int:client_id>/contacts/new', methods=['GET', 'POST'])
@login_required
def contact_new(client_id):
    """Create new client contact"""
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            execute_db("""
                INSERT INTO client_contacts (
                    client_id, first_name, last_name, title, phone
                ) VALUES (?, ?, ?, ?, ?)
            """, (
                client_id,
                data.get('first_name'),
                data.get('last_name'),
                data.get('title') or None,
                data.get('phone') or None
            ))
            
            flash('Contact added successfully!', 'success')
            return redirect(url_for('client_view', client_id=client_id))
            
        except Exception as e:
            flash(f'Error adding contact: {str(e)}', 'error')
    
    return render_template('contact_form.html', contact=None, client_id=client_id)

@app.route('/clients/<int:client_id>/contacts/<int:contact_id>/edit', methods=['GET', 'POST'])
@login_required
def contact_edit(client_id, contact_id):
    """Edit client contact"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            execute_db("""
                UPDATE client_contacts SET
                    first_name = ?, last_name = ?, title = ?, phone = ?
                WHERE id = ? AND client_id = ?
            """, (
                data.get('first_name'),
                data.get('last_name'),
                data.get('title') or None,
                data.get('phone') or None,
                contact_id,
                client_id
            ))
            
            conn.close()
            flash('Contact updated successfully!', 'success')
            return redirect(url_for('client_view', client_id=client_id))
            
        except Exception as e:
            flash(f'Error updating contact: {str(e)}', 'error')
    
    contact = conn.execute("""
        SELECT * FROM client_contacts WHERE id = ? AND client_id = ?
    """, (contact_id, client_id)).fetchone()
    
    if not contact:
        flash('Contact not found', 'error')
        conn.close()
        return redirect(url_for('client_view', client_id=client_id))
    
    conn.close()
    return render_template('contact_form.html', contact=contact, client_id=client_id)

@app.route('/clients/<int:client_id>/contacts/<int:contact_id>/delete', methods=['POST'])
@login_required
def contact_delete(client_id, contact_id):
    """Delete client contact"""
    try:
        execute_db("""
            DELETE FROM client_contacts WHERE id = ? AND client_id = ?
        """, (contact_id, client_id))
        flash('Contact deleted successfully', 'success')
    except Exception as e:
        flash(f'Error deleting contact: {str(e)}', 'error')
    
    return redirect(url_for('client_view', client_id=client_id))

@app.route('/api/check/claimant', methods=['GET'])
@login_required
def api_check_claimant():
    """Check if a claimant with the same name already exists"""
    first_name = request.args.get('first_name', '').strip()
    last_name = request.args.get('last_name', '').strip()
    
    if not first_name or not last_name:
        return jsonify({'exists': False, 'claimants': []})
    
    conn = get_db()
    
    # Check for exact matches (case-insensitive)
    claimants = conn.execute("""
        SELECT id, first_name, last_name, date_of_birth, email, 
               address, city, province, postal_code,
               home_phone, work_phone, cel_phone
        FROM claimants 
        WHERE LOWER(TRIM(first_name)) = LOWER(?) 
        AND LOWER(TRIM(last_name)) = LOWER(?)
        ORDER BY id DESC
        LIMIT 10
    """, (first_name, last_name)).fetchall()
    
    conn.close()
    
    if claimants:
        claimants_list = [dict(c) for c in claimants]
        return jsonify({
            'exists': True,
            'claimants': claimants_list
        })
    else:
        return jsonify({
            'exists': False,
            'claimants': []
        })

@app.route('/api/search/clients')
@login_required
def search_clients():
    """API endpoint to search clients"""
    query = request.args.get('q', '')
    if not query or len(query) < 2:
        return jsonify([])
    
    conn = get_db()
    clients = conn.execute("""
        SELECT id, company_name, address, city, province, postal_code, assistant_phone
        FROM clients
        WHERE company_name LIKE ? OR company_alias LIKE ?
        ORDER BY company_name
        LIMIT 10
    """, (f'%{query}%', f'%{query}%')).fetchall()
    
    results = [{
        'id': row['id'],
        'company_name': row['company_name'],
        'address': row['address'],
        'city': row['city'],
        'province': row['province'],
        'postal_code': row['postal_code'],
        'phone': row['assistant_phone']
    } for row in clients]
    
    conn.close()
    return jsonify(results)

@app.route('/api/search/lawyers')
@login_required
def search_lawyers():
    """API endpoint to search lawyers"""
    query = request.args.get('q', '')
    if not query or len(query) < 2:
        return jsonify([])
    
    conn = get_db()
    lawyers = conn.execute("""
        SELECT DISTINCT lf.id, lf.firm_name, lf.address, lf.city, lf.phone
        FROM lawyer_firms lf
        WHERE lf.firm_name LIKE ?
        ORDER BY lf.firm_name
        LIMIT 10
    """, (f'%{query}%',)).fetchall()
    
    results = [{
        'id': row['id'],
        'firm_name': row['firm_name'],
        'address': row['address'],
        'city': row['city'],
        'phone': row['phone']
    } for row in lawyers]
    
    conn.close()
    return jsonify(results)

@app.route('/api/search/doctors')
@login_required
def search_doctors():
    """API endpoint to search doctors"""
    query = request.args.get('q', '')
    if not query or len(query) < 2:
        return jsonify([])
    
    conn = get_db()
    doctors = conn.execute("""
        SELECT id, name, first_name, last_name, prefix, specialty, 
               phone, cell_phone, email, clinic_city, clinic_province,
               clinic_address, clinic_postal_code
        FROM doctors
        WHERE name LIKE ? OR first_name LIKE ? OR last_name LIKE ? OR specialty LIKE ?
        ORDER BY name
        LIMIT 10
    """, (f'%{query}%', f'%{query}%', f'%{query}%', f'%{query}%')).fetchall()
    
    results = [{
        'id': row['id'],
        'name': row['name'],
        'first_name': row['first_name'],
        'last_name': row['last_name'],
        'prefix': row['prefix'],
        'specialty': row['specialty'],
        'phone': row['phone'],
        'cell_phone': row['cell_phone'],
        'email': row['email'],
        'city': row['clinic_city'],
        'province': row['clinic_province'],
        'clinic_address': row['clinic_address'],
        'clinic_postal_code': row['clinic_postal_code']
    } for row in doctors]
    
    conn.close()
    return jsonify(results)

# ============================================================================
# Lawyers Routes
# ============================================================================

@app.route('/lawyers')
@login_required
def lawyers_list():
    """List all lawyers"""
    search = request.args.get('search', '')
    
    conn = get_db()
    
    if search:
        lawyers = conn.execute("""
            SELECT lc.*, lf.firm_name, lf.city as firm_city, lf.phone as firm_phone,
                   lf.email as firm_email, lf.address as firm_address,
                   COUNT(c.id) as case_count
            FROM lawyer_contacts lc
            LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
            LEFT JOIN cases c ON lc.id = c.lawyer_contact_id
            WHERE lc.first_name LIKE ? OR lc.last_name LIKE ? OR lf.firm_name LIKE ?
            GROUP BY lc.id
            ORDER BY lc.last_name, lc.first_name
        """, (f'%{search}%', f'%{search}%', f'%{search}%')).fetchall()
    else:
        lawyers = conn.execute("""
            SELECT lc.*, lf.firm_name, lf.city as firm_city, lf.phone as firm_phone,
                   lf.email as firm_email, lf.address as firm_address,
                   COUNT(c.id) as case_count
            FROM lawyer_contacts lc
            LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
            LEFT JOIN cases c ON lc.id = c.lawyer_contact_id
            GROUP BY lc.id
            ORDER BY lc.last_name, lc.first_name
        """).fetchall()
    
    # Convert to dicts
    lawyers = [dict(lawyer) for lawyer in lawyers]
    
    conn.close()
    
    return render_template('lawyers_list.html', lawyers=lawyers, search=search)

@app.route('/lawyers/new', methods=['GET', 'POST'])
@login_required
def lawyer_new():
    """Create new lawyer"""
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            conn = get_db()
            
            # Create or get firm
            firm_id = None
            if data.get('firm_name'):
                existing_firm = conn.execute("""
                    SELECT id FROM lawyer_firms WHERE firm_name = ? AND city = ?
                """, (data.get('firm_name'), data.get('firm_city', ''))).fetchone()
                
                if existing_firm:
                    firm_id = existing_firm['id']
                    # Update firm info if provided
                    execute_db("""
                        UPDATE lawyer_firms SET
                            firm_alias = COALESCE(?, firm_alias),
                            address = COALESCE(?, address),
                            city = COALESCE(?, city),
                            province = COALESCE(?, province),
                            postal_code = COALESCE(?, postal_code),
                            phone = COALESCE(?, phone),
                            email = COALESCE(?, email)
                        WHERE id = ?
                    """, (
                        data.get('firm_alias') or None,
                        data.get('firm_address') or None,
                        data.get('firm_city') or None,
                        data.get('firm_province') or None,
                        data.get('firm_postal_code') or None,
                        data.get('firm_phone') or None,
                        data.get('firm_email') or None,
                        firm_id
                    ))
                else:
                    firm_id = execute_db("""
                        INSERT INTO lawyer_firms (
                            firm_name, firm_alias, address, city, province, postal_code, phone, email
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    """, (
                        data.get('firm_name'),
                        data.get('firm_alias') or None,
                        data.get('firm_address') or None,
                        data.get('firm_city') or None,
                        data.get('firm_province') or None,
                        data.get('firm_postal_code') or None,
                        data.get('firm_phone') or None,
                        data.get('firm_email') or None
                    ))
            
            # Create lawyer contact
            execute_db("""
                INSERT INTO lawyer_contacts (
                    salutation, first_name, last_name, title, lawyer_firm_id,
                    direct_number, ext, status, email, fax,
                    assistant_name, assistant_phone, assistant_email
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                data.get('salutation') or None,
                data.get('first_name'),
                data.get('last_name'),
                data.get('title') or None,
                firm_id,
                data.get('direct_number') or None,
                data.get('ext') or None,
                data.get('status') or None,
                data.get('email') or None,
                data.get('fax') or None,
                data.get('assistant_name') or None,
                data.get('assistant_phone') or None,
                data.get('assistant_email') or None
            ))
            
            conn.close()
            
            flash('Lawyer created successfully!', 'success')
            return redirect(url_for('lawyers_list'))
            
        except Exception as e:
            flash(f'Error creating lawyer: {str(e)}', 'error')
            conn.close()
    
    return render_template('lawyer_form.html', lawyer=None)

@app.route('/lawyers/<int:lawyer_id>/edit', methods=['GET', 'POST'])
@login_required
def lawyer_edit(lawyer_id):
    """Edit existing lawyer"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Create or update firm
            firm_id = data.get('firm_id')
            if data.get('firm_name'):
                if firm_id:
                    # Update existing firm
                    execute_db("""
                        UPDATE lawyer_firms SET
                            firm_name = ?, firm_alias = ?, address = ?,
                            city = ?, province = ?, postal_code = ?,
                            phone = ?, email = ?
                        WHERE id = ?
                    """, (
                        data.get('firm_name'),
                        data.get('firm_alias') or None,
                        data.get('firm_address') or None,
                        data.get('firm_city') or None,
                        data.get('firm_province') or None,
                        data.get('firm_postal_code') or None,
                        data.get('firm_phone') or None,
                        data.get('firm_email') or None,
                        firm_id
                    ))
                else:
                    # Create new firm
                    firm_id = execute_db("""
                        INSERT INTO lawyer_firms (
                            firm_name, firm_alias, address, city, province, postal_code, phone, email
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    """, (
                        data.get('firm_name'),
                        data.get('firm_alias') or None,
                        data.get('firm_address') or None,
                        data.get('firm_city') or None,
                        data.get('firm_province') or None,
                        data.get('firm_postal_code') or None,
                        data.get('firm_phone') or None,
                        data.get('firm_email') or None
                    ))
            
            # Update lawyer contact
            execute_db("""
                UPDATE lawyer_contacts SET
                    salutation = ?, first_name = ?, last_name = ?, title = ?,
                    lawyer_firm_id = ?, direct_number = ?, ext = ?, status = ?,
                    email = ?, fax = ?,
                    assistant_name = ?, assistant_phone = ?, assistant_email = ?
                WHERE id = ?
            """, (
                data.get('salutation') or None,
                data.get('first_name'),
                data.get('last_name'),
                data.get('title') or None,
                firm_id,
                data.get('direct_number') or None,
                data.get('ext') or None,
                data.get('status') or None,
                data.get('email') or None,
                data.get('fax') or None,
                data.get('assistant_name') or None,
                data.get('assistant_phone') or None,
                data.get('assistant_email') or None,
                lawyer_id
            ))
            
            conn.close()
            
            flash('Lawyer updated successfully!', 'success')
            return redirect(url_for('lawyers_list'))
            
        except Exception as e:
            flash(f'Error updating lawyer: {str(e)}', 'error')
            conn.close()
    
    # Get lawyer with firm info
    lawyer = conn.execute("""
        SELECT lc.*, lf.id as firm_id, lf.firm_name, lf.firm_alias, lf.address as firm_address,
               lf.city as firm_city, lf.province as firm_province, lf.postal_code as firm_postal_code,
               lf.phone as firm_phone, lf.email as firm_email
        FROM lawyer_contacts lc
        LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
        WHERE lc.id = ?
    """, (lawyer_id,)).fetchone()
    
    if not lawyer:
        flash('Lawyer not found', 'error')
        conn.close()
        return redirect(url_for('lawyers_list'))
    
    conn.close()
    return render_template('lawyer_form.html', lawyer=lawyer)

@app.route('/lawyers/<int:lawyer_id>/delete', methods=['POST'])
@login_required
def lawyer_delete(lawyer_id):
    """Delete lawyer"""
    try:
        execute_db("DELETE FROM lawyer_contacts WHERE id = ?", (lawyer_id,))
        flash('Lawyer deleted successfully', 'success')
    except Exception as e:
        flash(f'Error deleting lawyer: {str(e)}', 'error')
    
    return redirect(url_for('lawyers_list'))

# ============================================================================
# Doctors Routes
# ============================================================================

@app.route('/doctors')
@login_required
def doctors_list():
    """List all doctors"""
    search = request.args.get('search', '')
    
    conn = get_db()
    
    # Build query based on search
    if search:
        doctors = conn.execute("""
            SELECT * FROM doctors
            WHERE name LIKE ? OR specialty LIKE ? OR city LIKE ? OR phone LIKE ?
            ORDER BY name ASC
        """, (f'%{search}%', f'%{search}%', f'%{search}%', f'%{search}%')).fetchall()
    else:
        doctors = conn.execute("""
            SELECT * FROM doctors
            ORDER BY name ASC
        """).fetchall()
    
    # Convert to dicts
    doctors = [dict(doctor) for doctor in doctors]
    
    conn.close()
    
    return render_template('doctors_list.html', doctors=doctors, search=search)

@app.route('/doctors/new', methods=['GET', 'POST'])
@login_required
def doctor_new():
    """Create new doctor"""
    if request.method == 'POST':
        data = request.form.to_dict()
        
        # Construct full name from parts
        first_name = data.get('first_name')
        last_name = data.get('last_name')
        prefix = data.get('prefix')
        
        full_name = ""
        if first_name and last_name:
            full_name = f"{first_name} {last_name}".strip()
        
        try:
            # Phase 4: Process provider fees
            provider_fee = None
            trial_fee_per_hour = None
            if data.get('provider_fee'):
                try:
                    provider_fee = float(data.get('provider_fee'))
                except (ValueError, TypeError):
                    pass
            if data.get('trial_fee_per_hour'):
                try:
                    trial_fee_per_hour = float(data.get('trial_fee_per_hour'))
                except (ValueError, TypeError):
                    pass
            
            execute_db("""
                INSERT INTO doctors (name, prefix, first_name, last_name, specialty,
                                   mailing_address, mailing_city, mailing_province, mailing_postal_code,
                                   phone, cell_phone, email,
                                   clinic_address, clinic_city, clinic_province, clinic_postal_code,
                                   cancellation_fee, notes, provider_fee, trial_fee_per_hour, trial_fee_template)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                full_name,
                prefix or None,
                first_name,
                last_name,
                data.get('specialty') or None,
                data.get('mailing_address') or None,
                data.get('mailing_city') or None,
                data.get('mailing_province') or None,
                data.get('mailing_postal_code') or None,
                data.get('phone') or None,
                data.get('cell_phone') or None,
                data.get('email') or None,
                data.get('clinic_address') or None,
                data.get('clinic_city') or None,
                data.get('clinic_province') or None,
                data.get('clinic_postal_code') or None,
                data.get('cancellation_fee') or None,
                data.get('notes') or None,
                provider_fee,
                trial_fee_per_hour,
                data.get('trial_fee_template') or None
            ))
            
            flash('Doctor created successfully!', 'success')
            return redirect(url_for('doctors_list'))
            
        except Exception as e:
            flash(f'Error creating doctor: {str(e)}', 'error')
    
    return render_template('doctor_form.html', doctor=None)

@app.route('/doctors/<int:doctor_id>/edit', methods=['GET', 'POST'])
@login_required
def doctor_edit(doctor_id):
    """Edit existing doctor"""
    conn = get_db()

    if request.method == 'POST':
        data = request.form.to_dict()
        
        # Construct full name from parts
        first_name = data.get('first_name')
        last_name = data.get('last_name')
        prefix = data.get('prefix')
        
        full_name = ""
        if first_name and last_name:
            full_name = f"{first_name} {last_name}".strip()
        
        try:
            # Phase 4: Process provider fees
            provider_fee = None
            trial_fee_per_hour = None
            if data.get('provider_fee'):
                try:
                    provider_fee = float(data.get('provider_fee'))
                except (ValueError, TypeError):
                    pass
            if data.get('trial_fee_per_hour'):
                try:
                    trial_fee_per_hour = float(data.get('trial_fee_per_hour'))
                except (ValueError, TypeError):
                    pass
            
            execute_db("""
                UPDATE doctors 
                SET name = ?, prefix = ?, first_name = ?, last_name = ?, specialty = ?,
                    mailing_address = ?, mailing_city = ?, mailing_province = ?, mailing_postal_code = ?,
                    phone = ?, cell_phone = ?, email = ?,
                    clinic_address = ?, clinic_city = ?, clinic_province = ?, clinic_postal_code = ?,
                    cancellation_fee = ?, notes = ?,
                    provider_fee = ?, trial_fee_per_hour = ?, trial_fee_template = ?
                WHERE id = ?
            """, (
                full_name,
                prefix or None,
                first_name,
                last_name,
                data.get('specialty') or None,
                data.get('mailing_address') or None,
                data.get('mailing_city') or None,
                data.get('mailing_province') or None,
                data.get('mailing_postal_code') or None,
                data.get('phone') or None,
                data.get('cell_phone') or None,
                data.get('email') or None,
                data.get('clinic_address') or None,
                data.get('clinic_city') or None,
                data.get('clinic_province') or None,
                data.get('clinic_postal_code') or None,
                data.get('cancellation_fee') or None,
                data.get('notes') or None,
                provider_fee,
                trial_fee_per_hour,
                data.get('trial_fee_template') or None,
                doctor_id
            ))
            
            flash('Doctor updated successfully!', 'success')
            return redirect(url_for('doctors_list'))
            
        except Exception as e:
            flash(f'Error updating doctor: {str(e)}', 'error')

    doctor = conn.execute('SELECT * FROM doctors WHERE id = ?', (doctor_id,)).fetchone()

    # Convert to dict so Jinja2 templates can use .get()
    if doctor:
        doctor = dict(doctor)

    if not doctor:
        flash('Doctor not found', 'error')
        return redirect(url_for('doctors_list'))
    
    conn.close()
    return render_template('doctor_form.html', doctor=doctor)

@app.route('/doctors/<int:doctor_id>/delete', methods=['POST'])
@login_required
def doctor_delete(doctor_id):
    """Delete a doctor"""
    try:
        execute_db('DELETE FROM doctors WHERE id = ?', (doctor_id,))
        flash('Doctor deleted successfully!', 'success')
    except Exception as e:
        flash(f'Error deleting doctor: {str(e)}', 'error')
    
    return redirect(url_for('doctors_list'))

# ============================================================================
# Appointments Routes
# ============================================================================

# Phase 4: Uninvoiced Appointments Reminder
@app.route('/appointments/uninvoiced')
@login_required
def uninvoiced_appointments():
    """List appointments that are 30+ days old and haven't been invoiced"""
    from datetime import date, timedelta
    
    conn = get_db()
    thirty_days_ago = date.today() - timedelta(days=30)
    
    appointments = conn.execute("""
        SELECT 
            a.*,
            c.file_case_id,
            cl.first_name as claimant_first_name, 
            cl.last_name as claimant_last_name,
            a.doctor_name,
            CAST(julianday('now') - julianday(a.appointment_date) AS INTEGER) AS days_since
        FROM appointments a
        LEFT JOIN invoices i ON a.id = i.appointment_id
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        WHERE i.id IS NULL 
        AND a.appointment_date < ?
        AND a.appointment_date IS NOT NULL
        ORDER BY a.appointment_date ASC
    """, (thirty_days_ago,)).fetchall()
    
    # Convert to dicts so templates can safely use .get()
    appointments = [dict(apt) for apt in appointments]
    
    conn.close()
    
    return render_template('uninvoiced_appointments.html', appointments=appointments)

@app.route('/appointments')
@login_required
def appointments_list():
    """List all appointments with search and filter"""
    search = request.args.get('search', '')
    status_filter = request.args.get('status', '')
    date_filter = request.args.get('date_filter', '')
    
    conn = get_db()
    
    # Build query - order by closest to current date
    query = """SELECT a.*,
               c.file_case_id,
               c.record_id,
               cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name,
               cl.full_name AS claimant_full_name,
               cli.company_name AS client_company_name,
               a.doctor_name AS doctor_full_name,
               COALESCE(al.letter_count, 0) AS letter_count
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        LEFT JOIN (
            SELECT appointment_id, COUNT(*) AS letter_count
            FROM appointment_letters
            GROUP BY appointment_id
        ) al ON al.appointment_id = a.id
        WHERE 1=1"""
    params = []
    
    if search:
        query += """ AND (
            c.file_case_id LIKE ? OR
            cl.first_name LIKE ? OR
            cl.last_name LIKE ? OR
            cli.company_name LIKE ? OR
            a.doctor_name LIKE ? OR
            a.location LIKE ? OR
            a.description LIKE ?
        )"""
        search_term = f'%{search}%'
        params.extend([search_term] * 7)
    
    if status_filter:
        query += " AND a.status = ?"
        params.append(status_filter)
    
    if date_filter == 'upcoming':
        query += " AND a.appointment_date >= date('now')"
    elif date_filter == 'past':
        query += " AND a.appointment_date < date('now')"
    elif date_filter == 'today':
        query += " AND a.appointment_date = date('now')"
    elif date_filter == 'this_week':
        query += " AND a.appointment_date BETWEEN date('now') AND date('now', '+7 days')"
    
    # Order by closest to current date (upcoming first, then past)
    query += """ 
        ORDER BY 
            CASE 
                WHEN a.appointment_date >= date('now') THEN 0 
                ELSE 1 
            END,
            ABS(julianday(a.appointment_date) - julianday('now')),
            a.appointment_time
    """
    
    appointments = conn.execute(query, params).fetchall()
    
    # Convert to dicts
    appointments = [dict(apt) for apt in appointments]
    
    conn.close()
    
    from datetime import date
    return render_template('appointments_list.html', 
                          appointments=appointments,
                          search=search,
                          status_filter=status_filter,
                          date_filter=date_filter,
                          date=date.today())

@app.route('/appointments/new', methods=['GET', 'POST'])
@app.route('/appointments/new/<int:case_id>', methods=['GET', 'POST'])
@login_required
def appointment_new(case_id=None):
    """Create new appointment"""
    # Get case_id from URL parameter or query string
    if not case_id:
        case_id = request.args.get('case_id', type=int)
    
    if request.method == 'POST':
        data = request.form.to_dict()
        form_action = request.form.get('form_action', 'schedule')
        case_id = data.get('case_id')

        if not case_id:
            flash('Please select a case before scheduling the appointment.', 'error')
            return redirect(request.url)
        
        try:
            # Ensure reference is set; use next invoice number so it matches future invoice
            if not data.get('reference'):
                data['reference'] = generate_invoice_number()

            invoiced_flag = 1 if data.get('invoiced') == 'on' or form_action == 'schedule_invoice' else 0

            # Insert appointment with all Phase 2 fields
            appointment_id = execute_db("""
                INSERT INTO appointments (
                    case_id, appointment_date, appointment_time, doctor_name,
                    specialist_number, location, service_code, assessment_type,
                    description, date_confirmed, date_received, date_released,
                    reference, notes, status, invoiced,
                    timezone, provider_specialty, clinic_location,
                    pil_instructions_received, pil_instruction_type,
                    report_deadline_date, report_due_date,
                    confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                    confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                    in_calendar, retainer_required, retainer_requested_on,
                    retainer_received_on, retainer_amount,
                    atp_requested_on, atp_received_on,
                    loi_received, loi_received_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                int(case_id),
                data.get('appointment_date') or None,
                data.get('appointment_time') or None,
                data.get('doctor_name') or None,
                data.get('specialist_number') or None,
                data.get('location') or None,
                data.get('service_code') or None,
                data.get('assessment_type') or None,
                data.get('description') or None,
                data.get('date_confirmed') or None,
                data.get('date_received') or None,
                data.get('date_released') or None,
                data.get('reference'),
                data.get('notes') or None,
                data.get('status', 'scheduled'),
                invoiced_flag,
                data.get('timezone') or 'America/Edmonton',
                data.get('provider_specialty') or None,
                data.get('clinic_location') or None,
                1 if data.get('pil_instructions_received') == '1' else 0,
                data.get('pil_instruction_type') or None,
                data.get('report_deadline_date') or None,
                data.get('report_due_date') or None,
                1 if data.get('confirmation_deposit_letter_sent') == '1' else 0,
                data.get('confirmation_deposit_letter_date') or None,
                1 if data.get('confirmation_sent_to_doctor') == '1' else 0,
                data.get('confirmation_sent_to_doctor_date') or None,
                1 if data.get('in_calendar') == '1' else 0,
                1 if data.get('retainer_required') == '1' else 0,
                data.get('retainer_requested_on') or None,
                data.get('retainer_received_on') or None,
                float(data.get('retainer_amount')) if data.get('retainer_amount') else None,
                data.get('atp_requested_on') or None,
                data.get('atp_received_on') or None,
                1 if data.get('loi_received') == '1' else 0,
                data.get('loi_received_date') or None
            ))

            # Link any saved forms (reports with case_id but no appointment_id) to this appointment
            # Look for reports with this case_id that don't have an appointment_id yet
            conn = get_db()
            unlinked_reports = conn.execute("""
                SELECT id FROM reports 
                WHERE case_id = ? AND appointment_id IS NULL
            """, (int(case_id),)).fetchall()
            
            if unlinked_reports:
                for report in unlinked_reports:
                    conn.execute("""
                        UPDATE reports SET appointment_id = ? WHERE id = ?
                    """, (appointment_id, report['id']))
                conn.commit()
            
            conn.close()
            
            # Process pending uploaded files from session
            case_key = str(case_id)
            if 'pending_appointment_files' in session and case_key in session['pending_appointment_files']:
                pending_files = session['pending_appointment_files'][case_key]
                
                for file_info in pending_files:
                    try:
                        # Move file from temp to permanent location
                        temp_path = file_info['filepath']
                        if os.path.exists(temp_path):
                            perm_dir = 'uploads/appointment_letters'
                            os.makedirs(perm_dir, exist_ok=True)
                            perm_path = os.path.join(perm_dir, file_info['stored_filename'])
                            
                            # Move file
                            import shutil
                            shutil.move(temp_path, perm_path)
                            
                            # Create appointment_letters record
                            execute_db("""
                                INSERT INTO appointment_letters (
                                    appointment_id, file_name, file_path, file_size, file_type,
                                    letter_type, uploaded_by
                                ) VALUES (?, ?, ?, ?, ?, ?, ?)
                            """, (
                                appointment_id,
                                file_info['original_filename'],
                                file_info['stored_filename'],
                                file_info['file_size'],
                                file_info['file_type'],
                                file_info.get('letter_type', 'other'),
                                session.get('username', 'System')
                            ))
                    except Exception as e:
                        print(f"Error processing pending file {file_info.get('original_filename')}: {e}")
                
                # Clear pending files for this case
                del session['pending_appointment_files'][case_key]
                session.modified = True

            # Send email with details and attachments
            email_to = data.get('email_to') or None
            email_cc = data.get('email_cc') or None
            email_note = data.get('email_note') or None
            send_email_fn = globals().get('send_appointment_email')
            if callable(send_email_fn):
                ok, msg = send_email_fn(appointment_id, email_to, email_cc, email_note)
            else:
                app.logger.error('send_appointment_email helper missing; skipping email send for appointment %s', appointment_id)
                ok, msg = False, 'Email helper unavailable; appointment saved but no email sent.'
            if msg:
                category = 'success' if ok else 'error'
                flash(msg, category)
            
            flash('Appointment created successfully!', 'success')
            if form_action == 'schedule_invoice':
                flash('Invoice form opened with the same reference number.', 'info')
                return redirect(url_for('invoice_new', appointment_id=appointment_id))

            return redirect(url_for('appointment_view', appointment_id=appointment_id))
            
        except Exception as e:
            flash(f'Error creating appointment: {str(e)}', 'error')
            return redirect(request.url)
    
    # Get doctors for dropdown (cases now use search)
    conn = get_db()
    doctors = conn.execute("""
        SELECT id, name, first_name, last_name, specialty
        FROM doctors
        ORDER BY name
    """).fetchall()
    conn.close()
    
    # Load service codes from CSV
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    reference_number = generate_invoice_number()
    print(f"[appointment_new] Rendering form with reference_number={reference_number}")
    
    # If case_id provided, get case details for auto-population
    case_data = None
    if case_id:
        conn = get_db()
        case_data = conn.execute("""
            SELECT c.id, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            WHERE c.id = ?
        """, (case_id,)).fetchone()
        conn.close()

    return render_template(
        'appointment_form.html',
        appointment=None,
        doctors=doctors,
        service_codes=service_codes,
        reference_number=reference_number,
        case_id=case_id,
        case_data=case_data
    )

@app.route('/appointments/<int:appointment_id>')
@login_required
def appointment_view(appointment_id):
    """View appointment details"""
    conn = get_db()
    appointment = conn.execute("""
        SELECT a.*,
               c.file_case_id,
               c.record_id,
               cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name,
               cl.full_name AS claimant_full_name,
               cli.company_name AS client_company_name,
               a.doctor_name AS doctor_full_name
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        WHERE a.id = ?
    """, (appointment_id,)).fetchone()
    
    if not appointment:
        flash('Appointment not found', 'error')
        conn.close()
        return redirect(url_for('appointments_list'))
    
    # Convert appointment to dict so templates can safely use .get()
    appointment = dict(appointment)
    
    # Get attached letters
    letters = conn.execute("""
        SELECT * FROM appointment_letters WHERE appointment_id = ?
        ORDER BY uploaded_at DESC
    """, (appointment_id,)).fetchall()
    letters = [dict(letter) for letter in letters]
    
    # Get confirmation letters (from case_reports table)
    confirmation_letters = conn.execute("""
        SELECT * FROM case_reports
        WHERE appointment_id = ? AND report_type = 'Confirmation Letter'
        ORDER BY report_date DESC, uploaded_at DESC
    """, (appointment_id,)).fetchall()
    confirmation_letters = [dict(cl) for cl in confirmation_letters]
    
    # Get additional services (if table exists)
    additional_services = []
    try:
        additional_services = conn.execute("""
            SELECT * FROM appointment_additional_services
            WHERE appointment_id = ?
            ORDER BY service_type, id
        """, (appointment_id,)).fetchall()
        additional_services = [dict(service) for service in additional_services]
    except Exception:
        # Table might not exist or have different structure
        pass
    
    # Get meds documentation (if table exists)
    meds = []
    try:
        meds = conn.execute("""
            SELECT * FROM appointment_meds
            WHERE appointment_id = ?
            ORDER BY uploaded_at DESC
        """, (appointment_id,)).fetchall()
        meds = [dict(med) for med in meds]
    except Exception:
        # Table might not exist or have different structure
        pass
    
    # Get medical documentation (if table exists)
    medical_docs = []
    try:
        medical_docs = conn.execute("""
            SELECT * FROM medical_documentation
            WHERE appointment_id = ?
            ORDER BY uploaded_at DESC
        """, (appointment_id,)).fetchall()
        medical_docs = [dict(doc) for doc in medical_docs]
    except Exception:
        # Table might not exist or have different structure
        pass
    
    conn.close()
    
    return render_template('appointment_view.html', 
                         appointment=appointment, 
                         letters=letters,
                         confirmation_letters=confirmation_letters,
                         additional_services=additional_services,
                         meds=meds,
                         medical_docs=medical_docs)

@app.route('/appointments/<int:appointment_id>/edit', methods=['GET', 'POST'])
@login_required
def appointment_edit(appointment_id):
    """Edit appointment"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        form_action = request.form.get('form_action', 'schedule')

        if not data.get('case_id'):
            flash('Please select a case before updating the appointment.', 'error')
            return redirect(request.url)
        
        try:
            case_id_int = int(data.get('case_id'))
            invoiced_flag = 1 if data.get('invoiced') == 'on' or form_action == 'schedule_invoice' else 0

            execute_db("""
                UPDATE appointments SET
                    case_id = ?, appointment_date = ?, appointment_time = ?,
                    doctor_name = ?, specialist_number = ?, location = ?,
                    service_code = ?, assessment_type = ?, description = ?,
                    date_confirmed = ?, date_received = ?, date_released = ?,
                    reference = ?, notes = ?, status = ?, invoiced = ?,
                    timezone = ?, provider_specialty = ?, clinic_location = ?,
                    pil_instructions_received = ?, pil_instruction_type = ?,
                    report_deadline_date = ?, report_due_date = ?,
                    confirmation_deposit_letter_sent = ?, confirmation_deposit_letter_date = ?,
                    confirmation_sent_to_doctor = ?, confirmation_sent_to_doctor_date = ?,
                    in_calendar = ?, retainer_required = ?, retainer_requested_on = ?,
                    retainer_received_on = ?, retainer_amount = ?,
                    atp_requested_on = ?, atp_received_on = ?,
                    loi_received = ?, loi_received_date = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            """, (
                case_id_int,
                data.get('appointment_date') or None,
                data.get('appointment_time') or None,
                data.get('doctor_name') or None,
                data.get('specialist_number') or None,
                data.get('location') or None,
                data.get('service_code') or None,
                data.get('assessment_type') or None,
                data.get('description') or None,
                data.get('date_confirmed') or None,
                data.get('date_received') or None,
                data.get('date_released') or None,
                data.get('reference') or None,
                data.get('notes') or None,
                data.get('status', 'scheduled'),
                invoiced_flag,
                data.get('timezone') or 'America/Edmonton',
                data.get('provider_specialty') or None,
                data.get('clinic_location') or None,
                1 if data.get('pil_instructions_received') == '1' else 0,
                data.get('pil_instruction_type') or None,
                data.get('report_deadline_date') or None,
                data.get('report_due_date') or None,
                1 if data.get('confirmation_deposit_letter_sent') == '1' else 0,
                data.get('confirmation_deposit_letter_date') or None,
                1 if data.get('confirmation_sent_to_doctor') == '1' else 0,
                data.get('confirmation_sent_to_doctor_date') or None,
                1 if data.get('in_calendar') == '1' else 0,
                1 if data.get('retainer_required') == '1' else 0,
                data.get('retainer_requested_on') or None,
                data.get('retainer_received_on') or None,
                float(data.get('retainer_amount')) if data.get('retainer_amount') else None,
                data.get('atp_requested_on') or None,
                data.get('atp_received_on') or None,
                1 if data.get('loi_received') == '1' else 0,
                data.get('loi_received_date') or None,
                appointment_id
            ))
            
            conn.close()
            flash('Appointment updated successfully!', 'success')
            if form_action == 'schedule_invoice':
                flash('Invoice form opened with the same reference number.', 'info')
                return redirect(url_for('invoice_new', appointment_id=appointment_id))

            return redirect(url_for('appointment_view', appointment_id=appointment_id))
            
        except Exception as e:
            flash(f'Error updating appointment: {str(e)}', 'error')
            conn.close()
            return redirect(request.url)
    
    appointment = conn.execute("""
        SELECT * FROM appointments WHERE id = ?
    """, (appointment_id,)).fetchone()
    
    if not appointment:
        flash('Appointment not found', 'error')
        conn.close()
        return redirect(url_for('appointments_list'))
    
    # Load case data for display (file_case_id, claimant name)
    case_data = None
    if appointment['case_id']:
        case_data = conn.execute(
            """
            SELECT c.id, c.file_case_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cli.company_name as client_company_name
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            WHERE c.id = ?
            """,
            (appointment['case_id'],)
        ).fetchone()
    
    # Get doctors for dropdown (cases now use search)
    doctors = conn.execute("""
        SELECT id, name, first_name, last_name, specialty
        FROM doctors
        ORDER BY name
    """).fetchall()
    conn.close()
    
    # Load service codes from CSV
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    reference_number = appointment['reference'] or generate_invoice_number()
    print(f"[appointment_edit] Rendering form for appointment {appointment_id} with reference_number={reference_number}")

    return render_template(
        'appointment_form.html',
        appointment=appointment,
        doctors=doctors,
        service_codes=service_codes,
        reference_number=reference_number,
        case_id=appointment['case_id'],
        case_data=case_data
    )

@app.route('/appointments/<int:appointment_id>/delete', methods=['POST'])
@login_required
def appointment_delete(appointment_id):
    """Delete appointment"""
    try:
        conn = get_db()
        
        # Delete associated letters first
        letters = conn.execute("""
            SELECT file_path FROM appointment_letters WHERE appointment_id = ?
        """, (appointment_id,)).fetchall()
        
        for letter in letters:
            filepath = os.path.join('uploads/appointment_letters', letter['file_path'])
            if os.path.exists(filepath):
                os.remove(filepath)
        
        execute_db('DELETE FROM appointment_letters WHERE appointment_id = ?', (appointment_id,))
        execute_db('DELETE FROM appointments WHERE id = ?', (appointment_id,))
        
        conn.close()
        flash('Appointment deleted successfully', 'success')
    except Exception as e:
        flash(f'Error deleting appointment: {str(e)}', 'error')
    
    return redirect(url_for('appointments_list'))

@app.route('/appointments/<int:appointment_id>/upload_letter', methods=['POST'])
@login_required
def appointment_upload_letter(appointment_id):
    """Upload letter to appointment"""
    # appointment_id comes from URL parameter, not form
    
    if 'letter_file' not in request.files:
        flash('No file selected', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    file = request.files['letter_file']
    
    if file.filename == '':
        flash('No file selected', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    if not allowed_file(file.filename):
        flash('Invalid file type', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    try:
        original_filename = secure_filename(file.filename)
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{timestamp}_{original_filename}"
        
        filepath = os.path.join('uploads/appointment_letters', filename)
        file.save(filepath)
        
        file_size = os.path.getsize(filepath)
        file_extension = original_filename.rsplit('.', 1)[1].lower()
        
        data = request.form.to_dict()
        
        execute_db("""
            INSERT INTO appointment_letters (
                appointment_id, file_name, file_path, file_size, file_type,
                letter_type, description, uploaded_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            appointment_id,
            original_filename,
            filename,
            file_size,
            file_extension,
            data.get('letter_type') or 'other',
            data.get('description') or None,
            session.get('username', 'System')
        ))
        
        flash('Letter uploaded successfully!', 'success')
    except Exception as e:
        flash(f'Error uploading letter: {str(e)}', 'error')
        if os.path.exists(filepath):
            os.remove(filepath)
    
    return redirect(url_for('appointment_view', appointment_id=appointment_id))

@app.route('/appointments/letters/<int:letter_id>/download')
@login_required
def appointment_letter_download(letter_id):
    """Download appointment letter"""
    conn = get_db()
    letter = conn.execute("""
        SELECT file_path, file_name FROM appointment_letters WHERE id = ?
    """, (letter_id,)).fetchone()
    conn.close()
    
    if not letter:
        abort(404)
    
    return send_from_directory(
        'uploads/appointment_letters',
        letter['file_path'],
        as_attachment=True,
        download_name=letter['file_name']
    )

@app.route('/appointments/letters/<int:letter_id>/delete', methods=['POST'])
@login_required
def appointment_letter_delete(letter_id):
    """Delete appointment letter"""
    try:
        conn = get_db()
        letter = conn.execute("""
            SELECT file_path, appointment_id FROM appointment_letters WHERE id = ?
        """, (letter_id,)).fetchone()
        
        if letter:
            filepath = os.path.join('uploads/appointment_letters', letter['file_path'])
            if os.path.exists(filepath):
                os.remove(filepath)
            
            execute_db('DELETE FROM appointment_letters WHERE id = ?', (letter_id,))
            appointment_id = letter['appointment_id']
            flash('Letter deleted successfully', 'success')
        else:
            flash('Letter not found', 'error')
            appointment_id = None
        
        conn.close()
        
        if appointment_id:
            return redirect(url_for('appointment_view', appointment_id=appointment_id))
    except Exception as e:
        flash(f'Error deleting letter: {str(e)}', 'error')
    
    return redirect(url_for('appointments_list'))

# ============================================================================
# Phase 3: Appointment Additional Services Routes
# ============================================================================

@app.route('/appointments/<int:appointment_id>/additional_services', methods=['GET', 'POST'])
@login_required
def appointment_additional_services(appointment_id):
    """Manage additional services for an appointment"""
    conn = get_db()
    
    # Verify appointment exists
    appointment = conn.execute("SELECT * FROM appointments WHERE id = ?", (appointment_id,)).fetchone()
    if not appointment:
        flash('Appointment not found', 'error')
        conn.close()
        return redirect(url_for('appointments_list'))
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            execute_db("""
                INSERT INTO appointment_additional_services (
                    appointment_id, service_type, present, name, company, language, travel_details
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                appointment_id,
                data.get('service_type'),
                1 if data.get('present') == '1' else 0,
                data.get('name') or None,
                data.get('company') or None,
                data.get('language') or None,
                data.get('travel_details') or None
            ))
            
            conn.close()
            flash('Additional service added successfully!', 'success')
            return redirect(url_for('appointment_additional_services', appointment_id=appointment_id))
        except Exception as e:
            flash(f'Error adding service: {str(e)}', 'error')
            conn.close()
    
    # Get all services for this appointment
    services = conn.execute("""
        SELECT * FROM appointment_additional_services
        WHERE appointment_id = ?
        ORDER BY service_type, id
    """, (appointment_id,)).fetchall()
    services = [dict(service) for service in services]
    
    conn.close()
    
    return render_template('appointment_additional_services.html',
                         appointment=dict(appointment),
                         services=services)

@app.route('/appointments/additional_services/<int:service_id>/edit', methods=['GET', 'POST'])
@login_required
def appointment_additional_service_edit(service_id):
    """Edit an additional service"""
    conn = get_db()
    
    service = conn.execute("""
        SELECT aas.*, a.id as appointment_id
        FROM appointment_additional_services aas
        JOIN appointments a ON aas.appointment_id = a.id
        WHERE aas.id = ?
    """, (service_id,)).fetchone()
    
    if not service:
        flash('Service not found', 'error')
        conn.close()
        return redirect(url_for('appointments_list'))
    
    appointment_id = service['appointment_id']
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            execute_db("""
                UPDATE appointment_additional_services SET
                    service_type = ?, present = ?, name = ?, company = ?,
                    language = ?, travel_details = ?
                WHERE id = ?
            """, (
                data.get('service_type'),
                1 if data.get('present') == '1' else 0,
                data.get('name') or None,
                data.get('company') or None,
                data.get('language') or None,
                data.get('travel_details') or None,
                service_id
            ))
            
            conn.close()
            flash('Service updated successfully!', 'success')
            return redirect(url_for('appointment_additional_services', appointment_id=appointment_id))
        except Exception as e:
            flash(f'Error updating service: {str(e)}', 'error')
            conn.close()
    
    conn.close()
    return render_template('appointment_additional_service_form.html', service=dict(service))

@app.route('/appointments/additional_services/<int:service_id>/delete', methods=['POST'])
@login_required
def appointment_additional_service_delete(service_id):
    """Delete an additional service"""
    conn = get_db()
    service = conn.execute("""
        SELECT appointment_id FROM appointment_additional_services WHERE id = ?
    """, (service_id,)).fetchone()
    
    if not service:
        flash('Service not found', 'error')
        conn.close()
        return redirect(url_for('appointments_list'))
    
    appointment_id = service['appointment_id']
    
    try:
        execute_db("DELETE FROM appointment_additional_services WHERE id = ?", (service_id,))
        flash('Service deleted successfully!', 'success')
    except Exception as e:
        flash(f'Error deleting service: {str(e)}', 'error')
    
    conn.close()
    return redirect(url_for('appointment_additional_services', appointment_id=appointment_id))

# ============================================================================
# Phase 3: Appointment Meds Routes
# ============================================================================

@app.route('/appointments/<int:appointment_id>/meds/upload', methods=['POST'])
@login_required
def appointment_meds_upload(appointment_id):
    """Upload meds documentation for an appointment"""
    if 'meds_file' not in request.files:
        flash('No file selected', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    file = request.files['meds_file']
    
    if file.filename == '':
        flash('No file selected', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    if not allowed_file(file.filename):
        flash('Invalid file type. Allowed: PDF, DOC, DOCX, TXT, JPG, PNG', 'error')
        return redirect(url_for('appointment_view', appointment_id=appointment_id))
    
    try:
        original_filename = secure_filename(file.filename)
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{timestamp}_{original_filename}"
        
        upload_dir = os.path.join('uploads', 'appointment_meds')
        os.makedirs(upload_dir, exist_ok=True)
        filepath = os.path.join(upload_dir, filename)
        file.save(filepath)
        
        file_size = os.path.getsize(filepath)
        file_extension = original_filename.rsplit('.', 1)[1].lower() if '.' in original_filename else ''
        
        data = request.form.to_dict()
        
        # Use description column (as per schema) but handle both notes and description
        try:
            execute_db("""
                INSERT INTO appointment_meds (
                    appointment_id, file_name, file_path, file_size, file_type,
                    description, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                appointment_id,
                original_filename,
                filename,
                file_size,
                file_extension,
                data.get('notes') or None,
                session.get('username', 'System')
            ))
        except Exception:
            # Fallback to notes if description column doesn't exist
            execute_db("""
                INSERT INTO appointment_meds (
                    appointment_id, file_name, file_path, file_size, file_type,
                    notes, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                appointment_id,
                original_filename,
                filename,
                file_size,
                file_extension,
                data.get('notes') or None,
                session.get('username', 'System')
            ))
        
        flash('Meds documentation uploaded successfully!', 'success')
    except Exception as e:
        flash(f'Error uploading meds: {str(e)}', 'error')
        if 'filepath' in locals() and os.path.exists(filepath):
            os.remove(filepath)
    
    return redirect(url_for('appointment_view', appointment_id=appointment_id))

@app.route('/appointments/meds/<int:med_id>/download')
@login_required
def appointment_meds_download(med_id):
    """Download meds documentation"""
    conn = get_db()
    med = conn.execute("""
        SELECT file_path, file_name FROM appointment_meds WHERE id = ?
    """, (med_id,)).fetchone()
    conn.close()
    
    if not med:
        abort(404)
    
    return send_from_directory(
        'uploads/appointment_meds',
        med['file_path'],
        as_attachment=True,
        download_name=med['file_name']
    )

@app.route('/appointments/meds/<int:med_id>/delete', methods=['POST'])
@login_required
def appointment_meds_delete(med_id):
    """Delete meds documentation"""
    try:
        conn = get_db()
        med = conn.execute("""
            SELECT file_path, appointment_id FROM appointment_meds WHERE id = ?
        """, (med_id,)).fetchone()
        
        if med:
            filepath = os.path.join('uploads/appointment_meds', med['file_path'])
            if os.path.exists(filepath):
                os.remove(filepath)
            
            execute_db('DELETE FROM appointment_meds WHERE id = ?', (med_id,))
            appointment_id = med['appointment_id']
            flash('Meds documentation deleted successfully', 'success')
        else:
            flash('Meds documentation not found', 'error')
            appointment_id = None
        
        conn.close()
        
        if appointment_id:
            return redirect(url_for('appointment_view', appointment_id=appointment_id))
    except Exception as e:
        flash(f'Error deleting meds: {str(e)}', 'error')
    
    return redirect(url_for('appointments_list'))

@app.route('/appointments/<int:appointment_id>/print')
@login_required
def appointment_print(appointment_id):
    """Print-friendly view of appointment"""
    from datetime import datetime
    conn = get_db()
    appointment = conn.execute("""
        SELECT a.*,
               c.file_case_id,
               c.record_id,
               cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name,
               cl.full_name AS claimant_full_name,
               cli.company_name AS client_company_name,
               a.doctor_name AS doctor_full_name
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        WHERE a.id = ?
    """, (appointment_id,)).fetchone()
    conn.close()
    
    if not appointment:
        flash('Appointment not found', 'error')
        return redirect(url_for('appointments_list'))
    
    # Get attached letters
    conn = get_db()
    letters = conn.execute("""
        SELECT * FROM appointment_letters WHERE appointment_id = ?
        ORDER BY uploaded_at DESC
    """, (appointment_id,)).fetchall()
    conn.close()
    
    return render_template('appointment_print.html', appointment=appointment, letters=letters, now=datetime.now)

@app.route('/api/search/cases')
@login_required
def search_cases():
    """Search cases by case number or claimant name"""
    query = request.args.get('q', '').strip()
    
    if not query or len(query) < 2:
        return jsonify([])
    
    conn = get_db()
    
    # Search by case number or claimant name
    results = conn.execute("""
        SELECT 
            c.id,
            c.file_case_id,
            c.client_file_number,
            cl.first_name AS claimant_first_name,
            cl.last_name AS claimant_last_name,
            cl.date_of_birth AS claimant_dob,
            cli.company_name AS client_company_name,
            c.file_open_date
        FROM cases c
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        WHERE 
            c.file_case_id LIKE ? OR
            cl.first_name LIKE ? OR
            cl.last_name LIKE ? OR
            (cl.first_name || ' ' || cl.last_name) LIKE ?
        ORDER BY c.file_case_id DESC
        LIMIT 20
    """, (f'%{query}%', f'%{query}%', f'%{query}%', f'%{query}%')).fetchall()
    
    conn.close()
    
    # Format results
    cases = []
    for row in results:
        cases.append({
            'id': row['id'],
            'file_case_id': row['file_case_id'],
            'client_file_number': row['client_file_number'],
            'claimant_name': f"{row['claimant_first_name'] or ''} {row['claimant_last_name'] or ''}".strip(),
            'claimant_dob': row['claimant_dob'],
            'client_company': row['client_company_name'],
            'date_opened': row['file_open_date']
        })
    
    return jsonify(cases)

@app.route('/api/cases/<int:case_id>/appointments')
@login_required
def api_case_appointments(case_id):
    """Get all appointments for a specific case"""
    conn = get_db()
    
    appointments = conn.execute("""
        SELECT id, appointment_date, appointment_time, doctor_name, provider_specialty
        FROM appointments
        WHERE case_id = ?
        ORDER BY appointment_date DESC, appointment_time DESC
    """, (case_id,)).fetchall()
    
    conn.close()
    
    results = []
    for apt in appointments:
        date_str = apt['appointment_date'] or ''
        time_str = f" {apt['appointment_time']}" if apt['appointment_time'] else ''
        doctor_str = f" - {apt['doctor_name']}" if apt['doctor_name'] else ''
        results.append({
            'id': apt['id'],
            'appointment_date': date_str,
            'display': f"{date_str}{time_str}{doctor_str}"
        })
    
    return jsonify(results)

@app.route('/api/cases/<int:case_id>/invoices')
@login_required
def api_case_invoices(case_id):
    """Get all invoices for a specific case"""
    conn = get_db()
    
    invoices = conn.execute("""
        SELECT id, invoice_number, invoice_date, total_amount, status
        FROM invoices
        WHERE case_id = ?
        ORDER BY invoice_date DESC, invoice_number DESC
    """, (case_id,)).fetchall()
    
    conn.close()
    
    results = []
    for inv in invoices:
        date_str = inv['invoice_date'] or ''
        amount_str = f" - ${inv['total_amount']:.2f}" if inv['total_amount'] else ''
        status_str = f" ({inv['status']})" if inv['status'] else ''
        results.append({
            'id': inv['id'],
            'invoice_number': inv['invoice_number'],
            'display': f"{inv['invoice_number']}{amount_str}{status_str}"
        })
    
    return jsonify(results)

# ============================================================================
# Reports Routes
# ============================================================================

@app.route('/reports')
@login_required
def reports_list():
    """List all reports with search and filter"""
    search = request.args.get('search', '')
    status_filter = request.args.get('status', '')
    report_type_filter = request.args.get('report_type', '')
    
    conn = get_db()
    
    # Build query
    query = "SELECT * FROM v_reports_full WHERE 1=1"
    params = []
    
    if search:
        query += """ AND (
            report_title LIKE ? OR
            file_case_id LIKE ? OR
            claimant_first_name LIKE ? OR
            claimant_last_name LIKE ? OR
            client_company_name LIKE ? OR
            lawyer_firm_name LIKE ? OR
            doctor_name LIKE ? OR
            description LIKE ?
        )"""
        search_term = f'%{search}%'
        params.extend([search_term] * 8)
    
    if status_filter:
        query += " AND status = ?"
        params.append(status_filter)
    
    if report_type_filter:
        query += " AND report_type = ?"
        params.append(report_type_filter)
    
    query += " ORDER BY uploaded_at DESC"
    
    reports = conn.execute(query, params).fetchall()
    
    # Get unique report types for filter
    report_types = conn.execute("""
        SELECT DISTINCT report_type FROM reports 
        WHERE report_type IS NOT NULL AND report_type != ''
        ORDER BY report_type
    """).fetchall()
    
    conn.close()
    
    return render_template('reports_list.html', 
                         reports=reports,
                         search=search,
                         status_filter=status_filter,
                         report_type_filter=report_type_filter,
                         report_types=report_types)

@app.route('/reports/new', methods=['GET', 'POST'])
@login_required
def report_new():
    """Upload new report"""
    if request.method == 'POST':
        # Check if file was uploaded
        if 'report_file' not in request.files:
            flash('No file selected', 'error')
            return redirect(request.url)
        
        file = request.files['report_file']
        
        if file.filename == '':
            flash('No file selected', 'error')
            return redirect(request.url)
        
        if not allowed_file(file.filename):
            flash('Invalid file type. Allowed: PDF, DOC, DOCX, TXT, JPG, PNG', 'error')
            return redirect(request.url)
        
        try:
            # Secure the filename
            original_filename = secure_filename(file.filename)
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"{timestamp}_{original_filename}"
            
            # Save file
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(filepath)
            
            # Get file size
            file_size = os.path.getsize(filepath)
            file_extension = original_filename.rsplit('.', 1)[1].lower()
            
            # Get form data
            data = request.form.to_dict()
            
            conn = get_db()
            
            # Insert report record
            report_id = execute_db("""
                INSERT INTO reports (
                    report_title, report_type, report_date, file_name,
                    file_path, file_size, file_type, case_id, doctor_id,
                    status, description, notes, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                data.get('report_title'),
                data.get('report_type') or None,
                data.get('report_date') or None,
                original_filename,
                filename,
                file_size,
                file_extension,
                data.get('case_id') or None,
                data.get('doctor_id') or None,
                data.get('status', 'draft'),
                data.get('description') or None,
                data.get('notes') or None,
                session.get('username', 'System')
            ))
            
            conn.close()
            
            flash('Report uploaded successfully!', 'success')
            return redirect(url_for('report_view', report_id=report_id))
            
        except Exception as e:
            flash(f'Error uploading report: {str(e)}', 'error')
            # Clean up file if database insert failed
            if os.path.exists(filepath):
                os.remove(filepath)
    
    # Get cases and doctors for dropdowns
    conn = get_db()
    cases = conn.execute("""
        SELECT c.id, c.file_case_id, cl.first_name, cl.last_name
        FROM cases c
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        ORDER BY c.file_case_id DESC
        LIMIT 100
    """).fetchall()
    
    doctors = conn.execute("""
        SELECT id, name, first_name, last_name, specialty
        FROM doctors
        ORDER BY name
    """).fetchall()
    conn.close()
    
    return render_template('report_form.html', report=None, cases=cases, doctors=doctors)

@app.route('/cases/<int:case_id>/report/upload', methods=['POST'])
@login_required
def case_report_upload(case_id):
    """Upload report for a specific case"""
    # Verify case exists
    conn = get_db()
    case = conn.execute('SELECT id FROM cases WHERE id = ?', (case_id,)).fetchone()
    if not case:
        conn.close()
        flash('Case not found', 'error')
        return redirect(url_for('cases_list'))
    
    # Check if file was uploaded
    if 'report_file' not in request.files:
        conn.close()
        flash('No file selected', 'error')
        return redirect(url_for('case_view', case_id=case_id))
    
    file = request.files['report_file']
    
    if file.filename == '':
        conn.close()
        flash('No file selected', 'error')
        return redirect(url_for('case_view', case_id=case_id))
    
    if not allowed_file(file.filename):
        conn.close()
        flash('Invalid file type. Allowed: PDF, DOC, DOCX, TXT, JPG, PNG', 'error')
        return redirect(url_for('case_view', case_id=case_id))
    
    try:
        # Secure the filename
        original_filename = secure_filename(file.filename)
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{timestamp}_{original_filename}"
        
        # Save file
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
        file.save(filepath)
        
        # Get file size
        file_size = os.path.getsize(filepath)
        file_extension = original_filename.rsplit('.', 1)[1].lower()
        
        # Get form data
        data = request.form.to_dict()
        
        # Insert report record
        report_id = execute_db("""
            INSERT INTO reports (
                report_title, report_type, report_date, file_name,
                file_path, file_size, file_type, case_id, doctor_id,
                status, description, notes, uploaded_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            data.get('report_title') or original_filename,
            data.get('report_type') or None,
            data.get('report_date') or None,
            original_filename,
            filename,
            file_size,
            file_extension,
            case_id,
            data.get('doctor_id') or None,
            data.get('status', 'draft'),
            data.get('description') or None,
            data.get('notes') or None,
            session.get('username', 'System')
        ))
        
        conn.close()
        
        flash('Report uploaded successfully!', 'success')
        return redirect(url_for('case_view', case_id=case_id) + '#appointments-section')
        
    except Exception as e:
        import traceback
        import sys
        traceback.print_exc(file=sys.stderr)
        conn.close()
        flash('An error occurred while uploading the report. Please try again.', 'error')
        # Clean up file if database insert failed
        if 'filepath' in locals() and os.path.exists(filepath):
            os.remove(filepath)
        return redirect(url_for('case_view', case_id=case_id))

@app.route('/reports/<int:report_id>')
@login_required
def report_view(report_id):
    """View report details"""
    conn = get_db()
    
    report = conn.execute("""
        SELECT * FROM v_reports_full WHERE id = ?
    """, (report_id,)).fetchone()
    form_content = conn.execute("""
        SELECT form_html_content FROM reports WHERE id = ?
    """, (report_id,)).fetchone()
    
    conn.close()
    
    if not report:
        flash('Report not found', 'error')
        return redirect(url_for('reports_list'))

    report = dict(report)
    if form_content:
        # Keep raw HTML for editing, but create unescaped version for preview
        raw_html = form_content['form_html_content']
        report['form_html_content'] = raw_html  # Raw for editor
        report['form_html_content_preview'] = unescape_html(raw_html) if raw_html else None  # Unescaped for preview
    else:
        report['form_html_content'] = None
        report['form_html_content_preview'] = None
    
    return render_template('report_view.html', report=report)

@app.route('/reports/<int:report_id>/edit')
@login_required
def report_edit_popup(report_id):
    """Open editable view for template-based reports"""
    conn = get_db()
    report = conn.execute("""
        SELECT r.*, c.file_case_id
        FROM reports r
        LEFT JOIN cases c ON r.case_id = c.id
        WHERE r.id = ?
    """, (report_id,)).fetchone()
    conn.close()
    
    if not report:
        flash('Report not found', 'error')
        return redirect(url_for('reports_list'))
    
    report = dict(report)
    if not report.get('form_html_content'):
        flash('This report does not have editable template content.', 'error')
        return redirect(url_for('report_view', report_id=report_id))
    
    report['form_template'] = report.get('form_template') or report.get('file_name') or 'report.html'
    return render_template('report_editor.html', report=report)

@app.route('/reports/<int:report_id>/download')
@login_required
def report_download(report_id):
    """Download report file"""
    conn = get_db()
    report = conn.execute("""
        SELECT file_path, file_name, file_type, form_html_content
        FROM reports WHERE id = ?
    """, (report_id,)).fetchone()
    conn.close()
    
    if not report:
        abort(404)
    
    # For template-based reports, stream fresh HTML to avoid exposing raw tags
    if report['file_type'] == 'html' and report['form_html_content']:
        download_name = report['file_name'] or f'report_{report_id}.html'
        # Get the raw content and ensure it's properly unescaped
        raw_content = report['form_html_content']
        # Unescape HTML entities (handles nested escaping automatically)
        content = unescape_html(raw_content)
        rendered_html = wrap_html_document(content)
        response = Response(rendered_html, mimetype='text/html; charset=utf-8')
        response.headers['Content-Disposition'] = f'attachment; filename=\"{download_name}\"'
        return response
    
    full_path = resolve_report_full_path(report['file_path'])
    if not full_path or not os.path.exists(full_path):
        abort(404)
    
    return send_file(
        full_path,
        as_attachment=True,
        download_name=report['file_name']
    )

@app.route('/reports/<int:report_id>/delete', methods=['POST'])
@login_required
def report_delete(report_id):
    """Delete report"""
    try:
        conn = get_db()
        report = conn.execute("""
            SELECT file_path FROM reports WHERE id = ?
        """, (report_id,)).fetchone()
        
        if report:
            # Delete file
            filepath = resolve_report_full_path(report['file_path'])
            if filepath and os.path.exists(filepath):
                os.remove(filepath)
            
            # Delete database record
            execute_db('DELETE FROM reports WHERE id = ?', (report_id,))
            flash('Report deleted successfully', 'success')
        else:
            flash('Report not found', 'error')
        
        conn.close()
    except Exception as e:
        flash(f'Error deleting report: {str(e)}', 'error')
    
    return redirect(url_for('reports_list'))

@app.route('/reports/<int:report_id>/print')
@login_required
def report_print(report_id):
    """Print-friendly view of report metadata"""
    conn = get_db()
    
    report = conn.execute("""
        SELECT * FROM v_reports_full WHERE id = ?
    """, (report_id,)).fetchone()
    
    conn.close()
    
    if not report:
        flash('Report not found', 'error')
        return redirect(url_for('reports_list'))
    
    from datetime import datetime
    return render_template('report_print.html', report=report, now=datetime.now)

# ============================================================================
# Invoices Routes
# ============================================================================

def generate_invoice_number():
    """Generate unique invoice/reference number shared by invoices and appointments"""
    conn = get_db()
    current_year = datetime.now().year
    pattern = f'INV-{current_year}-%'
    
    def extract_suffix(value):
        try:
            return int(value.split('-')[-1])
        except Exception:
            return 0
    
    last_invoice = conn.execute("""
        SELECT invoice_number FROM invoices 
        WHERE invoice_number LIKE ? 
        ORDER BY invoice_number DESC 
        LIMIT 1
    """, (pattern,)).fetchone()
    
    last_reference = conn.execute("""
        SELECT reference FROM appointments
        WHERE reference LIKE ?
        ORDER BY reference DESC
        LIMIT 1
    """, (pattern,)).fetchone()
    
    conn.close()
    
    last_num = 0
    if last_invoice and last_invoice['invoice_number']:
        last_num = max(last_num, extract_suffix(last_invoice['invoice_number']))
    if last_reference and last_reference['reference']:
        last_num = max(last_num, extract_suffix(last_reference['reference']))
    
    new_num = last_num + 1 if last_num else 1
    return f'INV-{current_year}-{new_num:04d}'

@app.route('/api/invoices/next_number')
@login_required
def api_next_invoice_number():
    """Return the next invoice number without creating an invoice"""
    try:
        return jsonify({ 'invoice_number': generate_invoice_number() })
    except Exception as e:
        return jsonify({ 'error': str(e) }), 500

# ============================================================================
# Forms API Routes
# ============================================================================

@app.route('/api/forms/list')
@login_required
def api_forms_list():
    """List all available form templates"""
    import os
    forms_dir = 'forms/templates_bundle_cursor_pretty'
    forms = []
    
    if os.path.exists(forms_dir):
        for filename in sorted(os.listdir(forms_dir)):
            if filename.endswith('.html'):
                # Create a readable name from filename
                name = filename.replace('.html', '').replace('_', ' ').title()
                forms.append({
                    'filename': filename,
                    'name': name
                })
    
    return jsonify({ 'forms': forms })

@app.route('/api/reports/check')
@login_required
def api_reports_check():
    """Check if a report exists for a given appointment and form"""
    appointment_id = request.args.get('appointment_id', type=int)
    form_name = request.args.get('form_name', '')
    
    if not appointment_id or not form_name:
        return jsonify({ 'report_id': None })
    
    conn = get_db()
    report = conn.execute("""
        SELECT id FROM reports 
        WHERE appointment_id = ? AND form_template = ?
        ORDER BY id DESC
        LIMIT 1
    """, (appointment_id, form_name)).fetchone()
    conn.close()
    
    if report:
        return jsonify({ 'report_id': report['id'] })
    else:
        return jsonify({ 'report_id': None })

@app.route('/api/forms/<path:form_name>/preview', methods=['GET', 'POST'])
@login_required
def api_form_preview(form_name):
    """Get form template with auto-populated data"""
    import os
    appointment_id = request.args.get('appointment_id')
    case_id = request.args.get('case_id')
    
    appointment = None
    
    if case_id and request.method == 'GET':
        # Get case and related data for existing case
        conn = get_db()
        case = conn.execute("""
            SELECT c.*, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                   cl.address as claimant_address, cl.city as claimant_city,
                   cl.province as claimant_province, cl.postal_code as claimant_postal,
                   cl.email as claimant_email, cl.home_phone as claimant_phone,
                   cli.company_name as client_company, cli.address as client_address,
                   cli.city as client_city, cli.province as client_province,
                   cli.postal_code as client_postal,
                   cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                   lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                   lf.city as lawyer_city, lf.province as lawyer_province,
                   lf.postal_code as lawyer_postal,
                   lc.first_name as lawyer_first, lc.last_name as lawyer_last
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
            LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
            LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
            WHERE c.id = ?
        """, (case_id,)).fetchone()
        conn.close()
        
        if not case:
            return jsonify({ 'error': 'Case not found' }), 404
        
        appointment = dict(case)
    elif appointment_id:
        # Get appointment and related data from database
        conn = get_db()
        appointment = conn.execute("""
            SELECT a.*, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                   cl.address as claimant_address, cl.city as claimant_city,
                   cl.province as claimant_province, cl.postal_code as claimant_postal,
                   cl.email as claimant_email, cl.home_phone as claimant_phone,
                   cli.company_name as client_company, cli.address as client_address,
                   cli.city as client_city, cli.province as client_province,
                   cli.postal_code as client_postal,
                   cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                   lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                   lf.city as lawyer_city, lf.province as lawyer_province,
                   lf.postal_code as lawyer_postal,
                   lc.first_name as lawyer_first, lc.last_name as lawyer_last,
                   d.first_name as doctor_first, d.last_name as doctor_last,
                   d.specialty as doctor_specialty
            FROM appointments a
            LEFT JOIN cases c ON a.case_id = c.id
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
            LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
            LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
            LEFT JOIN doctors d ON a.doctor_name = d.name OR a.doctor_name = (d.first_name || ' ' || d.last_name)
            WHERE a.id = ?
        """, (appointment_id,)).fetchone()
        conn.close()
        
        if not appointment:
            return jsonify({ 'error': 'Appointment not found' }), 404
        appointment = dict(appointment)
    elif request.method == 'POST':
        # Get form data directly from request
        form_data = request.json
        
        # Check if this is case form data (for new cases not yet saved)
        if form_data.get('case_form_data'):
            # Build appointment-like dict directly from case form data
            appointment = {
                'file_case_id': form_data.get('file_case_id') or '',
                'record_id': form_data.get('record_id') or '',
                'claimant_first_name': form_data.get('claimant_first_name') or '',
                'claimant_last_name': form_data.get('claimant_last_name') or '',
                'claimant_title': form_data.get('claimant_title') or '',
                'claimant_dob': form_data.get('claimant_date_of_birth') or '',
                'claimant_address': form_data.get('claimant_address') or '',
                'claimant_city': form_data.get('claimant_city') or '',
                'claimant_province': form_data.get('claimant_province') or '',
                'claimant_postal': form_data.get('claimant_postal_code') or '',
                'claimant_email': form_data.get('claimant_email') or '',
                'claimant_phone': form_data.get('claimant_home_phone') or form_data.get('claimant_cel_phone') or '',
                'client_company': form_data.get('client_company_name') or '',
                'client_address': form_data.get('client_address') or '',
                'client_city': form_data.get('client_city') or '',
                'client_province': form_data.get('client_province') or '',
                'client_postal': form_data.get('client_postal_code') or '',
                'client_contact_first': form_data.get('client_name') or '',
                'client_contact_last': '',
                'lawyer_firm': form_data.get('lawyer_firm_name') or '',
                'lawyer_address': form_data.get('lawyer_address') or '',
                'lawyer_city': form_data.get('lawyer_city') or '',
                'lawyer_province': form_data.get('lawyer_province') or '',
                'lawyer_postal': form_data.get('lawyer_postal_code') or '',
                'lawyer_first': form_data.get('lawyer_first_name') or '',
                'lawyer_last': form_data.get('lawyer_last_name') or '',
                'client_file_number': form_data.get('client_file_number') or '',
                'appointment_date': form_data.get('appointment_date') or '',
                'appointment_time': form_data.get('appointment_time') or '',
                'doctor_name': form_data.get('doctor_name') or '',
                'service_code': form_data.get('service_code') or ''
            }
            
            # Try to get doctor details if doctor_name is provided
            if form_data.get('doctor_name'):
                conn = get_db()
                doctor = conn.execute("""
                    SELECT first_name, last_name, specialty
                    FROM doctors
                    WHERE name = ? OR (first_name || ' ' || last_name) = ?
                    LIMIT 1
                """, (form_data.get('doctor_name'), form_data.get('doctor_name'))).fetchone()
                conn.close()
                
                if doctor:
                    appointment['doctor_first'] = doctor['first_name']
                    appointment['doctor_last'] = doctor['last_name']
                    appointment['doctor_specialty'] = doctor['specialty']
                else:
                    # Extract name parts if possible
                    name_parts = form_data.get('doctor_name', '').split()
                    if len(name_parts) > 0:
                        appointment['doctor_last'] = name_parts[-1]
                    if len(name_parts) > 1:
                        appointment['doctor_first'] = ' '.join(name_parts[:-1])
        else:
            # Existing logic for appointments with case_id
            case_id = form_data.get('case_id')
            
            if not case_id:
                return jsonify({ 'error': 'case_id is required for new appointments' }), 400
            
            # Get case and related data
            conn = get_db()
            case = conn.execute("""
                SELECT c.*, c.file_case_id, c.record_id,
                       cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                       cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                       cl.address as claimant_address, cl.city as claimant_city,
                       cl.province as claimant_province, cl.postal_code as claimant_postal,
                       cl.email as claimant_email, cl.home_phone as claimant_phone,
                       cli.company_name as client_company, cli.address as client_address,
                       cli.city as client_city, cli.province as client_province,
                       cli.postal_code as client_postal,
                       cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                       lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                       lf.city as lawyer_city, lf.province as lawyer_province,
                       lf.postal_code as lawyer_postal,
                       lc.first_name as lawyer_first, lc.last_name as lawyer_last
                FROM cases c
                LEFT JOIN claimants cl ON c.claimant_id = cl.id
                LEFT JOIN clients cli ON c.client_id = cli.id
                LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
                LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
                LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
                WHERE c.id = ?
            """, (case_id,)).fetchone()
            conn.close()
            
            if not case:
                return jsonify({ 'error': 'Case not found' }), 404
            
            # Build appointment-like dict from case and form data
            appointment = dict(case)
            appointment['appointment_date'] = form_data.get('appointment_date')
            appointment['appointment_time'] = form_data.get('appointment_time')
            appointment['doctor_name'] = form_data.get('doctor_name')
            appointment['service_code'] = form_data.get('service_code')
            
            # Try to get doctor details if doctor_name is provided
            if form_data.get('doctor_name'):
                conn = get_db()
                doctor = conn.execute("""
                    SELECT first_name, last_name, specialty
                    FROM doctors
                    WHERE name = ? OR (first_name || ' ' || last_name) = ?
                    LIMIT 1
                """, (form_data.get('doctor_name'), form_data.get('doctor_name'))).fetchone()
                conn.close()
                
                if doctor:
                    appointment['doctor_first'] = doctor['first_name']
                    appointment['doctor_last'] = doctor['last_name']
                    appointment['doctor_specialty'] = doctor['specialty']
                else:
                    # Extract name parts if possible
                    name_parts = form_data.get('doctor_name', '').split()
                    if len(name_parts) > 0:
                        appointment['doctor_last'] = name_parts[-1]
                    if len(name_parts) > 1:
                        appointment['doctor_first'] = ' '.join(name_parts[:-1])
    else:
        return jsonify({ 'error': 'appointment_id or form data is required' }), 400
    
    # Read form template
    form_path = os.path.join('forms/templates_bundle_cursor_pretty', form_name)
    if not os.path.exists(form_path):
        return jsonify({ 'error': 'Form template not found' }), 404
    
    with open(form_path, 'r', encoding='utf-8') as f:
        form_html = f.read()
    
    # Replace placeholders with actual data
    replacements = {
        '<clfirst>': appointment.get('client_contact_first') or '',
        '<cllast>': appointment.get('client_contact_last') or '',
        '<clcompany>': appointment.get('client_company') or '',
        '<claddress>': appointment.get('client_address') or '',
        '<clcity>': appointment.get('client_city') or '',
        '<clprovince>': appointment.get('client_province') or '',
        '<clpostal>': appointment.get('client_postal') or '',
        '<clemail>': '',
        '<cmtitle>': appointment.get('claimant_title') or '',
        '<cmfirst>': appointment.get('claimant_first_name') or '',
        '<cmlast>': appointment.get('claimant_last_name') or '',
        '<lwcompany>': appointment.get('lawyer_firm') or '',
        '<lwaddress>': appointment.get('lawyer_address') or '',
        '<lwcity>': appointment.get('lawyer_city') or '',
        '<lwprovince>': appointment.get('lawyer_province') or '',
        '<lwpostal>': appointment.get('lawyer_postal') or '',
        '<lwemail>': '',
        '<drtitle>': 'Dr.',
        '<drfirst>': appointment.get('doctor_first') or '',
        '<drlast>': appointment.get('doctor_last') or appointment.get('doctor_name') or '',
        '<ourfile>': appointment.get('file_case_id') or appointment.get('record_id') or '',
        '<lcdate>': appointment.get('appointment_date') or '',
        '<lcfee>': '',
    }
    
    # Replace all angle-bracket placeholders
    for placeholder, value in replacements.items():
        form_html = form_html.replace(placeholder, str(value))
    
    # Replace double-angle placeholders like << FILE NO >>
    import re
    def pick(*vals):
        for v in vals:
            if v:
                return str(v)
        return ''
    city_prov = ' / '.join([v for v in [appointment.get('client_city') or appointment.get('claimant_city'), appointment.get('client_province') or ''] if v]).strip(' / ')
    bracket_map = {
        'FILE NO': pick(appointment.get('file_case_id'), appointment.get('record_id')),
        'FILE NUMBER': pick(appointment.get('file_case_id'), appointment.get('record_id')),
        'CLAIM #': pick(appointment.get('client_file_number')),
        'CLAIM NUMBER': pick(appointment.get('client_file_number')),
        'DATE': pick(appointment.get('appointment_date')),
        'REPORT DUE': pick(appointment.get('date_released')),
        'DEADLINE DATE': pick(appointment.get('date_confirmed')),
        'LOI DATE': pick(appointment.get('date_received')),
        'CITY/PROV': city_prov,
    }
    def replace_double_angle(m):
        key = m.group(1).strip().upper()
        return bracket_map.get(key, m.group(0))
    form_html = re.sub(r"<<\s*([^>]+?)\s*>>", replace_double_angle, form_html)
    
    # Also replace closing tags like </clfirst>
    for placeholder in replacements.keys():
        closing_tag = placeholder.replace('<', '</')
        form_html = form_html.replace(closing_tag, '')
    
    return jsonify({ 
        'html': form_html,
        'form_name': form_name,
        'appointment_id': appointment_id or None
    })

@app.route('/api/forms/save', methods=['POST'])
@login_required
def api_form_save():
    """Save completed form as report"""
    data = request.json
    form_html = data.get('html')
    form_name = data.get('form_name') or 'report.html'
    appointment_id = data.get('appointment_id')
    case_id = data.get('case_id')  # Allow case_id directly for new appointments
    report_title = data.get('report_title', form_name.replace('.html', '').replace('_', ' ').title())
    report_id = data.get('report_id')
    
    if not form_html or not form_name:
        return jsonify({ 'error': 'Missing required fields: html and form_name' }), 400
    
    # Unescape HTML entities that may have been introduced by contenteditable
    # This ensures we store clean HTML, not escaped entities
    form_html = unescape_html(form_html)
    
    try:
        conn = get_db()
        
        if report_id:
            existing = conn.execute("""
                SELECT id, file_path, file_name, form_template
                FROM reports WHERE id = ?
            """, (report_id,)).fetchone()
            
            if not existing:
                conn.close()
                return jsonify({ 'error': 'Report not found' }), 404
            
            stored_path = existing['file_path']
            relative_path = stored_path
            if not relative_path:
                timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
                safe_name = secure_filename(form_name) or 'report.html'
                relative_path = os.path.join(app.config['UPLOAD_FOLDER'], f"{timestamp}_{safe_name}")
            
            full_path = resolve_report_full_path(relative_path)
            os.makedirs(os.path.dirname(full_path), exist_ok=True)
            # Wrap the UNESCAPED content for file download
            rendered_html = wrap_html_document(form_html)
            with open(full_path, 'w', encoding='utf-8') as f:
                f.write(rendered_html)
            file_size = os.path.getsize(full_path)
            
            # Store the UNESCAPED HTML in the database (already unescaped above)
            conn.execute("""
                UPDATE reports
                SET report_title = ?, report_date = ?, file_name = ?, file_path = ?, 
                    file_size = ?, uploaded_by = ?, form_template = ?, form_html_content = ?
                WHERE id = ?
            """, (
                report_title,
                datetime.now().date(),
                form_name,
                relative_path,
                file_size,
                session.get('username', 'System'),
                form_name,
                form_html,  # Already unescaped at the top of the function
                report_id
            ))
            
            conn.commit()
            conn.close()
            return jsonify({ 'report_id': report_id, 'report_title': report_title, 'updated': True })
        
        case_id_to_use = None
        
        # Get case_id - either from appointment or directly provided
        if appointment_id:
            appointment = conn.execute("SELECT case_id FROM appointments WHERE id = ?", (appointment_id,)).fetchone()
            if appointment:
                case_id_to_use = appointment['case_id']
            else:
                return jsonify({ 'error': 'Appointment not found' }), 404
        elif case_id:
            # Verify case exists
            case = conn.execute("SELECT id FROM cases WHERE id = ?", (case_id,)).fetchone()
            if case:
                case_id_to_use = case_id
            else:
                return jsonify({ 'error': 'Case not found' }), 404
        else:
            return jsonify({ 'error': 'Either appointment_id or case_id is required' }), 400
        
        report_id = save_form_template_report(
            case_id=case_id_to_use,
            form_name=form_name,
            html_content=form_html,
            report_title=report_title,
            uploaded_by=session.get('username', 'System'),
            appointment_id=appointment_id
        )
        
        if not report_id:
            return jsonify({ 'error': 'Unable to save report' }), 400
        
        conn.close()
        
        return jsonify({ 
            'success': True,
            'report_id': report_id,
            'message': 'Form saved successfully',
            'report_title': report_title
        })
        
    except Exception as e:
        return jsonify({ 'error': str(e) }), 500

@app.route('/invoices')
@login_required
def invoices_list():
    """List all invoices with search and filter"""
    # Initialize defaults
    invoices = []
    overdue_count_value = 0
    uninvoiced_count_value = 0
    search = request.args.get('search', '')
    status_filter = request.args.get('status', '')
    
    try:
        # Phase 4: Check for overdue invoices on page load
        check_and_update_overdue_invoices()
        
        conn = get_db()
        
        query = "SELECT * FROM v_invoices_full WHERE 1=1"
        params = []
        
        if search:
            query += """ AND (
                invoice_number LIKE ? OR
                file_case_id LIKE ? OR
                claimant_first_name LIKE ? OR
                claimant_last_name LIKE ? OR
                client_company_name LIKE ? OR
                specialist_name LIKE ?
            )"""
            search_term = f'%{search}%'
            params.extend([search_term] * 6)
        
        if status_filter:
            query += " AND status = ?"
            params.append(status_filter)
        
        query += " ORDER BY invoice_date DESC, invoice_number DESC"
        
        invoices = conn.execute(query, params).fetchall()
        
        # Convert to dicts so templates can safely use .get()
        invoices = [dict(inv) for inv in invoices]
        
        # Phase 4: Get overdue count for display
        try:
            overdue_count = conn.execute("""
                SELECT COUNT(*) as count FROM invoices 
                WHERE is_overdue = 1 AND status NOT IN ('paid', 'cancelled')
            """).fetchone()
            overdue_count_value = overdue_count['count'] if overdue_count else 0
        except Exception as e:
            print(f"Error getting overdue count: {e}", file=sys.stderr)
            overdue_count_value = 0
        
        # Phase 4: Get uninvoiced appointments (30+ days old)
        try:
            from datetime import date, timedelta
            thirty_days_ago = date.today() - timedelta(days=30)
            uninvoiced_appointments = conn.execute("""
                SELECT COUNT(*) as count FROM appointments a
                LEFT JOIN invoices i ON a.id = i.appointment_id
                WHERE i.id IS NULL 
                AND a.appointment_date < ?
                AND a.appointment_date IS NOT NULL
            """, (thirty_days_ago,)).fetchone()
            uninvoiced_count_value = uninvoiced_appointments['count'] if uninvoiced_appointments else 0
        except Exception as e:
            print(f"Error getting uninvoiced count: {e}", file=sys.stderr)
            uninvoiced_count_value = 0
        
        conn.close()
        
    except Exception as e:
        print(f"Error in invoices_list: {e}", file=sys.stderr)
        import traceback
        traceback.print_exc(file=sys.stderr)
        flash(f'Error loading invoices: {str(e)}', 'error')
    
    return render_template('invoices_list.html',
                         invoices=invoices,
                         search=search,
                         status_filter=status_filter,
                         overdue_count=overdue_count_value,
                         uninvoiced_count=uninvoiced_count_value)

@app.route('/invoices/new', methods=['GET', 'POST'])
@app.route('/invoices/new/<int:appointment_id>', methods=['GET', 'POST'])
@login_required
def invoice_new(appointment_id=None):
    """Create new invoice (optionally from appointment)"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Generate invoice number
            invoice_number = None
            # If creating from appointment, try to use appointment reference as invoice number
            if data.get('appointment_id'):
                conn_check = get_db()
                appt = conn_check.execute("SELECT reference FROM appointments WHERE id = ?", (data.get('appointment_id'),)).fetchone()
                conn_check.close()
                if appt and appt['reference'] and str(appt['reference']).startswith('INV-'):
                    invoice_number = appt['reference']
            if not invoice_number:
                invoice_number = generate_invoice_number()
            
            # Calculate tax and total
            fee = float(data.get('fee', 0))
            tax_rate = float(data.get('tax_rate', 13.0))
            tax_amount = round(fee * (tax_rate / 100), 2)
            total_amount = round(fee + tax_amount, 2)
            
            # Insert invoice
            invoice_id = execute_db("""
                INSERT INTO invoices (
                    invoice_number, invoice_date, appointment_id, case_id, specialist_id,
                    service_code, service_description, service_date,
                    fee, tax_rate, tax_amount, total_amount,
                    status, notes, internal_notes, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                invoice_number,
                data.get('invoice_date'),
                data.get('appointment_id') or None,
                data.get('case_id') or None,
                data.get('specialist_id') or None,
                data.get('service_code'),
                data.get('service_description'),
                data.get('service_date'),
                fee,
                tax_rate,
                tax_amount,
                total_amount,
                data.get('status', 'draft'),
                data.get('notes'),
                data.get('internal_notes'),
                session.get('username', 'System')
            ))
            
            conn.close()
            flash('Invoice created successfully!', 'success')
            return redirect(url_for('invoice_view', invoice_id=invoice_id))
            
        except Exception as e:
            conn.close()
            flash(f'Error creating invoice: {str(e)}', 'error')
    
    # Get appointment data if creating from appointment
    appointment = None
    if appointment_id:
        appointment = conn.execute("""
            SELECT a.*,
                   c.file_case_id,
                   c.record_id,
                   cl.first_name AS claimant_first_name,
                   cl.last_name AS claimant_last_name,
                   cl.full_name AS claimant_full_name,
                   cli.company_name AS client_company_name,
                   a.doctor_name AS doctor_full_name
            FROM appointments a
            LEFT JOIN cases c ON a.case_id = c.id
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            WHERE a.id = ?
        """, (appointment_id,)).fetchone()

        # Convert to dict so templates can safely use .get()
        if appointment:
            appointment = dict(appointment)

    # Get service codes from CSV
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    conn.close()
    
    from datetime import date
    today = date.today().strftime('%Y-%m-%d')
    
    return render_template('invoice_form.html', 
                         invoice=None, 
                         appointment=appointment,
                         line_items=[],
                         service_codes=service_codes,
                         today=today)

@app.route('/invoices/<int:invoice_id>')
@login_required
def invoice_view(invoice_id):
    """View invoice details"""
    conn = get_db()
    
    invoice = conn.execute("""
        SELECT * FROM v_invoices_full WHERE id = ?
    """, (invoice_id,)).fetchone()
    
    # Convert to dict so templates can safely use .get()
    if invoice:
        invoice = dict(invoice)
    
    conn.close()
    
    if not invoice:
        flash('Invoice not found', 'error')
        return redirect(url_for('invoices_list'))
    
    return render_template('invoice_view.html', invoice=invoice)

@app.route('/invoices/<int:invoice_id>/edit', methods=['GET', 'POST'])
@login_required
def invoice_edit(invoice_id):
    """Edit invoice"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Calculate tax and total
            fee = float(data.get('fee', 0))
            tax_rate = float(data.get('tax_rate', 13.0))
            tax_amount = round(fee * (tax_rate / 100), 2)
            total_amount = round(fee + tax_amount, 2)
            
            execute_db("""
                UPDATE invoices SET
                    invoice_date = ?, service_code = ?, service_description = ?,
                    service_date = ?, fee = ?, tax_rate = ?, tax_amount = ?,
                    total_amount = ?, status = ?, payment_date = ?,
                    payment_method = ?, payment_reference = ?,
                    notes = ?, internal_notes = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            """, (
                data.get('invoice_date'),
                data.get('service_code'),
                data.get('service_description'),
                data.get('service_date'),
                fee,
                tax_rate,
                tax_amount,
                total_amount,
                data.get('status', 'draft'),
                data.get('payment_date') or None,
                data.get('payment_method') or None,
                data.get('payment_reference') or None,
                data.get('notes'),
                data.get('internal_notes'),
                invoice_id
            ))
            
            conn.close()
            flash('Invoice updated successfully!', 'success')
            return redirect(url_for('invoice_view', invoice_id=invoice_id))
            
        except Exception as e:
            conn.close()
            flash(f'Error updating invoice: {str(e)}', 'error')
    
    invoice = conn.execute("""
        SELECT * FROM invoices WHERE id = ?
    """, (invoice_id,)).fetchone()

    # Convert to dict so templates can safely use .get()
    if invoice:
        invoice = dict(invoice)

    if not invoice:
        flash('Invoice not found', 'error')
        conn.close()
        return redirect(url_for('invoices_list'))
    
    # Get service codes
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            # Skip the first line (FILE MAKER CODES header)
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    # Note: Column name has trailing space: 'DESCRIPTION '
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
        pass
    
    conn.close()
    
    from datetime import date
    today = date.today().strftime('%Y-%m-%d')
    
    return render_template('invoice_form.html', 
                         invoice=invoice,
                         appointment=None,
                         service_codes=service_codes,
                         today=today)

@app.route('/invoices/<int:invoice_id>/delete', methods=['POST'])
@login_required
def invoice_delete(invoice_id):
    """Delete invoice"""
    try:
        execute_db('DELETE FROM invoices WHERE id = ?', (invoice_id,))
        flash('Invoice deleted successfully', 'success')
    except Exception as e:
        flash(f'Error deleting invoice: {str(e)}', 'error')
    
    return redirect(url_for('invoices_list'))

# Phase 4: Invoice Line Items Routes
@app.route('/invoices/<int:invoice_id>/line_items', methods=['GET', 'POST'])
@login_required
def invoice_line_items(invoice_id):
    """Manage line items for an invoice"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Phase 4: Pull expenses from appointment additional services
            if data.get('pull_expenses'):
                invoice_check = conn.execute("SELECT appointment_id FROM invoices WHERE id = ?", (invoice_id,)).fetchone()
                if invoice_check and invoice_check['appointment_id']:
                    additional_services = conn.execute("""
                        SELECT * FROM appointment_additional_services 
                        WHERE appointment_id = ? AND present = 1
                    """, (invoice_check['appointment_id'],)).fetchall()
                    
                    expense_type_map = {
                        'travel': 'travel',
                        'interpreter': 'interpreter',
                        'videographer': 'videographer',
                        'nominee': 'nominee'
                    }
                    
                    items_added = 0
                    for service in additional_services:
                        expense_type = expense_type_map.get(service['service_type'], 'travel')
                        description = service['service_type'].title()
                        if service['service_type'] == 'travel' and service['travel_details']:
                            description += f": {service['travel_details']}"
                        elif service['service_type'] == 'interpreter' and service['language']:
                            description += f" ({service['language']})"
                        elif service['name']:
                            description += f": {service['name']}"
                        
                        # Check if this expense already exists as a line item
                        existing = conn.execute("""
                            SELECT id FROM invoice_line_items 
                            WHERE invoice_id = ? AND expense_type = ? AND service_description LIKE ?
                        """, (invoice_id, expense_type, f'%{service["service_type"]}%')).fetchone()
                        
                        if not existing:
                            # Create line item for expense (amount will need to be entered manually)
                            execute_db("""
                                INSERT INTO invoice_line_items (
                                    invoice_id, appointment_id, service_description,
                                    expense_type, line_total
                                ) VALUES (?, ?, ?, ?, ?)
                            """, (
                                invoice_id,
                                invoice_check['appointment_id'],
                                description,
                                expense_type,
                                0.00  # Amount to be filled in manually
                            ))
                            items_added += 1
                    
                    if items_added > 0:
                        recalculate_invoice_totals(invoice_id)
                        conn.close()
                        flash(f'{items_added} expense(s) pulled from appointment! Please update amounts.', 'success')
                    else:
                        conn.close()
                        flash('No new expenses to pull. All expenses may already be added.', 'info')
                    return redirect(url_for('invoice_edit', invoice_id=invoice_id))
            
            # Calculate line total
            quantity = float(data.get('quantity', 0)) if data.get('quantity') else 0
            unit_price = float(data.get('unit_price', 0)) if data.get('unit_price') else 0
            line_total = round(quantity * unit_price, 2)
            
            execute_db("""
                INSERT INTO invoice_line_items (
                    invoice_id, appointment_id, service_code, service_description,
                    service_date, quantity, unit_price, line_total, expense_type
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                invoice_id,
                data.get('appointment_id') or None,
                data.get('service_code') or None,
                data.get('service_description') or None,
                data.get('service_date') or None,
                quantity if quantity > 0 else None,
                unit_price if unit_price > 0 else None,
                line_total,
                data.get('expense_type', 'service')
            ))
            
            # Recalculate invoice totals
            recalculate_invoice_totals(invoice_id)
            
            conn.close()
            flash('Line item added successfully!', 'success')
            return redirect(url_for('invoice_edit', invoice_id=invoice_id))
            
        except Exception as e:
            conn.close()
            flash(f'Error adding line item: {str(e)}', 'error')
    
    # Get existing line items
    line_items = conn.execute("""
        SELECT * FROM invoice_line_items WHERE invoice_id = ? ORDER BY id
    """, (invoice_id,)).fetchall()
    
    # Convert to dicts
    line_items = [dict(item) for item in line_items]
    
    invoice = conn.execute("SELECT * FROM invoices WHERE id = ?", (invoice_id,)).fetchone()
    conn.close()
    
    if not invoice:
        flash('Invoice not found', 'error')
        return redirect(url_for('invoices_list'))
    
    # Convert invoice to dict so templates can safely use .get()
    invoice = dict(invoice)
    
    # Get service codes
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}", file=sys.stderr)
    
    # Get appointment additional services for expense integration
    appointment_additional_services = []
    if invoice and invoice.get('appointment_id'):
        conn_check = get_db()
        additional_services = conn_check.execute("""
            SELECT * FROM appointment_additional_services 
            WHERE appointment_id = ? AND present = 1
        """, (invoice['appointment_id'],)).fetchall()
        # Convert to dicts
        appointment_additional_services = [dict(service) for service in additional_services]
        conn_check.close()
    
    return render_template('invoice_line_items.html',
                         invoice=invoice,
                         line_items=line_items,
                         service_codes=service_codes,
                         appointment_additional_services=appointment_additional_services)

@app.route('/invoices/line_items/<int:line_item_id>/edit', methods=['GET', 'POST'])
@login_required
def invoice_line_item_edit(line_item_id):
    """Edit a line item"""
    conn = get_db()
    
    line_item = conn.execute("""
        SELECT li.*, i.id as invoice_id 
        FROM invoice_line_items li
        JOIN invoices i ON li.invoice_id = i.id
        WHERE li.id = ?
    """, (line_item_id,)).fetchone()
    
    if not line_item:
        conn.close()
        flash('Line item not found', 'error')
        return redirect(url_for('invoices_list'))
    
    invoice_id = line_item['invoice_id']
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            quantity = float(data.get('quantity', 0)) if data.get('quantity') else 0
            unit_price = float(data.get('unit_price', 0)) if data.get('unit_price') else 0
            line_total = round(quantity * unit_price, 2)
            
            execute_db("""
                UPDATE invoice_line_items SET
                    service_code = ?, service_description = ?, service_date = ?,
                    quantity = ?, unit_price = ?, line_total = ?, expense_type = ?
                WHERE id = ?
            """, (
                data.get('service_code') or None,
                data.get('service_description') or None,
                data.get('service_date') or None,
                quantity if quantity > 0 else None,
                unit_price if unit_price > 0 else None,
                line_total,
                data.get('expense_type', 'service'),
                line_item_id
            ))
            
            # Recalculate invoice totals
            recalculate_invoice_totals(invoice_id)
            
            conn.close()
            flash('Line item updated successfully!', 'success')
            return redirect(url_for('invoice_edit', invoice_id=invoice_id))
            
        except Exception as e:
            conn.close()
            flash(f'Error updating line item: {str(e)}', 'error')
    
    # Get service codes
    service_codes = []
    try:
        import csv
        with open('data/FIle maker & WMA codes.csv', 'r', encoding='utf-8-sig') as f:
            next(f)
            reader = csv.DictReader(f)
            for row in reader:
                code = row.get('CODE', '').strip()
                if code:
                    desc = row.get('DESCRIPTION ', '') or row.get('DESCRIPTION', '')
                    service_codes.append({
                        'code': code,
                        'description': desc.strip()
                    })
    except Exception as e:
        print(f"Error loading service codes: {e}")
    
    conn.close()
    return render_template('invoice_line_item_form.html', line_item=line_item, service_codes=service_codes)

@app.route('/invoices/line_items/<int:line_item_id>/delete', methods=['POST'])
@login_required
def invoice_line_item_delete(line_item_id):
    """Delete a line item"""
    conn = get_db()
    
    line_item = conn.execute("""
        SELECT invoice_id FROM invoice_line_items WHERE id = ?
    """, (line_item_id,)).fetchone()
    
    if not line_item:
        conn.close()
        flash('Line item not found', 'error')
        return redirect(url_for('invoices_list'))
    
    invoice_id = line_item['invoice_id']
    
    try:
        execute_db("DELETE FROM invoice_line_items WHERE id = ?", (line_item_id,))
        
        # Recalculate invoice totals
        recalculate_invoice_totals(invoice_id)
        
        conn.close()
        flash('Line item deleted successfully!', 'success')
    except Exception as e:
        conn.close()
        flash(f'Error deleting line item: {str(e)}', 'error')
    
    return redirect(url_for('invoice_edit', invoice_id=invoice_id))

def recalculate_invoice_totals(invoice_id):
    """Recalculate invoice totals including line items"""
    conn = get_db()
    
    # Get base fee from invoice
    invoice = conn.execute("SELECT fee, tax_rate FROM invoices WHERE id = ?", (invoice_id,)).fetchone()
    if not invoice:
        conn.close()
        return
    
    base_fee = invoice['fee'] or 0
    tax_rate = invoice['tax_rate'] or 5.0
    
    # Sum all line items
    line_items = conn.execute("""
        SELECT COALESCE(SUM(line_total), 0) as total FROM invoice_line_items WHERE invoice_id = ?
    """, (invoice_id,)).fetchone()
    
    line_items_total = line_items['total'] if line_items else 0
    
    # Total fee = base fee + line items
    total_fee = base_fee + line_items_total
    tax_amount = round(total_fee * (tax_rate / 100), 2)
    total_amount = round(total_fee + tax_amount, 2)
    
    # Update invoice
    conn.execute("""
        UPDATE invoices SET
            tax_amount = ?, total_amount = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    """, (tax_amount, total_amount, invoice_id))
    
    conn.commit()
    conn.close()

# Phase 4: Overdue Payment Detection
def check_and_update_overdue_invoices():
    """Check all invoices and update overdue status"""
    from datetime import date, datetime
    
    conn = get_db()
    today = date.today()
    
    # Get all unpaid invoices with due dates
    invoices = conn.execute("""
        SELECT id, due_date, status, is_overdue 
        FROM invoices 
        WHERE status NOT IN ('paid', 'cancelled') 
        AND due_date IS NOT NULL
    """).fetchall()
    
    overdue_count = 0
    for invoice in invoices:
        if invoice['due_date']:
            try:
                due_date = datetime.strptime(invoice['due_date'], '%Y-%m-%d').date()
                is_overdue = 1 if today > due_date else 0
                
                # Update overdue status if changed
                if invoice['is_overdue'] != is_overdue:
                    conn.execute("""
                        UPDATE invoices SET
                            is_overdue = ?,
                            status = CASE 
                                WHEN ? = 1 AND status != 'cancelled' THEN 'overdue'
                                ELSE status
                            END
                        WHERE id = ?
                    """, (is_overdue, is_overdue, invoice['id']))
                    overdue_count += 1
            except:
                pass
    
    conn.commit()
    conn.close()
    return overdue_count

@app.route('/invoices/check_overdue', methods=['POST'])
@login_required
def check_overdue_invoices():
    """Manually trigger overdue check"""
    count = check_and_update_overdue_invoices()
    flash(f'Checked invoices. {count} invoice(s) marked as overdue.', 'success')
    return redirect(url_for('invoices_list'))

@app.route('/invoices/<int:invoice_id>/print')
@login_required
def invoice_print(invoice_id):
    """Print-friendly invoice view"""
    conn = get_db()
    
    invoice = conn.execute("""
        SELECT * FROM v_invoices_full WHERE id = ?
    """, (invoice_id,)).fetchone()
    
    # Convert to dict so templates can safely use .get()
    if invoice:
        invoice = dict(invoice)
    
    conn.close()
    
    if not invoice:
        flash('Invoice not found', 'error')
        return redirect(url_for('invoices_list'))
    
    return render_template('invoice_print.html', invoice=invoice)

# ============================================================================
# Search Route
# ============================================================================

@app.route('/search')
@login_required
def global_search():
    """Search across all fields"""
    query = request.args.get('q', '')
    status = request.args.get('status', '')
    assessment = request.args.get('assessment', '')
    date_range = request.args.get('date_range', '')
    
    conn = get_db()
    
    # Build SQL query
    sql = "SELECT * FROM v_cases_full WHERE 1=1"
    params = []
    
    # Search term
    if query:
        sql += """ AND (
            record_id LIKE ? OR 
            file_case_id LIKE ? OR
            claimant_first_name LIKE ? OR 
            claimant_last_name LIKE ? OR
            claimant_full_name LIKE ? OR
            client_company_name LIKE ? OR
            lawyer_firm_name LIKE ? OR
            lawyer_first_name LIKE ? OR
            lawyer_last_name LIKE ? OR
            case_notes LIKE ?
        )"""
        search_term = f'%{query}%'
        params.extend([search_term] * 10)
    
    # Status filter
    if status == 'active':
        sql += " AND (file_close_date IS NULL OR file_close_date = '')"
    elif status == 'closed':
        sql += " AND file_close_date IS NOT NULL AND file_close_date != ''"
    
    # Assessment filter
    if assessment:
        sql += " AND assessment_type = ?"
        params.append(assessment)
    
    # Date range filter
    if date_range:
        from datetime import datetime, timedelta
        today = datetime.now().date()
        
        if date_range == 'today':
            sql += " AND file_open_date = ?"
            params.append(today.isoformat())
        elif date_range == 'week':
            week_ago = today - timedelta(days=7)
            sql += " AND file_open_date >= ?"
            params.append(week_ago.isoformat())
        elif date_range == 'month':
            month_ago = today - timedelta(days=30)
            sql += " AND file_open_date >= ?"
            params.append(month_ago.isoformat())
        elif date_range == 'year':
            year_ago = today - timedelta(days=365)
            sql += " AND file_open_date >= ?"
            params.append(year_ago.isoformat())
    
    sql += " ORDER BY file_open_date DESC LIMIT 100"
    
    cases = conn.execute(sql, params).fetchall()
    conn.close()
    
    return render_template('search_results.html', 
                         cases=cases, 
                         query=query, 
                         status=status, 
                         assessment=assessment,
                         date_range=date_range)

# ============================================================================
# API Endpoints (for AJAX/JSON responses)
# ============================================================================

@app.route('/api/search')
def api_search():
    """Global search API"""
    query = request.args.get('q', '')
    
    if len(query) < 2:
        return jsonify({'results': []})
    
    conn = get_db()
    results = []
    
    # Search cases
    cases = conn.execute("""
        SELECT id, record_id, file_case_id, claimant_first_name, claimant_last_name
        FROM v_cases_full
        WHERE record_id LIKE ? OR file_case_id LIKE ? OR 
              claimant_first_name LIKE ? OR claimant_last_name LIKE ?
        LIMIT 5
    """, (f'%{query}%', f'%{query}%', f'%{query}%', f'%{query}%')).fetchall()
    
    for case in cases:
        results.append({
            'type': 'case',
            'title': f"Case {case['file_case_id']}",
            'subtitle': f"{case['claimant_first_name']} {case['claimant_last_name']}",
            'url': url_for('case_view', case_id=case['id'])
        })
    
    conn.close()
    
    return jsonify({'results': results})

# ============================================================================
# Error Handlers
# ============================================================================

@app.errorhandler(404)
def not_found(error):
    return render_template('404.html'), 404

@app.errorhandler(500)
def internal_error(error):
    import traceback
    import sys
    exc_type, exc_value, exc_traceback = sys.exc_info()
    # Log full traceback to server logs only (not exposed to user)
    traceback.print_exception(exc_type, exc_value, exc_traceback, file=sys.stderr)
    # Return generic error page to user (no sensitive data)
    return render_template('500.html'), 500

# ============================================================================
# Template Filters
# ============================================================================

@app.template_filter('format_date')
def format_date(date_string):
    """Format date for display"""
    if not date_string:
        return 'N/A'
    try:
        date_obj = datetime.strptime(str(date_string), '%Y-%m-%d')
        return date_obj.strftime('%B %d, %Y')
    except:
        return date_string

@app.template_filter('format_phone')
def format_phone(phone):
    """Format phone number"""
    if not phone:
        return 'N/A'
    # Simple formatting for 10-digit numbers
    digits = ''.join(filter(str.isdigit, str(phone)))
    if len(digits) == 10:
        return f"({digits[:3]}) {digits[3:6]}-{digits[6:]}"
    return phone

# ============================================================================
# Main
# ============================================================================

@app.route('/appointments/temp_upload', methods=['POST'])
@login_required
def appointment_temp_upload():
    """Upload attachment for appointment being created. Stores temporarily in session until appointment is saved."""
    try:
        case_id = request.form.get('case_id', type=int)
        if not case_id:
            return jsonify({'success': False, 'error': 'Case is required to upload files'}), 400
        
        if 'letter_file' not in request.files:
            return jsonify({'success': False, 'error': 'No file selected'}), 400
        
        file = request.files['letter_file']
        if file.filename == '':
            return jsonify({'success': False, 'error': 'No file selected'}), 400
        
        if not allowed_file(file.filename):
            return jsonify({'success': False, 'error': 'Invalid file type'}), 400
        
        original_filename = secure_filename(file.filename)
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"{timestamp}_{original_filename}"
        
        # Store in temporary appointment uploads folder
        upload_dir = 'uploads/temp_appointments'
        os.makedirs(upload_dir, exist_ok=True)
        filepath = os.path.join(upload_dir, filename)
        file.save(filepath)
        
        file_size = os.path.getsize(filepath)
        file_extension = original_filename.rsplit('.', 1)[1].lower()
        letter_type = request.form.get('letter_type', 'other')
        
        # Store file info in session for this case
        if 'pending_appointment_files' not in session:
            session['pending_appointment_files'] = {}
        
        case_key = str(case_id)
        if case_key not in session['pending_appointment_files']:
            session['pending_appointment_files'][case_key] = []
        
        session['pending_appointment_files'][case_key].append({
            'original_filename': original_filename,
            'stored_filename': filename,
            'filepath': filepath,
            'file_size': file_size,
            'file_type': file_extension,
            'letter_type': letter_type,
            'uploaded_at': datetime.now().isoformat()
        })
        
        session.modified = True
        
        return jsonify({
            'success': True,
            'message': 'File uploaded successfully! It will be attached when you save the appointment.',
            'filename': original_filename
        })
        
    except Exception as e:
        import traceback
        traceback.print_exc()
        return jsonify({'success': False, 'error': str(e)}), 500

# ============================================================================
# Phase 5: Bring Forward (B/F) Tasks Routes
# NOTE: These routes must be defined BEFORE if __name__ == '__main__'
# ============================================================================

@app.route('/tasks')
@login_required
def tasks_list():
    """List all Bring Forward tasks with filters"""
    status_filter = request.args.get('status', '')
    priority_filter = request.args.get('priority', '')
    assigned_filter = request.args.get('assigned', '')
    search = request.args.get('search', '')
    
    conn = get_db()
    
    query = """
        SELECT t.*, 
               c.file_case_id,
               a.appointment_date,
               i.invoice_number
        FROM bring_forward_tasks t
        LEFT JOIN cases c ON t.case_id = c.id
        LEFT JOIN appointments a ON t.appointment_id = a.id
        LEFT JOIN invoices i ON t.invoice_id = i.id
        WHERE 1=1
    """
    params = []
    
    if status_filter:
        query += " AND t.status = ?"
        params.append(status_filter)
    
    if priority_filter:
        query += " AND t.priority = ?"
        params.append(priority_filter)
    
    if assigned_filter:
        query += " AND t.assigned_to = ?"
        params.append(assigned_filter)
    
    if search:
        query += " AND (t.task_title LIKE ? OR t.task_description LIKE ?)"
        search_term = f'%{search}%'
        params.extend([search_term, search_term])
    
    query += " ORDER BY t.due_date ASC, t.priority DESC, t.created_at DESC"
    
    tasks = conn.execute(query, params).fetchall()
    
    # Convert to dicts
    tasks = [dict(task) for task in tasks]
    
    # Get counts for dashboard
    try:
        pending_count = conn.execute("SELECT COUNT(*) as count FROM bring_forward_tasks WHERE status = 'pending'").fetchone()
        overdue_count = conn.execute("""
            SELECT COUNT(*) as count FROM bring_forward_tasks 
            WHERE status = 'pending' AND due_date < date('now')
        """).fetchone()
        pending_count_value = pending_count['count'] if pending_count else 0
        overdue_count_value = overdue_count['count'] if overdue_count else 0
    except Exception as e:
        import sys
        print(f"Error getting task counts: {e}", file=sys.stderr)
        pending_count_value = 0
        overdue_count_value = 0
    
    conn.close()
    
    from datetime import date
    today = date.today()
    
    return render_template('tasks_list.html',
                         tasks=tasks,
                         status_filter=status_filter,
                         priority_filter=priority_filter,
                         assigned_filter=assigned_filter,
                         search=search,
                         pending_count=pending_count_value,
                         overdue_count=overdue_count_value,
                         today=today)

@app.route('/tasks/new', methods=['GET', 'POST'])
@app.route('/tasks/new/<int:case_id>', methods=['GET', 'POST'])
@app.route('/tasks/new/appointment/<int:appointment_id>', methods=['GET', 'POST'])
@app.route('/tasks/new/invoice/<int:invoice_id>', methods=['GET', 'POST'])
@login_required
def task_new(case_id=None, appointment_id=None, invoice_id=None):
    """Create new Bring Forward task"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Get IDs from form or URL
            case_id = case_id or (int(data.get('case_id')) if data.get('case_id') else None)
            appointment_id = appointment_id or (int(data.get('appointment_id')) if data.get('appointment_id') else None)
            invoice_id = invoice_id or (int(data.get('invoice_id')) if data.get('invoice_id') else None)
            
            task_id = execute_db("""
                INSERT INTO bring_forward_tasks (
                    case_id, appointment_id, invoice_id,
                    task_title, task_description, due_date,
                    priority, status, assigned_to, created_by, notes
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                case_id,
                appointment_id,
                invoice_id,
                data.get('task_title'),
                data.get('task_description') or None,
                data.get('due_date') or None,
                data.get('priority', 'medium'),
                data.get('status', 'pending'),
                data.get('assigned_to') or None,
                session.get('username', 'System'),
                data.get('notes') or None
            ))
            
            flash('Task created successfully!', 'success')
            
            # Redirect based on context
            if case_id:
                return redirect(url_for('case_view', case_id=case_id))
            elif appointment_id:
                return redirect(url_for('appointment_view', appointment_id=appointment_id))
            elif invoice_id:
                return redirect(url_for('invoice_view', invoice_id=invoice_id))
            else:
                return redirect(url_for('tasks_list'))
                
        except Exception as e:
            flash(f'Error creating task: {str(e)}', 'error')
            import traceback
            traceback.print_exc(file=sys.stderr)
    
    # Get dropdown data
    cases = conn.execute("SELECT id, file_case_id FROM cases ORDER BY file_case_id DESC LIMIT 100").fetchall()
    appointments = conn.execute("""
        SELECT id, appointment_date, case_id 
        FROM appointments 
        ORDER BY appointment_date DESC 
        LIMIT 100
    """).fetchall()
    invoices = conn.execute("""
        SELECT id, invoice_number 
        FROM invoices 
        ORDER BY invoice_date DESC 
        LIMIT 100
    """).fetchall()
    
    # Pre-populate if coming from specific context
    prefill = {}
    if case_id:
        case = conn.execute("SELECT id, file_case_id FROM cases WHERE id = ?", (case_id,)).fetchone()
        if case:
            prefill['case_id'] = case['id']
            prefill['case_file_id'] = case['file_case_id']
    if appointment_id:
        appointment = conn.execute("SELECT id, case_id, appointment_date FROM appointments WHERE id = ?", (appointment_id,)).fetchone()
        if appointment:
            prefill['appointment_id'] = appointment['id']
            prefill['case_id'] = appointment['case_id']
    if invoice_id:
        invoice = conn.execute("SELECT id, case_id, invoice_number FROM invoices WHERE id = ?", (invoice_id,)).fetchone()
        if invoice:
            prefill['invoice_id'] = invoice['id']
            prefill['case_id'] = invoice.get('case_id')
    
    conn.close()
    
    return render_template('task_form.html',
                         task=None,
                         cases=cases,
                         appointments=appointments,
                         invoices=invoices,
                         prefill=prefill)

@app.route('/tasks/<int:task_id>')
@login_required
def task_view(task_id):
    """View task details"""
    conn = get_db()
    
    task = conn.execute("""
        SELECT t.*, 
               c.file_case_id,
               a.appointment_date,
               i.invoice_number
        FROM bring_forward_tasks t
        LEFT JOIN cases c ON t.case_id = c.id
        LEFT JOIN appointments a ON t.appointment_id = a.id
        LEFT JOIN invoices i ON t.invoice_id = i.id
        WHERE t.id = ?
    """, (task_id,)).fetchone()
    
    # Convert to dict
    if task:
        task = dict(task)
    
    conn.close()
    
    if not task:
        flash('Task not found', 'error')
        return redirect(url_for('tasks_list'))
    
    from datetime import date
    today = date.today()
    
    return render_template('task_view.html', task=task, today=today)

@app.route('/tasks/<int:task_id>/edit', methods=['GET', 'POST'])
@login_required
def task_edit(task_id):
    """Edit Bring Forward task"""
    conn = get_db()
    
    if request.method == 'POST':
        data = request.form.to_dict()
        
        try:
            # Handle completion
            if data.get('action') == 'complete':
                execute_db("""
                    UPDATE bring_forward_tasks SET
                        status = 'completed',
                        completed_at = CURRENT_TIMESTAMP,
                        notes = COALESCE(notes || '\n' || ?, ?)
                    WHERE id = ?
                """, (
                    data.get('completion_notes', ''),
                    data.get('completion_notes', ''),
                    task_id
                ))
                flash('Task marked as completed!', 'success')
            else:
                execute_db("""
                    UPDATE bring_forward_tasks SET
                        case_id = ?,
                        appointment_id = ?,
                        invoice_id = ?,
                        task_title = ?,
                        task_description = ?,
                        due_date = ?,
                        priority = ?,
                        status = ?,
                        assigned_to = ?,
                        notes = ?
                    WHERE id = ?
                """, (
                    int(data.get('case_id')) if data.get('case_id') else None,
                    int(data.get('appointment_id')) if data.get('appointment_id') else None,
                    int(data.get('invoice_id')) if data.get('invoice_id') else None,
                    data.get('task_title'),
                    data.get('task_description') or None,
                    data.get('due_date') or None,
                    data.get('priority', 'medium'),
                    data.get('status', 'pending'),
                    data.get('assigned_to') or None,
                    data.get('notes') or None,
                    task_id
                ))
                flash('Task updated successfully!', 'success')
            
            return redirect(url_for('task_view', task_id=task_id))
            
        except Exception as e:
            flash(f'Error updating task: {str(e)}', 'error')
            import traceback
            traceback.print_exc(file=sys.stderr)
    
    task = conn.execute("SELECT * FROM bring_forward_tasks WHERE id = ?", (task_id,)).fetchone()
    
    # Convert to dict
    if task:
        task = dict(task)
    
    if not task:
        conn.close()
        flash('Task not found', 'error')
        return redirect(url_for('tasks_list'))
    
    # Get dropdown data
    cases = conn.execute("SELECT id, file_case_id FROM cases ORDER BY file_case_id DESC LIMIT 100").fetchall()
    appointments = conn.execute("""
        SELECT id, appointment_date, case_id 
        FROM appointments 
        ORDER BY appointment_date DESC 
        LIMIT 100
    """).fetchall()
    invoices = conn.execute("""
        SELECT id, invoice_number 
        FROM invoices 
        ORDER BY invoice_date DESC 
        LIMIT 100
    """).fetchall()
    
    conn.close()
    
    return render_template('task_form.html',
                         task=task,
                         cases=cases,
                         appointments=appointments,
                         invoices=invoices,
                         prefill={})

@app.route('/tasks/<int:task_id>/delete', methods=['POST'])
@login_required
def task_delete(task_id):
    """Delete Bring Forward task"""
    try:
        execute_db('DELETE FROM bring_forward_tasks WHERE id = ?', (task_id,))
        flash('Task deleted successfully!', 'success')
    except Exception as e:
        flash(f'Error deleting task: {str(e)}', 'error')
    
    return redirect(url_for('tasks_list'))

# ============================================================================
# Phase 5.2: Case Reports Management
# ============================================================================

if __name__ == '__main__':
    app.run(debug=False, use_reloader=False, host='0.0.0.0', port=5050)