MSQH AIMS - Complete SQL for Self-Hosting

-- ============================================
-- MSQH AIMS Complete Database Schema
-- PostgreSQL / Supabase Compatible
-- Generated: 2025-01-21
-- ============================================

-- Enable Required Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================
-- DROP EXISTING TABLES (for clean install)
-- ============================================
DROP TABLE IF EXISTS user_role_navigation_category_rules CASCADE;
DROP TABLE IF EXISTS navigation_category_rules CASCADE;
DROP TABLE IF EXISTS navigation_categories CASCADE;
DROP TABLE IF EXISTS roadmap_items CASCADE;
DROP TABLE IF EXISTS surveyor_schedule_summary CASCADE;
DROP TABLE IF EXISTS user_surveyor_survey_summary CASCADE;
DROP TABLE IF EXISTS user_survey_performance CASCADE;
DROP TABLE IF EXISTS user_facility_survey_summary CASCADE;
DROP TABLE IF EXISTS event_to_notify CASCADE;
DROP TABLE IF EXISTS notification_channels CASCADE;
DROP TABLE IF EXISTS user_devices CASCADE;
DROP TABLE IF EXISTS user_role_assignments CASCADE;
DROP TABLE IF EXISTS system_health_metrics CASCADE;
DROP TABLE IF EXISTS feature_roadmap CASCADE;
DROP TABLE IF EXISTS alerts CASCADE;
DROP TABLE IF EXISTS user_list CASCADE;
DROP TABLE IF EXISTS error_logs CASCADE;
DROP TABLE IF EXISTS api_logs CASCADE;
DROP TABLE IF EXISTS api_list CASCADE;
DROP TABLE IF EXISTS survey_schedule_summary CASCADE;
DROP TABLE IF EXISTS survey_applications CASCADE;
DROP TABLE IF EXISTS accreditations CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS status_history CASCADE;
DROP TABLE IF EXISTS events CASCADE;
DROP TABLE IF EXISTS claims CASCADE;
DROP TABLE IF EXISTS training CASCADE;
DROP TABLE IF EXISTS resources CASCADE;
DROP TABLE IF EXISTS risks CASCADE;
DROP TABLE IF EXISTS memberships CASCADE;
DROP TABLE IF EXISTS surveyor_registry CASCADE;
DROP TABLE IF EXISTS survey_evidence CASCADE;
DROP TABLE IF EXISTS survey_service_standards CASCADE;
DROP TABLE IF EXISTS survey_roles CASCADE;
DROP TABLE IF EXISTS surveys CASCADE;
DROP TABLE IF EXISTS accreditation_standard_options CASCADE;
DROP TABLE IF EXISTS evidence_of_compliance CASCADE;
DROP TABLE IF EXISTS criteria CASCADE;
DROP TABLE IF EXISTS standards CASCADE;
DROP TABLE IF EXISTS topics CASCADE;
DROP TABLE IF EXISTS service_standards CASCADE;
DROP TABLE IF EXISTS accreditation_standard_editions CASCADE;
DROP TABLE IF EXISTS accreditation_programmes CASCADE;
DROP TABLE IF EXISTS hospital_staff CASCADE;
DROP TABLE IF EXISTS hospitals CASCADE;
DROP TABLE IF EXISTS theme_templates CASCADE;
DROP TABLE IF EXISTS group_page_list CASCADE;
DROP TABLE IF EXISTS user_role_groups CASCADE;
DROP TABLE IF EXISTS user_role_page_access CASCADE;
DROP TABLE IF EXISTS page_list CASCADE;
DROP TABLE IF EXISTS groups CASCADE;
DROP TABLE IF EXISTS user_roles CASCADE;
DROP TABLE IF EXISTS system_settings CASCADE;

-- ============================================
-- 1. USER ROLES
-- ============================================
CREATE TABLE user_roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_code VARCHAR(100) UNIQUE NOT NULL,
    role_name VARCHAR(255) NOT NULL,
    description TEXT,
    color VARCHAR(20) DEFAULT '#800020',
    is_active BOOLEAN DEFAULT true,
    is_system_role BOOLEAN DEFAULT false,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_roles IS 'System user roles for access control';

-- ============================================
-- 2. GROUPS
-- ============================================
CREATE TABLE groups (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    group_code VARCHAR(100) UNIQUE NOT NULL,
    group_name VARCHAR(255) NOT NULL,
    description TEXT,
    icon_name VARCHAR(100),
    color VARCHAR(20) DEFAULT '#800020',
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE groups IS 'Navigation and permission groups';

-- ============================================
-- 3. NAVIGATION CATEGORIES
-- ============================================
CREATE TABLE navigation_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_code VARCHAR(100) UNIQUE NOT NULL,
    category_name VARCHAR(255) NOT NULL,
    description TEXT,
    icon_name VARCHAR(100),
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE navigation_categories IS 'Navigation categories for menu organization';

-- ============================================
-- 4. NAVIGATION CATEGORY RULES
-- ============================================
CREATE TABLE navigation_category_rules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id UUID NOT NULL REFERENCES navigation_categories(id) ON DELETE CASCADE,
    rule_code VARCHAR(100) NOT NULL,
    rule_name VARCHAR(255) NOT NULL,
    description TEXT,
    input_type VARCHAR(50) CHECK (input_type IN ('checkbox', 'radio', 'select')) DEFAULT 'checkbox',
    options JSONB DEFAULT '[]',
    default_value VARCHAR(255),
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE navigation_category_rules IS 'Custom rules for navigation categories';

-- ============================================
-- 5. PAGE LIST
-- ============================================
CREATE TABLE page_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    page_code VARCHAR(100) UNIQUE NOT NULL,
    page_name VARCHAR(255) NOT NULL,
    label VARCHAR(255) NOT NULL,
    icon_name VARCHAR(100),
    category VARCHAR(100),
    category_id UUID REFERENCES navigation_categories(id) ON DELETE SET NULL,
    category_order INTEGER DEFAULT 0,
    menu_order INTEGER DEFAULT 0,
    description TEXT,
    theme_template_id UUID,
    is_active BOOLEAN DEFAULT true,
    active_from DATE,
    active_until DATE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE page_list IS 'Application pages and navigation items';

-- ============================================
-- 6. USER ROLE PAGE ACCESS
-- ============================================
CREATE TABLE user_role_page_access (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id UUID NOT NULL REFERENCES user_roles(id) ON DELETE CASCADE,
    page_id UUID NOT NULL REFERENCES page_list(id) ON DELETE CASCADE,
    "view" BOOLEAN DEFAULT false,
    "create" BOOLEAN DEFAULT false,
    edit BOOLEAN DEFAULT false,
    "delete" BOOLEAN DEFAULT false,
    misc JSONB DEFAULT '{}',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255),
    CONSTRAINT unique_role_page UNIQUE(role_id, page_id)
);

COMMENT ON TABLE user_role_page_access IS 'Permission matrix for role-page access';

