import { createResolver, t } from "@tailor-platform/sdk";
import { getDB } from "@/generated/kysely-tailordb";

/**
 * BFF (backend-for-frontend) resolver: journal lines joined to their journal
 * entry (and GL account), returned as flat denormalized rows grouped by
 * `journalEntryId` (each entry's lines are contiguous) so a table UI can render
 * them under their entry.
 *
 * Why a resolver (not a built-in query): the built-in `journalLines` /
 * `journalEntries` collection queries only filter one entity at a time, so they
 * cannot answer "lines whose entry is POSTED in this period" or "entries that
 * touch this account" — both sides of the join. This resolver inner-joins
 * JournalLine → JournalEntry → Account and applies filters across the join.
 *
 * Pagination is by ENTRY, not by line: a journal entry is a balanced unit
 * (Σdebit = Σcredit), so splitting its lines across a page boundary would show
 * a meaningless half-entry. Each page therefore returns whole entries' lines,
 * ordered so an entry's lines are contiguous — the client groups by
 * `journalEntryId` and every group is complete and balanced.
 */

type MainDbHandle = ReturnType<typeof getDB<"main-db">>;

const STATUSES = ["DRAFT", "CANCELLED", "POSTED"] as const;
type JournalEntryStatus = (typeof STATUSES)[number];

const SOURCE_DOCUMENT_TYPES = [
  "ACCOUNT_PAYABLE_DOCUMENT",
  "OUTGOING_PAYMENT",
  "SALES_INVOICE",
  "INVENTORY_LEDGER",
  "STANDARD_COST_REVISION",
  "PRODUCTION_ORDER",
  "YEAR_END_CLOSE",
] as const;
type SourceDocumentType = (typeof SOURCE_DOCUMENT_TYPES)[number];

const DEFAULT_PAGE_SIZE = 50;
const MAX_PAGE_SIZE = 200;

interface GroupedJournalLinesInput {
  companyId: string;
  status?: JournalEntryStatus;
  accountingPeriodId?: string;
  entryDateFrom?: Date;
  entryDateTo?: Date;
  accountId?: string;
  sourceDocumentType?: SourceDocumentType;
  sourceDocumentId?: string;
  descriptionContains?: string;
  orderBy: "entryDate" | "createdAt";
  orderDirection: "asc" | "desc";
  limit: number;
  offset: number;
}

interface JournalLineRow {
  lineId: string;
  journalEntryId: string;
  accountId: string;
  accountCode: string;
  accountName: string;
  debitAmount: string | null;
  creditAmount: string | null;
  lineDescription: string | null;
  entryDate: string;
  status: string;
  accountingPeriodId: string;
  entryDescription: string | null;
  sourceDocumentType: string | null;
  sourceDocumentId: string | null;
  postedAt: string | null;
}

const toIso = (value: unknown): string | null => {
  if (value == null) return null;
  if (value instanceof Date) return value.toISOString();
  // TailorDB timestamp/date columns come back as an ISO string or a Date; other
  // shapes are not expected, so avoid Object's default stringification.
  return typeof value === "string" ? value : null;
};

