/**
 * KB article version snapshot writer — kb-versioning spec (119).
 *
 * createArticleVersion: inserts an immutable snapshot into kb_article_versions.
 * version_number is assigned as MAX(version_number)+1 inside the same
 * transaction as the insert; UNIQUE (article_id, version_number) is the
 * concurrency guard.
 *
 * createArticleVersionWithRetry: wraps createArticleVersion with a single retry
 * on unique-violation (pg error code 23505).
 *
 * listArticleVersions: paginated list without content JSONB + author join.
 * getArticleVersion: full version including content.
 * restoreArticleVersion: snapshot current → overwrite article → audit row, all in
 *   one transaction per require-audit-in-transaction rule.
 */
import { eq, and, max, desc, count } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { kbArticleVersions } from '../schema/kb'
import { kbArticles } from '../schema/kb'
import { users } from '../schema/users'
import { tenantAuditLog } from '../schema/audit'

export interface CreateArticleVersionInput {
  articleId: string
  tenantId: string
  title: string
  content: Record<string, unknown>
  label?: string | null
  createdBy: string
}

export interface ArticleVersionSnapshot {
  id: string
  versionNumber: number
  articleId: string
  tenantId: string
  title: string
  content: Record<string, unknown>
  label: string | null
  createdBy: string
  createdAt: string
}

export interface ArticleVersionListItem {
  id: string
  version_number: number
  title: string
  label: string | null
  created_by: { id: string; name: string }
  created_at: string
}

export interface ArticleVersionFull extends ArticleVersionListItem {
  content: Record<string, unknown>
}

export interface RestoreVersionResult {
  article: {
    id: string
    title: string
    content: Record<string, unknown>
    status: string
    updated_at: string
  }
  safety_version: { id: string; version_number: number }
}

function isoDate(d: Date | string): string {
  return d instanceof Date ? d.toISOString() : String(d)
}

// ── Create ────────────────────────────────────────────────────────────────────

/**
 * Insert a version snapshot inside an existing Drizzle transaction (or at the
 * top-level db client if no transaction is active).
 *
 * Computes version_number as MAX+1 per article using a sub-select so it can be
 * called from inside a broader tx without a separate query round-trip.
 */
export async function createArticleVersion(
  db: Db | DbTx,
  input: CreateArticleVersionInput,
): Promise<ArticleVersionSnapshot> {
  // Derive next version number (MAX+1 per article; 1 if no versions yet)
  const [maxRow] = await db
    .select({ maxVer: max(kbArticleVersions.versionNumber) })
    .from(kbArticleVersions)
    .where(eq(kbArticleVersions.articleId, input.articleId))

  const nextVersion = (maxRow?.maxVer ?? 0) + 1

  const [row] = await db
    .insert(kbArticleVersions)
    .values({
      articleId: input.articleId,
      tenantId: input.tenantId,
      versionNumber: nextVersion,
      title: input.title,
      content: input.content,
      label: input.label ?? null,
      createdBy: input.createdBy,
    })
    .returning()

  if (!row) throw new Error('Failed to insert kb_article_versions row')

  return {
    id: row.id,
    versionNumber: row.versionNumber,
    articleId: row.articleId,
    tenantId: row.tenantId,
    title: row.title,
    content: row.content as Record<string, unknown>,
    label: row.label ?? null,
    createdBy: row.createdBy,
    createdAt: isoDate(row.createdAt),
  }
}

/**
 * Wraps createArticleVersion with a single retry on unique-violation (23505).
 * Use this anywhere outside of a broader transaction where a race on
 * version_number assignment is possible.
 */
export async function createArticleVersionWithRetry(
  db: Db | DbTx,
  input: CreateArticleVersionInput,
): Promise<ArticleVersionSnapshot> {
  try {
    return await createArticleVersion(db, input)
  } catch (err) {
    const pg = err as { code?: string }
    if (pg.code === '23505') {
      // Retry once on unique-violation
      return createArticleVersion(db, input)
    }
    throw err
  }
}

// ── List ──────────────────────────────────────────────────────────────────────

export async function listArticleVersions(
  db: Db,
  params: { articleId: string; tenantId: string; page: number; limit: number },
): Promise<{ data: ArticleVersionListItem[]; total: number }> {
  const offset = (params.page - 1) * params.limit

  const rows = await db
    .select({
      id: kbArticleVersions.id,
      version_number: kbArticleVersions.versionNumber,
      title: kbArticleVersions.title,
      label: kbArticleVersions.label,
      created_at: kbArticleVersions.createdAt,
      created_by_id: kbArticleVersions.createdBy,
      created_by_name: users.name,
    })
    .from(kbArticleVersions)
    .leftJoin(users, eq(users.id, kbArticleVersions.createdBy))
    .where(
      and(
        eq(kbArticleVersions.articleId, params.articleId),
        eq(kbArticleVersions.tenantId, params.tenantId),
      ),
    )
    .orderBy(desc(kbArticleVersions.versionNumber))
    .limit(params.limit)
    .offset(offset)

  const [countRow] = await db
    .select({ total: count() })
    .from(kbArticleVersions)
    .where(
      and(
        eq(kbArticleVersions.articleId, params.articleId),
        eq(kbArticleVersions.tenantId, params.tenantId),
      ),
    )

  const data: ArticleVersionListItem[] = rows.map((r) => ({
    id: r.id,
    version_number: r.version_number,
    title: r.title,
    label: r.label ?? null,
    created_by: { id: r.created_by_id, name: r.created_by_name ?? 'Unknown' },
    created_at: isoDate(r.created_at),
  }))

  return { data, total: countRow?.total ?? 0 }
}

