# Vera Medical CRM

A comprehensive Customer Relationship Management (CRM) system designed specifically for **Vera Medical** to manage medical assessment cases, appointments, invoices, reports, and all related business operations.

## 📋 Overview

This CRM application provides a complete, production-ready solution for managing medical assessment operations with an intuitive web-based interface for staff to:
- Track and manage medical assessment cases
- Schedule and manage appointments with doctors and specialists
- Generate and manage invoices automatically from appointments
- Upload and manage medical reports and documents
- Manage client (insurance companies) relationships
- Track lawyer firms and contacts
- Manage doctor/specialist information
- Create and send appointment confirmation letters
- Integrate form templates with appointment workflow
- Comprehensive search and filtering across all data

## 🛠 Technology Stack

- **Backend:** Python 3.8+ with Flask 3.0.0
- **Database:** SQLite3 (normalized relational schema)
- **Frontend:** HTML5 with Tailwind CSS
- **Architecture:** MVC pattern with server-side rendering
- **Authentication:** Session-based authentication with password hashing
- **File Handling:** Secure file uploads (PDF, DOC, DOCX, TXT, images)

## 📁 Project Structure

```
vera-medical-crm/
├── app.py                      # Main Flask application (57+ routes)
├── utils.py                    # Utility functions (sanitization, validation)
├── import_data.py              # Data import script (legacy data)
├── requirements.txt            # Python dependencies
├── restart_server.sh           # Server restart script
├── database/                   # Database directory
│   └── vera_medical.db         # SQLite database
├── templates/                  # HTML templates (27 templates)
│   ├── base.html              # Base template with navigation
│   ├── login.html             # Login page
│   ├── dashboard.html         # Dashboard/home page
│   ├── cases_list.html        # Cases listing
│   ├── case_view.html         # Case detail view
│   ├── case_form.html         # Case create/edit form
│   ├── clients_list.html      # Insurance clients listing
│   ├── client_view.html       # Client detail view
│   ├── client_form.html       # Client create/edit form
│   ├── contact_form.html      # Client contact form
│   ├── lawyers_list.html      # Lawyers listing
│   ├── lawyer_form.html       # Lawyer create/edit form
│   ├── doctors_list.html      # Doctors listing
│   ├── doctor_form.html       # Doctor create/edit form
│   ├── appointments_list.html # Appointments listing
│   ├── appointment_view.html  # Appointment detail view
│   ├── appointment_form.html  # Appointment create/edit form
│   ├── appointment_print.html # Appointment print view
│   ├── reports_list.html      # Reports listing
│   ├── report_view.html       # Report detail view
│   ├── report_form.html       # Report upload form
│   ├── report_print.html      # Report print view
│   ├── invoices_list.html     # Invoices listing
│   ├── invoice_view.html      # Invoice detail view
│   ├── invoice_form.html      # Invoice create/edit form
│   ├── invoice_print.html     # Invoice print view
│   ├── search_results.html    # Global search results
│   ├── 404.html               # Error page
│   └── 500.html               # Error page
├── static/                     # Static assets (CSS/JS)
├── uploads/                    # File uploads directory
│   ├── reports/               # Medical reports
│   └── appointment_letters/   # Appointment letters
├── forms/                      # Form templates
│   └── templates_bundle_cursor_pretty/  # HTML form templates
├── scripts/                    # Utility scripts
│   └── test_application.py    # Comprehensive integration test suite
└── docs/                       # Documentation
```

## 🗄 Database Schema

The application uses a **normalized relational database** with the following main tables:

### Core Tables
- **cases** - Main case records with foreign keys to related entities
- **claimants** - Injured parties/patients
- **lawyer_firms** - Law firms representing claimants
- **lawyer_contacts** - Individual lawyers at firms
- **clients** - Insurance companies
- **client_contacts** - Insurance adjusters/contacts
- **doctors** - Doctors and specialists
- **appointments** - Scheduled appointments linked to cases
- **appointment_letters** - Letter attachments for appointments
- **reports** - Medical reports and documents
- **invoices** - Invoices generated from appointments
- **users** - System users for authentication

