Entity Creation Table Mapping Guide

-- Step 0: Create tenant (MUST EXIST before anything else)
INSERT INTO identity.tenants (
  id, name, slug, status, plan_tier
) VALUES (
  'apollo-tenant-123',
  'Apollo Hospitals Group',
  'apollo-hospitals',
  'active',
  'enterprise'
);

-- Step 0: Tenant MUST exist (created separately)
-- See "Tenant Creation" section above

-- Step 1: Create user account
INSERT INTO identity.users (
  id, email, phone, first_name, last_name, is_active
) VALUES (
  'user-sarah-123',
  'sarah.johnson@hospital.com',
  '+919876543210',
  'Sarah',
  'Johnson',
  true
);

-- Step 2: Create practitioner record (GLOBAL entity - no tenant_id)
INSERT INTO identity.practitioners (
  id, user_id, first_name, last_name, gender,
  identifiers, qualifications, is_active
) VALUES (
  'practitioner-sarah-123',
  'user-sarah-123',
  'Sarah',
  'Johnson',
  'female',
  '[{"system": "mci", "value": "12345-KA-2010"}]'::jsonb,
  '[{"code": "MBBS", "issuer": "Bangalore Medical College", "year": 2010}]'::jsonb,
  true
);

-- Step 3: Create tenant membership (links user to tenant)
INSERT INTO identity.user_memberships (
  user_id, tenant_id, role, status
) VALUES (
  'user-sarah-123',
  'apollo-tenant-456',  -- REQUIRED: links to tenant
  'practitioner',
  'active'
);

-- Step 4: Create facility role assignment (links practitioner to facility)
-- NOTE: This step requires facility to exist, but can be done later
INSERT INTO identity.practitioner_roles (
  tenant_id, practitioner_id, facility_id, role_type,
  specialty_id, is_primary, is_active
) VALUES (
  'apollo-tenant-456',
  'practitioner-sarah-123',
  'facility-bannerghatta-789',  -- Facility must exist
  'physician',
  'specialty-cardiology-101',
  true,
  true
);

POST /identity/practitioners
{
  "tenant_id": "apollo-tenant-123",  # ✅ REQUIRED - cannot be omitted
  "user": {
    "email": "sarah.johnson@hospital.com",
    "phone": "+919876543210",
    "first_name": "Sarah",
    "last_name": "Johnson"
  },
  "practitioner": {
    "gender": "female",
    "identifiers": [
      {"system": "mci", "value": "12345-KA-2010"}
    ],
    "qualifications": [
      {"code": "MBBS", "issuer": "Bangalore Medical College", "year": 2010}
    ]
  },
  "roles": [  # Optional - can be created separately via PractitionerRole API
    {
      "facility_id": "facility-bannerghatta-789",
      "role_type": "physician",
      "specialty_id": "specialty-cardiology-101",
      "is_primary": true
    }
  ]
}

# Backend MUST enforce these rules:
1. tenant_id presence: true  # REQUIRED field
2. tenant must exist in identity.tenants
3. user email must be unique across all tenants
4. practitioner record created without tenant_id (global entity)
5. user_membership created to link user to tenant
6. practitioner_roles only created if facility exists

-- Step 0: Tenant MUST exist (created separately)
-- See "Tenant Creation" section above

-- Step 1: Create organization (legal entity)
INSERT INTO identity.organizations (
  id, tenant_id, name, organization_type, is_active
) VALUES (
  'org-apollo-main',
  'apollo-tenant-123',  -- REQUIRED: links to tenant
  'Apollo Hospital System',
  'hospital_system',
  true
);

-- Step 2: Create facility (physical location)
INSERT INTO identity.facilities (
  id, tenant_id, organization_id, name, service_level,
  bed_count, has_opd, has_ipd, operational_status
) VALUES (
  'facility-bannerghatta-456',
  'apollo-tenant-123',           -- REQUIRED: links to tenant
  'org-apollo-main',             -- REQUIRED: links to organization
  'Apollo Hospital Bannerghatta',
  'opd_ipd_surgery',
  350,
  true,
  true,
  'active'
);

-- Step 3: Create facility location (address)
INSERT INTO identity.facility_locations (
  id, tenant_id, facility_id, name, location_type,
  address_line_1, city, state, postal_code, country_code,
  is_primary, is_active
) VALUES (
  'location-bannerghatta-main-789',
  'apollo-tenant-123',           -- REQUIRED: links to tenant
  'facility-bannerghatta-456',   -- REQUIRED: links to facility
  'Main Campus',
  'building',
  '154/11, Bannerghatta Road',
  'Bangalore',
  'Karnataka',
  '560076',
  'IN',
  true,
  true
);

POST /identity/facilities
{
  "tenant_id": "apollo-tenant-123",      # ✅ REQUIRED - cannot be omitted
  "organization_id": "org-apollo-456",    # ✅ REQUIRED - cannot be omitted
  "facility": {
    "name": "Apollo Hospital Bannerghatta",
    "service_level": "opd_ipd_surgery",
    "bed_count": 350,
    "has_opd": true,
    "has_ipd": true
  },
  "location": {
    "name": "Main Campus",
    "address_line_1": "154/11, Bannerghatta Road",
    "city": "Bangalore",
    "state": "Karnataka",
    "postal_code": "560076",
    "country_code": "IN",
    "is_primary": true
  }
}

# Backend MUST enforce these rules:
1. tenant_id presence: true       # REQUIRED field
2. organization_id presence: true # REQUIRED field
3. tenant must exist in identity.tenants
4. organization must exist and belong to tenant
5. NO practitioner validation required

✅ Facility can be created WITHOUT any practitioners
✅ Practitioner can be created WITHOUT any facilities
✅ PractitionerRole links them together (created separately)

-- Step 0: Tenant and Facility MUST exist (created separately)

-- Step 1: Create user account (optional - for patient portal)
INSERT INTO identity.users (
  id, email, phone, first_name, last_name, is_active
) VALUES (
  'user-raj-123',
  'raj.kumar@gmail.com',
  '+919876543211',
  'Raj',
  'Kumar',
  true
);

-- Step 2: Create patient record (TENANT SCOPED)
INSERT INTO clinical.patients (
  id, tenant_id, user_id, mrn, first_name, last_name,
  gender, birth_date, blood_type, is_active
) VALUES (
  'patient-raj-456',
  'apollo-tenant-789',  -- REQUIRED: links to tenant
  'user-raj-123',       -- Optional: links to user
  'APOLLO-2024-001234',
  'Raj',
  'Kumar',
  'male',
  '1980-03-20',
  'O+',
  true
);

-- Step 3: Link patient to facility
INSERT INTO clinical.patient_facilities (
  patient_id, facility_id, registration_date, is_primary
) VALUES (
  'patient-raj-456',
  'facility-bannerghatta-101',  -- REQUIRED: links to facility
  '2026-01-15',
  true
);

TENANT (Foundation)
    ↓
    ├─→ ORGANIZATION (Legal Entity)
    │       ↓
    │   FACILITY (Physical Location)
    │       ↓
    │   FACILITY LOCATION (Address/Contact)
    │
    └─→ USER (Login Account)
            ↓
        PRACTITIONER (Global Profile)
            ↓
        USER MEMBERSHIP (Tenant Access)
            ↓
        PRACTITIONER ROLE (Links to Facility)