import { and, eq, inArray, lt, sql } from 'drizzle-orm';
import type { DoDbClient, DoTxDbClient } from '@/server/do-host/lib/db.js';
import { voucher } from '@platform-modules/commerce-fulfillment';
import {
  deals,
  dealSkus,
  orderLine,
  pageLayoutPointers,
  personalDealRequests,
  supportTickets,
  transactionCases,
  supportStateTransitions,
} from '../schema.js';
import { insert as insertSupportStateTransition } from './support-state-transitions.js';
import { asCaseId } from '@/server/platform-seams/ids.js';

export async function expireDealRows(db: DoDbClient, dealId: string, now: Date): Promise<void> {
  await db
    .update(deals)
    .set({ dealState: 'EXPIRED' })
    .where(and(eq(deals.id, dealId), inArray(deals.dealState, ['ACTIVE', 'PAUSED'])));
  const skus = await db
    .select({ id: dealSkus.id })
    .from(dealSkus)
    .where(eq(dealSkus.dealId, dealId));
  if (!skus.length) return;
  const lines = await db
    .select({ id: orderLine.id })
    .from(orderLine)
    .where(
      inArray(
        orderLine.variantId,
        skus.map((s) => s.id),
      ),
    );
  if (lines.length)
    await db
      .update(voucher)
      .set({ state: 'EXPIRED' })
      .where(
        and(
          inArray(
            voucher.lineId,
            lines.map((l) => l.id),
          ),
          eq(voucher.state, 'UNREDEEMED'),
          lt(voucher.expiresAt, now),
        ),
      );
}

export async function clearExpiredGoldWindow(
  db: DoDbClient,
  dealId: string,
  now: Date,
): Promise<void> {
  await db
    .update(deals)
    .set({ soldOutGoldExpiresAt: null })
    .where(and(eq(deals.id, dealId), lt(deals.soldOutGoldExpiresAt, now)));
}

export async function publishScheduledPageRow(db: DoDbClient, page: string, now: Date) {
  const [ptr] = await db
    .select()
    .from(pageLayoutPointers)
    .where(
      and(
        eq(pageLayoutPointers.page, page),
        sql`${pageLayoutPointers.scheduledVersionId} IS NOT NULL`,
        sql`${pageLayoutPointers.scheduledAt} <= ${now}`,
      ),
    )
    .limit(1);
  if (!ptr?.scheduledVersionId) return null;
  const [row] = await db
    .update(pageLayoutPointers)
    .set({ liveVersionId: ptr.scheduledVersionId, scheduledVersionId: null, scheduledAt: null })
    .where(eq(pageLayoutPointers.page, page))
    .returning();
  return row ?? null;
}

export async function expirePersonalOfferRow(db: DoDbClient, requestId: string, now: Date) {
  const [row] = await db
    .update(personalDealRequests)
    .set({ status: 'EXPIRED' })
    .where(
      and(
        eq(personalDealRequests.id, requestId),
        eq(personalDealRequests.status, 'PENDING'),
        sql`${personalDealRequests.responseDeadline} <= ${now}`,
      ),
    )
    .returning();
  return row ?? null;
}

export async function autocloseSupportTicket(db: DoDbClient, ticketId: string) {
  const [row] = await db
    .update(supportTickets)
    .set({ status: 'closed', closedAt: new Date(), updatedAt: new Date() })
    .where(and(eq(supportTickets.id, ticketId), eq(supportTickets.status, 'resolved')))
    .returning();
  return row ?? null;
}

export async function transitionSupportCaseTimeout(db: DoTxDbClient, caseId: string) {
  await db.transaction(async (tx) => {
    await tx
      .update(transactionCases)
      .set({ status: 'escalated', updatedAt: new Date() })
      .where(
        and(
          eq(transactionCases.id, asCaseId(caseId)),
          eq(transactionCases.status, 'vendor_review'),
        ),
      );
    await tx.insert(supportStateTransitions).values({
      parentType: 'case',
      parentId: asCaseId(caseId),
      fromState: 'vendor_review',
      toState: 'escalated',
      actorType: 'system',
      actorId: null,
      reason: 'vendor_window_timeout',
      metadata: {},
    });
    await tx
      .update(transactionCases)
      .set({ status: 'ai_handling', updatedAt: new Date() })
      .where(
        and(eq(transactionCases.id, asCaseId(caseId)), eq(transactionCases.status, 'escalated')),
      );
    await tx.insert(supportStateTransitions).values({
      parentType: 'case',
      parentId: asCaseId(caseId),
      fromState: 'escalated',
      toState: 'ai_handling',
      actorType: 'system',
      actorId: null,
      reason: 'phase5_ai_triage',
      metadata: {},
    });
  });
}

export async function autocloseSupportCase(db: DoTxDbClient, caseId: string) {
  const [row] = await db
    .update(transactionCases)
    .set({ status: 'closed', closedAt: new Date(), updatedAt: new Date() })
    .where(and(eq(transactionCases.id, asCaseId(caseId)), eq(transactionCases.status, 'resolved')))
    .returning();
  if (row)
    await insertSupportStateTransition(db, {
      parentType: 'case',
      parentId: asCaseId(caseId),
      fromState: 'resolved',
      toState: 'closed',
      actorType: 'system',
      actorId: null,
      reason: 'autoclose',
      metadata: {},
    });
  return row ?? null;
}

export async function autocloseSupportTicketWithTransition(db: DoDbClient, ticketId: string) {
  const row = await autocloseSupportTicket(db, ticketId);
  if (row)
    await insertSupportStateTransition(db, {
      parentType: 'ticket',
      parentId: ticketId,
      fromState: 'resolved',
      toState: 'closed',
      actorType: 'system',
      actorId: null,
      reason: 'autoclose_alarm',
      metadata: {},
    });
  return row;
}