ClientFlow Metrics

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'practitioner', -- 'practitioner' or 'client'
    full_name TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    owner_user_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL, -- The practitioner who owns the org
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    practitioner_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL, -- The practitioner managing this client
    client_user_id UUID REFERENCES users(id) ON DELETE SET NULL, -- Optional: If client has their own user account
    organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL, -- Optional: If client belongs to an organization
    name TEXT NOT NULL,
    email TEXT UNIQUE, -- Optional, if client_user_id is not set
    status TEXT DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE metric_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    practitioner_id UUID REFERENCES users(id) ON DELETE CASCADE NOT NULL,
    name TEXT NOT NULL,
    unit TEXT, -- e.g., 'kg', 'score', '%' 
    type TEXT NOT NULL, -- 'number', 'text', 'slider'
    min_value NUMERIC, -- For 'slider' type
    max_value NUMERIC, -- For 'slider' type
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE (practitioner_id, name) -- Ensure unique metric names per practitioner
);

CREATE TABLE client_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    metric_template_id UUID REFERENCES metric_templates(id) ON DELETE CASCADE NOT NULL,
    value TEXT NOT NULL, -- Store as text to accommodate numbers, text, or slider values
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_clients_practitioner_id ON clients(practitioner_id);
CREATE INDEX idx_metric_templates_practitioner_id ON metric_templates(practitioner_id);
CREATE INDEX idx_client_metrics_client_id ON client_metrics(client_id);
CREATE INDEX idx_client_metrics_metric_template_id ON client_metrics(metric_template_id);
CREATE INDEX idx_client_metrics_recorded_at ON client_metrics(recorded_at);