import { sql } from 'drizzle-orm'
import { describe, expect, it } from 'vitest'
import { createPgliteClient } from '@platform-modules/db/pglite'
import {
  authUsersCreationSql,
  userSessionsCreationSql,
  serviceTokensCreationSql,
  authUsersStatusMigrationSql,
  customAuthSchema,
} from './schema.js'

const LEGACY_AUTH_USERS = sql`
  CREATE TABLE auth_users (
    id text PRIMARY KEY,
    email text NOT NULL UNIQUE,
    password_hash text,
    session_version integer NOT NULL DEFAULT 0,
    roles text NOT NULL DEFAULT '["user"]',
    created_at timestamptz NOT NULL
  )
`

describe('authUsersCreationSql', () => {
  it('creates auth_users idempotently', async () => {
    const db = createPgliteClient({ schema: customAuthSchema })
    const ddl = authUsersCreationSql()
    expect(ddl).toContain('CREATE TABLE IF NOT EXISTS auth_users')
    await db.execute(ddl)
    await db.execute(ddl) // idempotent second run
    await db.execute(sql`
      INSERT INTO auth_users (id, email, session_version, roles, status, created_at)
      VALUES ('u1', 'u@example.com', 0, '["user"]', 'active', NOW())
    `)
    const [row] = await db
      .select({ id: customAuthSchema.authUsers.id })
      .from(customAuthSchema.authUsers)
      .limit(1)
    expect(row?.id).toBe('u1')
  })
})

describe('userSessionsCreationSql', () => {
  it('creates user_sessions idempotently (FKs auth_users)', async () => {
    const db = createPgliteClient({ schema: customAuthSchema })
    await db.execute(authUsersCreationSql())
    const ddl = userSessionsCreationSql()
    expect(ddl).toContain('CREATE TABLE IF NOT EXISTS user_sessions')
    await db.execute(ddl)
    await db.execute(ddl) // idempotent second run
    await db.execute(sql`
      INSERT INTO auth_users (id, email, session_version, roles, status, created_at)
      VALUES ('u1', 'u@example.com', 0, '["user"]', 'active', NOW())
    `)
    await db.execute(sql`
      INSERT INTO user_sessions (id, user_id, refresh_token_hash, expires_at, status)
      VALUES ('s1', 'u1', 'hash', NOW() + INTERVAL '7 days', 'active')
    `)
    const [row] = await db
      .select({ id: customAuthSchema.userSessions.id })
      .from(customAuthSchema.userSessions)
      .limit(1)
    expect(row?.id).toBe('s1')
  })
})

describe('serviceTokensCreationSql', () => {
  it('creates service_tokens idempotently (FKs auth_users)', async () => {
    const db = createPgliteClient({ schema: customAuthSchema })
    await db.execute(authUsersCreationSql())
    const ddl = serviceTokensCreationSql()
    expect(ddl).toContain('CREATE TABLE IF NOT EXISTS service_tokens')
    await db.execute(ddl)
    await db.execute(ddl) // idempotent second run
    await db.execute(sql`
      INSERT INTO auth_users (id, email, session_version, roles, status, created_at)
      VALUES ('u1', 'u@example.com', 0, '["user"]', 'active', NOW())
    `)
    await db.execute(sql`
      INSERT INTO service_tokens (id, user_id, token_hash, label, status, created_at)
      VALUES ('t1', 'u1', 'hash', 'test', 'active', NOW())
    `)
    const [row] = await db
      .select({ id: customAuthSchema.serviceTokens.id })
      .from(customAuthSchema.serviceTokens)
      .limit(1)
    expect(row?.id).toBe('t1')
  })
})

describe('authUsersStatusMigrationSql', () => {
  it('applies idempotently on pglite', async () => {
    const db = createPgliteClient({ schema: customAuthSchema })
    await db.execute(LEGACY_AUTH_USERS)
    const ddl = authUsersStatusMigrationSql()
    expect(ddl).toContain('ALTER TABLE auth_users ADD COLUMN IF NOT EXISTS status')
    await db.execute(ddl)
    await db.execute(ddl)
    await db.execute(sql`
      INSERT INTO auth_users (id, email, password_hash, session_version, roles, created_at)
      VALUES ('u1', 'u@example.com', 'hash', 0, '["user"]', NOW())
    `)
    const [row] = await db
      .select({ status: customAuthSchema.authUsers.status })
      .from(customAuthSchema.authUsers)
      .limit(1)
    expect(row?.status).toBe('active')
  })
})