### Key Relationships
- Cases → Claimants (Many-to-One)
- Cases → Clients (Many-to-One)
- Cases → Lawyer Firms (Many-to-One)
- Cases → Lawyer Contacts (Many-to-One)
- Appointments → Cases (Many-to-One)
- Appointments → Doctors (via doctor_name)
- Invoices → Appointments (Many-to-One)
- Invoices → Cases (Many-to-One)
- Reports → Cases (Many-to-One)
- Reports → Appointments (Many-to-One, optional)

## 🚀 Setup Instructions

### Prerequisites
- Python 3.8 or higher
- pip (Python package manager)
- Virtual environment support (recommended)

### Step 1: Clone/Navigate to Project Directory

```bash
cd /home/ab5.sw7ft.com/public_html
```

### Step 2: Create Virtual Environment (if not exists)

```bash
python3 -m venv venv
source venv/bin/activate
```

### Step 3: Install Dependencies

```bash
pip install -r requirements.txt
```

This installs:
- Flask==3.0.0
- Werkzeug==3.0.1
- python-dotenv==1.0.0 (optional)

### Step 4: Initialize Database

The database should already exist at `database/vera_medical.db`. If you need to import legacy data:

```bash
source venv/bin/activate
python3 import_data.py
```

### Step 5: Run the Application

**Development mode:**
```bash
source venv/bin/activate
python3 app.py
```

**Production mode (background):**
```bash
source venv/bin/activate && nohup python3 app.py > /tmp/flask_server.log 2>&1 &
```

**Or use the restart script:**
```bash
./restart_server.sh
```

The application will be accessible at:
```
http://localhost:5050
```

**Default Login Credentials:**
- Username: `admin`
- Password: `admin123`

⚠️ **Change the default password in production!**

## 📱 Features

### 🔐 Authentication
- **User login/logout** - Session-based authentication
- **Password hashing** - Secure password storage using Werkzeug
- **Protected routes** - All routes require authentication except login
- **Default admin user** - Auto-created on first run

### 📊 Dashboard
- **Overview statistics** - Total cases, active cases, clients, appointments
- **Recent activity** - Quick view of recent cases and appointments
- **Quick actions** - Fast access to common tasks
- **Search interface** - Global search across all entities

### 📁 Cases Management
- **Full CRUD operations** - Create, Read, Update, Delete cases
- **Search functionality** - Search by case ID, claimant name, client name
- **Filter by status** - Active/Closed/All
- **Pagination** - Efficient data browsing
- **Related data view** - See all appointments, invoices, reports for a case
- **Multi-appointment creation** - Create multiple appointments when creating a case
- **Validation** - Form validation and data sanitization

### 👥 Clients (Insurance Companies)
- **Full CRUD operations** - Manage insurance company information
- **Contact management** - Add/edit/delete client contacts (adjusters)
- **Case statistics** - View total, active, and closed cases per client
- **Recent cases** - Quick view of client's recent cases
- **Search API** - AJAX search for dropdowns

### ⚖️ Lawyers Management
- **Full CRUD operations** - Manage lawyer firms and contacts
- **Firm association** - Link lawyers to firms
- **Case tracking** - View all cases for each lawyer
- **Contact information** - Phone, email, address management
- **Search API** - AJAX search functionality

### 👨‍⚕️ Doctors Management
- **Full CRUD operations** - Manage doctor/specialist profiles
- **Specialty tracking** - Organize by medical specialty
- **Contact information** - Clinic and mailing addresses
- **Fee management** - Track cancellation fees
- **Appointment linking** - Connect to appointments
- **Search API** - AJAX search functionality

### 📅 Appointments Management
- **Full CRUD operations** - Create, view, edit, delete appointments
- **Smart date ordering** - Upcoming appointments first, then past
- **Comprehensive search** - Search by case, claimant, doctor, location, date
- **Advanced filtering** - Filter by date range and status
  - Date filters: Today, This Week, Upcoming, Past, All Dates
  - Status filters: Scheduled, Completed, Cancelled, No-Show
- **Letter attachments** - Upload confirmation letters, reminders, reports
  - Supported formats: PDF, DOC, DOCX, TXT
  - Letter types: Confirmation, Reminder, Report, Correspondence, Other
  - Download and delete functionality
- **Print functionality** - Professional print views
- **Cancel appointments** - Mark appointments as cancelled
- **Reference number generation** - Auto-generate invoice numbers
- **Email integration** - Send appointment details via email (SMTP or .eml file)
- **Status tracking** - Track appointment lifecycle
- **Link to invoices** - Generate invoices directly from appointments

