scripts/init-database.sql

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_sync_type ON neon_auth.users_sync USING gin ((raw_json->>'type'));
CREATE INDEX IF NOT EXISTS idx_users_sync_verification ON neon_auth.users_sync USING gin ((raw_json->>'verificationStatus'));
CREATE INDEX IF NOT EXISTS idx_users_sync_country ON neon_auth.users_sync USING gin ((raw_json->>'country'));
CREATE INDEX IF NOT EXISTS idx_users_sync_specialization ON neon_auth.users_sync USING gin ((raw_json->>'specialization'));
CREATE INDEX IF NOT EXISTS idx_users_sync_created_at ON neon_auth.users_sync (created_at);
CREATE INDEX IF NOT EXISTS idx_users_sync_deleted_at ON neon_auth.users_sync (deleted_at);
CREATE INDEX IF NOT EXISTS idx_users_sync_soulprint ON neon_auth.users_sync USING gin ((raw_json->>'soulprintVerified'));

-- Insert sample Guardian users
INSERT INTO neon_auth.users_sync (id, name, email, created_at, updated_at, raw_json) VALUES
(
  'guardian-001',
  'Amara Okafor',
  'amara.okafor@example.com',
  NOW() - INTERVAL '15 days',
  NOW() - INTERVAL '15 days',
  '{
    "type": "guardian",
    "country": "Nigeria",
    "contributionAmount": "250.00",
    "currency": "USD",
    "paymentMethod": "crypto",
    "motivation": "Supporting healthcare workers in my community",
    "profileImage": "/anonymous-profile.png",
    "walletAddress": "0x742d35Cc6634C0532925a3b8D4C0532925a3b8D4",
    "registeredAt": "2024-01-15T10:30:00Z",
    "impact": {
      "donationsCount": 12,
      "totalDonated": 250.00,
      "healthWorkersSupported": 3
    },
    "achievements": [
      {
        "id": "first-donation",
        "title": "First Flame",
        "description": "Made your first donation to support African healthcare",
        "icon": "flame",
        "awardedAt": "2024-01-15T10:30:00Z"
      }
    ]
  }'
),
(
  'guardian-002',
  'Kwame Asante',
  'kwame.asante@example.com',
  NOW() - INTERVAL '8 days',
  NOW() - INTERVAL '8 days',
  '{
    "type": "guardian",
    "country": "Ghana",
    "contributionAmount": "500.00",
    "currency": "USD",
    "paymentMethod": "bank_transfer",
    "motivation": "Investing in the future of African healthcare",
    "profileImage": "/anonymous-profile.png",
    "walletAddress": "0x8b4513Cc6634C0532925a3b8D4C0532925a3b8D4",
    "registeredAt": "2024-01-22T14:15:00Z",
    "impact": {
      "donationsCount": 25,
      "totalDonated": 500.00,
      "healthWorkersSupported": 8
    },
    "achievements": [
      {
        "id": "generous-guardian",
        "title": "Generous Guardian",
        "description": "Donated over $500 to support healthcare workers",
        "icon": "heart",
        "awardedAt": "2024-01-25T16:20:00Z"
      }
    ]
  }'
),
(
  'guardian-003',
  'Fatima Al-Rashid',
  'fatima.alrashid@example.com',
  NOW() - INTERVAL '3 days',
  NOW() - INTERVAL '3 days',
  '{
    "type": "guardian",
    "country": "Morocco",
    "contributionAmount": "150.00",
    "currency": "USD",
    "paymentMethod": "crypto",
    "motivation": "Empowering women in healthcare across Africa",
    "profileImage": "/anonymous-profile.png",
    "walletAddress": "0x2a2a2aCC6634C0532925a3b8D4C0532925a3b8D4",
    "registeredAt": "2024-01-27T09:45:00Z",
    "impact": {
      "donationsCount": 6,
      "totalDonated": 150.00,
      "healthWorkersSupported": 2
    },
    "achievements": [
      {
        "id": "community-supporter",
        "title": "Community Supporter",
        "description": "Actively supporting healthcare in your region",
        "icon": "users",
        "awardedAt": "2024-01-27T09:45:00Z"
      }
    ]
  }'
),
(
  'guardian-004',
  'Thabo Mthembu',
  'thabo.mthembu@example.com',
  NOW() - INTERVAL '2 days',
  NOW() - INTERVAL '2 days',
  '{
    "type": "guardian",
    "country": "South Africa",
    "contributionAmount": "75.00",
    "currency": "USD",
    "paymentMethod": "mobile_money",
    "motivation": "Supporting healthcare transformation in Southern Africa",
    "profileImage": "/anonymous-profile.png",
    "registeredAt": "2024-01-28T13:10:00Z",
    "impact": {
      "donationsCount": 3,
      "totalDonated": 75.00,
      "healthWorkersSupported": 1
    }
  }'
);