async function loadGroupedJournalLines(db: MainDbHandle, input: GroupedJournalLinesInput) {
  // Some filters restrict to entries that HAVE a matching line — by account, or
  // by a line whose description matches. TailorDB's SQL layer has no
  // DISTINCT / correlated subqueries, so resolve each as a plain
  // `journalEntryId IN (...)` list and intersect them (the same two-step shape
  // used elsewhere in erp-kit).
  const entryIdSets: string[][] = [];
  if (input.accountId) {
    const lineRows = await db
      .selectFrom("JournalLine")
      .select("journalEntryId")
      .where("accountId", "=", input.accountId)
      .execute();
    entryIdSets.push([...new Set(lineRows.map((r) => r.journalEntryId))]);
  }
  if (input.descriptionContains) {
    const lineRows = await db
      .selectFrom("JournalLine")
      .select("journalEntryId")
      .where("description", "ilike", `%${input.descriptionContains}%`)
      .execute();
    entryIdSets.push([...new Set(lineRows.map((r) => r.journalEntryId))]);
  }
  // Intersect the sets; null means "no line-based restriction", an empty
  // intersection means nothing matches.
  let restrictEntryIds: string[] | null = null;
  for (const ids of entryIdSets) {
    const set = new Set(ids);
    restrictEntryIds =
      restrictEntryIds === null ? ids : restrictEntryIds.filter((id) => set.has(id));
  }
  if (restrictEntryIds !== null && restrictEntryIds.length === 0) {
    return { hasNextPage: false, entryCount: 0, total: 0, rows: [] as JournalLineRow[] };
  }

  // Base filtered entry query (no projection yet), reused for both the total
  // count and the page of ids. Every filter is a `.where` (type-preserving), so
  // the reassigned `let` type-checks.
  let base = db.selectFrom("JournalEntry").where("companyId", "=", input.companyId);
  if (input.status) base = base.where("status", "=", input.status);
  if (input.accountingPeriodId) {
    base = base.where("accountingPeriodId", "=", input.accountingPeriodId);
  }
  if (input.entryDateFrom) base = base.where("entryDate", ">=", input.entryDateFrom);
  if (input.entryDateTo) base = base.where("entryDate", "<=", input.entryDateTo);
  if (input.sourceDocumentType) {
    base = base.where("sourceDocumentType", "=", input.sourceDocumentType);
  }
  if (input.sourceDocumentId) {
    base = base.where("sourceDocumentId", "=", input.sourceDocumentId);
  }
  if (restrictEntryIds) base = base.where("id", "in", restrictEntryIds);

  // Total matching entries across all pages — lets the client number entries
  // (the newest entry, shown first under desc order, is #total).
  const totalRow = await base
    .select((eb) => eb.fn.countAll<number>().as("count"))
    .executeTakeFirst();
  const total = Number(totalRow?.count ?? 0);

  // Order by the requested field, then createdAt as a tiebreaker (entryDate is
  // date-granular, so many entries share one date — within a date the most
  // recently created entry sorts first under desc), then id for determinism.
  // Fetch one extra to detect a following page.
  let entryQuery = base.select(["id"]).orderBy(input.orderBy, input.orderDirection);
  if (input.orderBy !== "createdAt") {
    entryQuery = entryQuery.orderBy("createdAt", input.orderDirection);
  }
  const entryRows = await entryQuery
    .orderBy("id", input.orderDirection)
    .limit(input.limit + 1)
    .offset(input.offset)
    .execute();

  const hasNextPage = entryRows.length > input.limit;
  const pageEntryIds = entryRows.slice(0, input.limit).map((r) => r.id);
  if (pageEntryIds.length === 0) {
    return { hasNextPage: false, entryCount: 0, total, rows: [] as JournalLineRow[] };
  }

  // Step 2 — all lines of the page's entries, joined for display fields.
  const rawRows = await db
    .selectFrom("JournalLine")
    .innerJoin("JournalEntry", "JournalEntry.id", "JournalLine.journalEntryId")
    .innerJoin("Account", "Account.id", "JournalLine.accountId")
    .where("JournalLine.journalEntryId", "in", pageEntryIds)
    .select([
      "JournalLine.id as lineId",
      "JournalLine.journalEntryId as journalEntryId",
      "JournalLine.accountId as accountId",
      "Account.code as accountCode",
      "Account.name as accountName",
      "JournalLine.debitAmount as debitAmount",
      "JournalLine.creditAmount as creditAmount",
      "JournalLine.description as lineDescription",
      "JournalEntry.entryDate as entryDate",
      "JournalEntry.status as status",
      "JournalEntry.accountingPeriodId as accountingPeriodId",
      "JournalEntry.description as entryDescription",
      "JournalEntry.sourceDocumentType as sourceDocumentType",
      "JournalEntry.sourceDocumentId as sourceDocumentId",
      "JournalEntry.postedAt as postedAt",
    ])
    // Within an entry, keep lines in creation order (SQL); the JS sort below is
    // stable, so it only re-groups by entry without disturbing this order.
    .orderBy("JournalLine.createdAt", "asc")
    .orderBy("JournalLine.id", "asc")
    .execute();

  // A SQL `IN` does not preserve the requested entry order, so re-order in JS to
  // the entry page order. `Array.prototype.sort` is stable, so each
  // journalEntryId's rows stay contiguous and in their SQL (creation) order.
  const entryOrder = new Map(pageEntryIds.map((id, index) => [id, index]));
  rawRows.sort(
    (a, b) => (entryOrder.get(a.journalEntryId) ?? 0) - (entryOrder.get(b.journalEntryId) ?? 0),
  );

  const rows: JournalLineRow[] = rawRows.map((r) => ({
    lineId: r.lineId,
    journalEntryId: r.journalEntryId,
    accountId: r.accountId,
    accountCode: r.accountCode,
    accountName: r.accountName,
    debitAmount: r.debitAmount ?? null,
    creditAmount: r.creditAmount ?? null,
    lineDescription: r.lineDescription ?? null,
    entryDate: toIso(r.entryDate) ?? "",
    status: r.status,
    accountingPeriodId: r.accountingPeriodId,
    entryDescription: r.entryDescription ?? null,
    sourceDocumentType: r.sourceDocumentType ?? null,
    sourceDocumentId: r.sourceDocumentId ?? null,
    postedAt: toIso(r.postedAt),
  }));

  return { hasNextPage, entryCount: pageEntryIds.length, total, rows };
}

// Convert a YYYY-MM-DD string to a Date; the `to` bound covers the whole day.
const parseDate = (value: string | null | undefined, endOfDay = false): Date | undefined => {
  if (!value) return undefined;
  return new Date(`${value}T${endOfDay ? "23:59:59.999" : "00:00:00.000"}Z`);
};

