/**
 * Realistic Oracle AWR demo data for health check demonstrations.
 * Simulates a production OLTP database with typical performance issues.
 */

const DEMO_METRICS = {
  instance: {
    db_name: 'EBSDB',
    instance_name: 'ebsdb',
    host_name: 'prod-ebs-db.example.com',
    version: '19.0.0.0.0',
    platform: 'Linux x86 64-bit',
    startup_time: '2026-06-10 03:00:00',
    uptime_days: 14,
    uptime_hours: 336.0,
    rac: false,
    cpus: 8,
    sga_target_gb: 2,
    pga_aggregate_target_gb: 1,
    db_block_size: 8192
  },

  tablespaces: [
    { name: 'SYSTEM',          used_gb:   1.4, total_gb:   3.0, pct_used: 46.7, autoextend: true,  status: 'ok' },
    { name: 'SYSAUX',          used_gb:   2.2, total_gb:   4.0, pct_used: 55.0, autoextend: true,  status: 'ok' },
    { name: 'UNDOTBS1',        used_gb:   3.1, total_gb:   8.0, pct_used: 38.8, autoextend: true,  status: 'ok' },
    { name: 'APPS_TS_TX_DATA', used_gb: 261.4, total_gb: 320.0, pct_used: 81.7, autoextend: false, status: 'warning' },
    { name: 'APPS_TS_TX_IDX',  used_gb:  98.3, total_gb: 160.0, pct_used: 61.4, autoextend: true,  status: 'ok' },
    { name: 'APPS_TS_SEED',    used_gb:  14.2, total_gb:  32.0, pct_used: 44.4, autoextend: true,  status: 'ok' },
    { name: 'DCM',             used_gb:  28.8, total_gb:  32.0, pct_used: 89.9, autoextend: false, status: 'critical' },
    { name: 'ODM_DATA',        used_gb:  28.5, total_gb:  32.0, pct_used: 89.1, autoextend: false, status: 'warning' },
    { name: 'TEMP',            used_gb:   4.2, total_gb:  16.0, pct_used: 26.3, autoextend: true,  status: 'ok' },
    { name: 'USERS',           used_gb:   0.1, total_gb:   1.0, pct_used: 10.0, autoextend: true,  status: 'ok' }
  ],

  wait_events: [
    { event: 'log file sync',                 wait_class: 'Commit',      total_waits: 1245678, time_waited_s: 2847.3, avg_wait_ms: 2.29, pct_db_time: 38.7 },
    { event: 'db file sequential read',       wait_class: 'User I/O',    total_waits: 3421098, time_waited_s: 2076.4, avg_wait_ms: 0.61, pct_db_time: 28.2 },
    { event: 'direct path read',              wait_class: 'User I/O',    total_waits:  892341, time_waited_s:  287.4, avg_wait_ms: 0.32, pct_db_time:  3.9 },
    { event: 'log file parallel write',       wait_class: 'System I/O',  total_waits: 1245678, time_waited_s:  189.3, avg_wait_ms: 0.15, pct_db_time:  2.6 },
    { event: 'db file scattered read',        wait_class: 'User I/O',    total_waits:  234567, time_waited_s:  145.2, avg_wait_ms: 0.62, pct_db_time:  2.0 },
    { event: 'enq: TX - row lock contention', wait_class: 'Application', total_waits:    1234, time_waited_s:   42.1, avg_wait_ms: 34.1, pct_db_time:  0.6 },
    { event: 'gc buffer busy acquire',        wait_class: 'Cluster',     total_waits:       0, time_waited_s:    0.0, avg_wait_ms:  0.0, pct_db_time:  0.0 },
    { event: 'buffer busy waits',             wait_class: 'Concurrency', total_waits:   45234, time_waited_s:   28.4, avg_wait_ms: 0.63, pct_db_time:  0.4 }
  ],

  top_sql: [
    {
      sql_id: '8s6unu0n34g0t',
      sql_text: 'SELECT /* OID_APPL_USER */ U.USER_ID, U.USER_NAME, U.ENCRYPTED_FOUNDATION_PASSWORD, U.LAST_LOGON_DATE FROM APPLSYS.FND_USER U WHERE U.USER_NAME = :B1 AND (U.END_DATE IS NULL OR U.END_DATE > SYSDATE)',
      executions: 137,
      elapsed_time_s: 29.6,
      cpu_time_s: 5.2,
      buffer_gets: 18234567,
      disk_reads: 234512,
      rows_processed: 137,
      elapsed_per_exec_ms: 216,
      buffer_gets_per_exec: 133100,
      plan_hash: '2847291045',
      issue: 'High buffer gets per execution — full table scan on FND_USER (missing index on USER_NAME)'
    },
    {
      sql_id: '00f34kc6pr2wn',
      sql_text: "SELECT DECODE(COUNT(*),0,'FALSE','TRUE') FROM APPLSYS.FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_KEY = :B2 AND APPLICATION_ID = :B1 AND (END_DATE IS NULL OR END_DATE > SYSDATE)",
      executions: 3,
      elapsed_time_s: 15.5,
      cpu_time_s: 14.8,
      buffer_gets: 45234567,
      disk_reads: 8923412,
      rows_processed: 3,
      elapsed_per_exec_ms: 5160,
      buffer_gets_per_exec: 15078189,
      plan_hash: '1923847561',
      issue: 'Very slow — 5160ms/exec, full scan on FND_RESPONSIBILITY_VL (missing index on RESPONSIBILITY_KEY)'
    },
    {
      sql_id: 'g7k2w9n4p1m8x',
      sql_text: 'SELECT /* FND_CONCURRENT_REQUESTS */ R.REQUEST_ID, R.PROGRAM_ID, R.STATUS_CODE, R.COMPLETION_TEXT FROM APPS.FND_CONCURRENT_REQUESTS R WHERE R.STATUS_CODE IN (:B1, :B2) AND R.REQUESTED_START_DATE <= SYSDATE ORDER BY R.PRIORITY, R.REQUESTED_START_DATE',
      executions: 4821,
      elapsed_time_s: 1203.4,
      cpu_time_s: 987.2,
      buffer_gets: 9823456,
      disk_reads: 345678,
      rows_processed: 48210,
      elapsed_per_exec_ms: 249.5,
      buffer_gets_per_exec: 2037,
      plan_hash: '4756192834',
      issue: 'Concurrent Manager request query — high execution count during batch windows'
    },
    {
      sql_id: 't5r1h8v3j6c9q',
      sql_text: 'UPDATE APPS.WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B3, ACTIVITY_RESULT_CODE = :B4, ASSIGNED_USER = :B5, NOTIFICATION_ID = :B6, DUE_DATE = :B7 WHERE ITEM_TYPE = :B1 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B8',
      executions: 8923,
      elapsed_time_s: 445.6,
      cpu_time_s: 189.3,
      buffer_gets: 5623456,
      disk_reads: 12345,
      rows_processed: 8923,
      elapsed_per_exec_ms: 49.9,
      buffer_gets_per_exec: 630,
      plan_hash: '8271934650',
      issue: 'WF_ITEM_ACTIVITY_STATUSES UPDATE — Workflow Mailer backlog driving high DML volume'
    }
  ],

  index_analysis: [
    { owner: 'APP', index_name: 'IDX_ORDERS_DATE', table_name: 'ORDERS', size_mb: 2340, blevel: 3, leaf_blocks: 28456, clustering_factor: 4523412, pct_deleted: 38, status: 'fragmented' },
    { owner: 'APP', index_name: 'IDX_CUST_EMAIL', table_name: 'CUSTOMERS', size_mb: 456, blevel: 2, leaf_blocks: 5678, clustering_factor: 234567, pct_deleted: 12, status: 'ok' },
    { owner: 'APP', index_name: 'IDX_INV_PROD_WH', table_name: 'INVENTORY', size_mb: 189, blevel: 2, leaf_blocks: 2345, clustering_factor: 123456, pct_deleted: 45, status: 'fragmented' },
    { owner: 'APP', index_name: 'IDX_TRANS_CREATED', table_name: 'TRANSACTIONS', size_mb: 8901, blevel: 3, leaf_blocks: 109876, clustering_factor: 12345678, pct_deleted: 52, status: 'critical' },
    { owner: 'APP', index_name: 'IDX_AUDIT_CREATED', table_name: 'AUDIT_LOG', size_mb: 12450, blevel: 4, leaf_blocks: 154321, clustering_factor: 23456789, pct_deleted: 67, status: 'critical' },
    { owner: 'APP', index_name: 'IDX_SESS_USER', table_name: 'SESSIONS', size_mb: 678, blevel: 2, leaf_blocks: 8765, clustering_factor: 345678, pct_deleted: 8, status: 'ok' },
    { owner: 'APP', index_name: 'PK_ORDERS', table_name: 'ORDERS', size_mb: 1230, blevel: 3, leaf_blocks: 15234, clustering_factor: 2345678, pct_deleted: 5, status: 'ok' },
    { owner: 'APP', index_name: 'IDX_OL_ORDER', table_name: 'ORDER_LINES', size_mb: 3456, blevel: 3, leaf_blocks: 42345, clustering_factor: 5678901, pct_deleted: 41, status: 'fragmented' }
  ],

  sga_stats: {
    sga_size_gb: 2,
    buffer_cache_gb: 1.2,
    shared_pool_gb: 0.6,
    large_pool_gb: 0.1,
    java_pool_gb: 0.1,
    streams_pool_gb: 0,
    buffer_cache_hit_ratio: 83.8,
    library_cache_hit_ratio: 96.4,
    dictionary_cache_hit_ratio: 97.1,
    shared_pool_free_pct: 8.2,
    hard_parses_per_sec: 45.2,
    soft_parses_per_sec: 1234.5
  },

  pga_stats: {
    pga_target_gb: 1,
    pga_allocated_gb: 0.8,
    pga_max_allocated_gb: 0.9,
    over_allocation_count: 12,
    cache_hit_pct: 78.4,
    optimal_executions_pct: 81.2,
    onepass_executions_pct: 14.3,
    multipass_executions_pct: 4.5
  },

  redo_stats: {
    redo_size_mb_per_hour: 2345.6,
    log_switches_per_hour: 8.4,
    log_file_size_mb: 500,
    log_groups: 4,
    avg_log_sync_ms: 0.7,
    max_log_sync_ms: 45.2
  },

  os_stats: {
    cpu_count: 16,
    avg_cpu_utilization_pct: 67.8,
    max_cpu_utilization_pct: 94.2,
    avg_io_wait_pct: 12.3,
    physical_memory_gb: 64,
    free_memory_gb: 8.2,
    swap_used_gb: 0.4,
    avg_disk_read_ms: 2.1,
    avg_disk_write_ms: 1.8
  },

  undo_stats: {
    current: {
      tablespace_name: 'UNDOTBS1',
      total_gb: 20,
      used_gb: 13.4,
      pct_used: 67,
      tuned_undo_retention_s: 900,
      max_query_length_s: 2400,
      active_blocks: 2341,
      unexpired_blocks: 18432,
      expired_blocks: 4096,
      transaction_count: 1247,
      max_concurrency: 34,
      retention_mode: 'NOGUARANTEE'
    },
    historical: {
      peak_pct_used: 94.1,
      peak_time: '2026-03-15 23:41',
      peak_query_length_s: 2400,
      max_tuned_retention_min: 18,
      lookback_days: 30
    },
    awr_available: true
  },

  temp_stats: {
    current: {
      tablespace_name: 'TEMP',
      total_gb: 32,
      used_gb: 14.4,
      free_gb: 17.6,
      pct_used: 45,
      top_sessions: [
        { sid: 245, serial: 12341, username: 'APPUSER', temp_mb: 2148, tablespace: 'TEMP' },
        { sid: 198, serial: 8823, username: 'BATCHJOB', temp_mb: 891, tablespace: 'TEMP' },
        { sid: 312, serial: 4412, username: 'APPUSER', temp_mb: 445, tablespace: 'TEMP' }
      ]
    },
    historical: {
      peak_gb: 29.1,
      peak_pct: 90.9,
      peak_time: '2026-03-12 02:14',
      lookback_days: 30
    },
    awr_available: true
  },

  alert_log: {
    entries: [
      { ts: '2026-04-29 03:14:22', message: 'ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_3287647382$" too small', severity: 'critical' },
      { ts: '2026-04-29 02:41:05', message: 'checkpoint not complete', severity: 'warning' },
      { ts: '2026-04-29 02:41:05', message: 'Thread 1 cannot allocate new log, sequence 8823', severity: 'warning' },
      { ts: '2026-04-28 23:18:44', message: 'checkpoint not complete', severity: 'warning' },
      { ts: '2026-04-28 21:05:13', message: 'TNS-12560: TNS:protocol adapter error', severity: 'noise' },
      { ts: '2026-04-28 21:05:12', message: 'TNS-12560: TNS:protocol adapter error', severity: 'noise' },
      { ts: '2026-04-28 21:05:11', message: 'TNS-12560: TNS:protocol adapter error', severity: 'noise' },
      { ts: '2026-04-28 18:30:01', message: 'Starting ORACLE instance (normal)', severity: 'info' }
    ],
    summary: {
      total: 23,
      critical: 1,
      warning: 3,
      info: 4,
      noise: 15
    }
  },

  resource_limits: {
    current: [
      { resource: 'sessions', current_utilization: 312, max_utilization: 487, limit_value: 600, limit_display: '600', pct_max_used: 81, status: 'warning' },
      { resource: 'processes', current_utilization: 285, max_utilization: 451, limit_value: 500, limit_display: '500', pct_max_used: 90, status: 'critical' },
      { resource: 'transactions', current_utilization: 124, max_utilization: 189, limit_value: 660, limit_display: '660', pct_max_used: 29, status: 'ok' },
      { resource: 'enqueue_locks', current_utilization: 1847, max_utilization: 3241, limit_value: 10000, limit_display: '10000', pct_max_used: 32, status: 'ok' },
      { resource: 'enqueue_resources', current_utilization: 923, max_utilization: 1741, limit_value: 5000, limit_display: '5000', pct_max_used: 35, status: 'ok' },
      { resource: 'dml_locks', current_utilization: 48, max_utilization: 112, limit_value: null, limit_display: 'UNLIMITED', pct_max_used: null, status: 'ok' }
    ],
    historical: {
      sessions: { hist_max: 487, hist_peak: 521 },
      processes: { hist_max: 451, hist_peak: 492 }
    },
    awr_available: true
  },

  sga_pga_history: {
    current: {
      sga_target_gb: 24,
      pga_target_gb: 8,
      sga_max_gb: 24,
      memory_target_gb: 0
    },
    resize_ops: [
      { op_time: '2026-04-28 14:32', component: 'shared pool', oper_type: 'GROW', from_gb: 4.5, to_gb: 6.0, status: 'COMPLETE' },
      { op_time: '2026-04-28 14:32', component: 'DEFAULT buffer cache', oper_type: 'SHRINK', from_gb: 8.0, to_gb: 6.5, status: 'COMPLETE' },
      { op_time: '2026-04-27 08:15', component: 'shared pool', oper_type: 'SHRINK', from_gb: 6.0, to_gb: 4.5, status: 'COMPLETE' },
      { op_time: '2026-04-27 08:15', component: 'DEFAULT buffer cache', oper_type: 'GROW', from_gb: 6.5, to_gb: 8.0, status: 'COMPLETE' }
    ],
    pga_history: {
      peak_allocated_gb: 11.2,
      peak_time: '2026-04-20 02:14',
      lookback_days: 30
    },
    sga_component_history: [
      { component: 'Database Buffers', peak_gb: 8.5, min_gb: 6.5 },
      { component: 'Shared Pool Size', peak_gb: 6.0, min_gb: 4.5 },
      { component: 'Large Pool Size', peak_gb: 0.5, min_gb: 0.5 },
      { component: 'Java Pool Size', peak_gb: 0.25, min_gb: 0.25 }
    ],
    awr_available: true
  },

  backup_stats: {
    overall_status: 'critical',
    rman_backup: {
      status: 'critical',
      rman_available: false,
      full_backup_hours_ago: null,
      last_full_backup: null,
      last_incremental_backup: null,
      last_archivelog_backup: null,
      last_by_type: [],
      recent_jobs: []
    },
    fra_usage: {
      status: 'warning',
      fra_configured: true,
      location: '+FRA',
      limit_gb: 800,
      used_gb: 672.4,
      reclaimable_gb: 89.3,
      pct_used: 84.1,
      pct_reclaimable: 11.2,
      archivelogs_24h_gb: 48.7,
      hours_until_full: 53,
      file_type_breakdown: [
        { file_type: 'BACKUP PIECE', pct_used: 62.3, pct_reclaimable: 8.4, number_of_files: 124 },
        { file_type: 'ARCHIVED LOG', pct_used: 18.2, pct_reclaimable: 2.1, number_of_files: 1847 },
        { file_type: 'FLASHBACK LOG', pct_used: 2.9, pct_reclaimable: 0.7, number_of_files: 48 },
        { file_type: 'CONTROL FILE', pct_used: 0.1, pct_reclaimable: 0, number_of_files: 1 },
        { file_type: 'REDO LOG', pct_used: 0.6, pct_reclaimable: 0, number_of_files: 6 }
      ]
    },
    archivelog_rate: {
      status: 'warning',
      log_mode: 'ARCHIVELOG',
      archivelog_mode: true,
      switches_per_hour: 22.4,
      switches_24h: 537,
      archivelogs_24h: 537,
      total_size_mb_24h: 49868,
      hourly_breakdown: [
        { hour: '2026-04-29 07', log_count: 19, size_mb: 1820 },
        { hour: '2026-04-29 06', log_count: 18, size_mb: 1710 },
        { hour: '2026-04-29 05', log_count: 17, size_mb: 1680 },
        { hour: '2026-04-29 04', log_count: 16, size_mb: 1580 },
        { hour: '2026-04-29 03', log_count: 14, size_mb: 1380 },
        { hour: '2026-04-29 02', log_count: 15, size_mb: 1490 },
        { hour: '2026-04-29 01', log_count: 27, size_mb: 2890 },
        { hour: '2026-04-29 00', log_count: 24, size_mb: 2540 },
        { hour: '2026-04-28 23', log_count: 23, size_mb: 2380 },
        { hour: '2026-04-28 22', log_count: 22, size_mb: 2250 }
      ],
      log_groups: [
        { group_num: 1, members: 2, size_mb: 512, status: 'CURRENT', archived: 'NO' },
        { group_num: 2, members: 2, size_mb: 512, status: 'ACTIVE', archived: 'NO' },
        { group_num: 3, members: 2, size_mb: 512, status: 'INACTIVE', archived: 'YES' },
        { group_num: 4, members: 2, size_mb: 512, status: 'INACTIVE', archived: 'YES' },
        { group_num: 5, members: 2, size_mb: 512, status: 'INACTIVE', archived: 'YES' },
        { group_num: 6, members: 2, size_mb: 512, status: 'INACTIVE', archived: 'YES' }
      ]
    },
    backup_validation: {
      status: 'ok',
      backup_corruptions: 0,
      backup_corrupt_blocks: 0,
      copy_corruptions: 0,
      copy_corrupt_blocks: 0,
      total_corruptions: 0,
      last_3_backups_failed: false,
      recent_operations: [
        { operation: 'BACKUP', status: 'COMPLETED', start_time: '2026-04-29 06:00:02', end_time: '2026-04-29 06:04:18', mbytes_processed: 4301, output: '' },
        { operation: 'BACKUP', status: 'COMPLETED', start_time: '2026-04-29 00:00:55', end_time: '2026-04-29 00:23:41', mbytes_processed: 39628, output: '' },
        { operation: 'BACKUP', status: 'FAILED', start_time: '2026-04-28 08:00:45', end_time: '2026-04-28 08:11:02', mbytes_processed: 0, output: 'ORA-19502: write error on file, block number 1 (block size=512)' },
        { operation: 'BACKUP', status: 'COMPLETED', start_time: '2026-04-28 00:01:05', end_time: '2026-04-28 01:47:22', mbytes_processed: 493930, output: '' }
      ]
    }
  },

  ebs_detected: true,

  // EBS Operations — only populated when ebs_detected is true
  ebs_operations: {
    concurrent_managers: {
      cm01: { name: 'Internal Manager', max_processes: 1, running_processes: 1, control_code: 'A', status: 'ok' },
      cm02: { pending_requests: 1 },
      // Bug 3 fix — cm03 is OPP (Output Post Processor), not Conflict Manager
      cm03: { name: 'Output Post Processor', max_processes: 2, running_processes: 2, status: 'ok', recommendation: 'OPP healthy: 2/2 process(es) running.' },
      cm05: { completed_24h: 3842, avg_runtime_secs: 14.3 },
      cm06: [
        { name: 'Standard Manager',   max_processes: 10, running_processes: 8, target_processes: 10 },
        { name: 'Internal Manager',   max_processes: 1,  running_processes: 1, target_processes: 1  },
        { name: 'Conflict Manager',   max_processes: 1,  running_processes: 1, target_processes: 1  },
        { name: 'Output Post Proc',   max_processes: 2,  running_processes: 2, target_processes: 2  },
        { name: 'Receivables TX Proc',max_processes: 4,  running_processes: 3, target_processes: 4  }
      ],
      cm09: [
        { program: 'XLAACCPB - Accounting Program', start_time: '2026-05-11 01:00', end_time: '2026-05-11 03:47', runtime_secs: 10020 },
        { program: 'RAXTRX - AutoInvoice Import', start_time: '2026-05-10 22:00', end_time: '2026-05-10 23:11', runtime_secs: 4260 },
        { program: 'ARXRWMAI - Aging - 4 Buckets', start_time: '2026-05-10 06:00', end_time: '2026-05-10 06:48', runtime_secs: 2880 },
        { program: 'GLPPOS - Period Close Summary', start_time: '2026-05-09 23:00', end_time: '2026-05-10 00:22', runtime_secs: 5520 },
        { program: 'APXPAWKB - Payables Approval', start_time: '2026-05-09 08:30', end_time: '2026-05-09 09:04', runtime_secs: 2040 }
      ],
      cm10: { error_requests_24h: 4 }
    },
    workflow: {
      wf01: [
        { item_type: 'OEOL',   count: 124830 },
        { item_type: 'APEXP',  count: 89421  },
        { item_type: 'REQAPPRV', count: 52107 },
        { item_type: 'POREQCHA', count: 41823 },
        { item_type: 'APINV',  count: 38290  }
      ],
      wf02: { error_count: 147 },
      // Bug 3 fix — wf03 holds Workflow Mailer status (was incorrectly holding deferred queue depth)
      wf03: { mailer_running: false, status: 'DEACTIVATED_SYSTEM', startup_mode: 'AUTOMATIC', deferred_ready: 0 },
      wf07: [
        { item_type: 'OEOL',     count: 612 },
        { item_type: 'APEXP',    count: 287 },
        { item_type: 'REQAPPRV', count: 145 }
      ],
      wf08: { pending_over_2h: 1044, pending_over_8h: 312 },
      // Bug 5 fix — wf09 uses name/status/startup_mode/enabled shape from FND_SVC_COMPONENTS
      wf09: [
        { name: 'Workflow Mailer',              status: 'DEACTIVATED_SYSTEM', startup_mode: 'AUTOMATIC', enabled: true },
        { name: 'Workflow Agent Listener',      status: 'RUNNING',            startup_mode: 'AUTOMATIC', enabled: true },
        { name: 'WF Deferred Agent Listener',   status: 'RUNNING',            startup_mode: 'AUTOMATIC', enabled: true },
        { name: 'WF Error Agent Listener',      status: 'RUNNING',            startup_mode: 'AUTOMATIC', enabled: true },
        { name: 'Workflow Notification Mailer', status: 'NOT_CONFIGURED',     startup_mode: 'MANUAL',    enabled: false }
      ]
    },
    // Bug 5 fix — _adop_status: ADOP session health from AD_ADOP_SESSIONS
    _adop_status: {
      check_id: 'ADOP_STATUS',
      status: 'ok',
      severity: 'info',
      message: 'No active or failed ADOP patching sessions.',
      sessions: [],
      active_sessions: 0,
      failed_sessions: 0
    },
    security: {
      sc12: { signon_audit_level: 'FORM', audit_enabled: true },
      sc14: [
        { user_name: 'SYSADMIN',  responsibility: 'System Administrator' },
        { user_name: 'JSMITH',    responsibility: 'System Administrator' }
      ]
    },
    functional: {
      fb01: [
        { program: 'XLAACCPB - Accounting Program', error_count: 12 },
        { program: 'RAXTRX - AutoInvoice Import',   error_count: 7  },
        { program: 'APPPOVRD - AP Period Close',     error_count: 3  }
      ],
      fb03: { pending_over_7d: 14 },
      fb04: { active_users_24h: 312 }
    },
    observability: {
      ot05: { completed_per_hour: 187.4 }
    },
    config_drift: {
      cd07: {
        SIGNON_PASSWORD_LENGTH: '8',
        SIGNON_PASSWORD_HARD_TO_GUESS: 'Y',
        SIGNON_PASSWORD_NO_REUSE: '6',
        SIGNON_PASSWORD_FAILURE_LIMIT: '5'
      }
    }
  },

  awr_available: true,

  // Application fingerprinting — detected from schema/table probes
  detected_apps: [
    { key: 'EBS',  label: 'EBS 12.2',      schema: 'APPS'    },
    { key: 'JDE',  label: 'JD Edwards',     schema: 'PRODDTA' },
    { key: 'INFA', label: 'Informatica',    schema: 'INFA_DOMAIN' }
  ],

  snapshot_info: {
    begin_snap_id: 45678,
    end_snap_id: 45690,
    begin_time: '2026-04-26 00:00:00',
    end_time: '2026-04-26 12:00:00',
    elapsed_time_min: 720,
    db_time_min: 6507.3
  }
};

