param([string]$SecretsFile = (Join-Path $PSScriptRoot 'secrets.psd1'))
. (Join-Path $PSScriptRoot 'common.ps1')
$healthSettings = Read-LocalSettings $SecretsFile
$databaseName = $healthSettings.DatabaseName

# 只操作已有的独立本地数据库，基础 system/infra 应已经初始化。
Invoke-LocalMySql $healthSettings 'SELECT 1 FROM system_users LIMIT 1;' $databaseName | Out-Null
$memberExists = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME='member_user';")[0]
if ($memberExists -gt 0) {
    $uniqueIndex = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME='member_user' AND INDEX_NAME='uk_member_mobile_tenant' AND NON_UNIQUE=0;")[0]
    if ($uniqueIndex -ne 2) {
        throw 'Existing member_user lacks the verified tenant/mobile unique index. Review duplicates and the existing schema before adding it; this initializer will not rewrite an existing table.'
    }
}
foreach ($fileName in @('V000__member_schema.sql', 'V001__health_identity.sql', 'V002__health_menu.sql', 'V003__health_elderly_basic_info.sql', 'V004__health_elderly_social_info.sql', 'V005__health_collection_draft.sql', 'V006__health_social_support.sql', 'V007__health_elderly_collection_remaining.sql')) {
    $sqlPath = Join-Path $repoPath "mysql_script/health/$fileName"
    $sql = Get-Content -LiteralPath $sqlPath -Raw -Encoding UTF8
    if ($sql -match '(?im)^\s*(DROP\s|TRUNCATE\s|DELETE\s|UPDATE\s|ALTER\s|USE\s|CREATE\s+DATABASE\s)') {
        throw "Unexpected destructive or modifying statement in $fileName. Review it before applying."
    }
    $sourcePath = $sqlPath.Replace('\', '/')
    Invoke-LocalMySql $healthSettings "SOURCE $sourcePath;" $databaseName | Out-Null
    Write-Output "Applied $fileName (existing rows preserved)."
}
Invoke-LocalMySql $healthSettings "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME LIKE 'health_%' ORDER BY table_name;" $databaseName
$healthTableCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME IN ('health_identity','health_elderly_profile','health_volunteer_profile','health_family_relation');")[0]
$healthPermissionCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM system_menu WHERE permission IN ('health:identity:query','health:identity:create','health:identity:update','health:identity:audit','health:identity:update-status','health:family-relation:query','health:family-relation:create','health:family-relation:review','health:family-relation:revoke') AND deleted=b'0';" $databaseName)[0]
$basicInfoColumnCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$databaseName' AND ((TABLE_NAME='health_identity' AND COLUMN_NAME IN ('id_card','ethnicity','has_wechat')) OR (TABLE_NAME='health_elderly_profile' AND COLUMN_NAME IN ('collection_step','special_case')));")[0]
$socialColumnCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME='health_elderly_profile' AND COLUMN_NAME IN ('marital_status','native_place','residence_area','residence_address','residence_type','living_status','household_type','education_level','occupation','elderly_university');")[0]
$supportTableCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME IN ('health_collection_draft','health_region_altitude','health_elderly_spouse');")[0]
$remainingColumnCount = [int]@(Invoke-LocalMySql $healthSettings "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='$databaseName' AND TABLE_NAME='health_elderly_profile' AND COLUMN_NAME IN ('insurance_type','income_range','medical_insurance_number','care_level','disabled','disability_type','disability_reason','chronic_disease_status','chronic_diseases','father_alive','mother_alive','father_death_reason','mother_death_reason','father_death_reason_detail','mother_death_reason_detail','emergency_contacts','collector_id','collector_type','collect_time','collection_completed_time','consent_confirmed','consent_confirm_time','consent_collector_id');")[0]
if ($healthTableCount -ne 4 -or $healthPermissionCount -ne 9 -or $basicInfoColumnCount -ne 5 -or $socialColumnCount -ne 10 -or $supportTableCount -ne 3 -or $remainingColumnCount -ne 23) { throw "Health schema or menus were not initialized completely (step-one: $basicInfoColumnCount, step-two: $socialColumnCount, support tables: $supportTableCount, remaining collection columns: $remainingColumnCount)." }
Write-Output 'Health schema and menus are ready. Reload Maven and restart your IDEA backend/frontend to load the new code.'