### 📄 Reports Management
- **Full CRUD operations** - Upload, view, edit, delete reports
- **File uploads** - Support for PDF, DOC, DOCX, TXT, images
- **Report types** - Categorize by type (IME Report, etc.)
- **Status tracking** - Draft, Final, etc.
- **Case/Appointment linking** - Link reports to cases and appointments
- **Print functionality** - Professional print views
- **Download functionality** - Secure file downloads
- **Temp upload** - Upload reports before appointment creation
- **Form integration** - Save completed forms as reports

### 💰 Invoicing System
- **Automatic invoice generation** - Create from appointments with auto-populated data
- **Reference number matching** - Invoice number matches appointment reference
- **Tax calculations** - Automatic tax calculation (GST/HST)
- **Full CRUD operations** - Create, view, edit, delete invoices
- **Status tracking** - Draft, Sent, Paid, etc.
- **Print functionality** - Professional invoice print views
- **Service code management** - Link to service codes from CSV
- **Case/Appointment linking** - Full relationship tracking

### 📋 Forms Integration
- **Form template library** - Access to 50+ HTML form templates
- **Template listing** - Browse all available forms
- **Form preview** - Preview forms with auto-populated data
- **Auto-population** - Replace placeholders with case/appointment data
  - Client information (name, address, contact)
  - Claimant information (name, DOB, address)
  - Doctor information (name, address)
  - Lawyer information (firm, contact)
  - Case details (file number, dates)
  - Appointment details (date, time, location)
- **Form saving** - Save completed forms as reports
- **Email integration** - Send forms via email

### 🔍 Search & Filtering
- **Global search** - Search across all entities
  - Cases, Claimants, Clients, Lawyers, Doctors, Appointments
- **API endpoints** - JSON search APIs for dropdowns
  - `/api/search/cases` - Case search
  - `/api/search/clients` - Client search
  - `/api/search/lawyers` - Lawyer search
  - `/api/search/doctors` - Doctor search
  - `/api/search` - Global search
- **Fuzzy matching** - Intelligent name matching
- **Real-time results** - Fast AJAX-powered search

### 📎 File Management
- **Secure uploads** - Validated file types and sizes
- **Organized storage** - Separate directories for reports and letters
- **File metadata** - Track file size, type, upload date
- **Download protection** - Secure file downloads
- **File deletion** - Safe file removal with database cleanup

### 🖨️ Print Functionality
- **Print-optimized views** - Clean, professional print layouts
- **Appointment prints** - Full appointment details
- **Invoice prints** - Professional invoice format
- **Report prints** - Document view format
- **Browser print support** - Works with browser print dialog

## 🔍 API Endpoints

### Authentication
- `GET /login` - Login page
- `POST /login` - Authenticate user
- `GET /logout` - Logout user

### Dashboard
- `GET /` - Dashboard home page

### Cases
- `GET /cases` - List all cases (with search and filters)
- `GET /cases/<id>` - View case details
- `GET /cases/new` - New case form
- `POST /cases/new` - Create new case
- `GET /cases/<id>/edit` - Edit case form
- `POST /cases/<id>/edit` - Update case
- `POST /cases/<id>/delete` - Delete case

### Clients
- `GET /clients` - List all insurance clients
- `GET /clients/<id>` - View client profile
- `GET /clients/<id>/edit` - Edit client form
- `POST /clients/<id>/edit` - Update client
- `GET /clients/<id>/contacts/new` - New contact form
- `POST /clients/<id>/contacts/new` - Create contact
- `GET /clients/<id>/contacts/<contact_id>/edit` - Edit contact
- `POST /clients/<id>/contacts/<contact_id>/edit` - Update contact
- `POST /clients/<id>/contacts/<contact_id>/delete` - Delete contact

### Lawyers
- `GET /lawyers` - List all lawyers
- `GET /lawyers/new` - New lawyer form
- `POST /lawyers/new` - Create lawyer
- `GET /lawyers/<id>/edit` - Edit lawyer form
- `POST /lawyers/<id>/edit` - Update lawyer
- `POST /lawyers/<id>/delete` - Delete lawyer