export default createResolver({
  name: "journalLinesGroupedByEntryId",
  operation: "query",
  input: {
    companyId: t.string().description("Company whose journal to list"),
    status: t
      .string({ optional: true })
      .description("Filter by entry status: DRAFT | CANCELLED | POSTED"),
    accountingPeriodId: t
      .string({ optional: true })
      .description("Restrict to one accounting period"),
    entryDateFrom: t
      .string({ optional: true })
      .description("Entry date lower bound (inclusive, YYYY-MM-DD)"),
    entryDateTo: t
      .string({ optional: true })
      .description("Entry date upper bound (inclusive, YYYY-MM-DD)"),
    accountId: t
      .string({ optional: true })
      .description("Only entries that have a line posted to this GL account"),
    sourceDocumentType: t
      .string({ optional: true })
      .description("Filter by the entry's source document type"),
    sourceDocumentId: t.string({ optional: true }).description("Filter by source document ID"),
    descriptionContains: t
      .string({ optional: true })
      .description(
        "Case-insensitive substring match on a journal line's description; returns whole entries that have a matching line",
      ),
    orderBy: t
      .string({ optional: true })
      .description("Entry sort field: entryDate | createdAt (default entryDate)"),
    orderDirection: t
      .string({ optional: true })
      .description("Sort direction: asc | desc (default desc)"),
    limit: t
      .int({ optional: true })
      .description(`Entries per page (default ${DEFAULT_PAGE_SIZE}, max ${MAX_PAGE_SIZE})`),
    offset: t.int({ optional: true }).description("Entries to skip (default 0)"),
  },
  body: async (context) => {
    const db = getDB("main-db");

    const status = context.input.status;
    if (status && !STATUSES.includes(status as JournalEntryStatus)) {
      throw new Error(`Invalid status: ${status}. Expected one of ${STATUSES.join(", ")}.`);
    }
    const sourceDocumentType = context.input.sourceDocumentType;
    if (
      sourceDocumentType &&
      !SOURCE_DOCUMENT_TYPES.includes(sourceDocumentType as SourceDocumentType)
    ) {
      throw new Error(
        `Invalid sourceDocumentType: ${sourceDocumentType}. Expected one of ${SOURCE_DOCUMENT_TYPES.join(", ")}.`,
      );
    }
    const orderBy = context.input.orderBy ?? "entryDate";
    if (orderBy !== "entryDate" && orderBy !== "createdAt") {
      throw new Error(`Invalid orderBy: ${orderBy}. Expected entryDate or createdAt.`);
    }
    const orderDirection = context.input.orderDirection ?? "desc";
    if (orderDirection !== "asc" && orderDirection !== "desc") {
      throw new Error(`Invalid orderDirection: ${orderDirection}. Expected asc or desc.`);
    }
    const limit = Math.min(context.input.limit ?? DEFAULT_PAGE_SIZE, MAX_PAGE_SIZE);

    return loadGroupedJournalLines(db, {
      companyId: context.input.companyId,
      status: status as JournalEntryStatus | undefined,
      accountingPeriodId: context.input.accountingPeriodId ?? undefined,
      entryDateFrom: parseDate(context.input.entryDateFrom),
      entryDateTo: parseDate(context.input.entryDateTo, true),
      accountId: context.input.accountId ?? undefined,
      sourceDocumentType: sourceDocumentType as SourceDocumentType | undefined,
      sourceDocumentId: context.input.sourceDocumentId ?? undefined,
      descriptionContains: context.input.descriptionContains ?? undefined,
      orderBy,
      orderDirection,
      limit: limit < 1 ? DEFAULT_PAGE_SIZE : limit,
      offset: Math.max(context.input.offset ?? 0, 0),
    });
  },
  output: t
    .object({
      hasNextPage: t.bool().description("True when more entries follow this page"),
      entryCount: t.int().description("Number of journal entries represented in this page"),
      total: t.int().description("Total matching journal entries across all pages"),
      rows: t
        .object(
          {
            lineId: t.string().description("Journal line ID"),
            journalEntryId: t.string().description("Owning journal entry ID (group key)"),
            accountId: t.string().description("GL account ID"),
            accountCode: t.string().description("GL account code"),
            accountName: t.string().description("GL account name"),
            debitAmount: t.string({ optional: true }).description("Debit amount (decimal string)"),
            creditAmount: t
              .string({ optional: true })
              .description("Credit amount (decimal string)"),
            lineDescription: t.string({ optional: true }).description("Line description"),
            entryDate: t.string().description("Entry date (ISO 8601)"),
            status: t.string().description("Entry status: DRAFT | CANCELLED | POSTED"),
            accountingPeriodId: t.string().description("Accounting period ID"),
            entryDescription: t.string({ optional: true }).description("Entry description"),
            sourceDocumentType: t
              .string({ optional: true })
              .description("Entry source document type"),
            sourceDocumentId: t.string({ optional: true }).description("Entry source document ID"),
            postedAt: t.string({ optional: true }).description("When the entry was posted (ISO)"),
          },
          { array: true },
        )
        .description("Flat journal-line rows, contiguous per journalEntryId (grouped by entry)"),
    })
    .description("Journal lines joined to their entry, paginated by entry"),
});