import type { FastifyInstance } from "fastify";
import { listClientSponsorsByClientIds } from "../../lib/client-sponsors.js";
import { requireSession } from "../../lib/auth.js";
import {
  countKommoConversationsNeedingAttention,
  countKommoFailedDeliveries,
  countKommoRetryScheduledDeliveries,
  countUnlinkedKommoLeads,
  listKommoConversationIssues,
  listKommoLeads,
  processDueKommoOutboundDeliveries,
} from "../../lib/kommo.js";
import { prisma } from "../../lib/prisma.js";
import { getSessionProfile } from "../../lib/session.js";

export async function registerDashboardRoutes(app: FastifyInstance) {
  app.get("/bootstrap", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    try {
      await processDueKommoOutboundDeliveries({
        lawFirmId: profile.lawFirm.id,
        limit: 5,
      });
    } catch (error) {
      request.log.error(
        {
          err: error,
          lawFirmId: profile.lawFirm.id,
        },
        "Kommo retry processor failed during dashboard bootstrap",
      );
    }

    const [
      clientsCount,
      openCasesCount,
      caseStatuses,
      clients,
      cases,
      aiSettings,
      credentials,
      activeWorkflowInstancesCount,
      pendingRequiredDocumentsCount,
      pendingCaseFormsCount,
      pendingDocumentReviewsCount,
      overdueDocumentReviewsCount,
      dueNext24hReviewCount,
      upcomingReviewDeadlines,
      caseStatusBreakdownRows,
      openedCasesByMonthRows,
      unlinkedKommoLeadsCount,
      unlinkedKommoLeads,
      kommoRetryScheduledCount,
      kommoFailedDeliveriesCount,
      kommoConversationsNeedingAttentionCount,
      kommoConversationIssues,
    ] = await Promise.all([
      prisma.client.count({
        where: {
          law_firm_id: profile.lawFirm.id,
          deleted_at: null,
        },
      }),
      prisma.caseRecord.count({
        where: {
          law_firm_id: profile.lawFirm.id,
          deleted_at: null,
          archived_at: null,
          status_code: {
            not: "closed",
          },
        },
      }),
      prisma.caseStatus.findMany({
        orderBy: {
          sort_order: "asc",
        },
      }),
      prisma.client.findMany({
        where: {
          law_firm_id: profile.lawFirm.id,
          deleted_at: null,
        },
        orderBy: {
          created_at: "desc",
        },
        take: 25,
      }),
      prisma.caseRecord.findMany({
        where: {
          law_firm_id: profile.lawFirm.id,
          deleted_at: null,
        },
        orderBy: {
          created_at: "desc",
        },
      }),
      prisma.lawFirmAiSetting.findUnique({
        where: {
          law_firm_id: profile.lawFirm.id,
        },
      }),
      prisma.lawFirmAiCredential.findMany({
        where: {
          law_firm_id: profile.lawFirm.id,
        },
        orderBy: {
          created_at: "desc",
        },
      }),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM workflow_instances
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND status_code = 'active'
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM case_required_documents
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND status_code = 'pending'
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM case_forms
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND status_code = 'pending'
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM document_review_items
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND current_status NOT IN ('approved', 'rejected')
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM document_review_items
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND current_status NOT IN ('approved', 'rejected')
          AND due_at < NOW()
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<Array<{ total: number }>>`
        SELECT COUNT(*) AS total
        FROM document_review_items
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND current_status NOT IN ('approved', 'rejected')
          AND due_at >= NOW()
          AND due_at <= DATE_ADD(NOW(), INTERVAL 1 DAY)
      `.then((rows) => Number(rows[0]?.total ?? 0)),
      prisma.$queryRaw<
        Array<{
          id: string;
          document_name: string;
          due_at: Date;
          current_status: string;
          case_id: string;
          case_number: string;
          case_title: string;
        }>
      >`
        SELECT
          dri.id,
          dri.document_name,
          dri.due_at,
          dri.current_status,
          c.id AS case_id,
          c.case_number,
          c.title AS case_title
        FROM document_review_items dri
        INNER JOIN cases c ON c.id = dri.case_id
        WHERE dri.law_firm_id = ${profile.lawFirm.id}
          AND dri.current_status NOT IN ('approved', 'rejected')
          AND dri.due_at >= NOW()
          AND dri.due_at <= DATE_ADD(NOW(), INTERVAL 1 DAY)
        ORDER BY dri.due_at ASC
        LIMIT 12
      `,
      prisma.$queryRaw<Array<{ status_code: string; total: number }>>`
        SELECT status_code, COUNT(*) AS total
        FROM cases
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND deleted_at IS NULL
          AND archived_at IS NULL
        GROUP BY status_code
      `,
      prisma.$queryRaw<Array<{ month_start: string; total: number }>>`
        SELECT DATE_FORMAT(opened_at, '%Y-%m-01') AS month_start, COUNT(*) AS total
        FROM cases
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND deleted_at IS NULL
          AND archived_at IS NULL
          AND opened_at >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 5 MONTH)
        GROUP BY DATE_FORMAT(opened_at, '%Y-%m-01')
        ORDER BY month_start ASC
      `,
      countUnlinkedKommoLeads(profile.lawFirm.id),
      listKommoLeads({
        lawFirmId: profile.lawFirm.id,
        status: "unlinked",
        limit: 6,
      }),
      countKommoRetryScheduledDeliveries(profile.lawFirm.id),
      countKommoFailedDeliveries(profile.lawFirm.id),
      countKommoConversationsNeedingAttention(profile.lawFirm.id),
      listKommoConversationIssues({
        lawFirmId: profile.lawFirm.id,
        limit: 6,
      }),
    ]);

    const clientsMap = new Map(
      clients.map((client) => [
        client.id,
        `${client.first_name} ${client.last_name}`.trim(),
      ]),
    );
    const clientSponsorMap = await listClientSponsorsByClientIds({
      lawFirmId: profile.lawFirm.id,
      clientIds: clients.map((client) => client.id),
    });

    const caseStatusLabelByCode = new Map(caseStatuses.map((status) => [status.code, status.name]));
    const monthFormatter = new Intl.DateTimeFormat(profile.lawFirm.defaultLocale || "pt-BR", {
      month: "short",
      year: "2-digit",
      timeZone: profile.lawFirm.timezone || "America/New_York",
    });
    const openedCasesByMonth = Array.from({ length: 6 }, (_value, index) => {
      const date = new Date();
      date.setDate(1);
      date.setMonth(date.getMonth() - (5 - index));
      const monthKey = date.toISOString().slice(0, 7);
      const matchedRow = openedCasesByMonthRows.find((row) => row.month_start.startsWith(monthKey));

      return {
        month: monthFormatter.format(date),
        total: Number(matchedRow?.total ?? 0),
      };
    });

    return {
      me: profile.user,
      lawFirm: profile.lawFirm,
      summary: {
        clientsCount,
        openCasesCount,
        aiCredentialsCount: credentials.filter((credential) => credential.is_active)
          .length,
        activeWorkflowInstancesCount,
        pendingRequiredDocumentsCount,
        pendingCaseFormsCount,
        pendingDocumentReviewsCount,
        overdueDocumentReviewsCount,
        dueNext24hReviewCount,
        unlinkedKommoLeadsCount,
        kommoRetryScheduledCount,
        kommoFailedDeliveriesCount,
        kommoConversationsNeedingAttentionCount,
      },
      operations: {
        caseStatusBreakdown: caseStatusBreakdownRows.map((row) => ({
          statusCode: row.status_code,
          label: caseStatusLabelByCode.get(row.status_code) ?? row.status_code,
          total: Number(row.total ?? 0),
        })),
        openedCasesByMonth,
        upcomingReviewDeadlines: upcomingReviewDeadlines.map((item) => ({
          id: item.id,
          documentName: item.document_name,
          dueAt: item.due_at,
          currentStatus: item.current_status,
          caseId: item.case_id,
          caseNumber: item.case_number,
          caseTitle: item.case_title,
        })),
        unlinkedKommoLeads,
        kommoConversationIssues,
      },
      lookups: {
        caseStatuses: caseStatuses.map((status) => ({
          code: status.code,
          name: status.name,
          isClosedStatus: status.is_closed_status,
        })),
      },
      clients: clients.map((client) => ({
        id: client.id,
        clientNumber: client.client_number,
        name: `${client.first_name} ${client.last_name}`.trim(),
        email: client.email,
        phone: client.phone,
        preferredLanguage: client.preferred_language,
        createdAt: client.created_at,
        sponsor: clientSponsorMap.get(client.id) ?? null,
      })),
      cases: cases.map((caseRecord) => ({
        id: caseRecord.id,
        caseNumber: caseRecord.case_number,
        title: caseRecord.title,
        caseTypeCode: caseRecord.case_type_code,
        caseSubtypeCode: caseRecord.case_subtype_code,
        statusCode: caseRecord.status_code,
        priorityCode: caseRecord.priority_code,
        clientId: caseRecord.client_id,
        clientName: clientsMap.get(caseRecord.client_id) ?? "Unknown client",
        openedAt: caseRecord.opened_at,
        archivedAt: caseRecord.archived_at,
      })),
      ai: {
        settings: aiSettings
          ? {
              provider: aiSettings.default_provider,
              model: aiSettings.default_model,
              rateLimitPerMinute: aiSettings.rate_limit_per_minute,
              monthlyBudget: aiSettings.monthly_budget,
              alertThreshold: aiSettings.alert_threshold,
              activeCredentialId: aiSettings.active_credential_id,
            }
          : null,
        credentials: credentials.map((credential) => ({
          id: credential.id,
          provider: credential.provider,
          credentialName: credential.credential_name,
          keyLast4: credential.key_last4,
          isActive: credential.is_active,
          isDefault: credential.is_default,
          createdAt: credential.created_at,
        })),
      },
    };
  });
}