-- Multi-Practice (Multi-Tenant) Migration
-- Run: psql -U efsi -d kinosave -f /var/www/kinometric/sql/multi_practice_migration.sql
--
-- Creates: practices, user_practices tables
-- Adds: practice_id to patients, test_results, questions
-- Migrates: all existing data to "Default Practice" (id=1)

BEGIN;

-- 1A. Create practices table
CREATE TABLE IF NOT EXISTS practices (
    id                   SERIAL PRIMARY KEY,
    name                 VARCHAR(255) NOT NULL,
    slug                 VARCHAR(100) UNIQUE,
    address              TEXT,
    city                 VARCHAR(100),
    state                VARCHAR(2),
    zip                  VARCHAR(10),
    phone                VARCHAR(20),
    athena_practice_id   VARCHAR(50),
    athena_client_id     VARCHAR(255),
    athena_client_secret VARCHAR(255),
    athena_base_url      VARCHAR(255) DEFAULT 'https://api.preview.platform.athenahealth.com',
    athena_default_dept  VARCHAR(10) DEFAULT '1',
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at           TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 1B. Create user_practices table
CREATE TABLE IF NOT EXISTS user_practices (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    practice_id INTEGER NOT NULL REFERENCES practices(id) ON DELETE CASCADE,
    role        VARCHAR(20) NOT NULL DEFAULT 'clinician'
                CHECK (role IN ('admin', 'clinician', 'readonly')),
    is_default  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, practice_id)
);

-- Partial unique index: each user can have only one default practice
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_practices_default
    ON user_practices(user_id) WHERE is_default = TRUE;

-- 1C. Add practice_id columns (nullable first for migration)
ALTER TABLE patients ADD COLUMN IF NOT EXISTS practice_id INTEGER REFERENCES practices(id);
ALTER TABLE test_results ADD COLUMN IF NOT EXISTS practice_id INTEGER REFERENCES practices(id);
ALTER TABLE questions ADD COLUMN IF NOT EXISTS practice_id INTEGER REFERENCES practices(id);

-- Indexes for practice-scoped queries
CREATE INDEX IF NOT EXISTS idx_patients_practice ON patients(practice_id);
CREATE INDEX IF NOT EXISTS idx_test_results_practice ON test_results(practice_id);
CREATE INDEX IF NOT EXISTS idx_questions_practice ON questions(practice_id);

-- 1D. Data migration

-- Insert Default Practice with Athena config from athena_config.json
INSERT INTO practices (id, name, slug, athena_practice_id, athena_client_id, athena_client_secret, athena_base_url, athena_default_dept)
VALUES (
    1,
    'Default Practice',
    'default',
    '195900',
    '0oa10vmfcm5Q5x52b298',
    'k9rsrQqW4RkZw4bT6Ai0BvlrrIkj3qHOO1MrU6QSOk-w-ImPAPc0AROfAdTYBNa4',
    'https://api.preview.platform.athenahealth.com',
    '1'
) ON CONFLICT (id) DO NOTHING;

-- Reset sequence to avoid conflicts on next insert
SELECT setval('practices_id_seq', (SELECT COALESCE(MAX(id), 0) FROM practices));

-- Assign all patients to Default Practice
UPDATE patients SET practice_id = 1 WHERE practice_id IS NULL;

-- Assign all test_results based on their patient's practice
UPDATE test_results SET practice_id = (
    SELECT practice_id FROM patients WHERE patient_id = test_results.patient_id
) WHERE practice_id IS NULL;

-- Assign all questions based on their patient's practice (with fallback to 1)
UPDATE questions SET practice_id = COALESCE(
    (SELECT practice_id FROM patients WHERE patient_id = questions.patient_id),
    1
) WHERE practice_id IS NULL;

-- Insert user_practices for all existing users
-- steve(1) = admin, Dan(2) = clinician, efsi(3) = admin
INSERT INTO user_practices (user_id, practice_id, role, is_default) VALUES
    (1, 1, 'admin', TRUE),
    (2, 1, 'clinician', TRUE),
    (3, 1, 'admin', TRUE)
ON CONFLICT (user_id, practice_id) DO NOTHING;

-- Now enforce NOT NULL on practice_id columns
ALTER TABLE patients ALTER COLUMN practice_id SET NOT NULL;
ALTER TABLE test_results ALTER COLUMN practice_id SET NOT NULL;
ALTER TABLE questions ALTER COLUMN practice_id SET NOT NULL;

-- Verify migration
DO $$
DECLARE
    null_patients INTEGER;
    null_tests INTEGER;
    null_questions INTEGER;
    membership_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO null_patients FROM patients WHERE practice_id IS NULL;
    SELECT COUNT(*) INTO null_tests FROM test_results WHERE practice_id IS NULL;
    SELECT COUNT(*) INTO null_questions FROM questions WHERE practice_id IS NULL;
    SELECT COUNT(*) INTO membership_count FROM user_practices;

    IF null_patients > 0 OR null_tests > 0 OR null_questions > 0 THEN
        RAISE EXCEPTION 'Migration incomplete: % null patients, % null tests, % null questions',
            null_patients, null_tests, null_questions;
    END IF;

    RAISE NOTICE 'Migration OK: 0 nulls, % user_practices rows', membership_count;
END $$;

COMMIT;
