#!/usr/bin/env python3
"""
Safe Migration Runner for To-Do List Updates
This script safely adds new database columns and tables, checking for existence first.
"""

import sqlite3
import os
import sys

def column_exists(conn, table_name, column_name):
    """Check if a column exists in a table"""
    cursor = conn.execute(f"PRAGMA table_info({table_name})")
    columns = [row[1] for row in cursor.fetchall()]
    return column_name in columns

def table_exists(conn, table_name):
    """Check if a table exists"""
    cursor = conn.execute("""
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
    """, (table_name,))
    return cursor.fetchone() is not None

def safe_add_column(conn, table_name, column_name, column_definition):
    """Safely add a column if it doesn't exist"""
    if not column_exists(conn, table_name, column_name):
        try:
            conn.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {column_definition}")
            print(f"  ✓ Added column {table_name}.{column_name}")
            return True
        except Exception as e:
            print(f"  ✗ Error adding column {table_name}.{column_name}: {e}")
            return False
    else:
        print(f"  - Column {table_name}.{column_name} already exists, skipping")
        return True

def run_migration():
    """Run the migration"""
    db_path = 'database/vera_medical.db'
    
    if not os.path.exists(db_path):
        print(f"Error: Database not found at {db_path}")
        sys.exit(1)
    
    print("=" * 60)
    print("Vera Medical CRM - To-Do List Migration")
    print("=" * 60)
    print(f"Database: {db_path}\n")
    
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    
    try:
        # =====================================================================
        # CASE INFORMATION UPDATES
        # =====================================================================
        print("\n[1] Case Information Updates...")
        safe_add_column(conn, 'cases', 'service_location', 'TEXT')
        
        # =====================================================================
        # CLAIMANT DETAILS UPDATES
        # =====================================================================
        print("\n[2] Claimant Details Updates...")
        safe_add_column(conn, 'claimants', 'gender', 'TEXT')
        # title already exists
        
        # Create name history table
        if not table_exists(conn, 'claimant_name_history'):
            conn.execute("""
                CREATE TABLE claimant_name_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    claimant_id INTEGER NOT NULL,
                    old_last_name TEXT NOT NULL,
                    new_last_name TEXT NOT NULL,
                    changed_date DATE DEFAULT CURRENT_DATE,
                    changed_by TEXT,
                    notes TEXT,
                    FOREIGN KEY (claimant_id) REFERENCES claimants(id)
                )
            """)
            conn.execute("CREATE INDEX idx_claimant_name_history_claimant_id ON claimant_name_history(claimant_id)")
            print("  ✓ Created table claimant_name_history")
        else:
            print("  - Table claimant_name_history already exists")
        
        # =====================================================================
        # CLIENT DETAILS UPDATES
        # =====================================================================
        print("\n[3] Client Details Updates...")
        safe_add_column(conn, 'clients', 'client_name', 'TEXT')
        safe_add_column(conn, 'clients', 'email', 'TEXT')
        safe_add_column(conn, 'clients', 'fax', 'TEXT')
        safe_add_column(conn, 'clients', 'assistant_name', 'TEXT')
        safe_add_column(conn, 'clients', 'assistant_email', 'TEXT')
        # assistant_phone already exists
        
        # =====================================================================
        # OPPOSING LAWYER UPDATES
        # =====================================================================
        print("\n[4] Opposing Lawyer Updates...")
        # email may already exist in lawyer_firms
        safe_add_column(conn, 'lawyer_firms', 'email', 'TEXT')
        safe_add_column(conn, 'lawyer_firms', 'fax', 'TEXT')
        safe_add_column(conn, 'lawyer_contacts', 'assistant_name', 'TEXT')
        safe_add_column(conn, 'lawyer_contacts', 'assistant_phone', 'TEXT')
        safe_add_column(conn, 'lawyer_contacts', 'assistant_email', 'TEXT')
        
        # =====================================================================
        # APPOINTMENT DETAILS UPDATES
        # =====================================================================
        print("\n[5] Appointment Details Updates...")
        safe_add_column(conn, 'appointments', 'timezone', "TEXT DEFAULT 'America/Edmonton'")
        safe_add_column(conn, 'appointments', 'provider_specialty', 'TEXT')
        safe_add_column(conn, 'appointments', 'clinic_location', 'TEXT')
        safe_add_column(conn, 'appointments', 'pil_instructions_received', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'pil_instruction_type', 'TEXT')
        safe_add_column(conn, 'appointments', 'report_deadline_date', 'DATE')
        safe_add_column(conn, 'appointments', 'report_due_date', 'DATE')
        safe_add_column(conn, 'appointments', 'confirmation_deposit_letter_sent', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'confirmation_deposit_letter_date', 'DATE')
        safe_add_column(conn, 'appointments', 'confirmation_sent_to_doctor', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'confirmation_sent_to_doctor_date', 'DATE')
        safe_add_column(conn, 'appointments', 'in_calendar', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'retainer_required', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'retainer_requested_on', 'DATE')
        safe_add_column(conn, 'appointments', 'retainer_received_on', 'DATE')
        safe_add_column(conn, 'appointments', 'retainer_amount', 'DECIMAL(10,2)')
        safe_add_column(conn, 'appointments', 'atp_requested_on', 'DATE')
        safe_add_column(conn, 'appointments', 'atp_received_on', 'DATE')
        safe_add_column(conn, 'appointments', 'loi_received', 'INTEGER DEFAULT 0')
        safe_add_column(conn, 'appointments', 'loi_received_date', 'DATE')
        
        # =====================================================================
        # ADDITIONAL SERVICES TABLE
        # =====================================================================
        print("\n[6] Additional Services Table...")
        if not table_exists(conn, 'appointment_additional_services'):
            conn.execute("""
                CREATE TABLE appointment_additional_services (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    appointment_id INTEGER NOT NULL,
                    service_type TEXT NOT NULL,
                    present INTEGER DEFAULT 0,
                    name TEXT,
                    company TEXT,
                    language TEXT,
                    travel_details TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
                )
            """)
            conn.execute("CREATE INDEX idx_appointment_additional_services_appointment_id ON appointment_additional_services(appointment_id)")
            print("  ✓ Created table appointment_additional_services")
        else:
            print("  - Table appointment_additional_services already exists")
        
        # =====================================================================
        # MEDICAL DOCUMENTATION TABLE
        # =====================================================================
        print("\n[7] Medical Documentation Table...")
        if not table_exists(conn, 'medical_documentation'):
            conn.execute("""
                CREATE TABLE medical_documentation (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_id INTEGER,
                    appointment_id INTEGER,
                    file_name TEXT NOT NULL,
                    file_path TEXT NOT NULL,
                    file_size INTEGER,
                    file_type TEXT,
                    receipt_method TEXT,
                    printed_required INTEGER DEFAULT 0,
                    waybill_tracking_number TEXT,
                    documents_received_date DATE,
                    amount_received DECIMAL(10,2),
                    document_type TEXT,
                    return_or_shred TEXT,
                    client_provided_waybill INTEGER DEFAULT 0,
                    clinic_chart INTEGER DEFAULT 0,
                    clinic_chart_sent INTEGER DEFAULT 0,
                    uploaded_by TEXT,
                    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (case_id) REFERENCES cases(id),
                    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
                )
            """)
            conn.execute("CREATE INDEX idx_medical_documentation_case_id ON medical_documentation(case_id)")
            conn.execute("CREATE INDEX idx_medical_documentation_appointment_id ON medical_documentation(appointment_id)")
            print("  ✓ Created table medical_documentation")
        else:
            print("  - Table medical_documentation already exists")
        
        # =====================================================================
        # APPOINTMENT MEDS TABLE
        # =====================================================================
        print("\n[8] Appointment Meds Table...")
        if not table_exists(conn, 'appointment_meds'):
            conn.execute("""
                CREATE TABLE appointment_meds (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    appointment_id INTEGER NOT NULL,
                    file_name TEXT NOT NULL,
                    file_path TEXT NOT NULL,
                    file_size INTEGER,
                    file_type TEXT,
                    description TEXT,
                    uploaded_by TEXT,
                    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
                )
            """)
            conn.execute("CREATE INDEX idx_appointment_meds_appointment_id ON appointment_meds(appointment_id)")
            print("  ✓ Created table appointment_meds")
        else:
            print("  - Table appointment_meds already exists")
        
        # =====================================================================
        # INVOICE/ACCOUNTING UPDATES
        # =====================================================================
        print("\n[9] Invoice/Accounting Updates...")
        if table_exists(conn, 'invoices'):
            safe_add_column(conn, 'invoices', 'appointment_reference', 'TEXT')
            safe_add_column(conn, 'invoices', 'client_name', 'TEXT')
            # specialist_id may already exist
            safe_add_column(conn, 'invoices', 'specialist_id', 'INTEGER')
            safe_add_column(conn, 'invoices', 'quantity', 'DECIMAL(10,2)')
            safe_add_column(conn, 'invoices', 'hourly_rate', 'DECIMAL(10,2)')
            safe_add_column(conn, 'invoices', 'due_date', 'DATE')
            safe_add_column(conn, 'invoices', 'due_date_option', 'TEXT')
            safe_add_column(conn, 'invoices', 'provider_fee', 'DECIMAL(10,2)')
            safe_add_column(conn, 'invoices', 'markup_percent', 'DECIMAL(5,2)')
            safe_add_column(conn, 'invoices', 'markup_amount', 'DECIMAL(10,2)')
            safe_add_column(conn, 'invoices', 'delivery_method', 'TEXT')
            safe_add_column(conn, 'invoices', 'additional_expenses', 'DECIMAL(10,2)')
            safe_add_column(conn, 'invoices', 'expenses_breakdown', 'TEXT')
            safe_add_column(conn, 'invoices', 'is_overdue', 'INTEGER DEFAULT 0')
            safe_add_column(conn, 'invoices', 'overdue_notification_sent', 'INTEGER DEFAULT 0')
            safe_add_column(conn, 'invoices', 'overdue_notification_date', 'DATE')
            safe_add_column(conn, 'invoices', 'report_sent', 'INTEGER DEFAULT 0')
            safe_add_column(conn, 'invoices', 'report_deadline_date', 'DATE')
            safe_add_column(conn, 'invoices', 'date_released', 'DATE')
        else:
            print("  - Invoices table does not exist, skipping invoice updates")
        
        # =====================================================================
        # INVOICE SERVICES TABLE
        # =====================================================================
        print("\n[10] Invoice Services Table...")
        if not table_exists(conn, 'invoice_services'):
            conn.execute("""
                CREATE TABLE invoice_services (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    invoice_id INTEGER NOT NULL,
                    service_code TEXT,
                    service_description TEXT,
                    quantity DECIMAL(10,2),
                    unit_price DECIMAL(10,2),
                    total_price DECIMAL(10,2),
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
                )
            """)
            conn.execute("CREATE INDEX idx_invoice_services_invoice_id ON invoice_services(invoice_id)")
            print("  ✓ Created table invoice_services")
        else:
            print("  - Table invoice_services already exists")
        
        # =====================================================================
        # DOCTOR FEES TABLE
        # =====================================================================
        print("\n[11] Doctor Fees Table...")
        if not table_exists(conn, 'doctor_fees'):
            conn.execute("""
                CREATE TABLE doctor_fees (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    doctor_id INTEGER NOT NULL,
                    fee_type TEXT NOT NULL,
                    service_code TEXT,
                    fee_amount DECIMAL(10,2),
                    trial_hourly_rate DECIMAL(10,2),
                    effective_date DATE,
                    notes TEXT,
                    file_path TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
                )
            """)
            conn.execute("CREATE INDEX idx_doctor_fees_doctor_id ON doctor_fees(doctor_id)")
            print("  ✓ Created table doctor_fees")
        else:
            print("  - Table doctor_fees already exists")
        
        # =====================================================================
        # REFERRAL FORMS TABLE
        # =====================================================================
        print("\n[12] Referral Forms Table...")
        if not table_exists(conn, 'referral_forms'):
            conn.execute("""
                CREATE TABLE referral_forms (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_id INTEGER NOT NULL,
                    file_name TEXT NOT NULL,
                    file_path TEXT NOT NULL,
                    file_size INTEGER,
                    file_type TEXT,
                    source TEXT,
                    uploaded_by TEXT,
                    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (case_id) REFERENCES cases(id)
                )
            """)
            conn.execute("CREATE INDEX idx_referral_forms_case_id ON referral_forms(case_id)")
            print("  ✓ Created table referral_forms")
        else:
            print("  - Table referral_forms already exists")
        
        # =====================================================================
        # BRING FORWARD TASKS TABLE
        # =====================================================================
        print("\n[13] Bring Forward Tasks Table...")
        if not table_exists(conn, 'bring_forward_tasks'):
            conn.execute("""
                CREATE TABLE bring_forward_tasks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_id INTEGER,
                    appointment_id INTEGER,
                    task_description TEXT NOT NULL,
                    due_date DATE,
                    status TEXT DEFAULT 'pending',
                    assigned_to TEXT,
                    created_by TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    completed_at TIMESTAMP,
                    notes TEXT,
                    FOREIGN KEY (case_id) REFERENCES cases(id),
                    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
                )
            """)
            conn.execute("CREATE INDEX idx_bring_forward_tasks_case_id ON bring_forward_tasks(case_id)")
            conn.execute("CREATE INDEX idx_bring_forward_tasks_due_date ON bring_forward_tasks(due_date)")
            conn.execute("CREATE INDEX idx_bring_forward_tasks_status ON bring_forward_tasks(status)")
            print("  ✓ Created table bring_forward_tasks")
        else:
            print("  - Table bring_forward_tasks already exists")
        
        # =====================================================================
        # ADDITIONAL INDEXES
        # =====================================================================
        print("\n[14] Additional Indexes...")
        try:
            conn.execute("CREATE INDEX IF NOT EXISTS idx_appointments_pil_instructions ON appointments(pil_instruction_type)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_appointments_report_deadline ON appointments(report_deadline_date)")
            if table_exists(conn, 'invoices'):
                conn.execute("CREATE INDEX IF NOT EXISTS idx_invoices_due_date ON invoices(due_date)")
                conn.execute("CREATE INDEX IF NOT EXISTS idx_invoices_is_overdue ON invoices(is_overdue)")
            print("  ✓ Created additional indexes")
        except Exception as e:
            print(f"  - Index creation: {e}")
        
        # Commit all changes
        conn.commit()
        print("\n" + "=" * 60)
        print("✓ Migration completed successfully!")
        print("=" * 60)
        
    except Exception as e:
        conn.rollback()
        print(f"\n✗ Migration failed: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)
    finally:
        conn.close()

if __name__ == '__main__':
    run_migration()