import { asc, desc, eq } from 'drizzle-orm';
import type { DrizzleClient, TxDrizzleClient } from '../client';
import {
  agentDefinitions,
  agentDefinitionVersions,
  users,
  type AgentDefinition,
  type NewAgentDefinition,
} from '../schema';

export interface AgentDefinitionVersionRow {
  id: string;
  version: number;
  createdAt: Date;
  changedBy: string | null;
  changedByName: string | null;
}

export async function getAgentDefinition(
  db: DrizzleClient,
  id: string,
): Promise<AgentDefinition | null> {
  const rows = await db.select().from(agentDefinitions).where(eq(agentDefinitions.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function getAgentDefinitionBySlug(
  db: DrizzleClient,
  slug: string,
): Promise<AgentDefinition | null> {
  const rows = await db
    .select()
    .from(agentDefinitions)
    .where(eq(agentDefinitions.slug, slug))
    .limit(1);
  return rows[0] ?? null;
}

export async function listAgentDefinitions(db: DrizzleClient): Promise<AgentDefinition[]> {
  return db.select().from(agentDefinitions).orderBy(asc(agentDefinitions.slug));
}

export async function updateAgentDefinition(
  db: TxDrizzleClient,
  id: string,
  updates: Partial<NewAgentDefinition>,
  changedBy?: string,
): Promise<AgentDefinition> {
  return db.transaction(async (tx) => {
    const [current] = await tx
      .select()
      .from(agentDefinitions)
      .where(eq(agentDefinitions.id, id))
      .limit(1);
    if (!current) {
      throw new Error(`Agent definition not found: ${id}`);
    }

    const nextVersion = current.version + 1;
    const { version: _version, id: _id, createdAt: _createdAt, ...safeUpdates } = updates;

    const [updated] = await tx
      .update(agentDefinitions)
      .set({
        ...safeUpdates,
        version: nextVersion,
        updatedAt: new Date(),
      })
      .where(eq(agentDefinitions.id, id))
      .returning();

    if (!updated) {
      throw new Error(`Agent definition update failed: ${id}`);
    }

    await tx.insert(agentDefinitionVersions).values({
      definitionId: id,
      version: nextVersion,
      snapshot: updated,
      changedBy: changedBy ?? null,
    });

    return updated;
  });
}

export async function getAgentDefinitionVersions(
  db: DrizzleClient,
  definitionId: string,
): Promise<AgentDefinitionVersionRow[]> {
  const rows = await db
    .select({
      id: agentDefinitionVersions.id,
      version: agentDefinitionVersions.version,
      createdAt: agentDefinitionVersions.createdAt,
      changedBy: agentDefinitionVersions.changedBy,
      changedByName: users.displayName,
    })
    .from(agentDefinitionVersions)
    .leftJoin(users, eq(users.id, agentDefinitionVersions.changedBy))
    .where(eq(agentDefinitionVersions.definitionId, definitionId))
    .orderBy(desc(agentDefinitionVersions.version));

  return rows;
}