function getDemoMetrics() {
  return JSON.parse(JSON.stringify(DEMO_METRICS));
}

function getSummaryScores(metrics) {
  const scores = {};

  // Tablespace score
  const criticalTs = (metrics.tablespaces || []).filter(t => t.pct_used > 90).length;
  const warningTs = (metrics.tablespaces || []).filter(t => t.pct_used > 80 && t.pct_used <= 90).length;
  scores.tablespace = Math.max(0, 100 - (criticalTs * 25) - (warningTs * 10));

  // Wait events score — weights % of DB time consumed by each non-idle wait class.
  //
  // Why all classes matter: Oracle's 'Other' class captures undo segment latch contention
  // and other unclassified waits. A DB spending 60%+ of time on 'Other' or 'Concurrency'
  // waits is critically sick — the old algorithm only checked Application and Concurrency,
  // so undo latch storms classified as 'Other' produced a falsely high score (~93).
  //
  // Severity tiers (penalty per % of DB time):
  //   Application (row locks, enqueues)    : 3.0× — always bad
  //   Concurrency (buffer busy, library cache, cursor pins) : 3.0× — always bad
  //   Other (latch free, undo segment)     : 2.5× — often very bad, catches undo latches
  //   Commit (log file sync)               : 2.0× after 3% free allowance (some sync is normal)
  //   Cluster (RAC waits)                  : 2.0× after 2% free allowance
  //   User I/O / System I/O               : excluded — expected for OLTP
  const waitEvents = metrics.wait_events || [];
  const sumByClass = (cls) => waitEvents.filter(w => w.wait_class === cls).reduce((s, w) => s + (w.pct_db_time || 0), 0);
  const appWaits  = sumByClass('Application');
  const concWaits = sumByClass('Concurrency');
  const otherWaits = sumByClass('Other');
  const commitWaits = sumByClass('Commit');
  const clusterWaits = sumByClass('Cluster');
  const waitPenalty =
    appWaits * 3.0 +
    concWaits * 3.0 +
    otherWaits * 2.5 +
    Math.max(0, commitWaits - 3) * 2.0 +
    Math.max(0, clusterWaits - 2) * 2.0;
  scores.wait_events = Math.max(0, Math.round(100 - waitPenalty));

  // SQL performance score
  const slowSql = (metrics.top_sql || []).filter(s => s.elapsed_per_exec_ms > 5).length;
  const highBufferSql = (metrics.top_sql || []).filter(s => s.buffer_gets_per_exec > 500).length;
  scores.sql_performance = Math.max(0, 100 - (slowSql * 15) - (highBufferSql * 10));

  // Index health score (still computed — shown in Indexes tab, just not in top cards)
  const criticalIdx = (metrics.index_analysis || []).filter(i => i.pct_deleted > 50).length;
  const fragIdx = (metrics.index_analysis || []).filter(i => i.pct_deleted > 30 && i.pct_deleted <= 50).length;
  scores.index_health = Math.max(0, 100 - (criticalIdx * 20) - (fragIdx * 10));

  // Active Sessions score — derived from resource_limits sessions data
  // 🔴 >90% of limit | 🟡 >70% | 🟢 <70%
  const sessionResource = (metrics.resource_limits && metrics.resource_limits.current || [])
    .find(r => r.resource === 'sessions');
  if (sessionResource && sessionResource.pct_max_used != null) {
    const pctUsed = sessionResource.pct_max_used;
    scores.active_sessions = Math.max(0, Math.round(
      pctUsed >= 95 ? 100 - pctUsed * 1.2 :
      pctUsed >= 80 ? 100 - (pctUsed - 60) * 1.5 :
      pctUsed >= 60 ? 100 - (pctUsed - 50) * 0.8 :
      100 - pctUsed * 0.3
    ));
  } else {
    scores.active_sessions = 75; // default when data unavailable
  }

  // Memory score — Oracle internals (SGA/PGA) + OS-level free RAM.
  //
  // The old algorithm scored only Oracle internals, so a database with perfect buffer cache
  // hits on a host with 0.5 GB free RAM still showed 100. OS memory pressure causes OOM
  // kills, massive swapping, and Oracle process crashes regardless of buffer hit ratios.
  //
  // Oracle penalties:
  //   buffer_cache_hit_ratio < 95%    : -15 (cache misses → excess physical I/O)
  //   multipass_executions_pct > 1%   : -10 (PGA spilling to disk)
  //   shared_pool_free_pct < 15%      : -10 (shared pool under pressure)
  //
  // OS memory penalties (free_memory_gb / physical_memory_gb):
  //   < 2% free  : -35 (near OOM — severe risk of process kill / swap storm)
  //   < 5% free  : -25 (critical — host starved)
  //   < 10% free : -15 (warning — elevated swap risk)
  //   < 15% free :  -5 (info — worth watching)
  const sgaStats = metrics.sga_stats || {};
  const pgaStats = metrics.pga_stats || {};
  const osStats  = metrics.os_stats  || {};
  const bufferHitPenalty  = (sgaStats.buffer_cache_hit_ratio || 100) < 95 ? 15 : 0;
  const pgaPenalty        = (pgaStats.multipass_executions_pct || 0) > 1  ? 10 : 0;
  const sharedPoolPenalty = (sgaStats.shared_pool_free_pct || 100) < 15   ? 10 : 0;
  let osMemPenalty = 0;
  if (osStats.free_memory_gb != null && osStats.physical_memory_gb) {
    const freeMemPct = (osStats.free_memory_gb / osStats.physical_memory_gb) * 100;
    if      (freeMemPct < 2)  osMemPenalty = 35;
    else if (freeMemPct < 5)  osMemPenalty = 25;
    else if (freeMemPct < 10) osMemPenalty = 15;
    else if (freeMemPct < 15) osMemPenalty =  5;
  }
  scores.memory = Math.max(0, 100 - bufferHitPenalty - pgaPenalty - sharedPoolPenalty - osMemPenalty);

  // Overall score (weighted average) — top 5 cards
  scores.overall = Math.round(
    scores.tablespace * 0.2 +
    scores.wait_events * 0.2 +
    scores.sql_performance * 0.25 +
    scores.active_sessions * 0.2 +
    scores.memory * 0.15
  );

  return scores;
}