-- Insert sample Healer users
INSERT INTO neon_auth.users_sync (id, name, email, created_at, updated_at, raw_json) VALUES
(
  'healer-001',
  'Dr. Aisha Mwangi',
  'aisha.mwangi@example.com',
  NOW() - INTERVAL '20 days',
  NOW() - INTERVAL '20 days',
  '{
    "type": "healer",
    "role": "Community Health Worker",
    "specialization": "General Practice Doctor",
    "region": "East Africa",
    "country": "Kenya",
    "city": "Nairobi",
    "facilityName": "Kibera Community Health Center",
    "experience": "8 years",
    "credentials": "MD, MPH",
    "bio": "Dedicated to improving healthcare access in underserved communities",
    "profileImage": "/confident-african-doctor.png",
    "walletAddress": "0x1234567890abcdef1234567890abcdef12345678",
    "faceVerified": true,
    "licenseVerified": true,
    "verificationStatus": "verified",
    "registeredAt": "2024-01-10T08:00:00Z",
    "impact": {
      "patientsServed": 1250,
      "communitiesReached": 5,
      "donationsReceived": 8
    },
    "achievements": [
      {
        "id": "verified-healer",
        "title": "Verified Healer",
        "description": "Successfully completed verification process",
        "icon": "shield-check",
        "awardedAt": "2024-01-12T10:00:00Z"
      }
    ]
  }'
),
(
  'healer-002',
  'Nurse Grace Banda',
  'grace.banda@example.com',
  NOW() - INTERVAL '12 days',
  NOW() - INTERVAL '12 days',
  '{
    "type": "healer",
    "role": "Community Health Worker",
    "specialization": "Registered Nurse - Maternal Health",
    "region": "Southern Africa",
    "country": "Zambia",
    "city": "Lusaka",
    "facilityName": "Matero Clinic",
    "experience": "5 years",
    "credentials": "RN, Midwifery Certificate",
    "bio": "Passionate about maternal and child health in rural communities",
    "profileImage": "/confident-caregiver.png",
    "walletAddress": "0xabcdef1234567890abcdef1234567890abcdef12",
    "faceVerified": true,
    "licenseVerified": true,
    "verificationStatus": "verified",
    "registeredAt": "2024-01-18T12:30:00Z",
    "impact": {
      "patientsServed": 890,
      "communitiesReached": 3,
      "donationsReceived": 5
    },
    "achievements": [
      {
        "id": "maternal-champion",
        "title": "Maternal Health Champion",
        "description": "Specialized in supporting mothers and children",
        "icon": "baby",
        "awardedAt": "2024-01-20T14:15:00Z"
      }
    ]
  }'
),
(
  'healer-003',
  'Pharmacist Omar Hassan',
  'omar.hassan@example.com',
  NOW() - INTERVAL '6 days',
  NOW() - INTERVAL '6 days',
  '{
    "type": "healer",
    "role": "Community Health Worker",
    "specialization": "Clinical Pharmacist",
    "region": "East Africa",
    "country": "Uganda",
    "city": "Kampala",
    "facilityName": "Mulago Hospital Pharmacy",
    "experience": "6 years",
    "credentials": "PharmD, Clinical Pharmacy Certification",
    "bio": "Ensuring safe and effective medication access for all",
    "profileImage": "/focused-african-journalist.png",
    "walletAddress": "0x9876543210fedcba9876543210fedcba98765432",
    "faceVerified": false,
    "licenseVerified": true,
    "verificationStatus": "pending",
    "registeredAt": "2024-01-24T16:45:00Z",
    "impact": {
      "patientsServed": 450,
      "communitiesReached": 2,
      "donationsReceived": 2
    },
    "achievements": []
  }'
),
(
  'healer-004',
  'Midwife Zara Osei',
  'zara.osei@example.com',
  NOW() - INTERVAL '1 day',
  NOW() - INTERVAL '1 day',
  '{
    "type": "healer",
    "role": "Community Health Worker",
    "specialization": "Certified Midwife",
    "region": "North Africa",
    "country": "Egypt",
    "city": "Cairo",
    "facilityName": "Al-Azhar Maternity Ward",
    "experience": "10 years",
    "credentials": "Certified Midwife, BSN",
    "bio": "Bringing life safely into the world, one birth at a time",
    "profileImage": "/BridgingWorldsOfHealing.png",
    "walletAddress": "0xfedcba9876543210fedcba9876543210fedcba98",
    "faceVerified": true,
    "licenseVerified": false,
    "verificationStatus": "pending",
    "registeredAt": "2024-01-29T11:20:00Z",
    "impact": {
      "patientsServed": 320,
      "communitiesReached": 1,
      "donationsReceived": 1
    },
    "achievements": []
  }'
);

