-- Vera Medical CRM - Normalized Database Schema
-- Based on VMdata_Schema_Analysis.md
-- Created: October 28, 2025

-- =============================================================================
-- TABLE: cases (Main table)
-- =============================================================================
CREATE TABLE IF NOT EXISTS cases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    record_id TEXT UNIQUE NOT NULL,  -- Original _Record_ID from CSV
    file_case_id TEXT,
    internal_case_id TEXT,
    
    -- Case Information
    assessment_type TEXT,  -- IME - Standard, IME - Legal
    case_notes TEXT,
    case_open_month TEXT,
    case_open_week TEXT,
    
    -- Dates
    file_open_date DATE,
    file_close_date DATE,
    date_of_loss DATE,
    create_date DATE,
    create_time TIME,
    update_date DATE,
    update_time TIME,
    
    -- Metrics
    file_open_to_first_appt INTEGER,
    date_released_to_file_closed INTEGER,
    appointment_exists INTEGER,  -- Boolean: 0 or 1
    
    -- Foreign Keys
    claimant_id INTEGER,
    lawyer_firm_id INTEGER,
    lawyer_contact_id INTEGER,
    client_id INTEGER,
    client_contact_id INTEGER,
    
    -- Audit
    created_by TEXT,
    updated_by TEXT,
    
    -- MYOB Fields
    client_file_number TEXT,
    myob_export_client_name TEXT,
    
    FOREIGN KEY (claimant_id) REFERENCES claimants(id),
    FOREIGN KEY (lawyer_firm_id) REFERENCES lawyer_firms(id),
    FOREIGN KEY (lawyer_contact_id) REFERENCES lawyer_contacts(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (client_contact_id) REFERENCES client_contacts(id)
);

-- =============================================================================
-- TABLE: claimants (Injured parties)
-- =============================================================================
CREATE TABLE IF NOT EXISTS claimants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- Personal Information
    first_name TEXT,
    last_name TEXT,
    full_name TEXT,
    title TEXT,  -- Mr., Ms., Mrs., etc.
    date_of_birth DATE,
    
    -- Contact Information
    address TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT,
    
    -- Phone Numbers
    home_phone TEXT,
    cel_phone TEXT,
    work_phone TEXT,
    work_extension TEXT,
    fax TEXT,
    
    -- Email
    email TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================================================
-- TABLE: lawyer_firms (Law firms representing claimants)
-- =============================================================================
CREATE TABLE IF NOT EXISTS lawyer_firms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_id TEXT,  -- Original Claimant Lawyer ID from CSV
    
    -- Firm Information
    firm_name TEXT NOT NULL,
    firm_alias TEXT,
    
    -- Contact Information
    address TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT,
    phone TEXT,
    fax TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(firm_name, city)
);

-- =============================================================================
-- TABLE: lawyer_contacts (Individual lawyers at firms)
-- =============================================================================
CREATE TABLE IF NOT EXISTS lawyer_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_id TEXT,  -- Original Lawyer Contact ID from CSV
    
    -- Personal Information
    first_name TEXT,
    last_name TEXT,
    salutation TEXT,  -- Mr., Ms., etc.
    title TEXT,
    
    -- Associated Firm
    lawyer_firm_id INTEGER,
    company_name TEXT,
    
    -- Status
    representation TEXT,
    status TEXT,  -- Active, No longer there, etc.
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lawyer_firm_id) REFERENCES lawyer_firms(id)
);

-- =============================================================================
-- TABLE: clients (Insurance companies)
-- =============================================================================
CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_id TEXT,  -- Original Client ID from CSV
    
    -- Company Information
    company_name TEXT NOT NULL,
    company_alias TEXT,
    
    -- Contact Information
    address TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT,
    country TEXT,
    
    -- Phone
    assistant_phone TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(company_name)
);

-- =============================================================================
-- TABLE: client_contacts (Insurance adjusters/contacts)
-- =============================================================================
CREATE TABLE IF NOT EXISTS client_contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_id TEXT,  -- Original Client Contact ID from CSV
    
    -- Personal Information
    first_name TEXT,
    last_name TEXT,
    display_name TEXT,
    salutation TEXT,  -- Mr., Ms., etc.
    title TEXT,
    
    -- Contact Information
    phone TEXT,
    
    -- Associated Client
    client_id INTEGER,
    
    -- Status
    status TEXT,  -- Active, No longer here, etc.
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

-- =============================================================================
-- INDEXES for Performance
-- =============================================================================