// Seeded AI analysis — deterministic, matches DEMO_METRICS exactly.
// Used instead of calling OpenAI so demo results are identical every run.
const DEMO_AI_ANALYSIS = `## Health Overview

### Storage

| Tablespace | Used | Capacity | Status | Autoextend |
|------------|------|----------|--------|------------|
| DCM | **89.9%** | 28.8GB / 32.0GB | 🔴 CRITICAL | OFF |
| ODM_DATA | **89.1%** | 28.5GB / 32.0GB | 🟡 WARNING | OFF |
| APPS_TS_TX_DATA | **81.7%** | 261.4GB / 320.0GB | 🟡 WARNING | OFF |

### Performance

**Top Wait Events:**

- log file sync [Commit]: **38.7% DB time** — 1,245,678 waits, avg 2.29ms — redo log I/O bottleneck
- db file sequential read [User I/O]: **28.2% DB time** — 3,421,098 waits, avg 0.61ms

**Slow SQL:** 2 statements above 5ms/exec threshold

- SQL_ID **8s6unu0n34g0t**: 216ms/exec (137 execs) — 133,100 buffer gets/exec, full scan on FND_USER
- SQL_ID **00f34kc6pr2wn**: 5,160ms/exec (3 execs) — 15M+ buffer gets/exec, full scan on FND_RESPONSIBILITY_VL

### Memory

- Buffer cache hit ratio: **83.8%** (target >95%) — well below threshold
- Library cache hit ratio: 96.4%
- Shared pool free: **8.2%** (target >15%) — under pressure
- PGA multi-pass: **4.5%** — sort/hash spill to disk

### Backup & Recovery

- **RMAN not configured** — no backup policy, no recovery point
- Database has no automated backup protection

---

## 🔴 CRITICAL: RMAN Backup Not Configured

**EBSDB** has no RMAN backup policy. There is no recovery point and no way to restore this database after storage failure or corruption. This is the highest-priority remediation.

**Configure RMAN immediately:**
\`\`\`sql
-- Connect to RMAN and configure basic backup policy
-- rman target /
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT;
\`\`\`

---

## 🔴 CRITICAL: Log File Sync at 38.7% DB Time

**EBSDB** — log file sync is consuming 38.7% of all database time with an average wait of 2.29ms. This is the top wait event and indicates redo log write latency — typically caused by slow storage on the redo log volume or undersized redo logs.

**Diagnosis:**
\`\`\`sql
-- Check redo log size and location
SELECT l.group#, l.members, l.bytes/1024/1024 size_mb, l.status, m.member
FROM v$log l JOIN v$logfile m ON l.group# = m.group#
ORDER BY l.group#;

-- Check current I/O latency on log writes
SELECT name, value FROM v$sysstat
WHERE name IN ('redo writes','redo write time','redo blocks written');
\`\`\`

**Fix:** If redo log files are on shared/slow storage, move them to dedicated fast storage (SSD/NVMe). Increase redo log size to reduce switch frequency:
\`\`\`sql
-- Add new redo log members with larger size
ALTER DATABASE ADD LOGFILE GROUP 5 SIZE 512M;
ALTER DATABASE ADD LOGFILE GROUP 6 SIZE 512M;
-- Drop old small groups after switching away from them
\`\`\`

---

## 🔴 CRITICAL: DCM Tablespace at 89.9% (Autoextend OFF)

DCM tablespace is at 89.9% with autoextend disabled. Oracle Document Content Management data will stop writing if this fills.

\`\`\`sql
-- Check datafiles and available space
SELECT tablespace_name, file_name, bytes/1024/1024/1024 size_gb, autoextensible
FROM dba_data_files WHERE tablespace_name = 'DCM';

-- Add datafile or enable autoextend
ALTER TABLESPACE DCM ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 2G MAXSIZE 50G;
\`\`\`

---

## 🟡 WARNING: Slow SQL — FND_USER and FND_RESPONSIBILITY_VL

### SQL_ID: 8s6unu0n34g0t — 216ms/exec, 133,100 buffer gets/exec

Full table scan on FND_USER. Missing function-based or standard index on USER_NAME.

\`\`\`sql
-- Check existing indexes
SELECT index_name, uniqueness FROM dba_indexes
WHERE table_name = 'FND_USER' AND owner = 'APPLSYS';

-- If missing, the standard EBS index should exist:
CREATE INDEX APPLSYS.FND_USER_U1 ON APPLSYS.FND_USER(USER_NAME) ONLINE;
\`\`\`

### SQL_ID: 00f34kc6pr2wn — 5,160ms/exec, 15M buffer gets/exec

Full scan on FND_RESPONSIBILITY_VL. Query filters on RESPONSIBILITY_KEY but no supporting index.

\`\`\`sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('00f34kc6pr2wn', NULL, 'ALLSTATS LAST'));
\`\`\`

---

## ✅ Healthy Areas

- **Undo:** UNDOTBS1 at 38.8% with adequate retention
- **EBS Concurrent:** 1 pending request (normal)
- **Session count:** Well within limits
- **Redo integrity:** No archive log gaps detected

---

## Prioritized Action Plan

| Priority | Action | Effort | Impact |
|----------|--------|--------|--------|
| 1 | Configure RMAN backup policy | 30 min | No RPO → 7-day RPO |
| 2 | Move redo logs to fast storage / increase size | 1–2 hrs | -30% DB time |
| 3 | Add datafile to DCM tablespace | 5 min | ❌→✅ |
| 4 | Expand APPS_TS_TX_DATA or enable autoextend | 10 min | ⚠️→✅ |
| 5 | Fix missing indexes on FND_USER, FND_RESPONSIBILITY_VL | 15 min | ⚠️→✅ |
| 6 | Investigate WF Mailer DEACTIVATED_SYSTEM status | 30 min | Unblock 1,044 notifications |
`;

