-- departments_migration.sql
-- Extends existing departments table, adds user-department assignments,
-- creates Athena Sandbox practice, populates 32 sandbox departments.
--
-- Existing departments table has: id, name (UNIQUE)
-- Used by: providers(department_id), locations(department_id)
-- Existing rows: PASS-TEST(1), PASS-MD(2), PASS-FL(3), PASS-PA(7)
--
-- Run: psql -U efsi kinosave -f /var/www/kinometric/sql/departments_migration.sql

BEGIN;

-- ============================================================================
-- 1. Extend departments table with practice and athena columns
-- ============================================================================

-- Add new columns (nullable for legacy rows)
ALTER TABLE departments ADD COLUMN IF NOT EXISTS practice_id INTEGER REFERENCES practices(id) ON DELETE CASCADE;
ALTER TABLE departments ADD COLUMN IF NOT EXISTS athena_department_id VARCHAR(20);
ALTER TABLE departments ADD COLUMN IF NOT EXISTS patient_facing_name VARCHAR(255);
ALTER TABLE departments ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE departments ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP;

-- Assign existing legacy departments to Default Practice (id=1)
UPDATE departments SET practice_id = 1 WHERE practice_id IS NULL;

-- Drop old name-only unique constraint, add practice-scoped uniqueness
ALTER TABLE departments DROP CONSTRAINT IF EXISTS departments_name_key;
-- Unique athena dept ID within a practice (for athena-linked departments)
CREATE UNIQUE INDEX IF NOT EXISTS idx_departments_practice_athena
    ON departments (practice_id, athena_department_id)
    WHERE athena_department_id IS NOT NULL;

-- ============================================================================
-- 2. Create user_practice_departments join table
--    Links a user's practice membership to specific departments.
--    If a user has NO rows here for a practice, they see ALL departments.
-- ============================================================================
CREATE TABLE IF NOT EXISTS user_practice_departments (
    id                SERIAL PRIMARY KEY,
    user_practice_id  INTEGER NOT NULL REFERENCES user_practices(id) ON DELETE CASCADE,
    department_id     INTEGER NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
    UNIQUE(user_practice_id, department_id)
);

-- ============================================================================
-- 3. Create "Athena Sandbox" practice with athena config from Default Practice
-- ============================================================================
INSERT INTO practices (name, slug, athena_practice_id, athena_client_id, athena_client_secret, athena_base_url, athena_default_dept)
SELECT 'Athena Sandbox', 'athena-sandbox',
       athena_practice_id, athena_client_id, athena_client_secret, athena_base_url, athena_default_dept
FROM practices WHERE id = 1;

-- ============================================================================
-- 4. Strip athena config from Default Practice (patients stay, no EMR)
-- ============================================================================
UPDATE practices
SET athena_practice_id = NULL,
    athena_client_id = NULL,
    athena_client_secret = NULL,
    athena_base_url = NULL,
    athena_default_dept = NULL
WHERE id = 1;

-- ============================================================================
-- 5. Add user memberships to Athena Sandbox
--    Steve: admin (sandbox is his testing ground)
--    efsi: admin (superadmin, needs access to both)
--    Dan: stays on Default Practice only
-- ============================================================================

-- Steve on Athena Sandbox (non-default — Default Practice remains his default)
INSERT INTO user_practices (user_id, practice_id, role, is_default)
SELECT 1, id, 'admin', false FROM practices WHERE slug = 'athena-sandbox';

-- efsi on Athena Sandbox (non-default)
INSERT INTO user_practices (user_id, practice_id, role, is_default)
SELECT 3, id, 'admin', false FROM practices WHERE slug = 'athena-sandbox';

-- ============================================================================
-- 6. Populate 32 sandbox departments from athenaOne sandbox (practice 195900)
-- ============================================================================
INSERT INTO departments (practice_id, athena_department_id, name, patient_facing_name)
SELECT p.id, v.athena_id, v.name, v.patient_facing_name
FROM practices p,
(VALUES
    ('1',   'Cruickshank HEALTH CARE',            'Rome Office - this is a test to see how long this field actually is. 75 ch'),
    ('21',  'MacGyver MED GROUP',                 'Cartersville Office-'),
    ('82',  'Murazik HEALTH CARE',                'Six Hills'),
    ('102', 'Blick HEATLH INC',                   'Cardiology Consults for 7 Hills'),
    ('142', 'Deckow INC',                         'Patient Face Name Test Department 47 College St'),
    ('143', 'Johns INC',                          'Johns INC'),
    ('144', 'Bailey HEALTH CARE',                 'Bailey HEALTH CARE'),
    ('145', 'Murray HEALTH GROUP',                'Shawnee Mission Health'),
    ('147', 'Wunsch HEATLH INC',                  'Ohio Health Primary Care Pickerington Office 1234'),
    ('148', 'Metz HEATLH INC',                    'Patient Face Name Test Department 47 College St'),
    ('149', 'Kub HEATLH INC',                     'Psych OP'),
    ('150', 'Gerlach HEATLH INC',                 'Gerlach HEATLH INC'),
    ('155', 'Schmitt LLC',                        'Schmitt LLC'),
    ('157', 'Jacobs ATHENA HEALTH CARE INC',      'CommOpsDevTest'),
    ('159', 'Cassin MED GROUP',                   'Cassin MED GROUP'),
    ('160', 'Koepp HEALTH LLC',                   'Koepp HEALTH LLC'),
    ('162', 'Kessler ATHENA HEALTH CARE INC',     'Main Office - NY'),
    ('163', 'Macejkovic HEALTH CARE',             'Seven Hills rholaday test ISE'),
    ('164', 'Bogisich LLC',                       'Bogisich LLC'),
    ('165', 'Spencer HEALTH LLC',                 'Spencer HEALTH LLC'),
    ('166', 'Lockman MED GROUP',                  'Training Dept Patient Facing Name'),
    ('168', 'Nitzsche MEDICAL CARE',              'Nitzsche MEDICAL CARE'),
    ('169', 'Swift INC',                          '16 11 testing'),
    ('170', 'Beer INC',                           '6 Hills Medical Group'),
    ('171', 'Senger LLC',                         'Senger LLC'),
    ('180', 'Gutmann LLC',                        'HCP TIN'),
    ('181', 'McKenzie HEALTH GROUP',              'Grantville Family Care'),
    ('183', 'Bechtelar INC',                      'Bechtelar INC'),
    ('184', 'Purdy HEATLH INC',                   'Purdy HEATLH INC'),
    ('185', 'Jones ATHENA HEALTH CARE INC',       'Jones ATHENA HEALTH CARE INC'),
    ('192', 'Hollywood',                          'Hollywood'),
    ('193', 'Boston Office',                      'Boston Office')
) AS v(athena_id, name, patient_facing_name)
WHERE p.slug = 'athena-sandbox';

COMMIT;