// ── Get full version ──────────────────────────────────────────────────────────

export async function getArticleVersion(
  db: Db,
  params: { versionId: string; articleId: string; tenantId: string },
): Promise<ArticleVersionFull | null> {
  const [row] = await db
    .select({
      id: kbArticleVersions.id,
      version_number: kbArticleVersions.versionNumber,
      title: kbArticleVersions.title,
      content: kbArticleVersions.content,
      label: kbArticleVersions.label,
      created_at: kbArticleVersions.createdAt,
      created_by_id: kbArticleVersions.createdBy,
      created_by_name: users.name,
    })
    .from(kbArticleVersions)
    .leftJoin(users, eq(users.id, kbArticleVersions.createdBy))
    .where(
      and(
        eq(kbArticleVersions.id, params.versionId),
        eq(kbArticleVersions.articleId, params.articleId),
        eq(kbArticleVersions.tenantId, params.tenantId),
      ),
    )
    .limit(1)

  if (!row) return null

  return {
    id: row.id,
    version_number: row.version_number,
    title: row.title,
    content: row.content as Record<string, unknown>,
    label: row.label ?? null,
    created_by: { id: row.created_by_id, name: row.created_by_name ?? 'Unknown' },
    created_at: isoDate(row.created_at),
  }
}

// ── Restore ───────────────────────────────────────────────────────────────────

/**
 * Restore an article to a prior version in a single transaction:
 *  1. Snapshot current state as safety version
 *  2. Overwrite article title/content
 *  3. Insert audit row (require-audit-in-transaction)
 *
 * Returns null for the article if not found (caller should 404).
 */
export async function restoreArticleVersion(
  db: Db,
  params: {
    articleId: string
    versionId: string
    tenantId: string
    userId: string
  },
): Promise<RestoreVersionResult> {
  const runTx = async (): Promise<RestoreVersionResult> => {
    return db.transaction(async (tx) => {
      // Resolve article — verify tenant ownership
      const [articleRow] = await tx
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.id, params.articleId),
            eq(kbArticles.tenantId, params.tenantId),
          ),
        )
        .limit(1)

      if (!articleRow) {
        throw Object.assign(new Error('Article not found'), { status: 404 as const })
      }

      // Resolve target version
      const [targetVersion] = await tx
        .select()
        .from(kbArticleVersions)
        .where(
          and(
            eq(kbArticleVersions.id, params.versionId),
            eq(kbArticleVersions.articleId, params.articleId),
            eq(kbArticleVersions.tenantId, params.tenantId),
          ),
        )
        .limit(1)

      if (!targetVersion) {
        throw Object.assign(new Error('Version not found'), { status: 404 as const })
      }

      // Step 1: Snapshot current article state as safety version
      const safetyVersion = await createArticleVersion(tx, {
        articleId: params.articleId,
        tenantId: params.tenantId,
        title: articleRow.title,
        content: articleRow.content as Record<string, unknown>,
        label: `Auto-saved before restore to v${targetVersion.versionNumber}`,
        createdBy: params.userId,
      })

      // Step 2: Overwrite article title/content
      const [updatedArticle] = await tx
        .update(kbArticles)
        .set({
          title: targetVersion.title,
          content: targetVersion.content as Record<string, unknown>,
          updatedAt: new Date(),
          updatedBy: params.userId,
        })
        .where(and(eq(kbArticles.tenantId, params.tenantId), eq(kbArticles.id, params.articleId)))
        .returning()

      if (!updatedArticle) throw new Error('Failed to update article during restore')

      // Step 3: Audit row — require-audit-in-transaction
      await tx.insert(tenantAuditLog).values({
        tenantId: params.tenantId,
        userId: params.userId,
        eventType: 'kb_article.restored',
        entityType: 'kb_article',
        entityId: params.articleId,
        beforeState: { from_version: safetyVersion.versionNumber - 1 },
        afterState: { to_version: targetVersion.versionNumber },
      })

      return {
        article: {
          id: updatedArticle.id,
          title: updatedArticle.title,
          content: updatedArticle.content as Record<string, unknown>,
          status: updatedArticle.status,
          updated_at: isoDate(updatedArticle.updatedAt),
        },
        safety_version: {
          id: safetyVersion.id,
          version_number: safetyVersion.versionNumber,
        },
      }
    })
  }

  try {
    return await runTx()
  } catch (err) {
    const pg = err as { code?: string; status?: number }
    // Re-throw 404 signals directly — don't retry on not-found
    if (pg.status === 404) throw err
    // Retry once on unique-violation from the safety snapshot insert
    if (pg.code === '23505') {
      return runTx()
    }
    throw err
  }
}