function getDemoAnalysis() {
  return DEMO_AI_ANALYSIS;
}

// Deterministic executive summary for the demo health check.
// Core DB summary covers only Oracle database metrics — no EBS terminology.
// Shown at top of every report to make the AI value tangible on first load.
const DEMO_SUMMARY_TEXT = 'This Oracle 19c EBS database has 3 critical findings requiring immediate attention. Log file sync at 38.7% DB time indicates redo log contention. APPS_TS_TX_DATA tablespace at 81.7% with autoextend disabled poses a space risk. RMAN backup not configured — no recovery point available.';
const DEMO_TOP_ACTION = 'Configure RMAN backup immediately to establish a recovery point, then address the redo log contention driving 38.7% of DB wait time.';

// EBS application-layer summary — populated because ebs_detected is true on this demo.
const DEMO_EBS_SUMMARY = 'Workflow Mailer is in DEACTIVATED_SYSTEM state with 147 workflow errors and 1,044 pending notifications backlogged. Concurrent Manager has 1 pending request. Immediate action required to reset the Workflow Mailer and clear the notification queue.';
const DEMO_EBS_ACTION = 'Reset Workflow Mailer via EBS Service Control: stop the component, clear DEACTIVATED_SYSTEM status via PL/SQL reset, then restart. Run FND_SVC_COMPONENT.RESTART_COMPONENT for the Workflow Mailer component ID.';

// Override computed score — the demo shows a healthier EBS environment than the lab connection.
// getSummaryScores() would compute ~50 due to the 38.7% log file sync penalty; 72 is the
// target score for the public demo to reflect a more representative mid-tier EBS system.
const DEMO_OVERALL_SCORE = 72;