### Doctors
- `GET /doctors` - List all doctors
- `GET /doctors/new` - New doctor form
- `POST /doctors/new` - Create doctor
- `GET /doctors/<id>/edit` - Edit doctor form
- `POST /doctors/<id>/edit` - Update doctor
- `POST /doctors/<id>/delete` - Delete doctor

### Appointments
- `GET /appointments` - List all appointments (with search and filters)
- `GET /appointments/new` - New appointment form
- `GET /appointments/new/<case_id>` - New appointment for specific case
- `POST /appointments/new` - Create appointment
- `GET /appointments/<id>` - View appointment details
- `GET /appointments/<id>/edit` - Edit appointment form
- `POST /appointments/<id>/edit` - Update appointment
- `POST /appointments/<id>/delete` - Delete appointment
- `POST /appointments/<id>/cancel` - Cancel appointment
- `POST /appointments/<id>/upload_letter` - Upload letter attachment
- `GET /appointments/<id>/print` - Print appointment view
- `GET /appointments/letters/<letter_id>/download` - Download letter
- `POST /appointments/letters/<letter_id>/delete` - Delete letter
- `POST /appointments/temp_upload` - Upload file before appointment creation

### Reports
- `GET /reports` - List all reports
- `GET /reports/new` - New report form
- `POST /reports/new` - Upload report
- `GET /reports/<id>` - View report details
- `GET /reports/<id>/download` - Download report file
- `POST /reports/<id>/delete` - Delete report
- `GET /reports/<id>/print` - Print report view

### Invoices
- `GET /invoices` - List all invoices
- `GET /invoices/new` - New invoice form
- `GET /invoices/new/<appointment_id>` - New invoice from appointment
- `POST /invoices/new` - Create invoice
- `GET /invoices/<id>` - View invoice details
- `GET /invoices/<id>/edit` - Edit invoice form
- `POST /invoices/<id>/edit` - Update invoice
- `POST /invoices/<id>/delete` - Delete invoice
- `GET /invoices/<id>/print` - Print invoice view
- `GET /api/invoices/next_number` - Get next invoice number

### Forms
- `GET /api/forms/list` - List all available form templates
- `GET /api/forms/<form_name>/preview` - Preview form with data
- `POST /api/forms/<form_name>/preview` - Preview form with POST data
- `POST /api/forms/save` - Save completed form as report

### Search API
- `GET /search` - Global search page
- `GET /api/search` - Global search (JSON)
- `GET /api/search/cases` - Case search (JSON)
- `GET /api/search/clients` - Client search (JSON)
- `GET /api/search/lawyers` - Lawyer search (JSON)
- `GET /api/search/doctors` - Doctor search (JSON)

## 🎨 User Interface

### Design System
- **Framework:** Tailwind CSS for responsive, modern design
- **Icons:** Font Awesome for consistent iconography
- **Color Scheme:**
  - Primary (Blue): Cases, main actions
  - Secondary (Green): Claimants, success states
  - Tertiary (Purple): Clients, statistics
  - Accent (Orange): Contacts, warnings
  - Red: Delete actions, errors
  - Yellow: Edit actions, warnings

### Navigation
- **Top navigation bar** with main sections
- **Breadcrumbs** for context
- **Back buttons** for easy navigation
- **Mobile-responsive** design
- **Quick action buttons** throughout

### UI Components
- **Cards** - Information grouping with gradients
- **Tables** - Data listing with sorting and pagination
- **Forms** - Clean inputs with validation
- **Badges** - Status indicators (Scheduled, Completed, Cancelled, etc.)
- **Modals** - For confirmations and forms
- **Buttons** - Consistent styling and hover states

## 📊 Data Statistics

### Current Database
- **305,679+** case records (legacy import)
- **~45,000** unique claimants
- **~80** insurance companies
- **~150** law firms
- **~250** lawyer contacts
- **~300** insurance adjusters
- **Dynamic** appointments, invoices, reports (growing)

### Date Range
- Cases from **2003 to 2025** (22+ years of historical data)
- Current operations ongoing

### Geographic Coverage
- Primarily **Alberta, Canada**
- Cities: Calgary, Edmonton, St. Albert, and others

## 🔧 Configuration

### Database Configuration
Edit `app.py` to change database settings:
```python
app.config['DATABASE'] = 'database/vera_medical.db'
```

