CREATE TABLE IF NOT EXISTS geriatric_care_manager_intakes (
    id BIGSERIAL PRIMARY KEY,
    patient_id BIGINT NOT NULL,
    intake_date DATE NOT NULL,
    care_manager_name VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'draft',
    functional_assessment JSONB DEFAULT '{}'::jsonb,
    cognitive_mood JSONB DEFAULT '{}'::jsonb,
    home_safety JSONB DEFAULT '{}'::jsonb,
    social_support_nutrition JSONB DEFAULT '{}'::jsonb,
    advance_care_planning JSONB DEFAULT '{}'::jsonb,
    summary_care_plan JSONB DEFAULT '{}'::jsonb,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_geriatric_intake_patient_id
    ON geriatric_care_manager_intakes (patient_id);

CREATE TABLE IF NOT EXISTS geriatric_intake_insurance (
    id BIGSERIAL PRIMARY KEY,
    intake_id BIGINT NOT NULL REFERENCES geriatric_care_manager_intakes(id) ON DELETE CASCADE,
    insurance_type VARCHAR(32) NOT NULL CHECK (insurance_type IN ('primary', 'secondary', 'government', 'ltc')),
    carrier VARCHAR(255),
    subscriber_name VARCHAR(255),
    policy_member_id VARCHAR(120),
    group_number VARCHAR(120),
    has_medicare BOOLEAN DEFAULT FALSE,
    has_medicaid BOOLEAN DEFAULT FALSE,
    ltc_policy_number VARCHAR(120),
    ltc_daily_benefit NUMERIC(10, 2),
    ltc_elimination_period VARCHAR(120),
    notes TEXT,
    imported BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_geriatric_insurance_intake_id
    ON geriatric_intake_insurance (intake_id);

CREATE TABLE IF NOT EXISTS geriatric_intake_medication_snapshot (
    id BIGSERIAL PRIMARY KEY,
    intake_id BIGINT NOT NULL REFERENCES geriatric_care_manager_intakes(id) ON DELETE CASCADE,
    medication_name VARCHAR(255) NOT NULL,
    dose VARCHAR(120),
    route VARCHAR(120),
    frequency VARCHAR(120),
    prescriber VARCHAR(255),
    status VARCHAR(80),
    imported BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS geriatric_intake_allergy_snapshot (
    id BIGSERIAL PRIMARY KEY,
    intake_id BIGINT NOT NULL REFERENCES geriatric_care_manager_intakes(id) ON DELETE CASCADE,
    allergen VARCHAR(255) NOT NULL,
    reaction VARCHAR(255),
    severity VARCHAR(80),
    status VARCHAR(80),
    notes TEXT,
    imported BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS geriatric_intake_diagnosis_snapshot (
    id BIGSERIAL PRIMARY KEY,
    intake_id BIGINT NOT NULL REFERENCES geriatric_care_manager_intakes(id) ON DELETE CASCADE,
    diagnosis VARCHAR(255) NOT NULL,
    icd10_code VARCHAR(32),
    status VARCHAR(80),
    onset_date DATE,
    notes TEXT,
    imported BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