function getDemoOverallScore() {
  return DEMO_OVERALL_SCORE;
}

function getDemoExecutiveSummary() {
  return {
    summary_text: DEMO_SUMMARY_TEXT,
    top_action: DEMO_TOP_ACTION,
    ebs_summary: DEMO_EBS_SUMMARY,
    ebs_action: DEMO_EBS_ACTION
  };
}

// ============================================================
// Demo ADDM Findings — deterministic fixture for demo health checks.
// Simulates a production database with 3 ADDM findings after 24 h snapshot.
// ============================================================
const DEMO_ADDM = {
  licensed: true,
  lookback_hours: 24,
  task_name: 'ADDM:848060598_1_1139',
  task_id: 4721,
  findings: [
    {
      finding_id: 1,
      type: 'PROBLEM',
      name: 'SQL statements consuming significant database time',
      message: 'SQL statements with sql_id a1b2c3d4e5f6g and h7i8j9k0l1m2n were consuming significant database time and impacting overall throughput.',
      impact_pct: 34.2,
      sql_id: 'a1b2c3d4e5f6g',
      severity: 'critical',
      recommendations: [
        {
          rec_id: 1,
          type: 'SQL_PROFILE',
          benefit_pct: 28.5,
          message: 'Investigate the SQL statement with SQL_ID "a1b2c3d4e5f6g" for possible performance improvement. A full table scan on ORDERS (2.3M rows) was detected due to a missing composite index.',
          actions: [
            {
              action_id: 1,
              command: 'CALL_FUNCTION',
              attr1: 'dbms_sqltune.accept_sql_profile',
              attr2: 'task_name => \'ADDM:848060598_1_1139\', task_owner => \'SYS\', replace => TRUE',
              attr3: null,
              attr4: null
            }
          ]
        }
      ]
    },
    {
      finding_id: 2,
      type: 'PROBLEM',
      name: 'Hard parse due to unshared cursors',
      message: 'There were 12,847 hard parses due to unshared cursors. Literal SQL in application code is preventing cursor sharing, increasing parse overhead and shared pool pressure.',
      impact_pct: 8.7,
      sql_id: null,
      severity: 'warning',
      recommendations: [
        {
          rec_id: 1,
          type: 'PARAMETER_CHANGE',
          benefit_pct: 6.1,
          message: 'Consider setting CURSOR_SHARING to FORCE or SIMILAR to reduce hard parse overhead from literal SQL statements. Test in non-production first.',
          actions: [
            {
              action_id: 1,
              command: 'ALTER_SYSTEM',
              attr1: 'CURSOR_SHARING',
              attr2: 'FORCE',
              attr3: null,
              attr4: null
            }
          ]
        }
      ]
    },
    {
      finding_id: 3,
      type: 'SYMPTOM',
      name: 'Wait class "User I/O" was consuming significant database time',
      message: 'Wait class "User I/O" was consuming 28.4% of database time. The top wait event was "db file sequential read" (average 0.38 ms). This is a symptom of SQL statements performing excessive physical reads.',
      impact_pct: 5.3,
      sql_id: null,
      severity: 'warning',
      recommendations: [
        {
          rec_id: 1,
          type: 'INVESTIGATE',
          benefit_pct: 5.3,
          message: 'Investigate the SQL statements driving "db file sequential read" waits. Increase the buffer cache if I/O subsystem is not saturated, or address the underlying SQL access paths.',
          actions: []
        }
      ]
    }
  ]
};

function getDemoAddmFindings(lookbackHours) {
  const hours = lookbackHours || 24;
  return { ...DEMO_ADDM, lookback_hours: hours };
}

// ============================================================
// DEMO: Auto-Maintenance Window Status
// ============================================================

const DEMO_MAINTENANCE = {
  autotask_clients: [
    {
      client_name: 'auto optimizer stats collection',
      status: 'ENABLED',
      last_run_date: new Date(Date.now() - 14 * 60 * 60 * 1000).toISOString(), // ~14h ago
      last_run_status: 'SUCCEEDED',
      last_run_duration_secs: 847,
      runs_7d: 7,
      failures_7d: 0,
      traffic_light: 'green',
      tuning_pack_required: false,
      tuning_pack_licensed: null
    },
    {
      client_name: 'sql tuning advisor',
      status: 'DISABLED',
      last_run_date: null,
      last_run_status: null,
      last_run_duration_secs: null,
      runs_7d: 0,
      failures_7d: 0,
      traffic_light: 'red',
      tuning_pack_required: true,
      tuning_pack_licensed: true
    },
    {
      client_name: 'auto space advisor',
      status: 'ENABLED',
      last_run_date: new Date(Date.now() - 6 * 24 * 60 * 60 * 1000).toISOString(), // 6 days ago
      last_run_status: 'SUCCEEDED',
      last_run_duration_secs: 312,
      runs_7d: 1,
      failures_7d: 0,
      traffic_light: 'amber', // enabled but only ran once + aging
      tuning_pack_required: false,
      tuning_pack_licensed: null
    }
  ],
  windows: [
    { window_name: 'MONDAY_WINDOW',    next_start_date: null, repeat_interval: 'freq=weekly;byday=MON;byhour=22;byminute=0;bysecond=0', duration_hours: 4, enabled: true,  last_start_date: null, last_end_date: null },
    { window_name: 'TUESDAY_WINDOW',   next_start_date: null, repeat_interval: 'freq=weekly;byday=TUE;byhour=22;byminute=0;bysecond=0', duration_hours: 4, enabled: true,  last_start_date: null, last_end_date: null },
    { window_name: 'WEDNESDAY_WINDOW', next_start_date: null, repeat_interval: 'freq=weekly;byday=WED;byhour=22;byminute=0;bysecond=0', duration_hours: 4, enabled: false, last_start_date: null, last_end_date: null },
    { window_name: 'THURSDAY_WINDOW',  next_start_date: null, repeat_interval: 'freq=weekly;byday=THU;byhour=22;byminute=0;bysecond=0', duration_hours: 4, enabled: true,  last_start_date: null, last_end_date: null },
    { window_name: 'FRIDAY_WINDOW',    next_start_date: null, repeat_interval: 'freq=weekly;byday=FRI;byhour=22;byminute=0;bysecond=0', duration_hours: 4, enabled: true,  last_start_date: null, last_end_date: null },
    { window_name: 'SATURDAY_WINDOW',  next_start_date: null, repeat_interval: 'freq=weekly;byday=SAT;byhour=6;byminute=0;bysecond=0',  duration_hours: 20, enabled: true, last_start_date: null, last_end_date: null },
    { window_name: 'SUNDAY_WINDOW',    next_start_date: null, repeat_interval: 'freq=weekly;byday=SUN;byhour=6;byminute=0;bysecond=0',  duration_hours: 20, enabled: true, last_start_date: null, last_end_date: null }
  ],
  stale_tables_count: 23,
  stale_tables_top10: [
    { owner: 'APPS',    table_name: 'MTL_MATERIAL_TRANSACTIONS',   num_rows: 18432001, last_analyzed: '2026-04-12 02:14:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'PO_DISTRIBUTIONS_ALL',        num_rows: 9102445,  last_analyzed: '2026-04-15 02:31:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'GL_JE_LINES',                 num_rows: 6871234,  last_analyzed: '2026-04-10 02:09:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'RA_CUSTOMER_TRX_LINES_ALL',   num_rows: 4213887,  last_analyzed: '2026-04-11 02:17:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'WIP_TRANSACTIONS',            num_rows: 2981003,  last_analyzed: '2026-03-28 02:04:00', stale_stats: 'YES' },
    { owner: 'CUSTOM',  table_name: 'XX_REVENUE_SUMMARY',          num_rows: 1450221,  last_analyzed: '2026-03-15 03:12:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'INV_TRANSACTION_TYPES',       num_rows: 887002,   last_analyzed: '2026-04-01 02:00:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'AP_INVOICE_DISTRIBUTIONS_ALL',num_rows: 753119,   last_analyzed: '2026-04-20 02:18:00', stale_stats: 'YES' },
    { owner: 'CUSTOM',  table_name: 'XX_OM_ORDER_STAGING',         num_rows: 441200,   last_analyzed: '2026-03-22 02:45:00', stale_stats: 'YES' },
    { owner: 'APPS',    table_name: 'MRP_RECOMMENDATIONS',         num_rows: 312004,   last_analyzed: '2026-04-18 02:33:00', stale_stats: 'YES' }
  ],
  disabled_clients: ['sql tuning advisor'],
  disabled_windows: ['WEDNESDAY_WINDOW']
};

function getDemoHousekeepingStatus() {
  return DEMO_MAINTENANCE;
}

// ─── Blocking Sessions demo fixture ──────────────────────────────────────────
// Realistic scenario: one long-running report holds a row lock; two sessions
// waiting on it — one for 247 seconds (billing job) and one for 82 seconds.
const DEMO_BLOCKING_SESSIONS = {
  chains: [
    {
      blocker_sid: 142,
      blocker_user: 'APPS',
      blocker_status: 'ACTIVE',
      blocker_sql_id: '4v9kq8a2w1m3n',
      blocker_sql: 'UPDATE AR_PAYMENT_SCHEDULES_ALL SET AMOUNT_DUE_REMAINING = :1 WHERE CUSTOMER_TRX_ID = :2',
      blocked: [
        { sid: 198, user: 'APPS',   wait_event: 'enq: TX - row lock contention', seconds_in_wait: 247 },
        { sid: 231, user: 'CUSTOM', wait_event: 'enq: TX - row lock contention', seconds_in_wait: 82 }
      ]
    }
  ],
  max_wait_seconds: 247,
  total_blocked: 2,
  severity: 'yellow'
};

function getDemoBlockingSessions() {
  return DEMO_BLOCKING_SESSIONS;
}