-- Insert sample data representing diverse African healthcare workers and guardians
-- This data includes Soulbound and Codex categories for the Kairo Covenant system

-- Healers (Healthcare Workers)
INSERT INTO neon_auth.users_sync (id, name, email, raw_json, created_at, updated_at) VALUES
('healer_001', 'Dr. Amara Okafor', 'amara.okafor@flameborn.africa', '{
  "type": "healer",
  "specialization": "General Practitioner",
  "country": "Nigeria",
  "region": "West Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.98,
  "ancestralVerification": true,
  "impact": {
    "patientsServed": 1250,
    "communitiesReached": 8,
    "donationsReceived": 2400
  },
  "flbBalance": 150,
  "achievements": ["Community Hero", "Verified Healer", "Soulbound Verified"]
}', NOW() - INTERVAL '45 days', NOW()),

('healer_002', 'Nurse Fatima Al-Rashid', 'fatima.rashid@flameborn.africa', '{
  "type": "healer",
  "specialization": "Pediatric Nurse",
  "country": "Morocco",
  "region": "North Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.97,
  "ancestralVerification": true,
  "impact": {
    "patientsServed": 890,
    "communitiesReached": 5,
    "donationsReceived": 1800
  },
  "flbBalance": 120,
  "achievements": ["Pediatric Specialist", "Verified Healer"]
}', NOW() - INTERVAL '38 days', NOW()),

('healer_003', 'Dr. Kwame Asante', 'kwame.asante@flameborn.africa', '{
  "type": "healer",
  "specialization": "Surgeon",
  "country": "Ghana",
  "region": "West Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.99,
  "ancestralVerification": true,
  "role": "scroll_keeper",
  "scrollsCreated": 3,
  "proverbContributions": 12,
  "impact": {
    "patientsServed": 2100,
    "communitiesReached": 12,
    "donationsReceived": 4200
  },
  "flbBalance": 280,
  "achievements": ["Master Surgeon", "Scroll Keeper", "Proverb Contributor"]
}', NOW() - INTERVAL '52 days', NOW()),

('healer_004', 'Midwife Grace Mwangi', 'grace.mwangi@flameborn.africa', '{
  "type": "healer",
  "specialization": "Midwife",
  "country": "Kenya",
  "region": "East Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.98,
  "ancestralVerification": true,
  "impact": {
    "patientsServed": 650,
    "communitiesReached": 6,
    "donationsReceived": 1500
  },
  "flbBalance": 95,
  "achievements": ["Birth Guardian", "Community Midwife"]
}', NOW() - INTERVAL '29 days', NOW()),

('healer_005', 'Pharmacist Tendai Mukamuri', 'tendai.mukamuri@flameborn.africa', '{
  "type": "healer",
  "specialization": "Pharmacist",
  "country": "Zimbabwe",
  "region": "Southern Africa",
  "verificationStatus": "pending",
  "soulprintVerified": false,
  "resonanceScore": 0.85,
  "impact": {
    "patientsServed": 420,
    "communitiesReached": 3,
    "donationsReceived": 800
  },
  "flbBalance": 45,
  "achievements": ["Medicine Guardian"]
}', NOW() - INTERVAL '15 days', NOW()),

