src/lib/syncSupabaseToLocal.ts

import { createServerClient, isSupabaseConfigured } from './supabase/server'
import { Pool } from 'pg'

const LOCAL_DB_URL = process.env.LOCAL_DATABASE_URL

let pool: Pool | null = null
if (LOCAL_DB_URL) {
  pool = new Pool({ connectionString: LOCAL_DB_URL })
}

async function upsertLocal(table: string, row: Record<string, any>) {
  if (!pool) return
  const cols = Object.keys(row)
  if (!cols.includes('id')) {
    throw new Error(`Row for ${table} missing id`)
  }
  const colList = cols.map((c) => `"${c}"`).join(',')
  const vals = Object.values(row)
  const placeholders = cols.map((_, i) => `$${i + 1}`).join(',')
  const updates = cols.map((c) => `"${c}" = EXCLUDED."${c}"`).join(',')
  const query = `INSERT INTO ${table} (${colList}) VALUES(${placeholders}) ON CONFLICT (id) DO UPDATE SET ${updates}`
  await pool.query(query, vals)
}

export async function syncSupabaseToLocal() {
  if (!pool) {
    throw new Error('LOCAL_DATABASE_URL required for pull sync')
  }
  if (!isSupabaseConfigured() || !process.env.SUPABASE_SERVICE_ROLE_KEY) {
    console.error('Missing Supabase service credentials for pull sync; skipping pull')
    return { synced: 0 }
  }

  const supabase = await createServerClient({ service: true })
  let synced = 0

  const pull = async (table: string) => {
    const { data, error } = await supabase.from(table).select('*')
    if (error) throw error
    for (const row of data || []) {
      await upsertLocal(table, row as any)
      synced++
    }
  }

  await pull('rooms')
  await pull('codes')
  await pull('room_messages')

  return { synced }
}

if (require.main === module) {
  syncSupabaseToLocal().then((r) => {
    console.log('Pull sync result', r)
    process.exit(0)
  }).catch((e) => {
    console.error('Pull sync failed', e)
    process.exit(1)
  })
}