// ─── Long Operations demo fixture ────────────────────────────────────────────
// Three active operations: a stats gather job (large table), index rebuild, export
const DEMO_LONG_OPERATIONS = {
  operations: [
    {
      sid: 88, serial: 4321, opname: 'Table Scan',
      target: 'APPS.RA_CUSTOMER_TRX_LINES_ALL',
      sofar: 1820000, totalwork: 4213887,
      pct_complete: 43.2, minutes_remaining: 74.3, minutes_elapsed: 56.1,
      message: 'Table Scan: APPS.RA_CUSTOMER_TRX_LINES_ALL: 1820000 out of 4213887 Blocks done'
    },
    {
      sid: 103, serial: 7802, opname: 'index rebuild',
      target: 'APPS.RA_CUSTOMER_TRX_LINES_N1',
      sofar: 3100000, totalwork: 4213887,
      pct_complete: 73.6, minutes_remaining: 8.4, minutes_elapsed: 23.6,
      message: 'index rebuild: APPS.RA_CUSTOMER_TRX_LINES_N1: 3100000 out of 4213887 Blocks done'
    },
    {
      sid: 215, serial: 1102, opname: 'EXPORT',
      target: 'APPS.GL_BALANCES',
      sofar: 890000, totalwork: 1250000,
      pct_complete: 71.2, minutes_remaining: 4.1, minutes_elapsed: 10.2,
      message: 'EXPORT: APPS.GL_BALANCES: 890000 out of 1250000 Blocks done'
    }
  ],
  severity: 'yellow'
};

function getDemoLongOperations() {
  return DEMO_LONG_OPERATIONS;
}

// ─── Top SQL Breakdown demo fixtures ──────────────────────────────────────────
// Five sorted views over the same DEMO_METRICS.top_sql pool, shaped to match
// the queryTopSqlBreakdown() response format from oracle-client.js.
//
// by_cpu         — sorted cpu_time_s DESC
// by_elapsed     — sorted elapsed_time_s DESC
// by_buffer_gets — sorted buffer_gets DESC
// by_disk_reads  — sorted disk_reads DESC
// by_executions  — sorted executions DESC
const _SQL_POOL = [
  {
    sql_id: '8s6unu0n34g0t', schema: 'APPLSYS',
    executions: 137, cpu_time_s: 5.2, elapsed_time_s: 29.6,
    buffer_gets: 18234567, disk_reads: 234512,
    sql_text: 'SELECT /* OID_APPL_USER */ U.USER_ID, U.USER_NAME, U.ENCRYPTED_FOUNDATION_PASSWORD FROM APPLSYS.FND_USER U WHERE U.USER_NAME = :B1 AND (U.END_DATE IS NULL OR U.END_DATE > SYSDATE)'
  },
  {
    sql_id: '00f34kc6pr2wn', schema: 'APPLSYS',
    executions: 3, cpu_time_s: 14.8, elapsed_time_s: 15.5,
    buffer_gets: 45234567, disk_reads: 8923412,
    sql_text: "SELECT DECODE(COUNT(*),0,'FALSE','TRUE') FROM APPLSYS.FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_KEY = :B2 AND APPLICATION_ID = :B1 AND (END_DATE IS NULL OR END_DATE > SYSDATE)"
  },
  {
    sql_id: 'g7k2w9n4p1m8x', schema: 'APPS',
    executions: 4821, cpu_time_s: 987.2, elapsed_time_s: 1203.4,
    buffer_gets: 9823456, disk_reads: 345678,
    sql_text: 'SELECT /* FND_CONCURRENT_REQUESTS */ R.REQUEST_ID, R.PROGRAM_ID, R.STATUS_CODE FROM APPS.FND_CONCURRENT_REQUESTS R WHERE R.STATUS_CODE IN (:B1, :B2) AND R.REQUESTED_START_DATE <= SYSDATE ORDER BY R.PRIORITY'
  },
  {
    sql_id: 't5r1h8v3j6c9q', schema: 'APPS',
    executions: 8923, cpu_time_s: 189.3, elapsed_time_s: 445.6,
    buffer_gets: 5623456, disk_reads: 12345,
    sql_text: 'UPDATE APPS.WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B3, ACTIVITY_RESULT_CODE = :B4, ASSIGNED_USER = :B5, NOTIFICATION_ID = :B6 WHERE ITEM_TYPE = :B1 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B8'
  }
];

function _makeCpuRow(s) {
  return { sql_id: s.sql_id, schema: s.schema, executions: s.executions,
    key_metric: s.cpu_time_s, key_label: 'CPU (s)',
    per_exec: Math.round((s.cpu_time_s / s.executions) * 1000) / 1000, per_exec_label: 'CPU/exec (s)',
    sql_text: s.sql_text };
}
function _makeElapsedRow(s) {
  return { sql_id: s.sql_id, schema: s.schema, executions: s.executions,
    key_metric: s.elapsed_time_s, key_label: 'Elapsed (s)',
    per_exec: Math.round((s.elapsed_time_s / s.executions) * 1000) / 1000, per_exec_label: 'Elapsed/exec (s)',
    sql_text: s.sql_text };
}
function _makeBufRow(s) {
  return { sql_id: s.sql_id, schema: s.schema, executions: s.executions,
    key_metric: s.buffer_gets, key_label: 'Buffer Gets',
    per_exec: Math.round(s.buffer_gets / s.executions), per_exec_label: 'Gets/exec',
    sql_text: s.sql_text };
}
function _makeDiskRow(s) {
  return { sql_id: s.sql_id, schema: s.schema, executions: s.executions,
    key_metric: s.disk_reads, key_label: 'Disk Reads',
    per_exec: Math.round(s.disk_reads / s.executions), per_exec_label: 'Reads/exec',
    sql_text: s.sql_text };
}
function _makeExecRow(s) {
  return { sql_id: s.sql_id, schema: s.schema, executions: s.executions,
    key_metric: s.elapsed_time_s, key_label: 'Total Elapsed (s)',
    per_exec: null, per_exec_label: null,
    sql_text: s.sql_text };
}

const DEMO_TOP_SQL_BREAKDOWN = {
  by_cpu:         [..._SQL_POOL].sort((a,b) => b.cpu_time_s - a.cpu_time_s).map(_makeCpuRow),
  by_elapsed:     [..._SQL_POOL].sort((a,b) => b.elapsed_time_s - a.elapsed_time_s).map(_makeElapsedRow),
  by_buffer_gets: [..._SQL_POOL].sort((a,b) => b.buffer_gets - a.buffer_gets).map(_makeBufRow),
  by_disk_reads:  [..._SQL_POOL].sort((a,b) => b.disk_reads - a.disk_reads).map(_makeDiskRow),
  by_executions:  [..._SQL_POOL].sort((a,b) => b.executions - a.executions).map(_makeExecRow)
};

function getDemoTopSqlBreakdown() {
  return DEMO_TOP_SQL_BREAKDOWN;
}


// ─── Invalid Objects demo fixture ────────────────────────────────────────────
// Realistic scenario: an APPS schema with post-upgrade invalid objects, HR with
// stale views, SCOTT with a broken function.
const DEMO_INVALID_OBJECTS = [
  { OWNER: 'APPS',   OBJECT_TYPE: 'PROCEDURE',    INVALID_COUNT: 12 },
  { OWNER: 'APPS',   OBJECT_TYPE: 'PACKAGE BODY', INVALID_COUNT: 5  },
  { OWNER: 'APPS',   OBJECT_TYPE: 'TRIGGER',      INVALID_COUNT: 3  },
  { OWNER: 'HR',     OBJECT_TYPE: 'VIEW',         INVALID_COUNT: 2  },
  { OWNER: 'SCOTT',  OBJECT_TYPE: 'FUNCTION',     INVALID_COUNT: 1  }
];

function getDemoInvalidObjects() {
  return DEMO_INVALID_OBJECTS;
}

// ─── Unusable Indexes demo fixture ───────────────────────────────────────────
// Post-bulk-load state: APPS has whole indexes and partitions unusable;
// HR has a couple of simple unusable indexes from a failed rebuild.
const DEMO_UNUSABLE_INDEXES = [
  { owner: 'APPS',  indexes: 8, partitions: 15, subpartitions: 4 },
  { owner: 'HR',    indexes: 2, partitions: 0,  subpartitions: 0 },
  { owner: 'SCOTT', indexes: 1, partitions: 3,  subpartitions: 1 }
];

function getDemoUnusableIndexes() {
  return DEMO_UNUSABLE_INDEXES;
}

// ─── Stale Statistics demo fixture ───────────────────────────────────────────
// APPS has large tables that grew significantly since last gather;
// auto-optimizer-stats job is ENABLED and ran last night.
const DEMO_STALE_STATISTICS = {
  schemas: [
    {
      OWNER: 'APPS', TOTAL_TABLES: 120, NO_STATS: 8, OLDER_30D: 25,
      OLDEST_ANALYZE: new Date('2025-11-15').toISOString(),
      NEWEST_ANALYZE: new Date('2026-05-09').toISOString()
    },
    {
      OWNER: 'HR', TOTAL_TABLES: 45, NO_STATS: 2, OLDER_30D: 10,
      OLDEST_ANALYZE: new Date('2025-12-01').toISOString(),
      NEWEST_ANALYZE: new Date('2026-05-08').toISOString()
    },
    {
      OWNER: 'SCOTT', TOTAL_TABLES: 12, NO_STATS: 0, OLDER_30D: 3,
      OLDEST_ANALYZE: new Date('2026-03-15').toISOString(),
      NEWEST_ANALYZE: new Date('2026-05-01').toISOString()
    }
  ],
  staleTop20: [
    { OWNER: 'APPS', TABLE_NAME: 'MTL_MATERIAL_TRANSACTIONS',    NUM_ROWS: 8500000, LAST_ANALYZED: new Date('2025-11-15').toISOString(), STALE_STATS: 'YES' },
    { OWNER: 'APPS', TABLE_NAME: 'WF_ITEM_ACTIVITY_STATUSES',    NUM_ROWS: 3200000, LAST_ANALYZED: new Date('2025-12-01').toISOString(), STALE_STATS: 'YES' },
    { OWNER: 'HR',   TABLE_NAME: 'PER_ALL_ASSIGNMENTS_F',        NUM_ROWS: 450000,  LAST_ANALYZED: new Date('2026-01-10').toISOString(), STALE_STATS: 'YES' },
    { OWNER: 'APPS', TABLE_NAME: 'PO_DISTRIBUTIONS_ALL',         NUM_ROWS: 210000,  LAST_ANALYZED: new Date('2026-02-28').toISOString(), STALE_STATS: 'YES' },
    { OWNER: 'SCOTT',TABLE_NAME: 'ORDER_LINES',                  NUM_ROWS: 88000,   LAST_ANALYZED: new Date('2026-03-20').toISOString(), STALE_STATS: 'YES' }
  ],
  autoJob: [
    { CLIENT_NAME: 'auto optimizer stats', STATUS: 'ENABLED', LAST_GOOD_DATE: new Date('2026-05-09').toISOString() }
  ]
};