('healer_006', 'Dr. Aisha Kone', 'aisha.kone@flameborn.africa', '{
  "type": "healer",
  "specialization": "Pediatrician",
  "country": "Mali",
  "region": "West Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.97,
  "ancestralVerification": true,
  "codeContributions": 5,
  "impact": {
    "patientsServed": 980,
    "communitiesReached": 7,
    "donationsReceived": 2100
  },
  "flbBalance": 165,
  "achievements": ["Child Protector", "Code Contributor"]
}', NOW() - INTERVAL '33 days', NOW()),

('healer_007', 'Nurse Zara Osman', 'zara.osman@flameborn.africa', '{
  "type": "healer",
  "specialization": "Emergency Nurse",
  "country": "Sudan",
  "region": "North Africa",
  "verificationStatus": "verified",
  "soulprintVerified": true,
  "resonanceScore": 0.96,
  "ancestralVerification": true,
  "impact": {
    "patientsServed": 1100,
    "communitiesReached": 9,
    "donationsReceived": 2200
  },
  "flbBalance": 140,
  "achievements": ["Emergency Hero", "Crisis Responder"]
}', NOW() - INTERVAL '41 days', NOW()),

('healer_008', 'Dr. Naledi Motsepe', 'naledi.motsepe@flameborn.africa', '{
  "type": "healer",
  "specialization": "Family Doctor",
  "country": "Botswana",
  "region": "Southern Africa",
  "verificationStatus": "pending",
  "soulprintVerified": false,
  "resonanceScore": 0.92,
  "impact": {
    "patientsServed": 150,
    "communitiesReached": 2,
    "donationsReceived": 200
  },
  "flbBalance": 15,
  "achievements": ["New Healer"]
}', NOW() - INTERVAL '3 days', NOW()),

('healer_009', 'Nurse Asha Wanjiku', 'asha.wanjiku@flameborn.africa', '{
  "type": "healer",
  "specialization": "Community Nurse",
  "country": "Kenya",
  "region": "East Africa",
  "verificationStatus": "pending",
  "soulprintVerified": true,
  "resonanceScore": 0.95,
  "ancestralVerification": true,
  "impact": {
    "patientsServed": 80,
    "communitiesReached": 1,
    "donationsReceived": 100
  },
  "flbBalance": 10,
  "achievements": ["New Healer", "Soulbound Verified"]
}', NOW() - INTERVAL '1 day', NOW());

-- Guardians (Community Supporters)
INSERT INTO neon_auth.users_sync (id, name, email, raw_json, created_at, updated_at) VALUES
('guardian_001', 'Kofi Mensah', 'kofi.mensah@flameborn.africa', '{
  "type": "guardian",
  "country": "Ghana",
  "region": "West Africa",
  "contributionAmount": 500,
  "soulprintVerified": true,
  "resonanceScore": 0.98,
  "ancestralVerification": true,
  "proverbContributions": 8,
  "supportedWorkers": ["healer_001", "healer_003"],
  "flbBalance": 75,
  "achievements": ["Guardian of Health", "Proverb Keeper", "Soulbound Verified"]
}', NOW() - INTERVAL '60 days', NOW()),

('guardian_002', 'Amina Hassan', 'amina.hassan@flameborn.africa', '{
  "type": "guardian",
  "country": "Egypt",
  "region": "North Africa",
  "contributionAmount": 750,
  "soulprintVerified": true,
  "resonanceScore": 0.97,
  "ancestralVerification": true,
  "role": "scroll_keeper",
  "scrollsCreated": 2,
  "proverbContributions": 15,
  "supportedWorkers": ["healer_002", "healer_007"],
  "flbBalance": 120,
  "achievements": ["Master Guardian", "Scroll Keeper", "Wisdom Keeper"]
}', NOW() - INTERVAL '55 days', NOW()),