-- ============================================
-- 7. USER ROLE NAVIGATION CATEGORY RULES
-- ============================================
CREATE TABLE user_role_navigation_category_rules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id UUID NOT NULL REFERENCES user_roles(id) ON DELETE CASCADE,
    category_id UUID NOT NULL REFERENCES navigation_categories(id) ON DELETE CASCADE,
    rule_id UUID NOT NULL REFERENCES navigation_category_rules(id) ON DELETE CASCADE,
    rule_value VARCHAR(255),
    is_enabled BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_role_navigation_category_rules IS 'Role-specific category rule values';

-- ============================================
-- 8. USER ROLE GROUPS
-- ============================================
CREATE TABLE user_role_groups (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id UUID NOT NULL REFERENCES user_roles(id) ON DELETE CASCADE,
    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255),
    CONSTRAINT unique_role_group UNIQUE(role_id, group_id)
);

COMMENT ON TABLE user_role_groups IS 'Maps roles to navigation groups';

-- ============================================
-- 9. GROUP PAGE LIST
-- ============================================
CREATE TABLE group_page_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    page_id UUID NOT NULL REFERENCES page_list(id) ON DELETE CASCADE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255),
    CONSTRAINT unique_group_page UNIQUE(group_id, page_id)
);

COMMENT ON TABLE group_page_list IS 'Maps pages to navigation groups';

-- ============================================
-- 10. THEME TEMPLATES
-- ============================================
CREATE TABLE theme_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    template_name VARCHAR(255) NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT false,
    theme_settings JSONB NOT NULL DEFAULT '{}',
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE theme_templates IS 'UI theme configurations';

-- ============================================
-- 11. ALERT (System Health Alerts)
-- ============================================
CREATE TABLE alerts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    severity VARCHAR(50) CHECK (severity IN ('info', 'warning', 'error', 'critical')) NOT NULL,
    is_read BOOLEAN DEFAULT false,
    triggered_by VARCHAR(255),
    metadata JSONB DEFAULT '{}',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE alerts IS 'System health alerts and notifications';

-- ============================================
-- 12. SYSTEM HEALTH METRICS (Pre-computed)
-- ============================================
CREATE TABLE system_health_metrics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    api_call_count_hourly INTEGER DEFAULT 0,
    error_count_hourly INTEGER DEFAULT 0,
    active_users_count_hourly INTEGER DEFAULT 0,
    duplicate_system_settings_count INTEGER DEFAULT 0,
    duplicate_user_count INTEGER DEFAULT 0,
    duplicate_survey_application_count INTEGER DEFAULT 0,
    total_system_settings_count INTEGER DEFAULT 0,
    total_user_count INTEGER DEFAULT 0,
    total_survey_application_count INTEGER DEFAULT 0,
    latest_dashboard_update_timestamp TIMESTAMP WITH TIME ZONE,
    features_developed_count INTEGER DEFAULT 0,
    features_planned_count INTEGER DEFAULT 0,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE system_health_metrics IS 'Pre-computed system health metrics for dashboard';

