List Files | Just Commands | Repo | Logs

← back | Copy

import type { ProblemsRecord, ProblemTagsRecord, ProblemCategoriesRecord } from "@/pocketbase-types";
import { getDb } from "@/dataAccess/getDb";

export interface DirectoryQuery {
  text: string | null;
  category: string | null;  // category ID
  tags: string[];           // tag IDs (AND logic)
}

export interface ProblemWithTagNames extends Omit<ProblemsRecord, "problemTags"> {
  problemTags: string[];  // tag names instead of IDs
}

export interface CategoryWithProblems extends ProblemCategoriesRecord {
  problems: ProblemWithTagNames[];
}

export interface TagWithGain {
  id: string;
  name: string;
  count: number;
  gain: number;
}

export interface DirectoryData {
  problems: ProblemsRecord[];
  tags: ProblemTagsRecord[];
  categories: ProblemCategoriesRecord[];
  searchResults: ProblemsRecord[];
  categoryView: CategoryWithProblems[];
  availableTags: TagWithGain[];  // Tags sorted by information gain
}

export const sampleCategories: Omit<ProblemCategoriesRecord, "id" | "created" | "updated">[] = [
  { name: "Drain Cleaning", refId: "cat_drain_cleaning", parent: undefined, org: undefined },
  { name: "Pressure Control Systems", refId: "cat_pressure_control_systems", parent: undefined, org: undefined },
  { name: "Toilet Repair and or Replace", refId: "cat_toilet_repair_replace", parent: undefined, org: undefined },
  { name: "Pumps", refId: "cat_pumps", parent: undefined, org: undefined },
  { name: "Water Heaters", refId: "cat_water_heaters", parent: undefined, org: undefined },
  { name: "Bathroom Fixtures", refId: "cat_bathroom_fixtures", parent: undefined, org: undefined },
  { name: "Kitchen Fixtures", refId: "cat_kitchen_fixtures", parent: undefined, org: undefined },
  { name: "Accessories", refId: "cat_accessories", parent: undefined, org: undefined },
  { name: "Water Leak Search and Repairs", refId: "cat_water_leak_search_repairs", parent: undefined, org: undefined },
  { name: "Filtration", refId: "cat_filtration", parent: undefined, org: undefined },
  { name: "Gas Leak Search and Repairs", refId: "cat_gas_leak_search_repairs", parent: undefined, org: undefined },
  { name: "Drain Leak Search and Repair", refId: "cat_drain_leak_search_repair", parent: undefined, org: undefined },
  { name: "Generic: Based on Material", refId: "cat_generic_based_on_material", parent: undefined, org: undefined },
  { name: "Water Conditioning and Softening", refId: "cat_water_conditioning_softening", parent: undefined, org: undefined },
  { name: "Well Pumps", refId: "cat_well_pumps", parent: undefined, org: undefined },
  { name: "Frozen Water Lines", refId: "cat_frozen_water_lines", parent: undefined, org: undefined },
  { name: "R.P.Z. Backflow", refId: "cat_rpz_backflow", parent: undefined, org: undefined },
  { name: "Jetting and Main Line Replacements", refId: "cat_jetting_main_line_replacements", parent: undefined, org: undefined },
];

export function searchProblems(
  problems: ProblemsRecord[],
  query: string,
  tags: ProblemTagsRecord[]
): ProblemsRecord[] {
  if (!query.trim()) {
    return problems;
  }

  // Build tag ID to name map
  const tagIdToName = new Map<string, string>();
  for (const tag of tags) {
    tagIdToName.set(tag.id, tag.name?.toLowerCase() || '');
  }

  const searchTerms = query
    .toLowerCase()
    .split(/\s+/)
    .filter((term) => term.trim() !== "");

  return problems.filter((problem) => {
    const problemName = problem.name?.toLowerCase() || "";
    const problemDescription = problem.description?.toLowerCase() || "";

    // Get tag names for this problem
    const problemTagIds = getProblemTagIds(problem);
    const tagNames = problemTagIds
      .map((id) => tagIdToName.get(id) || '')
      .join(' ');

    const searchText = `${problemName} ${problemDescription} ${tagNames}`;

    // All search terms must be found in the combined text (AND logic)
    return searchTerms.every((term) => searchText.includes(term));
  });
}

// Get all descendants of a category (including itself), or all categories if null
function getDescendants(
  categoryId: string | null,
  categories: ProblemCategoriesRecord[]
): Set<string> {
  if (categoryId === null) {
    return new Set(categories.map((c) => c.id));
  }

  const descendants = new Set<string>();
  descendants.add(categoryId);

  // Build parent->children map
  const childrenMap = new Map<string, string[]>();
  for (const cat of categories) {
    if (cat.parent) {
      const children = childrenMap.get(cat.parent) || [];
      children.push(cat.id);
      childrenMap.set(cat.parent, children);
    }
  }

  // BFS to find all descendants
  const queue = [categoryId];
  while (queue.length > 0) {
    const current = queue.shift()!;
    const children = childrenMap.get(current) || [];
    for (const child of children) {
      if (!descendants.has(child)) {
        descendants.add(child);
        queue.push(child);
      }
    }
  }

  return descendants;
}

// Filter problems by tags (AND logic - must have ALL specified tags)
function filterByTags(
  problems: ProblemsRecord[],
  tagIds: string[]
): ProblemsRecord[] {
  if (tagIds.length === 0) {
    return problems;
  }

  return problems.filter((problem) => {
    let problemTagIds: string[] = [];
    if (problem.problemTags) {
      try {
        problemTagIds =
          typeof problem.problemTags === "string"
            ? JSON.parse(problem.problemTags)
            : problem.problemTags;
      } catch {
        problemTagIds = [];
      }
    }

    // Check if problem has ALL required tags
    return tagIds.every((tagId) => problemTagIds.includes(tagId));
  });
}