('guardian_003', 'Thabo Mthembu', 'thabo.mthembu@flameborn.africa', '{
  "type": "guardian",
  "country": "South Africa",
  "region": "Southern Africa",
  "contributionAmount": 300,
  "soulprintVerified": true,
  "resonanceScore": 0.99,
  "ancestralVerification": true,
  "codeContributions": 12,
  "supportedWorkers": ["healer_005"],
  "flbBalance": 90,
  "achievements": ["Code Guardian", "Tech Contributor", "Ubuntu Spirit"]
}', NOW() - INTERVAL '48 days', NOW()),

('guardian_004', 'Mariam Traore', 'mariam.traore@flameborn.africa', '{
  "type": "guardian",
  "country": "Burkina Faso",
  "region": "West Africa",
  "contributionAmount": 200,
  "soulprintVerified": false,
  "resonanceScore": 0.89,
  "supportedWorkers": ["healer_006"],
  "flbBalance": 35,
  "achievements": ["Community Supporter"]
}', NOW() - INTERVAL '25 days', NOW()),

('guardian_005', 'Joseph Mukasa', 'joseph.mukasa@flameborn.africa', '{
  "type": "guardian",
  "country": "Uganda",
  "region": "East Africa",
  "contributionAmount": 450,
  "soulprintVerified": true,
  "resonanceScore": 0.96,
  "ancestralVerification": true,
  "proverbContributions": 6,
  "supportedWorkers": ["healer_004"],
  "flbBalance": 65,
  "achievements": ["East African Guardian", "Wisdom Contributor"]
}', NOW() - INTERVAL '37 days', NOW()),

('guardian_006', 'Fatou Diallo', 'fatou.diallo@flameborn.africa', '{
  "type": "guardian",
  "country": "Senegal",
  "region": "West Africa",
  "contributionAmount": 600,
  "soulprintVerified": true,
  "resonanceScore": 0.98,
  "ancestralVerification": true,
  "role": "scroll_keeper",
  "scrollsCreated": 1,
  "proverbContributions": 10,
  "codeContributions": 3,
  "supportedWorkers": ["healer_001", "healer_006"],
  "flbBalance": 110,
  "achievements": ["Multi-Contributor", "Scroll Keeper", "Teranga Spirit"]
}', NOW() - INTERVAL '42 days', NOW()),

('guardian_007', 'Omar Benali', 'omar.benali@flameborn.africa', '{
  "type": "guardian",
  "country": "Tunisia",
  "region": "North Africa",
  "contributionAmount": 150,
  "soulprintVerified": false,
  "resonanceScore": 0.88,
  "supportedWorkers": [],
  "flbBalance": 20,
  "achievements": ["New Guardian"]
}', NOW() - INTERVAL '2 days', NOW());

-- Create a view for easy statistics querying
CREATE OR REPLACE VIEW community_stats AS
SELECT 
  COUNT(*) FILTER (WHERE raw_json->>'type' = 'healer') as total_healers,
  COUNT(*) FILTER (WHERE raw_json->>'type' = 'healer' AND raw_json->>'verificationStatus' = 'verified') as verified_healers,
  COUNT(*) FILTER (WHERE raw_json->>'type' = 'guardian') as total_guardians,
  COUNT(*) FILTER (WHERE raw_json->>'type' = 'guardian' AND (raw_json->>'contributionAmount')::numeric > 0) as active_guardians,
  COUNT(*) FILTER (WHERE raw_json->>'soulprintVerified' = 'true') as soulbound_total,
  COUNT(*) FILTER (WHERE raw_json->>'role' = 'scroll_keeper') as scroll_keepers,
  COUNT(*) FILTER (WHERE (raw_json->>'proverbContributions')::numeric > 0) as proverb_contributors,
  COUNT(*) FILTER (WHERE (raw_json->>'codeContributions')::numeric > 0) as code_contributors,
  SUM((raw_json->>'contributionAmount')::numeric) FILTER (WHERE raw_json->>'type' = 'guardian') as total_contributions,
  SUM((raw_json->'impact'->>'patientsServed')::numeric) FILTER (WHERE raw_json->>'type' = 'healer') as total_patients_served,
  COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '7 days') as new_this_week,
  COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days') as new_this_month
FROM neon_auth.users_sync;

-- Grant permissions (adjust as needed for your setup)
-- GRANT SELECT ON community_stats TO your_app_user;

COMMIT;