import { getConnections, PgTestClient } from 'pgsql-test';

let pg: PgTestClient;
let db: PgTestClient;
let teardown: () => Promise<void>;

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  // Seed using superuser to bypass RLS
  await pg.loadJson({
    'app.users': [
      {
        id: '550e8400-e29b-41d4-a716-446655440001',
        email: 'alice@example.com',
        name: 'Alice'
      },
      {
        id: '550e8400-e29b-41d4-a716-446655440002',
        email: 'bob@example.com',
        name: 'Bob'
      }
    ],
    'app.posts': [
      {
        id: 'post-1',
        title: 'First Post',
        owner_id: '550e8400-e29b-41d4-a716-446655440001'
      },
      {
        id: 'post-2',
        title: 'Second Post',
        owner_id: '550e8400-e29b-41d4-a716-446655440002'
      }
    ]
  });
});

afterAll(async () => {
  await teardown();
});

-- Insert users
INSERT INTO app.users (id, email, name) VALUES
  ('550e8400-e29b-41d4-a716-446655440001', 'alice@example.com', 'Alice'),
  ('550e8400-e29b-41d4-a716-446655440002', 'bob@example.com', 'Bob'),
  ('550e8400-e29b-41d4-a716-446655440003', 'charlie@example.com', 'Charlie');

-- Insert posts with foreign key references
INSERT INTO app.posts (id, title, owner_id) VALUES
  ('post-1', 'Alice Post 1', '550e8400-e29b-41d4-a716-446655440001'),
  ('post-2', 'Alice Post 2', '550e8400-e29b-41d4-a716-446655440001'),
  ('post-3', 'Bob Post', '550e8400-e29b-41d4-a716-446655440002');

import path from 'path';

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  await pg.loadSql([
    path.join(__dirname, 'fixtures/seed.sql')
  ]);
});

await pg.loadSql([
  path.join(__dirname, 'fixtures/users.sql'),
  path.join(__dirname, 'fixtures/posts.sql'),
  path.join(__dirname, 'fixtures/comments.sql')
]);

id,email,name
550e8400-e29b-41d4-a716-446655440001,alice@example.com,Alice
550e8400-e29b-41d4-a716-446655440002,bob@example.com,Bob
550e8400-e29b-41d4-a716-446655440003,charlie@example.com,Charlie

import path from 'path';

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  await pg.loadCsv({
    'app.users': path.join(__dirname, 'fixtures/users.csv'),
    'app.posts': path.join(__dirname, 'fixtures/posts.csv')
  });
});

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  // 1. Load large reference data from CSV
  await pg.loadCsv({
    'app.categories': path.join(__dirname, 'fixtures/categories.csv')
  });

  // 2. Load complex relationships from SQL
  await pg.loadSql([
    path.join(__dirname, 'fixtures/users-with-roles.sql')
  ]);

  // 3. Add test-specific data inline
  await pg.loadJson({
    'app.posts': [
      { title: 'Test Post', owner_id: testUserId, category_id: 1 }
    ]
  });
});

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());
});

afterAll(async () => {
  await teardown();
});

beforeEach(async () => {
  await pg.beforeEach();
  await db.beforeEach();
});

afterEach(async () => {
  await db.afterEach();
  await pg.afterEach();
});

describe('empty state tests', () => {
  it('handles no data gracefully', async () => {
    const result = await db.query('SELECT COUNT(*) FROM app.posts');
    expect(result.rows[0].count).toBe('0');
  });
});

describe('populated state tests', () => {
  beforeEach(async () => {
    await pg.loadJson({
      'app.posts': [
        { title: 'Test Post', owner_id: userId }
      ]
    });
  });

  it('finds existing posts', async () => {
    const result = await db.query('SELECT COUNT(*) FROM app.posts');
    expect(result.rows[0].count).toBe('1');
  });
});

// Bypass RLS for setup (use pg)
await pg.loadJson({
  'app.posts': [{ title: 'Admin Post', owner_id: adminId }]
});

// Respect RLS for user operations (use db with context)
db.setContext({
  role: 'authenticated',
  'request.jwt.claim.sub': userId
});

await db.loadJson({
  'app.posts': [{ title: 'User Post', owner_id: userId }]
});

__tests__/
├── fixtures/
│   ├── users.csv
│   ├── posts.csv
│   ├── seed.sql
│   └── complex-scenario.sql
├── users.test.ts
├── posts.test.ts
└── rls.test.ts