check-migrations.ts

import { createClient } from '@supabase/supabase-js'
import * as fs from 'fs'
import * as path from 'path'

const SUPABASE_URL = process.env.NEXT_PUBLIC_SUPABASE_URL || ''
const SUPABASE_SERVICE_ROLE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY || ''

if (!SUPABASE_URL || !SUPABASE_SERVICE_ROLE_KEY) {
  console.error('Missing SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY')
  process.exit(1)
}

const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY)

interface TableInfo {
  table_name: string
  category: string
  size: string
}

async function checkUnicourtTables() {
  console.log('='.repeat(80))
  console.log('UNICOURT & CEO DASHBOARD MIGRATION STATUS')
  console.log('='.repeat(80))
  console.log()

  // Check which tables exist
  const { data: tables, error } = await supabase
    .from('information_schema.tables')
    .select('table_name')
    .eq('table_schema', 'public')
    .or(
      'table_name.like.ext_unicourt%,' +
        'table_name.like.case_track%,' +
        'table_name.like.admin_%,' +
        'table_name.eq.advertiser_unicourt_links,' +
        'table_name.eq.unicourt_analytics_sync_log'
    )

  if (error) {
    console.error('Error querying tables:', error)

    // Fallback: try raw SQL
    const sqlQuery = fs.readFileSync(path.join(__dirname, 'check-unicourt-tables.sql'), 'utf-8')

    const { data: rawData, error: rawError } = await supabase.rpc('exec_sql', {
      sql: sqlQuery,
    })

    if (rawError) {
      console.error('Raw SQL error:', rawError)
      process.exit(1)
    }

    console.log('Tables found (raw):', rawData)
    return
  }

  const tableNames = (tables || []).map((t: any) => t.table_name)

  // Expected tables from migrations
  const expectedTables = {
    '20251212_003_unicourt_analytics_tables.sql': [
      'ext_unicourt_judge_analytics',
      'ext_unicourt_attorney_analytics',
      'ext_unicourt_lawfirm_analytics',
      'ext_unicourt_case_analytics',
      'unicourt_analytics_sync_log',
    ],
    '20251212_004_advertiser_unicourt_links.sql': ['advertiser_unicourt_links'],
    '20251212_005_unicourt_case_track_tables.sql': [
      'case_track_subscriptions',
      'case_track_events',
    ],
    '20251213_001_ceo_dashboard_tables.sql': [
      'admin_leads',
      'admin_activities',
      'admin_content_posts',
    ],
  }

  console.log('MIGRATION STATUS:\n')

  for (const [migration, expectedTableList] of Object.entries(expectedTables)) {
    console.log(`\n${migration}`)
    console.log('-'.repeat(80))

    let allPresent = true
    for (const tableName of expectedTableList) {
      const exists = tableNames.includes(tableName)
      const status = exists ? '✅ APPLIED' : '❌ PENDING'
      console.log(`  ${status}  ${tableName}`)
      if (!exists) allPresent = false
    }

    if (allPresent) {
      console.log(`  Status: ✅ FULLY APPLIED`)
    } else {
      console.log(`  Status: ⚠️  PARTIALLY APPLIED OR PENDING`)
    }
  }

  console.log('\n')
  console.log('='.repeat(80))
  console.log('SUMMARY')
  console.log('='.repeat(80))

  const totalExpected = Object.values(expectedTables).flat().length
  const totalFound = tableNames.length

  console.log(`Expected tables: ${totalExpected}`)
  console.log(`Found tables: ${totalFound}`)

  if (totalFound === totalExpected) {
    console.log('\n✅ All migrations have been applied successfully!')
  } else if (totalFound > 0) {
    console.log('\n⚠️  Some migrations are pending or partially applied.')
  } else {
    console.log('\n❌ No UniCourt or CEO tables found. Migrations need to be applied.')
  }

  // Check for additional columns in judge_analytics_cache
  console.log('\n')
  console.log('='.repeat(80))
  console.log('JUDGE_ANALYTICS_CACHE - MULTI-SOURCE COLUMNS')
  console.log('='.repeat(80))

  const { data: columns, error: colError } = await supabase
    .from('information_schema.columns')
    .select('column_name')
    .eq('table_schema', 'public')
    .eq('table_name', 'judge_analytics_cache')
    .in('column_name', [
      'unicourt_analytics_id',
      'data_sources',
      'source_weights',
      'reconciliation_status',
      'unicourt_confidence',
      'judgefinder_confidence',
      'reconciled_confidence',
      'unicourt_synced_at',
      'judgefinder_calculated_at',
      'reconciled_at',
      'metric_divergence',
    ])

  if (!colError && columns) {
    const foundColumns = columns.map((c: any) => c.column_name)
    const expectedColumns = [
      'unicourt_analytics_id',
      'data_sources',
      'source_weights',
      'reconciliation_status',
      'unicourt_confidence',
      'judgefinder_confidence',
      'reconciled_confidence',
      'unicourt_synced_at',
      'judgefinder_calculated_at',
      'reconciled_at',
      'metric_divergence',
    ]

    for (const col of expectedColumns) {
      const exists = foundColumns.includes(col)
      const status = exists ? '✅' : '❌'
      console.log(`  ${status}  ${col}`)
    }
  } else {
    console.log('  ⚠️  Could not check columns')
  }

  console.log('\n')
}

checkUnicourtTables().catch(console.error)