import { eq, and, desc, count, not, inArray } from 'drizzle-orm';
import type { DrizzleClient, TxDrizzleClient } from '../client';
import { supportTickets, supportStateTransitions } from '../schema';
import type { InferInsertModel, InferSelectModel } from 'drizzle-orm';

export type SupportTicketRow = InferSelectModel<typeof supportTickets>;
export type NewSupportTicket = InferInsertModel<typeof supportTickets>;

export async function insert(
  db: DrizzleClient,
  values: NewSupportTicket,
): Promise<SupportTicketRow> {
  const [row] = await db.insert(supportTickets).values(values).returning();
  return row!;
}

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

export async function listByOpener(
  db: DrizzleClient,
  openerId: string,
  status?: SupportTicketRow['status'],
) {
  const conds = [eq(supportTickets.openerId, openerId)];
  if (status) conds.push(eq(supportTickets.status, status));
  return db
    .select()
    .from(supportTickets)
    .where(and(...conds))
    .orderBy(desc(supportTickets.updatedAt));
}

export async function countByOpener(db: DrizzleClient, openerId: string): Promise<number> {
  const rows = await db
    .select({ count: count() })
    .from(supportTickets)
    .where(eq(supportTickets.openerId, openerId));
  return rows[0]?.count ?? 0;
}

export async function countOpenByOpener(db: DrizzleClient, openerId: string): Promise<number> {
  const rows = await db
    .select({ count: count() })
    .from(supportTickets)
    .where(
      and(
        eq(supportTickets.openerId, openerId),
        not(inArray(supportTickets.status, ['closed', 'resolved'])),
      ),
    );
  return rows[0]?.count ?? 0;
}

export async function listByStatus(db: DrizzleClient, status: SupportTicketRow['status']) {
  return db
    .select()
    .from(supportTickets)
    .where(eq(supportTickets.status, status))
    .orderBy(desc(supportTickets.updatedAt));
}

export async function updateStatus(
  db: DrizzleClient,
  id: string,
  status: SupportTicketRow['status'],
  extras: Partial<SupportTicketRow> = {},
): Promise<void> {
  await db
    .update(supportTickets)
    .set({ status, updatedAt: new Date(), ...extras })
    .where(eq(supportTickets.id, id));
}

export async function assignAgent(db: DrizzleClient, id: string, agentId: string): Promise<void> {
  await db
    .update(supportTickets)
    .set({ assignedAgentId: agentId, updatedAt: new Date() })
    .where(eq(supportTickets.id, id));
}

export async function incrementReopen(db: DrizzleClient, id: string): Promise<void> {
  const row = await findById(db, id);
  if (!row) return;
  await db
    .update(supportTickets)
    .set({ reopenCount: row.reopenCount + 1, updatedAt: new Date() })
    .where(eq(supportTickets.id, id));
}

export type AdminCloseTicketInput = {
  id: string;
  fromState: SupportTicketRow['status'];
  agentId: string;
};

/**
 * Atomically set ticket status to closed and insert a state-transition audit row.
 */
export async function adminClose(db: TxDrizzleClient, input: AdminCloseTicketInput): Promise<void> {
  const now = new Date();
  await db.transaction(async (tx) => {
    await tx
      .update(supportTickets)
      .set({ status: 'closed', closedAt: now, updatedAt: now })
      .where(eq(supportTickets.id, input.id));
    await tx.insert(supportStateTransitions).values({
      parentType: 'ticket',
      parentId: input.id,
      fromState: input.fromState,
      toState: 'closed',
      actorType: 'human_agent',
      actorId: input.agentId,
      reason: 'admin_closed',
    });
  });
}