### File Upload Configuration
```python
app.config['UPLOAD_FOLDER'] = 'uploads/reports'
app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024  # 50MB max
app.config['ALLOWED_EXTENSIONS'] = {'pdf', 'doc', 'docx', 'txt', 'jpg', 'jpeg', 'png'}
```

### Server Configuration
The server runs on:
- **Host:** `0.0.0.0` (all interfaces)
- **Port:** `5050`
- **Debug:** `False` (production)

Edit at bottom of `app.py`:
```python
app.run(debug=False, use_reloader=False, host='0.0.0.0', port=5050)
```

### Email Configuration
Email functionality supports:
- SMTP server configuration (if available)
- Fallback to .eml file generation in `/tmp/` directory

## 🧪 Testing

### Integration Test Suite
A comprehensive test suite is available at `scripts/test_application.py`:

```bash
python3 scripts/test_application.py
```

**Test Coverage:**
- ✅ Case creation with new entities
- ✅ Doctor creation
- ✅ Appointment creation and management
- ✅ All button/action functionality
- ✅ Forms API integration
- ✅ Cancel appointment functionality
- ✅ File uploads (letters and reports)
- ✅ Invoice creation from appointments
- ✅ Print functionality
- ✅ Search functionality
- ✅ Data integrity verification

**Test Data:**
- All test data marked with `TEST_AUTO_` prefix
- Easy cleanup after testing
- Comprehensive verification of database state

## 🐛 Troubleshooting

### Database not found error
**Error:** `Database not found`

**Solution:** Ensure database exists at `database/vera_medical.db` or run import script.

### Port already in use
**Error:** `Address already in use`

**Solution:** 
1. Kill existing process: `lsof -ti:5050 | xargs kill -9`
2. Or change port in `app.py`

### Missing dependencies
**Error:** `ModuleNotFoundError`

**Solution:** 
```bash
source venv/bin/activate
pip install -r requirements.txt
```

### Permission errors on uploads
**Solution:** Ensure `uploads/` directory has write permissions:
```bash
chmod -R 755 uploads/
```

### Server not starting
**Check logs:**
```bash
tail -f /tmp/flask_server.log
```

**Check if server is running:**
```bash
lsof -ti:5050
ps aux | grep app.py
```

## 📈 Future Enhancements

Potential features for future versions:
- [x] User authentication and authorization ✅
- [ ] Role-based access control (Admin, Staff, Read-only)
- [ ] Advanced reporting and analytics
- [ ] Export functionality (PDF, Excel)
- [x] Email notifications ✅ (basic implementation)
- [ ] Calendar integration for appointments
- [x] Document management ✅ (upload/attach files)
- [ ] Activity logging and audit trail
- [ ] Dashboard customization
- [ ] Bulk operations (bulk edit, bulk delete)
- [x] RESTful API ✅ (partial implementation)
- [ ] Mobile app
- [ ] Automated email sending (currently .eml files)
- [ ] Report generation and scheduling
- [ ] Payment tracking for invoices

## 📝 License

Proprietary software for Vera Medical. All rights reserved.

## 👥 Support

For questions or issues, please contact the Vera Medical IT department.

---

## 🎯 Quick Start Guide

### For Staff Members

1. **Access the CRM:** 
   - Open browser: `http://localhost:5050` (or provided URL)
   - Login with credentials provided by IT

2. **Dashboard:**
   - View overall statistics and recent activity
   - Quick search across all entities
   - Access main navigation tabs

3. **Create a Case:**
   - Click "Cases" → "New Case"
   - Fill in required fields
   - Optionally create appointments during case creation
   - Save

4. **Schedule an Appointment:**
   - From case view, click "New Appointment"
   - Or go to Appointments → New Appointment
   - Fill in date, time, doctor, location
   - Upload confirmation letter if needed
   - Save (optionally schedule + create invoice)

5. **Generate Invoice:**
   - From appointment view, click "Create Invoice"
   - Invoice auto-populated with appointment data
   - Adjust amounts, tax rate if needed
   - Save and print

6. **Upload Report:**
   - Go to Reports → New Report
   - Select case and/or appointment
   - Upload file (PDF, DOC, etc.)
   - Fill in report details
   - Save

7. **Search:**
   - Use global search bar on dashboard
   - Or use specific search on each list page
   - Filter by date, status, etc.

---

*Last Updated: October 31, 2025*  
*Version: 2.0.0*  
*Status: Production Ready*