import { describe, expect, it } from 'vitest'
import { comments, commentsTableSql } from './schema.js'

describe('comments drizzle table shape', () => {
  it('exposes expected column names', () => {
    const cols = Object.keys(comments)
    expect(cols).toContain('seq')
    expect(cols).toContain('id')
    expect(cols).toContain('targetType')
    expect(cols).toContain('targetId')
    expect(cols).toContain('parentId')
    expect(cols).toContain('depth')
    expect(cols).toContain('authorKind')
    expect(cols).toContain('authorUserId')
    expect(cols).toContain('authorEmail')
    expect(cols).toContain('authorUrl')
    expect(cols).toContain('body')
    expect(cols).toContain('bodyHtml')
    expect(cols).toContain('status')
    expect(cols).toContain('createdAtMs')
    expect(cols).toContain('createdAt')
    expect(cols).toContain('editedAt')
  })

  it('seq column is bigserial (notNull, no default in drizzle obj — auto from DB)', () => {
    // bigserial sets notNull and has DB-side default
    expect(comments.seq.notNull).toBe(true)
  })

  it('id is the primary key', () => {
    expect(comments.id.primary).toBe(true)
  })
})

describe('commentsTableSql', () => {
  it('default table name is "comments"', () => {
    const sql = commentsTableSql()
    expect(sql).toMatch(/CREATE TABLE IF NOT EXISTS comments/)
  })

  it('custom table name propagates', () => {
    const sql = commentsTableSql('post_comments')
    expect(sql).toMatch(/CREATE TABLE IF NOT EXISTS post_comments/)
    expect(sql).toMatch(/post_comments_target_keyset_idx/)
  })

  it('contains all required columns', () => {
    const sql = commentsTableSql()
    const cols = ['seq', 'id', 'target_type', 'target_id', 'parent_id', 'depth',
      'author_kind', 'author_user_id', 'author_name', 'author_email', 'author_url',
      'body', 'body_html', 'status', 'created_at_ms', 'created_at', 'edited_at']
    for (const col of cols) {
      expect(sql).toContain(col)
    }
  })

  it('contains all three indexes', () => {
    const sql = commentsTableSql()
    expect(sql).toMatch(/comments_target_keyset_idx/)
    expect(sql).toMatch(/comments_parent_id_idx/)
    expect(sql).toMatch(/comments_status_idx/)
  })

  it('parent_id index has WHERE clause (partial index)', () => {
    const sql = commentsTableSql()
    expect(sql).toMatch(/parent_id.*WHERE parent_id IS NOT NULL/s)
  })
})

describe('commentsTableSql — moderation index', () => {
  it('emits a (status, created_at_ms, seq) index for cross-target moderation scans', () => {
    const sql = commentsTableSql()
    expect(sql).toContain('comments_moderation_idx ON comments (status, created_at_ms, seq)')
  })

  it('honours a custom table name in the moderation index', () => {
    const sql = commentsTableSql('cmt')
    expect(sql).toContain('cmt_moderation_idx ON cmt (status, created_at_ms, seq)')
  })
})