// Get category IDs for a problem
function getProblemCategoryIds(problem: ProblemsRecord): string[] {
  if (!problem.problemCategories) return [];
  try {
    return typeof problem.problemCategories === "string"
      ? JSON.parse(problem.problemCategories)
      : problem.problemCategories;
  } catch {
    return [];
  }
}

// Get tag IDs for a problem
function getProblemTagIds(problem: ProblemsRecord): string[] {
  if (!problem.problemTags) return [];
  try {
    return typeof problem.problemTags === "string"
      ? JSON.parse(problem.problemTags)
      : problem.problemTags;
  } catch {
    return [];
  }
}

// Convert a problem to have tag names instead of tag IDs
function problemWithTagNames(
  problem: ProblemsRecord,
  tagIdToName: Map<string, string>
): ProblemWithTagNames {
  const tagIds = getProblemTagIds(problem);
  const tagNames = tagIds.map((id) => tagIdToName.get(id) || id);
  return {
    ...problem,
    problemTags: tagNames,
  };
}

// Calculate information gain for tags based on a set of problems
function calculateTagsWithGain(
  problems: ProblemsRecord[],
  allTags: ProblemTagsRecord[],
  selectedTagIds: string[]
): TagWithGain[] {
  if (problems.length === 0) return [];

  const totalProblems = problems.length;
  const currentEntropy = totalProblems > 1 ? Math.log2(totalProblems) : 0;

  const tagsWithGain: TagWithGain[] = [];

  for (const tag of allTags) {
    // Skip already selected tags
    if (selectedTagIds.includes(tag.id)) continue;

    // Count problems with this tag
    const countWith = problems.filter((problem) => {
      const tagIds = getProblemTagIds(problem);
      return tagIds.includes(tag.id);
    }).length;

    // Skip tags that don't appear in any problem
    if (countWith === 0) continue;

    const countWithout = totalProblems - countWith;

    // Calculate entropy after split
    let entropyAfterSplit = 0;

    if (countWith > 0) {
      const probWith = countWith / totalProblems;
      const entropyWith = countWith > 1 ? Math.log2(countWith) : 0;
      entropyAfterSplit += probWith * entropyWith;
    }

    if (countWithout > 0) {
      const probWithout = countWithout / totalProblems;
      const entropyWithout = countWithout > 1 ? Math.log2(countWithout) : 0;
      entropyAfterSplit += probWithout * entropyWithout;
    }

    const informationGain = currentEntropy - entropyAfterSplit;

    tagsWithGain.push({
      id: tag.id,
      name: tag.name || '',
      count: countWith,
      gain: informationGain,
    });
  }

  // Sort by information gain (descending)
  return tagsWithGain.sort((a, b) => b.gain - a.gain);
}

export async function loadDirectory(
  query: DirectoryQuery = { text: null, category: null, tags: [] }
): Promise<DirectoryData> {
  const db = await getDb();

  const problems = (await db.selectAll("problems")) || [];
  const tags = (await db.selectAll("problemTags")) || [];
  const categories = (await db.selectAll("problemCategories")) || [];

  // Build tag ID to name map for categoryView
  const tagIdToName = new Map<string, string>();
  for (const tag of tags) {
    tagIdToName.set(tag.id, tag.name || tag.id);
  }

  let searchResults: ProblemsRecord[];
  let categoryView: CategoryWithProblems[];
  let visibleProblems: ProblemsRecord[];

  if (query.text !== null && query.text.trim() !== "") {
    // Case 1: Text search active
    // searchResults = text-matched problems
    // categoryView = empty (no categories since all would have no problems)
    searchResults = searchProblems(problems, query.text, tags);
    categoryView = [];
    visibleProblems = searchResults;
  } else {
    // Case 2: Category/tag refinement
    // searchResults = empty
    // categoryView = visible categories get their problems
    searchResults = [];

    // Get visible categories (descendants of selected, or all)
    const visibleCats = getDescendants(query.category, categories);

    // Filter problems: must be in a visible category AND have all required tags
    const categoryFiltered = problems.filter((problem: ProblemsRecord) => {
      const problemCatIds = getProblemCategoryIds(problem);
      return problemCatIds.some((catId) => visibleCats.has(catId));
    });

    // Apply tag filter (AND logic)
    visibleProblems = filterByTags(categoryFiltered, query.tags);

    // Build categoryView: only include visible categories with problems
    // Convert tag IDs to tag names in problems for categoryView
    categoryView = categories
      .filter((category: ProblemCategoriesRecord) => visibleCats.has(category.id))
      .map((category: ProblemCategoriesRecord) => ({
        ...category,
        problems: visibleProblems
          .filter((p) => getProblemCategoryIds(p).includes(category.id))
          .map((p) => problemWithTagNames(p, tagIdToName)),
      }))
      .filter((category: CategoryWithProblems) => category.problems.length > 0);
  }

  // Calculate available tags with information gain based on visible problems
  const availableTags = calculateTagsWithGain(visibleProblems, tags, query.tags);

  return {
    problems,
    tags,
    categories,
    searchResults,
    categoryView,
    availableTags,
  };
}

export async function seedCategories(): Promise<void> {
  const db = await getDb();
  const now = new Date().toISOString();

  for (const cat of sampleCategories) {
    await db.dbConn.run(
      `INSERT OR IGNORE INTO problemCategories (id, refId, name, parent, org, book, created, updated)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [cat.refId, cat.refId, cat.name, cat.parent || null, cat.org || null, null, now, now]
    );
  }
}