import { eq, and, desc } from 'drizzle-orm';
import type { DrizzleClient, TxDrizzleClient } from '../client';
import { transactionCases, supportStateTransitions } from '../schema';
import type { InferInsertModel, InferSelectModel } from 'drizzle-orm';
import { asCaseId } from '../../platform-seams/ids';

export type TransactionCaseRow = InferSelectModel<typeof transactionCases>;
export type NewTransactionCase = InferInsertModel<typeof transactionCases>;

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

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

export async function lockHumanReviewById(
  db: TxDrizzleClient,
  id: string,
): Promise<TransactionCaseRow | null> {
  const rows = await db
    .select()
    .from(transactionCases)
    .where(and(eq(transactionCases.id, asCaseId(id)), eq(transactionCases.status, 'human_review')))
    .for('update');
  return rows[0] ?? null;
}

export async function listByRole(db: DrizzleClient, role: 'customer' | 'vendor', userId: string) {
  const col = role === 'customer' ? transactionCases.customerId : transactionCases.vendorId;
  return db
    .select()
    .from(transactionCases)
    .where(eq(col, userId))
    .orderBy(desc(transactionCases.updatedAt));
}

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

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

export async function transitionStatusIfCurrent(
  db: DrizzleClient,
  input: {
    id: string;
    expectedStatus: TransactionCaseRow['status'];
    status: TransactionCaseRow['status'];
    extras?: Partial<TransactionCaseRow>;
  },
): Promise<TransactionCaseRow | null> {
  const [row] = await db
    .update(transactionCases)
    .set({ status: input.status, updatedAt: new Date(), ...input.extras })
    .where(
      and(
        eq(transactionCases.id, asCaseId(input.id)),
        eq(transactionCases.status, input.expectedStatus),
      ),
    )
    .returning();
  return row ?? null;
}

export async function snapshotWindow(
  db: DrizzleClient,
  id: string,
  hours: number,
  expiresAt: Date,
): Promise<void> {
  await db
    .update(transactionCases)
    .set({ vendorWindowHours: hours, vendorWindowExpiresAt: expiresAt, updatedAt: new Date() })
    .where(eq(transactionCases.id, asCaseId(id)));
}

export async function setSlaDueAt(
  db: DrizzleClient,
  id: string,
  which: 'ai' | 'human',
  when: Date,
): Promise<void> {
  const col =
    which === 'ai'
      ? { slaAiDueAt: when, updatedAt: new Date() }
      : { slaHumanDueAt: when, updatedAt: new Date() };
  await db
    .update(transactionCases)
    .set(col)
    .where(eq(transactionCases.id, asCaseId(id)));
}

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

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

export type AdminCloseCaseInput = {
  id: string;
  fromState: TransactionCaseRow['status'];
  agentId: string;
};

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

export type ReassignVendorInput = {
  id: string;
  fromState: TransactionCaseRow['status'];
  agentId: string;
  reason: string;
  currentMetadata: Record<string, unknown>;
  reassignCount: number;
};

/**
 * Atomically set case status to vendor_review (increment reassign_count in metadata)
 * and insert a state-transition audit row.
 */
export async function reassignVendor(
  db: TxDrizzleClient,
  input: ReassignVendorInput,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .update(transactionCases)
      .set({
        status: 'vendor_review',
        assignedAgentId: null,
        updatedAt: new Date(),
        metadata: { ...input.currentMetadata, reassign_count: input.reassignCount + 1 },
      })
      .where(eq(transactionCases.id, asCaseId(input.id)));

    await tx.insert(supportStateTransitions).values({
      parentType: 'case',
      parentId: input.id,
      fromState: input.fromState,
      toState: 'vendor_review',
      actorType: 'human_agent',
      actorId: input.agentId,
      reason: input.reason,
    });
  });
}

export async function findOpenByPurchase(
  db: DrizzleClient,
  purchaseId: string,
): Promise<TransactionCaseRow | null> {
  const rows = await db
    .select()
    .from(transactionCases)
    .where(
      and(
        eq(transactionCases.orderLineId, purchaseId),
        // Not in terminal states
        and(eq(transactionCases.status, 'opened')),
      ),
    )
    .limit(1);
  return rows[0] ?? null;
}