/**
 * @factory/database-engine/query — grouping (TASK-0068 R2).
 *
 * `groupRecords` → `GroupMeta[]` for board/calendar/timeline/group-by. Group
 * key per value: select/status → option (label from config); date → bucket by
 * `config.groupGranularity` (day/week/month, default day); multi_select →
 * one group per option (record appears in each matched); person → per user;
 * number/checkbox/missing → "__empty" (label "(Empty)") always last. Groups
 * ordered by the property's semantics (select by option order, date
 * chronological, text locale, number numeric) with per-level `direction`.
 * Up to 3 nesting levels.
 */

import type { GroupSpec } from "@factory/shared/dto";

import type { RecordRow, PropertyInfo, PropertyValue } from "../values/types.js";
import { dateBucket } from "../values/dates.js";

export const MAX_GROUP_LEVELS = 3;
export const EMPTY_KEY = "__empty";
export const EMPTY_LABEL = "(Empty)";

export interface GroupMeta {
  key: string;
  label: string;
  count: number;
  records: RecordRow[];
  children?: GroupMeta[];
}

function optionName(config: Record<string, unknown>, id: string): string {
  const opts = Array.isArray(config["options"])
    ? (config["options"] as Array<Record<string, unknown>>)
    : [];
  const hit = opts.find((o) => o["id"] === id);
  return typeof hit?.["name"] === "string" ? hit["name"] : id;
}

function granularity(config: Record<string, unknown>): "day" | "week" | "month" {
  const g = config["groupGranularity"];
  return g === "week" || g === "month" ? g : "day";
}

interface Bucket {
  key: string;
  label: string;
  order: number;
  records: RecordRow[];
}

function groupLevel(rows: RecordRow[], property: PropertyInfo): GroupMeta[] {
  const cfg = property.config;
  const buckets = new Map<string, Bucket>();
  const empty: RecordRow[] = [];

  const put = (key: string, label: string, order: number, row: RecordRow) => {
    let b = buckets.get(key);
    if (!b) {
      b = { key, label, order, records: [] };
      buckets.set(key, b);
    }
    b.records.push(row);
  };

  for (const row of rows) {
    const v = row.values[property.id];
    switch (property.type) {
      case "select":
      case "status": {
        if (typeof v === "string" && v.length > 0) {
          const idx = Array.isArray(cfg["options"])
            ? (cfg["options"] as Array<Record<string, unknown>>).findIndex((o) => o["id"] === v)
            : -1;
          put(v, optionName(cfg, v), idx < 0 ? Number.MAX_SAFE_INTEGER : idx, row);
        } else empty.push(row);
        break;
      }
      case "multi_select": {
        if (Array.isArray(v) && v.length > 0) {
          v.forEach((x, i) => {
            if (typeof x === "string") put(x, optionName(cfg, x), i, row); // appears in each matched column
          });
        } else empty.push(row);
        break;
      }
      case "date": {
        if (typeof v === "string" && /^\d{4}-\d{2}-\d{2}/.test(v)) {
          const bucket = dateBucket(v, granularity(cfg));
          put(bucket, bucket, v.charCodeAt(0) /* chronological via string sort later */, row);
        } else empty.push(row);
        break;
      }
      case "date_range": {
        if (v && typeof v === "object" && typeof (v as { start?: unknown }).start === "string") {
          const start = (v as { start: string }).start;
          const bucket = dateBucket(start, granularity(cfg));
          put(bucket, bucket, start.charCodeAt(0), row);
        } else empty.push(row);
        break;
      }
      case "person": {
        if (Array.isArray(v) && v.length > 0) {
          v.forEach((x, i) => {
            if (typeof x === "string") put(x, x, i, row);
          });
        } else empty.push(row);
        break;
      }
      case "checkbox": {
        put(
          v === true ? "checked" : "unchecked",
          v === true ? "Checked" : "Unchecked",
          v === true ? 0 : 1,
          row,
        );
        break;
      }
      default: {
        // number / text / title / email / url / others: group by raw value
        if (
          v === undefined ||
          v === null ||
          (typeof v === "string" && v.length === 0) ||
          (Array.isArray(v) && v.length === 0)
        ) {
          empty.push(row);
        } else if (typeof v === "number") {
          put(`n:${v}`, String(v), v, row);
        } else if (typeof v === "string") {
          put(`s:${v}`, v, Number.MAX_SAFE_INTEGER, row);
        } else {
          empty.push(row);
        }
        break;
      }
    }
  }

  const metas: GroupMeta[] = [...buckets.values()].map((b) => ({
    key: b.key,
    label: b.label,
    count: b.records.length,
    records: b.records,
  }));

  // order by property semantics
  metas.sort((a, b) => {
    let r = 0;
    if (property.type === "date" || property.type === "date_range")
      r = a.key.localeCompare(b.key); // chronological
    else if (property.type === "number") r = Number(a.key.slice(2)) - Number(b.key.slice(2));
    else if (property.type === "select" || property.type === "status") {
      // already keyed by option index via order; re-derive
      const idx = (k: string) => {
        const opts = Array.isArray(cfg["options"])
          ? (cfg["options"] as Array<Record<string, unknown>>)
          : [];
        return opts.findIndex((o) => o["id"] === k) < 0
          ? Number.MAX_SAFE_INTEGER
          : opts.findIndex((o) => o["id"] === k);
      };
      r = idx(a.key) - idx(b.key);
    } else if (a.key.startsWith("s:") || b.key.startsWith("s:")) r = a.key.localeCompare(b.key);
    else r = a.key.localeCompare(b.key);
    return r;
  });

  // empty group always last
  if (empty.length > 0)
    metas.push({ key: EMPTY_KEY, label: EMPTY_LABEL, count: empty.length, records: empty });
  return metas;
}

/**
 * Group rows by up to 3 property levels. `groups` is the ordered spec list;
 * nested children use the same semantics per level. `direction` per level.
 */
export function groupRecords(
  rows: RecordRow[],
  groups: readonly GroupSpec[],
  properties: Map<string, PropertyInfo>,
): GroupMeta[] {
  if (groups.length === 0) return [];
  const level0 = groups[0]!;
  const property = properties.get(level0.propertyId);
  if (!property) return [];
  let metas = groupLevel(rows, property);
  // apply per-level direction
  if (level0.direction === "desc") {
    const empty = metas.filter((m) => m.key === EMPTY_KEY);
    const rest = metas.filter((m) => m.key !== EMPTY_KEY).reverse();
    metas = [...rest, ...empty];
  }
  // nest further levels
  if (groups.length > 1) {
    for (const m of metas) {
      m.children = groupRecords(
        m.records,
        groups.slice(1, Math.min(groups.length, MAX_GROUP_LEVELS + 1)),
        properties,
      );
    }
  }
  return metas;
}