// Schema contract for the offline-sync module tables (offline-sync updated_at fix).
//
// lib/offline/sync-engine.ts requires an `updated_at` column on every table
// registered via createRepository (lib/repositories/project-repo.ts): the push
// upsert carries it in the payload (stripLocalFields keeps it), the delta-pull
// filters `.gte('updated_at', since)`, and resolveConflict does Last-Write-Wins
// on it. Before supabase-migration-offline-sync-updated-at.sql, only `projects`
// had the column, so every sync upsert against the other real tables failed with
// "column <table>.updated_at does not exist" (observed in prod for
// workshop_actions + cycle_measurements). These tests reproduce that: they are
// RED without the migration and GREEN with it.
//
// The fifth createRepository target, shift_outputs (shiftRecordRepo), points at
// a table that was DROPPED in prod (supabase-migration-r6-shift-outputs-merge.sql,
// merged into lsc_shift_hours) — it is dead code and deliberately NOT covered
// here or by the migration.
//
// Gated on RLS_TEST_DATABASE_URL → skips in CI, runs via scripts/rls-test/run.sh.

import { describe, it, expect, beforeAll, afterAll } from 'vitest'
import { Client } from 'pg'

const DB_URL = process.env.RLS_TEST_DATABASE_URL
const USER_A_PROJECT = '9999aaaa-0000-0000-0000-0000000000a1' // seeded project
const USER_A_STEP = '9999aaaa-0000-0000-0000-0000000000b1' // seeded process_step
const SYNC_TABLES = ['process_steps', 'cycle_measurements', 'workshop_actions'] // real, non-dropped sync tables

describe.skipIf(!DB_URL)('Offline-sync updated_at schema (module tables)', () => {
  let client: Client
  const createdIds: { table: string; id: string }[] = []

  beforeAll(async () => {
    client = new Client({ connectionString: DB_URL })
    await client.connect()
  })

  afterAll(async () => {
    if (client) {
      for (const { table, id } of createdIds) {
        await client.query(`DELETE FROM public.${table} WHERE id = $1`, [id]).catch(() => {})
      }
      await client.end()
    }
  })

  it('every createRepository-backed module table has an updated_at column', async () => {
    const r = await client.query(
      `SELECT table_name FROM information_schema.columns
        WHERE table_schema = 'public' AND column_name = 'updated_at'
          AND table_name = ANY($1)`,
      [SYNC_TABLES],
    )
    expect(r.rows.map((x) => x.table_name as string).sort()).toEqual([...SYNC_TABLES].sort())
  })

  it('a sync-style upsert carrying updated_at succeeds on workshop_actions', async () => {
    // Reproduces the prod failure class (SQLSTATE 42703 undefined_column): the
    // Last-Write-Wins push writes updated_at, which errors without the column.
    // (Raw-pg wording differs slightly from the PostgREST-qualified prod message.)
    const r = await client.query(
      `INSERT INTO public.workshop_actions
         (project_id, action_number, area, description, status, updated_at)
       VALUES ($1, 9001, 'sync-smoke', 'sync smoke', 'open', now())
       RETURNING id`,
      [USER_A_PROJECT],
    )
    expect(r.rowCount).toBe(1)
    createdIds.push({ table: 'workshop_actions', id: r.rows[0].id })
  })

  it('a sync-style upsert carrying updated_at succeeds on cycle_measurements', async () => {
    const r = await client.query(
      `INSERT INTO public.cycle_measurements
         (process_step_id, cycle_number, cycle_time_sec, updated_at)
       VALUES ($1, 9001, 12.5, now())
       RETURNING id`,
      [USER_A_STEP],
    )
    expect(r.rowCount).toBe(1)
    createdIds.push({ table: 'cycle_measurements', id: r.rows[0].id })
  })

  it('the delta-pull filter (gte updated_at) is a valid query on all three tables', async () => {
    const since = '1970-01-01T00:00:00Z'
    for (const table of SYNC_TABLES) {
      const r = await client.query(
        `SELECT count(*)::int AS n FROM public.${table} WHERE updated_at >= $1`,
        [since],
      )
      expect(typeof r.rows[0].n).toBe('number')
    }
  })

  it('updated_at is NOT NULL after backfill (delta-pull would skip NULL rows)', async () => {
    const r = await client.query(
      `SELECT table_name FROM information_schema.columns
        WHERE table_schema = 'public' AND column_name = 'updated_at'
          AND table_name = ANY($1) AND is_nullable = 'NO'`,
      [SYNC_TABLES],
    )
    expect(r.rows.map((x) => x.table_name as string).sort()).toEqual([...SYNC_TABLES].sort())
  })
})