-- ============================================
-- 13. FEATURE ROADMAP
-- ============================================
CREATE TABLE feature_roadmap (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    feature_name VARCHAR(255) NOT NULL,
    status VARCHAR(50) CHECK (status IN ('planned', 'in_progress', 'developed', 'released')) NOT NULL,
    planned_release_date DATE,
    actual_release_date DATE,
    description TEXT,
    priority VARCHAR(50) CHECK (priority IN ('low', 'medium', 'high', 'critical')) DEFAULT 'medium',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE feature_roadmap IS 'Feature development roadmap and status tracking';

-- ============================================
-- 14. SYSTEM SETTINGS
-- ============================================
CREATE TABLE system_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    module VARCHAR(100) NOT NULL UNIQUE,
    settings JSONB NOT NULL DEFAULT '{}',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE system_settings IS 'Application-wide configuration settings';

-- ============================================
-- 15. USER LIST
-- ============================================
CREATE TABLE user_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(50),
    job_title VARCHAR(255),
    department VARCHAR(255),
    role_codes JSONB DEFAULT '[]',
    hospital_id UUID,
    status VARCHAR(50) CHECK (status IN ('ACTIVE', 'INACTIVE', 'PENDING', 'SUSPENDED')) DEFAULT 'ACTIVE',
    profile_photo_url TEXT,
    is_system_user BOOLEAN DEFAULT false,
    login_username VARCHAR(255),
    password_hash VARCHAR(255),
    allow_web_login BOOLEAN DEFAULT true,
    allow_mobile_login BOOLEAN DEFAULT true,
    last_login_date TIMESTAMP WITH TIME ZONE,
    last_login_platform VARCHAR(20) CHECK (last_login_platform IN ('WEB', 'MOBILE', 'API')),
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_list IS 'Extended user information and access management';

-- ============================================
-- 13. USER ROLE ASSIGNMENTS
-- ============================================
CREATE TABLE user_role_assignments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL REFERENCES user_list(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES user_roles(id) ON DELETE CASCADE,
    role_code VARCHAR(100) NOT NULL,
    assigned_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by_userlist_id UUID,
    is_active BOOLEAN DEFAULT true,
    expiry_date DATE,
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_role_assignments IS 'User to role assignments';

-- ============================================
-- 14. USER DEVICES
-- ============================================
CREATE TABLE user_devices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL REFERENCES user_list(id) ON DELETE CASCADE,
    device_name VARCHAR(255),
    device_type VARCHAR(50) CHECK (device_type IN ('MOBILE_IOS', 'MOBILE_ANDROID', 'WEB_BROWSER', 'TABLET', 'DESKTOP')) NOT NULL,
    device_token TEXT,
    device_fingerprint VARCHAR(255),
    browser_info TEXT,
    is_active BOOLEAN DEFAULT true,
    last_active_date TIMESTAMP WITH TIME ZONE,
    registered_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_devices IS 'User device registration and tracking';

-- ============================================
-- 15. NOTIFICATION CHANNELS
-- ============================================
CREATE TABLE notification_channels (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL REFERENCES user_list(id) ON DELETE CASCADE,
    channel_type VARCHAR(50) CHECK (channel_type IN ('EMAIL', 'SMS', 'PUSH_NOTIFICATION', 'IN_APP', 'WHATSAPP')) NOT NULL,
    channel_identifier VARCHAR(255) NOT NULL,
    is_enabled BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    preferences JSONB DEFAULT '{}',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    verified_date TIMESTAMP WITH TIME ZONE,
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE notification_channels IS 'User notification channel preferences';

-- ============================================
-- 16. EVENT TO NOTIFY
-- ============================================
CREATE TABLE event_to_notify (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL REFERENCES user_list(id) ON DELETE CASCADE,
    event_type VARCHAR(100) CHECK (event_type IN (
        'SURVEY_ASSIGNED', 'SURVEY_REMINDER', 'SURVEY_COMPLETED', 
        'REPORT_DUE', 'TEAM_MEMBER_ADDED', 'ROLE_CHANGED',
        'ACCREDITATION_EXPIRING', 'TRAINING_SCHEDULED', 
        'CLAIM_SUBMITTED', 'DOCUMENT_UPLOADED', 'SYSTEM_ANNOUNCEMENT'
    )) NOT NULL,
    is_enabled BOOLEAN DEFAULT true,
    notify_via_email BOOLEAN DEFAULT true,
    notify_via_sms BOOLEAN DEFAULT false,
    notify_via_push BOOLEAN DEFAULT true,
    notify_via_in_app BOOLEAN DEFAULT true,
    advance_notice_days INTEGER,
    custom_message_template TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE event_to_notify IS 'User notification event preferences';

-- ============================================
-- 17. API LIST
-- ============================================
CREATE TABLE api_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    api_endpoint VARCHAR(255) NOT NULL,
    api_method VARCHAR(10) CHECK (api_method IN ('GET', 'POST', 'PUT', 'DELETE', 'PATCH')) NOT NULL,
    api_name VARCHAR(255) NOT NULL,
    category VARCHAR(100) CHECK (category IN (
        'USER_MANAGEMENT', 'SURVEY_MANAGEMENT', 'HOSPITAL_MANAGEMENT',
        'ROLE_MANAGEMENT', 'NAVIGATION_MANAGEMENT', 'SYSTEM_SETTINGS',
        'AUDIT_LOG', 'DATA_EXPORT', 'DATA_IMPORT', 'AUTHENTICATION',
        'AUTHORIZATION', 'FILE_MANAGEMENT', 'NOTIFICATION',
        'REPORT_GENERATION', 'OTHER'
    )) NOT NULL,
    action VARCHAR(255) NOT NULL,
    description TEXT,
    is_critical BOOLEAN DEFAULT false,
    should_monitor BOOLEAN DEFAULT true,
    requires_admin BOOLEAN DEFAULT false,
    risk_level VARCHAR(20) CHECK (risk_level IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) DEFAULT 'LOW',
    retention_days INTEGER DEFAULT 90,
    send_alert BOOLEAN DEFAULT false,
    alert_recipients JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE api_list IS 'API endpoint registry and monitoring configuration';

-- ============================================
-- 18. API LOGS
-- ============================================
CREATE TABLE api_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    userlist_id UUID,
    user_email VARCHAR(255),
    user_roles JSONB DEFAULT '[]',
    request_payload JSONB,
    response_status INTEGER,
    response_time_ms INTEGER,
    ip_address VARCHAR(100),
    user_agent TEXT,
    error_message TEXT,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE api_logs IS 'API call audit and monitoring logs';

-- ============================================
-- 19. ERROR LOGS
-- ============================================
CREATE TABLE error_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    error_type VARCHAR(100),
    error_message TEXT,
    error_stack TEXT,
    user_id UUID,
    user_email VARCHAR(255),
    page VARCHAR(255),
    component VARCHAR(255),
    severity VARCHAR(50) CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')) DEFAULT 'MEDIUM',
    context JSONB DEFAULT '{}',
    is_resolved BOOLEAN DEFAULT false,
    resolved_date TIMESTAMP WITH TIME ZONE,
    resolved_by VARCHAR(255),
    resolution_notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE error_logs IS 'Application error tracking and monitoring';

-- ============================================
-- 20. ROADMAP ITEMS
-- ============================================
CREATE TABLE roadmap_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category VARCHAR(255) NOT NULL,
    category_description TEXT,
    category_order INTEGER DEFAULT 0,
    module_name VARCHAR(255) NOT NULL,
    module_status VARCHAR(50) CHECK (module_status IN ('DONE', 'IN_PROGRESS', 'PLANNED', 'FUTURE')) DEFAULT 'PLANNED',
    module_page VARCHAR(255),
    module_icon VARCHAR(100),
    feature_name VARCHAR(255) NOT NULL,
    feature_status VARCHAR(50) CHECK (feature_status IN ('DONE', 'IN_PROGRESS', 'PLANNED', 'FUTURE')) DEFAULT 'PLANNED',
    feature_page VARCHAR(255),
    notes TEXT,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE roadmap_items IS 'Custom roadmap features and modules';

-- ============================================
-- 21. HOSPITALS
-- ============================================
CREATE TABLE hospitals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hospital_code VARCHAR(100) UNIQUE NOT NULL,
    hospital_name VARCHAR(255) NOT NULL,
    hospital_name_malay VARCHAR(255),
    registration_number VARCHAR(100),
    hospital_type VARCHAR(50) CHECK (hospital_type IN ('GOVERNMENT', 'PRIVATE', 'UNIVERSITY', 'MILITARY', 'NGO')),
    bed_capacity INTEGER,
    address_line1 TEXT,
    address_line2 TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    postcode VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Malaysia',
    contact_person_name VARCHAR(255),
    contact_person_email VARCHAR(255),
    contact_person_phone VARCHAR(50),
    director_name VARCHAR(255),
    director_email VARCHAR(255),
    website VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    membership_status VARCHAR(50) CHECK (membership_status IN ('ACTIVE', 'GRACE_PERIOD', 'EXPIRED', 'SUSPENDED')),
    membership_expiry_date DATE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE hospitals IS 'Healthcare facilities registered in the system';

-- ============================================
-- 22. HOSPITAL STAFF
-- ============================================
CREATE TABLE hospital_staff (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hospital_id UUID NOT NULL REFERENCES hospitals(id) ON DELETE CASCADE,
    user_id UUID,
    user_email VARCHAR(255),
    user_name VARCHAR(255),
    role VARCHAR(100) CHECK (role IN ('FACILITY_ADMIN', 'FACILITY_USER', 'QUALITY_MANAGER', 'DEPARTMENT_HEAD', 'CLINICAL_STAFF')),
    department VARCHAR(255),
    assignment_date DATE,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE hospital_staff IS 'Staff members assigned to hospitals';

-- ============================================
-- 23. ACCREDITATION PROGRAMMES
-- ============================================
CREATE TABLE accreditation_programmes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    programme_code VARCHAR(100) UNIQUE NOT NULL,
    programme_name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_programmes IS 'Accreditation programme definitions';

-- ============================================
-- 24. ACCREDITATION STANDARD EDITIONS
-- ============================================
CREATE TABLE accreditation_standard_editions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    programme_id UUID NOT NULL REFERENCES accreditation_programmes(id) ON DELETE CASCADE,
    edition_code VARCHAR(100) NOT NULL,
    edition_name VARCHAR(255) NOT NULL,
    version VARCHAR(50),
    effective_date DATE,
    description TEXT,
    rating_config JSONB DEFAULT '{"max_stars": 5, "passing_value": 3, "excellent_value": 5, "enable_na": true}',
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_standard_editions IS 'Versions of accreditation standards';

-- ============================================
-- 25. SERVICE STANDARDS
-- ============================================
CREATE TABLE service_standards (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    edition_id UUID NOT NULL REFERENCES accreditation_standard_editions(id) ON DELETE CASCADE,
    standard_code VARCHAR(100) NOT NULL,
    standard_name VARCHAR(255) NOT NULL,
    description TEXT,
    "order" INTEGER DEFAULT 0,
    llm_context TEXT,
    llm_context_files JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE service_standards IS 'Service standard definitions within editions';

-- ============================================
-- 26. TOPICS
-- ============================================
CREATE TABLE topics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    service_standard_id UUID NOT NULL REFERENCES service_standards(id) ON DELETE CASCADE,
    topic_code VARCHAR(100) NOT NULL,
    topic_name VARCHAR(255) NOT NULL,
    description TEXT,
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE topics IS 'Topics within service standards';

-- ============================================
-- 27. STANDARDS
-- ============================================
CREATE TABLE standards (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    topic_id UUID NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
    edition_id UUID NOT NULL,
    standard_code VARCHAR(100) NOT NULL,
    standard_name VARCHAR(255) NOT NULL,
    description TEXT,
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE standards IS 'Standards level between Topics and Criteria';

-- ============================================
-- 28. CRITERIA
-- ============================================
CREATE TABLE criteria (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    standard_id UUID NOT NULL REFERENCES standards(id) ON DELETE CASCADE,
    edition_id UUID NOT NULL,
    criterion_code VARCHAR(100) NOT NULL,
    criterion_name VARCHAR(255) NOT NULL,
    description TEXT,
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE criteria IS 'Criteria within standards';

-- ============================================
-- 29. EVIDENCE OF COMPLIANCE
-- ============================================
CREATE TABLE evidence_of_compliance (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    criterion_id UUID NOT NULL REFERENCES criteria(id) ON DELETE CASCADE,
    parent_evidence_id UUID REFERENCES evidence_of_compliance(id) ON DELETE CASCADE,
    evidence_code VARCHAR(100) NOT NULL,
    evidence_name VARCHAR(255) NOT NULL,
    description TEXT,
    "order" INTEGER DEFAULT 0,
    llm_context TEXT,
    llm_context_files JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE evidence_of_compliance IS 'Evidence items for compliance assessment';

-- ============================================
-- 30. ACCREDITATION STANDARD OPTIONS
-- ============================================
CREATE TABLE accreditation_standard_options (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    edition_id UUID NOT NULL REFERENCES accreditation_standard_editions(id) ON DELETE CASCADE,
    option_type VARCHAR(50) CHECK (option_type IN ('RATING', 'CONTEXT', 'REPORT_EXAMPLE', 'OTHER')) NOT NULL,
    option_name VARCHAR(255) NOT NULL,
    option_value TEXT,
    description TEXT,
    files JSONB DEFAULT '[]',
    "order" INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_standard_options IS 'Configuration options for editions';

-- ============================================
-- 31. ACCREDITATIONS
-- ============================================
CREATE TABLE accreditations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hospital_id UUID NOT NULL REFERENCES hospitals(id) ON DELETE CASCADE,
    programme_id UUID REFERENCES accreditation_programmes(id) ON DELETE SET NULL,
    edition_id UUID REFERENCES accreditation_standard_editions(id) ON DELETE SET NULL,
    accreditation_decision VARCHAR(50) CHECK (accreditation_decision IN ('FOUR_YEAR', 'ONE_YEAR', 'NON_ACCREDITED')),
    certificate_number VARCHAR(100),
    certificate_issued_date DATE,
    accreditation_start_date DATE,
    accreditation_expiry_date DATE,
    status VARCHAR(50) CHECK (status IN ('ACTIVE', 'EXPIRED', 'SUSPENDED', 'REVOKED')) DEFAULT 'ACTIVE',
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditations IS 'Hospital accreditation certificates and status';

-- ============================================
-- 32. SURVEYS
-- ============================================
CREATE TABLE surveys (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_code VARCHAR(100) UNIQUE NOT NULL,
    hospital_id UUID NOT NULL REFERENCES hospitals(id) ON DELETE CASCADE,
    edition_id UUID REFERENCES accreditation_standard_editions(id) ON DELETE SET NULL,
    survey_type VARCHAR(100) CHECK (survey_type IN (
        'FULL_ACCREDITATION', 'INTERIM_SURVEY', 'RE_ACCREDITATION', 
        'SURVEILLANCE', 'FOLLOW_UP', 'FOCUS_SURVEY', 
        'SURPRISE_SURVEILLANCE', 'COMPLIANCE_REPORT'
    )) NOT NULL,
    accreditation_program VARCHAR(100) CHECK (accreditation_program IN (
        'MSQH_5TH_EDITION', 'MSQH_7TH_EDITION', 'MSQH_CLINICAL_RESEARCH',
        'MSQH_DIALYSIS', 'MSQH_LABORATORY', 'MSQH_BLOOD_BANK'
    )) NOT NULL,
    current_status VARCHAR(100) CHECK (current_status IN (
        'NEW', 'ACCEPT', 'REJECT', 'CANCEL',
        'RESPONDENT_COMPLETE', 'RESPONDENT_RETURN',
        'SURVEYOR_IN_PROGRESS', 'SURVEYOR_COMPLETE', 'SURVEYOR_RETURN',
        'COUNCILLOR_IN_PROGRESS', 'COUNCILLOR_COMPLETE', 'COUNCILLOR_RETURN',
        'ACCREDITED', 'NON_ACCREDITED'
    )) DEFAULT 'NEW',
    current_phase VARCHAR(100) CHECK (current_phase IN (
        'INITIAL_SURVEY', 'FOCUS_SURVEY', 'SURPRISE_SURVEILLANCE',
        'COMPLIANCE_REPORT_12M', 'COMPLIANCE_REPORT_24M', 'COMPLIANCE_REPORT_36M'
    )),
    application_date TIMESTAMP WITH TIME ZONE,
    application_validity_until DATE,
    requested_survey_dates JSONB DEFAULT '[]',
    scheduled_start_date DATE,
    scheduled_end_date DATE,
    actual_start_date DATE,
    actual_end_date DATE,
    submission_documents JSONB DEFAULT '[]',
    special_requirements TEXT,
    estimated_duration_days INTEGER,
    number_of_surveyors_required INTEGER,
    assigned_chief_surveyor_id UUID,
    secretariat_notes TEXT,
    internal_notes TEXT,
    priority_level VARCHAR(50) CHECK (priority_level IN ('LOW', 'MEDIUM', 'HIGH', 'URGENT')) DEFAULT 'MEDIUM',
    rejection_reason TEXT,
    cancellation_reason TEXT,
    accreditation_decision VARCHAR(50) CHECK (accreditation_decision IN ('FOUR_YEAR', 'ONE_YEAR', 'NON_ACCREDITED')),
    accreditation_expiry_date DATE,
    certificate_issued_date DATE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE surveys IS 'Accreditation survey applications and management';

-- ============================================
-- 33. SURVEY APPLICATIONS
-- ============================================
CREATE TABLE survey_applications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    hospital_id UUID NOT NULL REFERENCES hospitals(id) ON DELETE CASCADE,
    application_date DATE NOT NULL,
    application_status VARCHAR(50) CHECK (application_status IN ('SUBMITTED', 'REVIEWING', 'APPROVED', 'REJECTED')) DEFAULT 'SUBMITTED',
    requested_dates JSONB DEFAULT '[]',
    approved_date DATE,
    rejection_reason TEXT,
    submission_documents JSONB DEFAULT '[]',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE survey_applications IS 'Survey application submissions and status';

-- ============================================
-- 34. SURVEY SCHEDULE SUMMARY
-- ============================================
CREATE TABLE survey_schedule_summary (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    hospital_id UUID REFERENCES hospitals(id) ON DELETE CASCADE,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    scheduled_start_date DATE,
    scheduled_end_date DATE,
    actual_start_date DATE,
    actual_end_date DATE,
    survey_status VARCHAR(100),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE survey_schedule_summary IS 'Survey scheduling summary for calendar views';

-- ============================================
-- 35. SURVEY ROLES
-- ============================================
CREATE TABLE survey_roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    role_in_survey VARCHAR(100) CHECK (role_in_survey IN (
        'CHIEF_SURVEYOR', 'TEAM_MEMBER', 'TECHNICAL_OBSERVER',
        'TRAINEE_SURVEYOR', 'SUBJECT_MATTER_EXPERT', 'COUNCILLOR', 'TECHNICAL_REVIEWER'
    )) NOT NULL,
    assignment_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    assigned_by_user_id UUID,
    is_active BOOLEAN DEFAULT true,
    removal_date TIMESTAMP WITH TIME ZONE,
    removal_reason TEXT,
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE survey_roles IS 'Team members assigned to surveys';

-- ============================================
-- 36. SURVEY SERVICE STANDARDS
-- ============================================
CREATE TABLE survey_service_standards (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    service_standard_id UUID NOT NULL REFERENCES service_standards(id) ON DELETE CASCADE,
    assigned_role_id UUID REFERENCES survey_roles(id) ON DELETE SET NULL,
    is_enabled BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE survey_service_standards IS 'Service standards enabled for each survey';

-- ============================================
-- 37. SURVEY EVIDENCE
-- ============================================
CREATE TABLE survey_evidence (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    evidence_id UUID NOT NULL REFERENCES evidence_of_compliance(id) ON DELETE CASCADE,
    facility_rating VARCHAR(50) CHECK (facility_rating IN (
        'NOT_STARTED', 'COMPLIANT', 'PARTIALLY_COMPLIANT', 'NON_COMPLIANT', 'NOT_APPLICABLE'
    )) DEFAULT 'NOT_STARTED',
    facility_comment TEXT,
    facility_files JSONB DEFAULT '[]',
    surveyor_rating VARCHAR(50) CHECK (surveyor_rating IN (
        'NOT_STARTED', 'COMPLIANT', 'PARTIALLY_COMPLIANT', 'NON_COMPLIANT', 'NOT_APPLICABLE'
    )),
    surveyor_comment TEXT,
    surveyor_files JSONB DEFAULT '[]',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255),
    CONSTRAINT unique_survey_evidence UNIQUE(survey_id, evidence_id)
);

COMMENT ON TABLE survey_evidence IS 'Evidence submissions and ratings for surveys';

-- ============================================
-- 38. SURVEYOR REGISTRY
-- ============================================
CREATE TABLE surveyor_registry (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    surveyor_type VARCHAR(100) CHECK (surveyor_type IN (
        'SURVEYOR', 'CHIEF_SURVEYOR', 'COUNCILLOR', 'TECHNICAL_OBSERVER', 'TRAINEE_SURVEYOR'
    )) NOT NULL,
    specialization VARCHAR(255),
    certification_number VARCHAR(100),
    certification_date DATE,
    expiry_date DATE,
    total_surveys_completed INTEGER DEFAULT 0,
    average_rating NUMERIC(3,2) DEFAULT 0,
    status VARCHAR(50) CHECK (status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'EXPIRED')) DEFAULT 'ACTIVE',
    performance_notes TEXT,
    is_available BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE surveyor_registry IS 'Registered surveyors and their credentials';

-- ============================================
-- 39. USER FACILITY SURVEY SUMMARY
-- ============================================
CREATE TABLE user_facility_survey_summary (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL,
    user_id UUID,
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    survey_code VARCHAR(100),
    hospital_id UUID,
    hospital_name VARCHAR(255),
    survey_type VARCHAR(100),
    survey_status VARCHAR(100),
    application_date DATE,
    year_index INTEGER,
    month_index INTEGER,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_facility_survey_summary IS 'Facility user survey participation summary';

-- ============================================
-- 40. USER SURVEY PERFORMANCE
-- ============================================
CREATE TABLE user_survey_performance (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL,
    user_id UUID,
    user_name VARCHAR(255),
    total_surveys_as_facility INTEGER DEFAULT 0,
    total_surveys_as_surveyor INTEGER DEFAULT 0,
    total_surveys_as_councillor INTEGER DEFAULT 0,
    delayed_submissions INTEGER DEFAULT 0,
    on_time_submissions INTEGER DEFAULT 0,
    avg_rating_from_facility NUMERIC(3,2) DEFAULT 0,
    avg_rating_from_chief_surveyor NUMERIC(3,2) DEFAULT 0,
    total_ratings_received INTEGER DEFAULT 0,
    last_updated TIMESTAMP WITH TIME ZONE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_survey_performance IS 'Aggregated user performance metrics';

-- ============================================
-- 41. USER SURVEYOR SURVEY SUMMARY
-- ============================================
CREATE TABLE user_surveyor_survey_summary (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    userlist_id UUID NOT NULL,
    user_id UUID,
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    survey_code VARCHAR(100),
    hospital_name VARCHAR(255),
    role_type VARCHAR(100),
    survey_status VARCHAR(100),
    scheduled_start_date DATE,
    scheduled_end_date DATE,
    report_submitted_date DATE,
    is_delayed BOOLEAN DEFAULT false,
    rating_from_facility NUMERIC(2,1),
    rating_from_chief_surveyor NUMERIC(2,1),
    year_index INTEGER,
    month_index INTEGER,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE user_surveyor_survey_summary IS 'Surveyor survey participation summary';

-- ============================================
-- 42. SURVEYOR SCHEDULE SUMMARY
-- ============================================
CREATE TABLE surveyor_schedule_summary (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    surveyor_userlist_id UUID NOT NULL,
    surveyor_id UUID,
    surveyor_name VARCHAR(255),
    survey_id UUID NOT NULL REFERENCES surveys(id) ON DELETE CASCADE,
    survey_role_id UUID,
    hospital_id UUID,
    hospital_name VARCHAR(255),
    survey_code VARCHAR(100),
    role_type VARCHAR(100),
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    scheduled_start_date DATE,
    scheduled_end_date DATE,
    actual_start_date DATE,
    actual_end_date DATE,
    survey_status VARCHAR(100),
    assigned_date TIMESTAMP WITH TIME ZONE,
    is_available BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE surveyor_schedule_summary IS 'Surveyor availability and schedule tracking';

-- ============================================
-- 43. MEMBERSHIPS
-- ============================================
CREATE TABLE memberships (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hospital_id UUID NOT NULL REFERENCES hospitals(id) ON DELETE CASCADE,
    membership_type VARCHAR(100),
    start_date DATE,
    end_date DATE,
    status VARCHAR(50) CHECK (status IN ('ACTIVE', 'PENDING', 'EXPIRED', 'CANCELLED')),
    annual_fee NUMERIC(12,2),
    payment_status VARCHAR(50) CHECK (payment_status IN ('PAID', 'PENDING', 'OVERDUE', 'WAIVED')),
    notes TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE memberships IS 'Hospital membership records';

-- ============================================
-- 44. RISKS
-- ============================================
CREATE TABLE risks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    risk_title VARCHAR(255) NOT NULL,
    risk_description TEXT,
    category VARCHAR(100),
    severity VARCHAR(50) CHECK (severity IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    likelihood VARCHAR(50) CHECK (likelihood IN ('RARE', 'UNLIKELY', 'POSSIBLE', 'LIKELY', 'ALMOST_CERTAIN')),
    status VARCHAR(50) CHECK (status IN ('IDENTIFIED', 'ASSESSING', 'MITIGATING', 'RESOLVED', 'ACCEPTED')) DEFAULT 'IDENTIFIED',
    mitigation_plan TEXT,
    owner_user_id UUID,
    due_date DATE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE risks IS 'Risk management records';

-- ============================================
-- 45. RESOURCES
-- ============================================
CREATE TABLE resources (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    resource_name VARCHAR(255) NOT NULL,
    resource_type VARCHAR(100),
    description TEXT,
    file_url TEXT,
    category VARCHAR(100),
    tags JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT true,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE resources IS 'Document and resource library';

-- ============================================
-- 46. TRAINING
-- ============================================
CREATE TABLE training (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    training_title VARCHAR(255) NOT NULL,
    description TEXT,
    training_type VARCHAR(100),
    start_date DATE,
    end_date DATE,
    location VARCHAR(255),
    trainer VARCHAR(255),
    max_participants INTEGER,
    status VARCHAR(50) CHECK (status IN ('PLANNED', 'ONGOING', 'COMPLETED', 'CANCELLED')) DEFAULT 'PLANNED',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE training IS 'Training sessions and events';

-- ============================================
-- 47. CLAIMS
-- ============================================
CREATE TABLE claims (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    claim_title VARCHAR(255) NOT NULL,
    claim_type VARCHAR(100),
    description TEXT,
    claimant_user_id UUID,
    hospital_id UUID REFERENCES hospitals(id) ON DELETE SET NULL,
    claim_date DATE,
    amount NUMERIC(12,2),
    status VARCHAR(50) CHECK (status IN ('SUBMITTED', 'REVIEWING', 'APPROVED', 'REJECTED', 'PAID')) DEFAULT 'SUBMITTED',
    resolution TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE claims IS 'Expense and reimbursement claims';

-- ============================================
-- 48. EVENTS
-- ============================================
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_title VARCHAR(255) NOT NULL,
    event_type VARCHAR(100),
    description TEXT,
    start_date TIMESTAMP WITH TIME ZONE,
    end_date TIMESTAMP WITH TIME ZONE,
    location VARCHAR(255),
    organizer VARCHAR(255),
    status VARCHAR(50) CHECK (status IN ('SCHEDULED', 'ONGOING', 'COMPLETED', 'CANCELLED')) DEFAULT 'SCHEDULED',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE events IS 'Calendar events and schedules';

-- ============================================
-- 49. STATUS HISTORY
-- ============================================
CREATE TABLE status_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(100) NOT NULL,
    entity_id UUID NOT NULL,
    old_status VARCHAR(100),
    new_status VARCHAR(100) NOT NULL,
    changed_by_user_id UUID,
    change_reason TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE status_history IS 'Audit trail for status changes';

-- ============================================
-- 50. AUDIT LOGS
-- ============================================
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    entity_type VARCHAR(100) NOT NULL,
    entity_id UUID NOT NULL,
    action VARCHAR(50) NOT NULL,
    user_id UUID,
    user_email VARCHAR(255),
    changes JSONB DEFAULT '{}',
    ip_address VARCHAR(100),
    user_agent TEXT,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE audit_logs IS 'Complete audit trail of all changes';

-- ============================================
-- 51. NOTIFICATIONS
-- ============================================
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT,
    notification_type VARCHAR(100),
    related_entity_type VARCHAR(100),
    related_entity_id UUID,
    is_read BOOLEAN DEFAULT false,
    read_at TIMESTAMP WITH TIME ZONE,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE notifications IS 'User notifications';

-- ============================================
-- 52. ACCREDITATION APPLICATION FORMS (Dynamic Form Builder)
-- ============================================
CREATE TABLE accreditation_application_form_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    programme_id UUID NOT NULL REFERENCES accreditation_programmes(id) ON DELETE CASCADE,
    edition_id UUID NOT NULL REFERENCES accreditation_standard_editions(id) ON DELETE CASCADE,
    version_number INTEGER NOT NULL,
    is_active BOOLEAN DEFAULT true,
    enabled_in_main_application_form BOOLEAN DEFAULT false,
    submitted_by VARCHAR(255),
    submitter_ip VARCHAR(100),
    num_applications INTEGER DEFAULT 0,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_list IS 'Multi-version accreditation application forms per programme/edition';

-- ============================================
-- 53. FORM INPUT LIST (Questions)
-- ============================================
CREATE TABLE accreditation_application_form_input_list (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    form_id UUID NOT NULL REFERENCES accreditation_application_form_list(id) ON DELETE CASCADE,
    section_id VARCHAR(100),
    input_key VARCHAR(100) NOT NULL,
    label TEXT NOT NULL,
    description TEXT,
    input_type VARCHAR(50) CHECK (input_type IN (
        'short_text','long_text','email','password','phone','url','number','currency','date','time','datetime','month','week','multiple_choice','checkbox','dropdown','multi_dropdown','linear_scale','rating','yes_no','file_upload','image_upload','signature','location','matrix','ranking','slider'
    )) NOT NULL,
    display_order INTEGER DEFAULT 1,
    is_required BOOLEAN DEFAULT false,
    is_hidden BOOLEAN DEFAULT false,
    is_readonly BOOLEAN DEFAULT false,
    placeholder TEXT,
    default_value TEXT,
    validation_json JSONB DEFAULT '{}',
    settings_json JSONB DEFAULT '{}',
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_input_list IS 'Dynamic form inputs/questions per form version';

-- ============================================
-- 54. INPUT OPTIONS (Dropdown/Radio/Checkbox)
-- ============================================
CREATE TABLE accreditation_application_form_input_options (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    option_value VARCHAR(255) NOT NULL,
    option_label VARCHAR(255) NOT NULL,
    option_order INTEGER DEFAULT 1,
    is_default BOOLEAN DEFAULT false,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_input_options IS 'Options for selectable inputs';

-- ============================================
-- 55. FORM SUBMISSION ENTRIES (Header)
-- ============================================
CREATE TABLE form_submission_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    form_id UUID NOT NULL REFERENCES accreditation_application_form_list(id) ON DELETE CASCADE,
    hospital_name VARCHAR(255),
    contact_number VARCHAR(50),
    ip_address VARCHAR(100),
    user_agent TEXT,
    device_fingerprint VARCHAR(255),
    metadata JSONB DEFAULT '{}',
    submitted_by VARCHAR(255),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE form_submission_entries IS 'Top-level submission records with anti-spam metadata';

-- ============================================
-- 56. FORM SUBMISSION ANSWERS (Per Input)
-- ============================================
CREATE TABLE form_submission_answers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    submission_entry_id UUID NOT NULL REFERENCES form_submission_entries(id) ON DELETE CASCADE,
    form_id UUID NOT NULL REFERENCES accreditation_application_form_list(id) ON DELETE CASCADE,
    input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    answer_type VARCHAR(50) NOT NULL,
    answer_text TEXT,
    answer_number NUMERIC(15,2),
    answer_date DATE,
    answer_datetime TIMESTAMP WITH TIME ZONE,
    answer_json JSONB,
    answer_file_path TEXT,
    question_text TEXT NOT NULL,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE form_submission_answers IS 'Normalized answers for each submitted input';

-- ============================================
-- 57. INPUT FILE CONFIG (For file_upload fields)
-- ============================================
CREATE TABLE accreditation_application_form_input_file_config (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    storage_type VARCHAR(50) CHECK (storage_type IN ('local','s3','gcs')) DEFAULT 'local',
    upload_path TEXT NOT NULL,
    max_file_size_mb INTEGER DEFAULT 10,
    min_file_size_kb INTEGER DEFAULT 0,
    max_files INTEGER DEFAULT 1,
    allowed_mime_types JSONB,
    allowed_extensions JSONB,
    rename_strategy VARCHAR(50) CHECK (rename_strategy IN ('uuid','original','timestamp')) DEFAULT 'uuid',
    hash_algorithm VARCHAR(50) DEFAULT 'sha256',
    virus_scan BOOLEAN DEFAULT false,
    public_access BOOLEAN DEFAULT false,
    image_rules_json JSONB,
    cloud_config_json JSONB,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_input_file_config IS 'File upload constraints/configuration per input';

-- ============================================
-- 58. INPUT CONDITIONAL LOGIC
-- ============================================
CREATE TABLE accreditation_application_form_input_conditional_logic (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    source_input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    operator VARCHAR(50) CHECK (operator IN ('equals','not_equals','greater_than','less_than','contains','not_contains','starts_with','ends_with','is_empty','is_not_empty')) NOT NULL,
    value TEXT NOT NULL,
    action_type VARCHAR(50) CHECK (action_type IN ('show','hide','jump_to_section','require','enable','disable')) NOT NULL,
    target_input_id UUID REFERENCES accreditation_application_form_input_list(id) ON DELETE SET NULL,
    target_section_id VARCHAR(100),
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_input_conditional_logic IS 'Conditional visibility/behavior rules';

-- ============================================
-- 59. INPUT MATRIX ROWS/COLUMNS (for matrix type)
-- ============================================
CREATE TABLE accreditation_application_form_input_matrix_rows (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    row_label VARCHAR(255) NOT NULL,
    row_order INTEGER DEFAULT 1,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

CREATE TABLE accreditation_application_form_input_matrix_columns (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    input_id UUID NOT NULL REFERENCES accreditation_application_form_input_list(id) ON DELETE CASCADE,
    column_label VARCHAR(255) NOT NULL,
    column_value VARCHAR(255) NOT NULL,
    column_order INTEGER DEFAULT 1,
    created_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255)
);

COMMENT ON TABLE accreditation_application_form_input_matrix_rows IS 'Matrix rows for matrix inputs';
COMMENT ON TABLE accreditation_application_form_input_matrix_columns IS 'Matrix columns for matrix inputs';

-- ============================================
-- INDEXES (New Forms)
-- ============================================
CREATE INDEX idx_form_list_prog_edition ON accreditation_application_form_list(programme_id, edition_id);
CREATE INDEX idx_inputs_form ON accreditation_application_form_input_list(form_id);
CREATE INDEX idx_input_options_input ON accreditation_application_form_input_options(input_id);
CREATE INDEX idx_submissions_form ON form_submission_entries(form_id);
CREATE INDEX idx_answers_submission ON form_submission_answers(submission_entry_id);
CREATE INDEX idx_answers_input ON form_submission_answers(input_id);

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- User Roles
CREATE INDEX idx_user_roles_code ON user_roles(role_code);
CREATE INDEX idx_user_roles_active ON user_roles(is_active) WHERE is_active = true;

-- Groups
CREATE INDEX idx_groups_code ON groups(group_code);
CREATE INDEX idx_groups_order ON groups("order");

-- Page List
CREATE INDEX idx_page_list_code ON page_list(page_code);
CREATE INDEX idx_page_list_category ON page_list(category, category_order);
CREATE INDEX idx_page_list_active ON page_list(is_active) WHERE is_active = true;

-- User Role Page Access
CREATE INDEX idx_urpa_role ON user_role_page_access(role_id);
CREATE INDEX idx_urpa_page ON user_role_page_access(page_id);

-- Hospitals
CREATE INDEX idx_hospitals_code ON hospitals(hospital_code);
CREATE INDEX idx_hospitals_name ON hospitals(hospital_name);
CREATE INDEX idx_hospitals_type ON hospitals(hospital_type);
CREATE INDEX idx_hospitals_state ON hospitals(state);
CREATE INDEX idx_hospitals_status ON hospitals(membership_status);
CREATE INDEX idx_hospitals_active ON hospitals(is_active) WHERE is_active = true;

-- Hospital Staff
CREATE INDEX idx_hospital_staff_hospital ON hospital_staff(hospital_id);
CREATE INDEX idx_hospital_staff_user ON hospital_staff(user_id);
CREATE INDEX idx_hospital_staff_email ON hospital_staff(user_email);
CREATE INDEX idx_hospital_staff_active ON hospital_staff(is_active) WHERE is_active = true;

-- Accreditation
CREATE INDEX idx_editions_programme ON accreditation_standard_editions(programme_id);
CREATE INDEX idx_service_standards_edition ON service_standards(edition_id);
CREATE INDEX idx_topics_standard ON topics(service_standard_id);
CREATE INDEX idx_criteria_topic ON criteria(topic_id);
CREATE INDEX idx_evidence_criterion ON evidence_of_compliance(criterion_id);
CREATE INDEX idx_evidence_parent ON evidence_of_compliance(parent_evidence_id);

-- Surveys
CREATE INDEX idx_surveys_code ON surveys(survey_code);
CREATE INDEX idx_surveys_hospital ON surveys(hospital_id);
CREATE INDEX idx_surveys_edition ON surveys(edition_id);
CREATE INDEX idx_surveys_status ON surveys(current_status);
CREATE INDEX idx_surveys_type ON surveys(survey_type);
CREATE INDEX idx_surveys_dates ON surveys(scheduled_start_date, scheduled_end_date);
CREATE INDEX idx_surveys_priority ON surveys(priority_level);

-- Survey Roles
CREATE INDEX idx_survey_roles_survey ON survey_roles(survey_id);
CREATE INDEX idx_survey_roles_user ON survey_roles(user_id);
CREATE INDEX idx_survey_roles_active ON survey_roles(is_active) WHERE is_active = true;

-- Survey Service Standards
CREATE INDEX idx_sss_survey ON survey_service_standards(survey_id);
CREATE INDEX idx_sss_standard ON survey_service_standards(service_standard_id);
CREATE INDEX idx_sss_role ON survey_service_standards(assigned_role_id);

-- Survey Evidence
CREATE INDEX idx_survey_evidence_survey ON survey_evidence(survey_id);
CREATE INDEX idx_survey_evidence_evidence ON survey_evidence(evidence_id);
CREATE INDEX idx_survey_evidence_facility_rating ON survey_evidence(facility_rating);
CREATE INDEX idx_survey_evidence_surveyor_rating ON survey_evidence(surveyor_rating);

-- Surveyor Registry
CREATE INDEX idx_surveyor_user ON surveyor_registry(user_id);
CREATE INDEX idx_surveyor_type ON surveyor_registry(surveyor_type);
CREATE INDEX idx_surveyor_status ON surveyor_registry(status);
CREATE INDEX idx_surveyor_available ON surveyor_registry(is_available) WHERE is_available = true;

-- Status History
CREATE INDEX idx_status_history_entity ON status_history(entity_type, entity_id);
CREATE INDEX idx_status_history_date ON status_history(created_date DESC);

-- Audit Logs
CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_date ON audit_logs(created_date DESC);

-- Notifications
CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_unread ON notifications(user_id, is_read) WHERE is_read = false;
CREATE INDEX idx_notifications_date ON notifications(created_date DESC);

-- Events
CREATE INDEX idx_events_dates ON events(start_date, end_date);
CREATE INDEX idx_events_status ON events(status);

-- Claims
CREATE INDEX idx_claims_user ON claims(claimant_user_id);
CREATE INDEX idx_claims_hospital ON claims(hospital_id);
CREATE INDEX idx_claims_status ON claims(status);

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- Navigation Categories
CREATE INDEX idx_nav_categories_code ON navigation_categories(category_code);
CREATE INDEX idx_nav_categories_order ON navigation_categories("order");

-- Navigation Category Rules
CREATE INDEX idx_nav_rules_category ON navigation_category_rules(category_id);
CREATE INDEX idx_nav_rules_order ON navigation_category_rules("order");

-- User List
CREATE INDEX idx_userlist_email ON user_list(email);
CREATE INDEX idx_userlist_username ON user_list(login_username);
CREATE INDEX idx_userlist_hospital ON user_list(hospital_id);
CREATE INDEX idx_userlist_status ON user_list(status);

-- User Role Assignments
CREATE INDEX idx_user_role_assign_user ON user_role_assignments(userlist_id);
CREATE INDEX idx_user_role_assign_role ON user_role_assignments(role_id);
CREATE INDEX idx_user_role_assign_active ON user_role_assignments(is_active) WHERE is_active = true;

-- User Devices
CREATE INDEX idx_user_devices_user ON user_devices(userlist_id);
CREATE INDEX idx_user_devices_type ON user_devices(device_type);
CREATE INDEX idx_user_devices_active ON user_devices(is_active) WHERE is_active = true;

-- API List
CREATE INDEX idx_api_list_endpoint ON api_list(api_endpoint, api_method);
CREATE INDEX idx_api_list_category ON api_list(category);
CREATE INDEX idx_api_list_active ON api_list(is_active) WHERE is_active = true;

-- API Logs
CREATE INDEX idx_api_logs_endpoint ON api_logs(endpoint, method);
CREATE INDEX idx_api_logs_user ON api_logs(userlist_id);
CREATE INDEX idx_api_logs_timestamp ON api_logs(timestamp DESC);
CREATE INDEX idx_api_logs_status ON api_logs(response_status);

-- Error Logs
CREATE INDEX idx_error_logs_type ON error_logs(error_type);
CREATE INDEX idx_error_logs_severity ON error_logs(severity);
CREATE INDEX idx_error_logs_date ON error_logs(created_date DESC);
CREATE INDEX idx_error_logs_resolved ON error_logs(is_resolved);

-- Alerts
CREATE INDEX idx_alerts_severity ON alerts(severity);
CREATE INDEX idx_alerts_is_read ON alerts(is_read);
CREATE INDEX idx_alerts_created ON alerts(created_date DESC);

-- System Health Metrics
CREATE INDEX idx_shm_timestamp ON system_health_metrics(timestamp DESC);
CREATE INDEX idx_shm_error_count ON system_health_metrics(error_count_hourly);

-- Feature Roadmap
CREATE INDEX idx_feature_status ON feature_roadmap(status);
CREATE INDEX idx_feature_priority ON feature_roadmap(priority);

-- Roadmap Items
CREATE INDEX idx_roadmap_category ON roadmap_items(category);
CREATE INDEX idx_roadmap_module ON roadmap_items(module_name);
CREATE INDEX idx_roadmap_status ON roadmap_items(feature_status);

-- Accreditations
CREATE INDEX idx_accreditations_hospital ON accreditations(hospital_id);
CREATE INDEX idx_accreditations_status ON accreditations(status);
CREATE INDEX idx_accreditations_expiry ON accreditations(accreditation_expiry_date);

-- Survey Applications
CREATE INDEX idx_survey_apps_survey ON survey_applications(survey_id);
CREATE INDEX idx_survey_apps_hospital ON survey_applications(hospital_id);
CREATE INDEX idx_survey_apps_status ON survey_applications(application_status);

-- Survey Schedule Summary
CREATE INDEX idx_survey_sched_survey ON survey_schedule_summary(survey_id);
CREATE INDEX idx_survey_sched_year_month ON survey_schedule_summary(year, month);

-- User Survey Summaries
CREATE INDEX idx_user_fac_survey_user ON user_facility_survey_summary(userlist_id);
CREATE INDEX idx_user_fac_survey_survey ON user_facility_survey_summary(survey_id);
CREATE INDEX idx_user_fac_survey_year ON user_facility_survey_summary(year_index, month_index);

CREATE INDEX idx_user_surv_summary_user ON user_surveyor_survey_summary(userlist_id);
CREATE INDEX idx_user_surv_summary_survey ON user_surveyor_survey_summary(survey_id);
CREATE INDEX idx_user_surv_summary_year ON user_surveyor_survey_summary(year_index, month_index);

CREATE INDEX idx_surv_sched_surveyor ON surveyor_schedule_summary(surveyor_userlist_id);
CREATE INDEX idx_surv_sched_survey ON surveyor_schedule_summary(survey_id);
CREATE INDEX idx_surv_sched_year_month ON surveyor_schedule_summary(year, month);

-- SUCCESS MESSAGE
-- ============================================
DO $$
BEGIN
    RAISE NOTICE '========================================';
    RAISE NOTICE 'MSQH AIMS Schema Created Successfully!';
    RAISE NOTICE '51 Tables with indexes created';
    RAISE NOTICE '========================================';
END $$;