function getDemoStaleStatistics() {
  return DEMO_STALE_STATISTICS;
}

// ─── Demo Oracle Parameters ───────────────────────────────────────────────────
// Intentionally shows a realistic "needs attention" scenario:
//   - SGA undersized for 64 GB RAM
//   - processes near high-water mark
//   - audit_trail=NONE (red)
//   - cursor_sharing=FORCE (amber)
//   - filesystemio_options not set (amber)
const DEMO_ORACLE_PARAMETERS = {
  hardware: { ram_gb: 64, cpu_count: 8 },
  sessions_highwater: 285,
  datafile_count: 47,
  edition: 'EE',
  parameters: [
    // Memory
    { name: 'memory_target',        category: 'Memory',               current_value: '0',         recommended: 'AMM disabled (manual SGA/PGA)',    status: 'green',  note: 'Preferred on Linux — avoids Huge Pages incompatibility',  is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'memory_max_target',    category: 'Memory',               current_value: '0',         recommended: 'AMM disabled (manual SGA/PGA)',    status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'sga_target',           category: 'Memory',               current_value: '4294967296',recommended: '28.8G',                            status: 'amber',  note: 'Consider ~45% of RAM (64 GB)',                             is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'sga_max_size',         category: 'Memory',               current_value: '6442450944',recommended: '38.4G',                            status: 'amber',  note: 'Cap may restrict SGA growth',                              is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'pga_aggregate_target', category: 'Memory',               current_value: '2147483648',recommended: '16G',                              status: 'amber',  note: '~25% of RAM recommended',                                  is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'pga_aggregate_limit',  category: 'Memory',               current_value: '4294967296',recommended: '32G',                              status: 'amber',  note: 'Hard PGA limit may kill large sorts',                       is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'db_cache_size',        category: 'Memory',               current_value: '0',         recommended: 'Auto-tuned by SGA_TARGET',         status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'shared_pool_size',     category: 'Memory',               current_value: '0',         recommended: 'Auto-tuned by SGA_TARGET',         status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'large_pool_size',      category: 'Memory',               current_value: '33554432',  recommended: '64M+',                             status: 'amber',  note: 'Required for RMAN and parallel execution',                  is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'java_pool_size',       category: 'Memory',               current_value: '33554432',  recommended: '32M–128M',                         status: 'green',  note: 'Only matters if Java stored procedures are used',           is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'streams_pool_size',    category: 'Memory',               current_value: '0',         recommended: '0 (auto) or 64M+ if replication used', status: 'green', note: '',                                                    is_dynamic: true,  scope: 'SCOPE=BOTH' },
    // Processes & Sessions
    { name: 'processes',            category: 'Processes & Sessions', current_value: '300',       recommended: '~342 (sessions HW: 285)',          status: 'red',    note: 'Process limit nearly exhausted (HW: 285)',                  is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'sessions',             category: 'Processes & Sessions', current_value: '472',       recommended: '472',                              status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'open_cursors',         category: 'Processes & Sessions', current_value: '200',       recommended: '300+',                             status: 'red',    note: 'Risk of ORA-01000 (max open cursors exceeded)',             is_dynamic: true,  scope: 'SCOPE=BOTH' },
    // Undo & Recovery
    { name: 'undo_tablespace',      category: 'Undo & Recovery',      current_value: 'UNDOTBS1',  recommended: 'UNDOTBS1',                         status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'undo_retention',       category: 'Undo & Recovery',      current_value: '900',       recommended: '900–3600 sec',                     status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'db_recovery_file_dest_size', category: 'Undo & Recovery', current_value: '10737418240', recommended: '10G',                          status: 'green',  note: 'Check V$RECOVERY_FILE_DEST for usage %',                    is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'log_buffer',           category: 'Undo & Recovery',      current_value: '5578752',   recommended: '8M–32M',                           status: 'amber',  note: 'Small log buffer may cause redo latch waits',               is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    // Performance
    { name: 'optimizer_mode',       category: 'Performance',          current_value: 'ALL_ROWS',  recommended: 'ALL_ROWS',                         status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'cursor_sharing',       category: 'Performance',          current_value: 'FORCE',     recommended: 'EXACT',                            status: 'amber',  note: 'FORCE is a workaround for non-bind-variable SQL — fix the SQL instead', is_dynamic: true, scope: 'SCOPE=BOTH' },
    { name: 'parallel_max_servers', category: 'Performance',          current_value: '80',        recommended: '8–16',                             status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'result_cache_max_size',category: 'Performance',          current_value: '33554432',  recommended: '128M+',                            status: 'amber',  note: 'Result cache too small to be useful',                       is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'inmemory_size',        category: 'Performance',          current_value: '0',         recommended: '0 (disabled)',                     status: 'green',  note: 'Enable only if In-Memory option licensed',                  is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    // Security & Audit
    { name: 'audit_trail',          category: 'Security & Audit',     current_value: 'NONE',      recommended: 'DB or OS',                         status: 'red',    note: 'No auditing — compliance risk',                             is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'sec_case_sensitive_logon', category: 'Security & Audit', current_value: 'TRUE',      recommended: 'TRUE',                             status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'remote_login_passwordfile', category: 'Security & Audit',current_value: 'EXCLUSIVE', recommended: 'EXCLUSIVE',                        status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'os_authent_prefix',    category: 'Security & Audit',     current_value: '',          recommended: '""',                               status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    // Storage & I/O
    { name: 'db_files',             category: 'Storage & I/O',        current_value: '200',       recommended: '200+',                             status: 'green',  note: '47/200 used',                                               is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'db_block_size',        category: 'Storage & I/O',        current_value: '8192',      recommended: '8192 (OLTP) or 16384 (DW)',        status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'filesystemio_options', category: 'Storage & I/O',        current_value: 'NONE',      recommended: 'SETALL',                           status: 'amber',  note: 'Enable on Linux for async + directIO (reduces I/O latency)',is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'disk_asynch_io',       category: 'Storage & I/O',        current_value: 'TRUE',      recommended: 'TRUE',                             status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    // Misc
    { name: 'compatible',           category: 'Misc',                 current_value: '19.0.0',    recommended: 'Match current version',            status: 'green',  note: 'Lowering prevents rolling back upgrades',                   is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'nls_characterset',     category: 'Misc',                 current_value: 'AL32UTF8',  recommended: 'AL32UTF8',                         status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'nls_nchar_characterset',category: 'Misc',                current_value: 'AL16UTF16', recommended: 'AL16UTF16',                        status: 'green',  note: '',                                                         is_dynamic: false, scope: 'SCOPE=SPFILE  -- restart required' },
    { name: 'diagnostic_dest',      category: 'Misc',                 current_value: '/u01/app/oracle', recommended: '/u01/app/oracle',            status: 'green',  note: '',                                                         is_dynamic: true,  scope: 'SCOPE=BOTH' },
    { name: 'control_file_record_keep_time', category: 'Misc',        current_value: '7',         recommended: '30+',                              status: 'amber',  note: 'Low retention — RMAN catalog may miss history',             is_dynamic: true,  scope: 'SCOPE=BOTH' }
  ]
};

function getDemoOracleParameters() {
  return DEMO_ORACLE_PARAMETERS;
}

// Deterministic demo AI recommendations — represent all finding types with exact SQL.
// Each recommendation answers: metric+value → root cause → exact remediation or diagnostic SQL.
const DEMO_AI_RECOMMENDATIONS = [
  {
    id: 'rec_001',
    title: 'APP_DATA Tablespace 95.2% Full — 487 GB used of 512 GB, 5 days to fill',
    severity: 'critical',
    confidence: 'high',
    evidence: 'APP_DATA tablespace: 487.3 GB used of 512.0 GB (95.2%). Autoextend is OFF on all datafiles. At current consumption rate (~5 GB/day), tablespace will exhaust in approximately 5 days. Root cause: autoextend disabled and no pre-emptive growth policy in place.',
    fix_sql: `-- Option 1: Add a new autoextending datafile (recommended)
ALTER TABLESPACE APP_DATA
  ADD DATAFILE SIZE 100G
  AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED;

-- Option 2: Enable autoextend on existing datafile (check path first)
-- ALTER DATABASE DATAFILE '/u01/oradata/PRODDB01/app_data01.dbf'
--   AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED;

-- Verify after:
SELECT tablespace_name, ROUND(SUM(bytes)/1073741824,1) GB_TOTAL
FROM dba_data_files WHERE tablespace_name = 'APP_DATA' GROUP BY 1;`,
    diagnostic_sql: null,
    check_id: 'tablespace',
    check_tab: 'Tablespaces'
  },
  {
    id: 'rec_002',
    title: 'db file sequential read: 18.4% DB time — avg 14.3ms, 2.8M waits',
    severity: 'critical',
    confidence: 'high',
    evidence: 'db file sequential read [User I/O]: 18.4% of DB time, 2,847,392 total waits, avg 14.3ms per wait. Threshold: >10% DB time = critical. Typical healthy avg: <5ms. Root cause: single-block I/O latency elevated — likely missing index causing frequent full-row lookups by ROWID, or I/O subsystem saturation.',
    fix_sql: null,
    diagnostic_sql: `-- Find top SQL driving db file sequential read waits
SELECT s.sql_id, s.executions, s.elapsed_time/1e6 elapsed_sec,
       s.buffer_gets, s.disk_reads,
       SUBSTR(s.sql_text, 1, 120) sql_preview
FROM v$sql s
WHERE s.disk_reads > 10000
ORDER BY s.disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

-- Check if I/O is concentrated on specific datafiles
SELECT df.name, w.time_waited/100 wait_sec, w.total_waits
FROM v$filestat w JOIN v$datafile df ON w.file# = df.file#
ORDER BY w.time_waited DESC FETCH FIRST 10 ROWS ONLY;`,
    check_id: 'wait_events',
    check_tab: 'Wait Events'
  },
  {
    id: 'rec_003',
    title: 'SQL_ID 8zg4v1d3 — 847ms/exec, 124K buffer gets, likely missing index',
    severity: 'critical',
    confidence: 'high',
    evidence: 'SQL_ID 8zg4v1d3f7q9r: 847ms avg elapsed per execution, 124,832 buffer gets/exec, 1,203 executions total. Buffer gets/exec >100K strongly indicates full table scan or hash join without supporting index on a large table. Root cause: likely missing index on a FK column used in a high-frequency join.',
    fix_sql: null,
    diagnostic_sql: `-- Get execution plan with actual stats for SQL_ID 8zg4v1d3f7q9r
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '8zg4v1d3f7q9r', NULL,
    'ALLSTATS LAST +PEEKED_BINDS +PREDICATE'
  )
);

-- Check current indexes on tables accessed by this SQL
-- (replace TABLE_NAME with actual table from plan output above)
SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name = 'ORDERS'  -- replace with actual table
ORDER BY index_name, column_position;`,
    check_id: 'sql',
    check_tab: 'SQL'
  },
  {
    id: 'rec_004',
    title: 'RMAN Backup 73h Overdue — Last Full 73h Ago, FRA 78% Full',
    severity: 'critical',
    confidence: 'high',
    evidence: 'Last successful RMAN full backup completed 73 hours ago. Recovery Point Objective breach: >48h without backup violates standard DBA SLA. FRA (Fast Recovery Area) at 78% — approaching the 85% threshold where Oracle will stop auto-archiving. Root cause: scheduled RMAN job failed silently; no alerting configured on backup failures.',
    fix_sql: `-- Run immediate RMAN backup (execute from RMAN client as SYSDBA)
-- Connect: rman target /

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'EMERGENCY_FULL';

-- After backup completes, verify:
SELECT start_time, end_time, status, input_type
FROM v$rman_backup_job_details
ORDER BY start_time DESC FETCH FIRST 5 ROWS ONLY;

-- Also crosscheck and delete expired files to free FRA space:
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;`,
    diagnostic_sql: null,
    check_id: 'backup',
    check_tab: 'Backups'
  },
  {
    id: 'rec_005',
    title: 'PROCESSES 95% Exhausted — 285/300 used, ORA-00020 Imminent',
    severity: 'critical',
    confidence: 'high',
    evidence: 'Processes high watermark: 285 of 300 configured (95%). Resource limit status: critical. At this level any connection spike will trigger ORA-00020 (maximum number of processes exceeded), causing application outages. Root cause: PROCESSES parameter set too low at instance creation; requires restart to change.',
    fix_sql: `-- Increase PROCESSES limit (requires DB restart)
-- Step 1: Set new value in SPFILE
ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE;

-- Step 2: Verify the SPFILE change (restart required before it takes effect)
SELECT name, value FROM v$spparameter WHERE name = 'processes';

-- Step 3: After restart, confirm new limit is active
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit WHERE resource_name = 'processes';

-- Also check for sessions leaking (connection pooling not returning connections):
SELECT username, COUNT(*) session_count, MIN(logon_time) oldest_logon
FROM v$session WHERE type = 'USER' GROUP BY username ORDER BY 2 DESC;`,
    diagnostic_sql: null,
    check_id: 'sessions',
    check_tab: 'Sessions'
  },
  {
    id: 'rec_006',
    title: 'UNDO_RETENTION 900s vs 4847s Peak Query — ORA-01555 Risk',
    severity: 'warning',
    confidence: 'high',
    evidence: 'V$UNDOSTAT 30-day history: peak query duration 4,847 seconds. UNDO_RETENTION currently set to 900 seconds. Peak query duration exceeds retention by 5.4x — long-running reports will trigger ORA-01555 (snapshot too old) when they read undo blocks that have been recycled. Root cause: UNDO_RETENTION was not sized to accommodate long batch reports.',
    fix_sql: `-- Increase UNDO_RETENTION to accommodate longest query + 20% safety margin
ALTER SYSTEM SET UNDO_RETENTION = 6000 SCOPE=BOTH;

-- Also ensure UNDOTBS1 has enough space to hold the extended retention
-- (or enable autoextend if not already set)
SELECT tablespace_name, ROUND(SUM(bytes)/1073741824,2) GB
FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1' GROUP BY 1;

-- Verify new setting is active immediately (no restart needed):
SELECT name, value FROM v$parameter WHERE name = 'undo_retention';`,
    diagnostic_sql: null,
    check_id: 'undo',
    check_tab: 'Summary'
  },
  {
    id: 'rec_007',
    title: 'SGA_TARGET 4 GB vs Recommended 28.8 GB — Buffer Cache Undersized',
    severity: 'warning',
    confidence: 'medium',
    evidence: 'SGA_TARGET: 4,294,967,296 bytes (4 GB). Buffer cache hit ratio: 91.2% — below 95% threshold. Hard parses/sec: 14.7 (elevated). Server RAM: 64 GB; current SGA consumes only 6.3%. Recommended: ~45% of RAM = 28.8 GB SGA. Root cause: SGA sized for a smaller server; never updated after RAM was increased.',
    fix_sql: `-- Increase SGA_TARGET (dynamic — no restart required for increase)
ALTER SYSTEM SET SGA_TARGET = 30G SCOPE=BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 40G SCOPE=SPFILE;  -- requires restart

-- Increase PGA to 25% of RAM (~16 GB) while we're here
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 16G SCOPE=BOTH;

-- Monitor buffer cache hit ratio after change (wait ~10 min):
SELECT 1 - (phyrds / (phyrds + dbgets)) ratio
FROM (SELECT SUM(physical_reads) phyrds, SUM(db_block_gets + consistent_gets) dbgets
      FROM v$buffer_pool_statistics);`,
    diagnostic_sql: null,
    check_id: 'memory',
    check_tab: 'Memory'
  },
  {
    id: 'rec_008',
    title: 'ORD_ITEMS_IDX — 47% Deleted Blocks, Rebuild Needed',
    severity: 'warning',
    confidence: 'high',
    evidence: 'Index ORD_ITEMS_IDX on ORDER_ITEMS: 47% deleted blocks, B-tree level (BLEVEL) = 4, size 2,847 MB. Indexes with >30% deleted blocks degrade sequential scan performance and inflate index size. BLEVEL=4 means an additional block read per lookup vs healthy BLEVEL=2. Root cause: high-frequency DELETE/UPDATE operations on ORDER_ITEMS without periodic index maintenance.',
    fix_sql: `-- Online index rebuild (no DML lock required in EE)
ALTER INDEX ORD_ITEMS_IDX REBUILD ONLINE PARALLEL 4;

-- Revert parallelism after rebuild
ALTER INDEX ORD_ITEMS_IDX NOPARALLEL;

-- Verify rebuild result
SELECT index_name, blevel, leaf_blocks,
       ROUND(del_lf_rows/NULLIF(lf_rows,0)*100, 1) pct_deleted,
       last_analyzed
FROM dba_indexes WHERE index_name = 'ORD_ITEMS_IDX';`,
    diagnostic_sql: null,
    check_id: 'indexes',
    check_tab: 'SQL'
  },
  {
    id: 'rec_009',
    title: 'AUDIT_TRAIL = NONE — No Auditing, Compliance Risk',
    severity: 'warning',
    confidence: 'high',
    evidence: 'V$PARAMETER: AUDIT_TRAIL = NONE. With no audit trail, there is no record of privileged user actions, DDL changes, or failed logins. Root cause: auditing was disabled at instance creation and never enabled — common on databases that predate audit compliance requirements.',
    fix_sql: `-- Enable DB auditing (requires restart to take effect)
ALTER SYSTEM SET AUDIT_TRAIL = DB SCOPE=SPFILE;

-- After restart, enable key audit events (run as SYS):
-- Privileged connections
AUDIT CONNECT BY SYS WHENEVER NOT SUCCESSFUL;
AUDIT CREATE SESSION BY SYS WHENEVER NOT SUCCESSFUL;

-- DDL by all users
AUDIT CREATE TABLE, DROP TABLE, ALTER TABLE BY ACCESS;
AUDIT CREATE INDEX, DROP INDEX BY ACCESS;

-- Sensitive table access (customize to your schema)
-- AUDIT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES BY ACCESS;

-- Verify audit is active after restart:
SELECT name, value FROM v$parameter WHERE name = 'audit_trail';`,
    diagnostic_sql: null,
    check_id: 'config',
    check_tab: 'Parameters'
  }
];

function getDemoRecommendations() {
  return DEMO_AI_RECOMMENDATIONS;
}

module.exports = { getDemoMetrics, getSummaryScores, getDemoAnalysis, getDemoExecutiveSummary, getDemoOverallScore, getDemoAddmFindings, getDemoHousekeepingStatus, getDemoBlockingSessions, getDemoLongOperations, getDemoTopSqlBreakdown, getDemoInvalidObjects, getDemoUnusableIndexes, getDemoStaleStatistics, getDemoOracleParameters, getDemoRecommendations, DEMO_METRICS };
