// Generates supabase/seed.sql from the local Supabase database's own public
// catalog rows (is_public = true), rather than from a hand-written source
// file — supersedes the old generate-seed.mjs/src/domain/library.ts pair, so
// the app's own device/category/port/cable-type editors (used as an Admin,
// with "Publish directly to the shared library" checked) become the one way
// new seed data gets authored. Run via: npm run export-seed
//
// `--check` instead diffs what this would write against the current
// supabase/seed.sql and exits 1 without writing if they differ — used by
// `npm run db:reset` to refuse to blow away catalog entries you haven't
// exported yet (see package.json).
import { execFileSync } from 'node:child_process'
import { existsSync, readFileSync, writeFileSync } from 'node:fs'

const DB_CONTAINER = 'supabase_db_av-planner'
const SEED_PATH = new URL('../supabase/seed.sql', import.meta.url)

function queryJson(sql) {
  const wrapped = `select coalesce(json_agg(t), '[]'::json) from (${sql}) t;`
  const output = execFileSync(
    'docker',
    ['exec', DB_CONTAINER, 'psql', '-U', 'postgres', '-d', 'postgres', '-t', '-A', '-c', wrapped],
    { encoding: 'utf8' },
  )
  return JSON.parse(output.trim())
}

function sqlString(value) {
  if (value === undefined || value === null) return 'null'
  return `'${String(value).replace(/'/g, "''")}'`
}

function sqlTextArray(values) {
  if (!values || values.length === 0) return "'{}'"
  return `'{${values.map((v) => `"${v.replace(/"/g, '\\"')}"`).join(',')}}'`
}

function sqlNumber(value) {
  return value === undefined || value === null ? 'null' : String(value)
}

function loadCatalog() {
  return {
    categories: queryJson('select id, name from device_categories where is_public order by name'),
    manufacturers: queryJson('select id, name from manufacturers where is_public order by name'),
    portTypes: queryJson(
      'select id, name, category, family, compatible_family_ids, max_connections from port_types where is_public order by name',
    ),
    cableTypes: queryJson(
      'select id, name, family, family2, unit, cost_per_unit from cable_types where is_public order by name',
    ),
    deviceTemplates: queryJson(
      'select id, name, category_id, manufacturer_id, model, cost from device_templates where is_public order by name',
    ),
    devicePorts: queryJson(
      `select id, device_template_id, name, direction, port_type_id, sort_order, built_in_cable
       from device_template_ports
       where device_template_id in (select id from device_templates where is_public)
       order by device_template_id, sort_order`,
    ),
  }
}

function buildSeedContent(catalog) {
  const { categories, manufacturers, portTypes, cableTypes, deviceTemplates, devicePorts } = catalog
  const lines = []
  lines.push("-- Generated by scripts/export-seed.mjs from the local Supabase database's public")
  lines.push('-- catalog rows. Do not hand-edit — author new devices/connectors via the app\'s')
  lines.push('-- GUI instead (as an Admin, check "Publish directly to the shared library"),')
  lines.push('-- then re-run: npm run export-seed')
  lines.push('-- Seeds the public catalog: is_public = true, owner_id = null (system-owned).')
  lines.push('')

  lines.push('-- Device categories')
  for (const c of categories) {
    lines.push(`insert into public.device_categories (id, name, is_public) values (${sqlString(c.id)}, ${sqlString(c.name)}, true);`)
  }
  lines.push('')

  lines.push('-- Manufacturers')
  for (const m of manufacturers) {
    lines.push(`insert into public.manufacturers (id, name, is_public) values (${sqlString(m.id)}, ${sqlString(m.name)}, true);`)
  }
  lines.push('')

  lines.push('-- Port types')
  for (const pt of portTypes) {
    lines.push(
      `insert into public.port_types (id, name, category, family, compatible_family_ids, max_connections, is_public) values (${sqlString(pt.id)}, ${sqlString(pt.name)}, ${sqlString(pt.category)}, ${sqlString(pt.family)}, ${sqlTextArray(pt.compatible_family_ids)}, ${sqlNumber(pt.max_connections)}, true);`,
    )
  }
  lines.push('')

  lines.push('-- Cable types')
  for (const ct of cableTypes) {
    lines.push(
      `insert into public.cable_types (id, name, family, family2, unit, cost_per_unit, is_public) values (${sqlString(ct.id)}, ${sqlString(ct.name)}, ${sqlString(ct.family)}, ${sqlString(ct.family2)}, ${sqlString(ct.unit)}, ${sqlNumber(ct.cost_per_unit)}, true);`,
    )
  }
  lines.push('')

  lines.push('-- Device templates + their ports')
  const portsByTemplate = new Map()
  for (const port of devicePorts) {
    const list = portsByTemplate.get(port.device_template_id) ?? []
    list.push(port)
    portsByTemplate.set(port.device_template_id, list)
  }
  for (const dt of deviceTemplates) {
    lines.push(
      `insert into public.device_templates (id, name, category_id, manufacturer_id, model, cost, is_public) values (${sqlString(dt.id)}, ${sqlString(dt.name)}, ${sqlString(dt.category_id)}, ${sqlString(dt.manufacturer_id)}, ${sqlString(dt.model)}, ${sqlNumber(dt.cost)}, true);`,
    )
    for (const port of portsByTemplate.get(dt.id) ?? []) {
      lines.push(
        `insert into public.device_template_ports (id, device_template_id, name, direction, port_type_id, sort_order, built_in_cable) values (${sqlString(port.id)}, ${sqlString(port.device_template_id)}, ${sqlString(port.name)}, ${sqlString(port.direction)}, ${sqlString(port.port_type_id)}, ${port.sort_order}, ${port.built_in_cable});`,
      )
    }
  }
  lines.push('')

  return lines.join('\n') + '\n'
}

function summarize(catalog) {
  return `${catalog.categories.length} categories, ${catalog.manufacturers.length} manufacturers, ${catalog.portTypes.length} port types, ${catalog.cableTypes.length} cable types, ${catalog.deviceTemplates.length} device templates`
}

const isCheck = process.argv.includes('--check')

let catalog
try {
  catalog = loadCatalog()
} catch (err) {
  const message = err instanceof Error ? err.message : String(err)
  if (isCheck) {
    // Best-effort safety net, not a hard gate — if the local DB isn't
    // reachable (Docker down, container not started yet) don't block a
    // reset over it.
    console.warn(`Couldn't reach the local Supabase database to check seed.sql — proceeding without checking.\n${message}`)
    process.exit(0)
  }
  console.error(`Failed to read the public catalog from the local Supabase database.\n${message}`)
  process.exit(1)
}

const content = buildSeedContent(catalog)

if (isCheck) {
  const current = existsSync(SEED_PATH) ? readFileSync(SEED_PATH, 'utf8') : ''
  if (current === content) {
    console.log('supabase/seed.sql matches the database — safe to reset.')
    process.exit(0)
  }
  console.error(
    [
      "supabase/seed.sql is out of date with the local database's public catalog.",
      'Resetting now would discard whatever you\'ve published since it was last exported.',
      '',
      'Run `npm run export-seed` first to capture it, or run `npx supabase db reset` directly if you want to discard it.',
    ].join('\n'),
  )
  process.exit(1)
}

writeFileSync(SEED_PATH, content)
console.log(`Wrote supabase/seed.sql (${summarize(catalog)})`)