-- Cases indexes
CREATE INDEX IF NOT EXISTS idx_cases_record_id ON cases(record_id);
CREATE INDEX IF NOT EXISTS idx_cases_file_case_id ON cases(file_case_id);
CREATE INDEX IF NOT EXISTS idx_cases_internal_case_id ON cases(internal_case_id);
CREATE INDEX IF NOT EXISTS idx_cases_file_open_date ON cases(file_open_date);
CREATE INDEX IF NOT EXISTS idx_cases_file_close_date ON cases(file_close_date);
CREATE INDEX IF NOT EXISTS idx_cases_claimant_id ON cases(claimant_id);
CREATE INDEX IF NOT EXISTS idx_cases_client_id ON cases(client_id);

-- Claimants indexes
CREATE INDEX IF NOT EXISTS idx_claimants_last_name ON claimants(last_name);
CREATE INDEX IF NOT EXISTS idx_claimants_email ON claimants(email);

-- Lawyer firms indexes
CREATE INDEX IF NOT EXISTS idx_lawyer_firms_name ON lawyer_firms(firm_name);
CREATE INDEX IF NOT EXISTS idx_lawyer_firms_original_id ON lawyer_firms(original_id);

-- Lawyer contacts indexes
CREATE INDEX IF NOT EXISTS idx_lawyer_contacts_last_name ON lawyer_contacts(last_name);
CREATE INDEX IF NOT EXISTS idx_lawyer_contacts_firm ON lawyer_contacts(lawyer_firm_id);
CREATE INDEX IF NOT EXISTS idx_lawyer_contacts_original_id ON lawyer_contacts(original_id);

-- Clients indexes
CREATE INDEX IF NOT EXISTS idx_clients_name ON clients(company_name);
CREATE INDEX IF NOT EXISTS idx_clients_original_id ON clients(original_id);

-- Client contacts indexes
CREATE INDEX IF NOT EXISTS idx_client_contacts_last_name ON client_contacts(last_name);
CREATE INDEX IF NOT EXISTS idx_client_contacts_client ON client_contacts(client_id);
CREATE INDEX IF NOT EXISTS idx_client_contacts_original_id ON client_contacts(original_id);

-- =============================================================================
-- VIEWS for Common Queries
-- =============================================================================

-- Full case view with all related information
CREATE VIEW IF NOT EXISTS v_cases_full AS
SELECT 
    c.id,
    c.record_id,
    c.file_case_id,
    c.internal_case_id,
    c.assessment_type,
    c.case_notes,
    c.file_open_date,
    c.file_close_date,
    c.date_of_loss,
    c.appointment_exists,
    c.claimant_id,
    c.client_id,
    
    -- Claimant info
    cl.first_name AS claimant_first_name,
    cl.last_name AS claimant_last_name,
    cl.full_name AS claimant_full_name,
    cl.city AS claimant_city,
    COALESCE(cl.home_phone, cl.cel_phone) AS claimant_phone,
    
    -- Lawyer firm info
    lf.firm_name AS lawyer_firm_name,
    lf.city AS lawyer_firm_city,
    
    -- Lawyer contact info
    lc.first_name AS lawyer_first_name,
    lc.last_name AS lawyer_last_name,
    
    -- Client info
    cli.company_name AS client_company_name,
    
    -- Client contact info
    cc.first_name AS adjuster_first_name,
    cc.last_name AS adjuster_last_name,
    cc.display_name AS adjuster_display_name
    
FROM cases c
LEFT JOIN claimants cl ON c.claimant_id = cl.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 clients cli ON c.client_id = cli.id
LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id;

-- Active cases (not closed)
CREATE VIEW IF NOT EXISTS v_active_cases AS
SELECT * FROM v_cases_full
WHERE file_close_date IS NULL OR file_close_date = '';

-- Cases by client
CREATE VIEW IF NOT EXISTS v_cases_by_client AS
SELECT 
    cli.id AS client_id,
    cli.company_name,
    COUNT(c.id) AS case_count,
    SUM(CASE WHEN c.file_close_date IS NULL OR c.file_close_date = '' THEN 1 ELSE 0 END) AS active_cases,
    SUM(CASE WHEN c.file_close_date IS NOT NULL AND c.file_close_date != '' THEN 1 ELSE 0 END) AS closed_cases
FROM cases c
LEFT JOIN clients cli ON c.client_id = cli.id
GROUP BY cli.id, cli.company_name
ORDER BY case_count DESC;

