List Files | Just Commands | Repo | Logs

← back | Copy

import { getDb } from "@/dataAccess/getDb";
import { getApi } from "@/dataAccess/getApi";
import { convertFromPrefs } from "@/framework/currencies";
import { ChangesetProcessor } from "@/dataAccess/changeset-processor";

// ============================================================================
// Collection Schema Definitions
// ============================================================================

export interface FieldDef {
  name: string;
  type: "string" | "number" | "boolean" | "json" | "ref";
  required: boolean;
  description?: string;
  refCollection?: string;  // For ref types, which collection it references
}

export interface CollectionSchema {
  name: string;
  description: string;
  fields: FieldDef[];
}

/**
 * Schema definitions for all collections allowed in changeset processing.
 * These define the required and optional fields for create/update operations.
 *
 * System fields (id, org, book, created, updated) are added automatically.
 */
export const collectionSchemas: CollectionSchema[] = [
  {
    name: "books",
    description: "Pricebook definitions with parent-child relationships",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "parent", type: "ref", required: false, refCollection: "books", description: "Parent book for inheritance" },
    ],
  },
  {
    name: "menus",
    description: "Menu items within books",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "books", type: "ref", required: false, refCollection: "books", description: "Associated books" },
    ],
  },
  {
    name: "offers",
    description: "Pricing offers with time and material costs",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "costsTime", type: "ref", required: false, refCollection: "costsTime", description: "Time cost items" },
      { name: "costsMaterial", type: "ref", required: false, refCollection: "costsMaterial", description: "Material cost items" },
      { name: "techHandbook", type: "ref", required: false, refCollection: "contentItems", description: "Tech handbook content" },
      { name: "multiplier", type: "number", required: false, description: "Price multiplier" },
    ],
  },
  {
    name: "menuTiers",
    description: "Links menus to tiers and offers",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "menus", type: "ref", required: false, refCollection: "menus", description: "Associated menus" },
      { name: "tiers", type: "ref", required: false, refCollection: "tiers", description: "Associated tier" },
      { name: "offers", type: "ref", required: false, refCollection: "offers", description: "Associated offer" },
      { name: "menuCopy", type: "ref", required: false, refCollection: "menuCopy", description: "Menu copy content" },
      { name: "contentItems", type: "ref", required: false, refCollection: "contentItems", description: "Direct content items" },
    ],
  },
  {
    name: "contentItems",
    description: "Text content blocks (for menus, tech handbook, etc.)",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: false, description: "Display name" },
      { name: "content", type: "string", required: true, description: "Text content" },
    ],
  },
  {
    name: "menuCopy",
    description: "Menu descriptive text with content items",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: false, description: "Display name" },
      { name: "contentItems", type: "ref", required: false, refCollection: "contentItems", description: "Associated content items" },
    ],
  },
  {
    name: "warrantyCopy",
    description: "Warranty text content",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: false, description: "Display name" },
      { name: "contentItems", type: "ref", required: false, refCollection: "contentItems", description: "Associated content items" },
    ],
  },
  {
    name: "problems",
    description: "Service problems/issues",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "description", type: "string", required: false, description: "Problem description" },
      { name: "menus", type: "ref", required: false, refCollection: "menus", description: "Associated menus" },
      { name: "problemTags", type: "ref", required: false, refCollection: "problemTags", description: "Associated tags" },
      { name: "problemCategories", type: "ref", required: false, refCollection: "problemCategories", description: "Associated categories" },
    ],
  },
  {
    name: "problemCategories",
    description: "Hierarchical categories for problems",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "parent", type: "ref", required: false, refCollection: "problemCategories", description: "Parent category" },
    ],
  },
  {
    name: "problemTags",
    description: "Tags for filtering problems",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
    ],
  },
  {
    name: "checklists",
    description: "Service checklists",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
    ],
  },
  {
    name: "costsMaterial",
    description: "Material cost items (in base currency)",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: false, description: "Display name" },
      { name: "quantity", type: "number", required: true, description: "Cost amount in base currency" },
    ],
  },
  {
    name: "costsTime",
    description: "Time cost items (in hours)",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: false, description: "Display name" },
      { name: "hours", type: "number", required: true, description: "Number of hours" },
    ],
  },
  {
    name: "tierSets",
    description: "Groupings of tiers (e.g., Good/Better/Best)",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
    ],
  },
  {
    name: "tiers",
    description: "Service tier levels",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
      { name: "rank", type: "number", required: true, description: "Tier rank (lower = better)" },
      { name: "tierSets", type: "ref", required: false, refCollection: "tierSets", description: "Associated tier set" },
      { name: "warrantyCopy", type: "ref", required: false, refCollection: "warrantyCopy", description: "Warranty content" },
    ],
  },
  {
    name: "formulas",
    description: "Pricing calculation formulas",
    fields: [
      { name: "refId", type: "string", required: true, description: "Unique identifier within org" },
      { name: "name", type: "string", required: true, description: "Display name" },
    ],
  },
];

/**
 * Get schema for a specific collection
 */
export function getCollectionSchema(collectionName: string): CollectionSchema | undefined {
  return collectionSchemas.find(s => s.name === collectionName);
}

/**
 * Get list of all allowed collection names
 */
export function getAllowedCollections(): string[] {
  return collectionSchemas.map(s => s.name);
}

// ============================================================================
// Changes Data Types and Loading
// ============================================================================

export interface Change {
  id: number;
  book: string | null;
  changeset: any;
  status: string;
  errorMessage: string | null;
  created: string;
}

export interface ChangesData {
  changes: Change[];
  total: number;
}

export interface LoadChangesOptions {
  bookId?: string;
  limit?: number;
  offset?: number;
}

/**
 * Load pricebook changes from the local SQLite database
 * Changes are saved locally when processed via ChangesetProcessor
 */
export async function loadChanges(options: LoadChangesOptions = {}): Promise<ChangesData> {
  const db = await getDb();
  const { bookId, limit = 50, offset = 0 } = options;

  try {
    // Build query with optional book filter
    let whereClause = "";
    if (bookId) {
      whereClause = `WHERE book = '${bookId}'`;
    }

    // Get total count
    const countResult = await db.dbConn.query(
      `SELECT COUNT(*) as total FROM pricebookChanges ${whereClause}`
    );
    const total = countResult.values?.[0]?.total || 0;

    // Get paginated results
    const result = await db.dbConn.query(
      `SELECT * FROM pricebookChanges ${whereClause} ORDER BY created DESC LIMIT ${limit} OFFSET ${offset}`
    );

    const changes: Change[] = (result.values || []).map((row: any) => ({
      id: row.id,
      book: row.book || null,
      changeset: row.changeset ? JSON.parse(row.changeset) : null,
      status: row.status || "success",
      errorMessage: row.error_message || null,
      created: row.created,
    }));

    return {
      changes,
      total,
    };
  } catch (error) {
    console.error("[loadChanges] Error loading changes:", error);
    return { changes: [], total: 0 };
  }
}

/**
 * Get a single change by ID from local database
 */
export async function getChangeById(changeId: number): Promise<Change | null> {
  const db = await getDb();

  try {
    const result = await db.dbConn.query(
      `SELECT * FROM pricebookChanges WHERE id = ${changeId}`
    );

    if (!result.values || result.values.length === 0) {
      return null;
    }

    const row = result.values[0];
    return {
      id: row.id,
      book: row.book || null,
      changeset: row.changeset ? JSON.parse(row.changeset) : null,
      status: row.status || "success",
      errorMessage: row.error_message || null,
      created: row.created,
    };
  } catch (error) {
    console.error("[getChangeById] Error loading change:", error);
    return null;
  }
}

// ============================================================================
// Changeset Creation Helpers
// ============================================================================

export interface CreateCostsMaterialInput {
  refId: string;
  name?: string;
  quantity: number; // In user's preferred currency
}

export interface ChangesetItem {
  collection: string;
  operation: string;
  data: Record<string, any>;
}

/**
 * Create a costsMaterial changeset item with quantity converted from
 * user's preferred currency to base currency (XAG).
 *
 * @param input - The costsMaterial data with quantity in preferred currency
 * @returns Changeset item ready for processing, or null if conversion fails
 */
export async function createCostsMaterialChange(
  input: CreateCostsMaterialInput
): Promise<ChangesetItem | null> {
  const baseQuantity = await convertFromPrefs(input.quantity);

  if (baseQuantity === null) {
    console.error("[createCostsMaterialChange] Currency conversion failed - no prefs set");
    return null;
  }

  return {
    collection: "costsMaterial",
    operation: "create",
    data: {
      refId: input.refId,
      name: input.name,
      quantity: baseQuantity,
    },
  };
}

/**
 * Create multiple costsMaterial changeset items with quantities converted
 * from user's preferred currency to base currency.
 *
 * @param inputs - Array of costsMaterial data with quantities in preferred currency
 * @returns Array of changeset items ready for processing
 */
export async function createCostsMaterialChanges(
  inputs: CreateCostsMaterialInput[]
): Promise<ChangesetItem[]> {
  const results: ChangesetItem[] = [];

  for (const input of inputs) {
    const change = await createCostsMaterialChange(input);
    if (change) {
      results.push(change);
    }
  }

  return results;
}

export interface UpdateCostsMaterialInput {
  refId: string; // Required to identify the record to update
  name?: string;
  quantity?: number; // In user's preferred currency
}

/**
 * Create a costsMaterial update changeset item with quantity converted from
 * user's preferred currency to base currency (XAG).
 *
 * @param input - The costsMaterial data with quantity in preferred currency
 * @returns Changeset item ready for processing, or null if conversion fails
 */
export async function updateCostsMaterialChange(
  input: UpdateCostsMaterialInput
): Promise<ChangesetItem | null> {
  const data: Record<string, any> = {
    refId: input.refId,
  };

  if (input.name !== undefined) {
    data.name = input.name;
  }

  if (input.quantity !== undefined) {
    const baseQuantity = await convertFromPrefs(input.quantity);
    if (baseQuantity === null) {
      console.error("[updateCostsMaterialChange] Currency conversion failed - no prefs set");
      return null;
    }
    data.quantity = baseQuantity;
  }

  return {
    collection: "costsMaterial",
    operation: "update",
    data,
  };
}

/**
 * Create multiple costsMaterial update changeset items with quantities converted
 * from user's preferred currency to base currency.
 *
 * @param inputs - Array of costsMaterial data with quantities in preferred currency
 * @returns Array of changeset items ready for processing
 */
export async function updateCostsMaterialChanges(
  inputs: UpdateCostsMaterialInput[]
): Promise<ChangesetItem[]> {
  const results: ChangesetItem[] = [];

  for (const input of inputs) {
    const change = await updateCostsMaterialChange(input);
    if (change) {
      results.push(change);
    }
  }

  return results;
}

// ============================================================================
// Load Books for Selection
// ============================================================================

export interface Book {
  id: string;
  refId?: string;
  name?: string;
}

/**
 * Load all books from the local SQLite database
 */
export async function loadBooks(): Promise<Book[]> {
  const db = await getDb();
  return (await db.books.getAll()) || [];
}

// ============================================================================
// Download Changes from Pocketbase API
// ============================================================================

export interface DownloadChangesResult {
  success: boolean;
  downloaded: number;
  processed: number;
  errors: string[];
}

/**
 * Download changes from Pocketbase API that are newer than local records,
 * and process them through the ChangesetProcessor.
 *
 * Fetches changes for all books belonging to the user's org, plus their parent books.
 */
export async function downloadChanges(): Promise<DownloadChangesResult> {
  const errors: string[] = [];
  let downloaded = 0;
  let processed = 0;

  try {
    const db = await getDb();
    const { pb } = await getApi();

    // Get the user's active org
    const user = pb.authStore.model;
    const orgId = user?.activeOrg;
    if (!orgId) {
      return {
        success: false,
        downloaded: 0,
        processed: 0,
        errors: ["No active organization found"],
      };
    }

    console.log("[downloadChanges] Starting download for org:", orgId);

    // Get all books for this org
    const orgBooks = await pb.collection("books").getFullList({
      filter: `org = '${orgId}'`,
      expand: "parent",
    });

    console.log("[downloadChanges] Found", orgBooks.length, "books for org");

    // Collect all book IDs we need to fetch changes for (org books + their parents)
    const bookIdsToSync = new Set<string>();
    for (const book of orgBooks) {
      bookIdsToSync.add(book.id);
      if (book.parent) {
        bookIdsToSync.add(book.parent);
      }
    }

    console.log("[downloadChanges] Syncing changes for", bookIdsToSync.size, "books (including parents)");

    // Get the latest created timestamp from local pricebookChanges
    const latestResult = await db.dbConn.query(
      "SELECT MAX(created) as latest FROM pricebookChanges"
    );
    const latestCreated = latestResult.values?.[0]?.latest || null;

    console.log("[downloadChanges] Last local changeset:", latestCreated || "none");

    // Download changesets for all books
    let allChanges: any[] = [];

    for (const bookId of bookIdsToSync) {
      try {
        let filter = `book = '${bookId}'`;
        if (latestCreated) {
          filter += ` && created > "${latestCreated}"`;
        }

        console.log(`[downloadChanges] Fetching for book ${bookId} with filter:`, filter);

        const bookChanges = await pb.collection("pricebookChanges").getFullList({
          filter,
          sort: "created",
        });

        console.log(`[downloadChanges] Book ${bookId}: downloaded ${bookChanges.length} changes`);
        allChanges = allChanges.concat(bookChanges);
      } catch (e) {
        console.error(`[downloadChanges] Failed to fetch changes for book ${bookId}:`, e);
        errors.push(`Failed to fetch changes for book ${bookId}: ${e}`);
      }
    }

    // Sort all changes by created date (oldest first)
    allChanges.sort(
      (a, b) =>
        Number(new Date(a.created as string)) -
        Number(new Date(b.created as string))
    );

    downloaded = allChanges.length;
    console.log("[downloadChanges] Total changes to apply:", downloaded);

    if (allChanges.length === 0) {
      return {
        success: true,
        downloaded: 0,
        processed: 0,
        errors,
      };
    }

    // Process each changeset through the ChangesetProcessor
    const processor = new ChangesetProcessor(db.dbConn);

    for (const record of allChanges) {
      try {
        const changeset = record.changeset;
        if (!changeset || !Array.isArray(changeset)) {
          console.warn(`[downloadChanges] Skipping record ${record.id}: invalid changeset`);
          continue;
        }

        // Process the changeset (this also saves it to local pricebookChanges)
        await processor.processChangeset(
          changeset,
          "clientApp",
          true, // use transaction
          record.book || undefined
        );
        processed++;
      } catch (error) {
        const errorMsg = `Failed to process record ${record.id}: ${error instanceof Error ? error.message : String(error)}`;
        console.error(`[downloadChanges] ${errorMsg}`);
        errors.push(errorMsg);
      }
    }

    // Persist to disk
    await db.saveDb();

    console.log(`[downloadChanges] Complete: ${processed} succeeded, ${errors.length} failed`);

    return {
      success: errors.length === 0,
      downloaded,
      processed,
      errors,
    };
  } catch (error) {
    console.error("[downloadChanges] Error:", error);
    return {
      success: false,
      downloaded,
      processed,
      errors: [error instanceof Error ? error.message : String(error)],
    };
  }
}

// ============================================================================
// Commit Changes to Pocketbase API
// ============================================================================

export interface CommitChangeOptions {
  bookId: string;
  orgId?: string; // Optional - will use activeOrg from session if not provided
}

export interface CommitChangeResult {
  success: boolean;
  recordId?: string;
  error?: string;
}

/**
 * Commit a single changeset item to the Pocketbase API.
 * Creates a record in pricebookChanges which triggers server-side processing.
 *
 * @param change - The changeset item to commit
 * @param options - Options including bookId and optional orgId
 * @returns Result with success status and record ID or error
 */
export async function commitChange(
  change: ChangesetItem,
  options: CommitChangeOptions
): Promise<CommitChangeResult> {
  return commitChanges([change], options);
}

/**
 * Commit multiple changeset items.
 * First processes locally via ChangesetProcessor, then saves to Pocketbase API.
 *
 * @param changes - Array of changeset items to commit
 * @param options - Options including bookId and optional orgId
 * @returns Result with success status and record ID or error
 */
export async function commitChanges(
  changes: ChangesetItem[],
  options: CommitChangeOptions
): Promise<CommitChangeResult> {
  try {
    const db = await getDb();
    const { pb } = await getApi();

    // Get org ID from options or from the authenticated user's activeOrg
    let orgId = options.orgId;
    if (!orgId) {
      const user = pb.authStore.model;
      orgId = user?.activeOrg;
      if (!orgId) {
        return {
          success: false,
          error: "No organization ID provided and no active organization found",
        };
      }
    }

    // First, process locally via ChangesetProcessor
    console.log("[commitChanges] Processing changeset locally:", JSON.stringify(changes, null, 2));
    console.log("[commitChanges] orgId:", orgId, "bookId:", options.bookId);
    const processor = new ChangesetProcessor(db.dbConn);
    try {
      await processor.processChangeset(changes, orgId, true, options.bookId);
      console.log("[commitChanges] Local processing succeeded");
      await db.saveDb();
      console.log("[commitChanges] Database saved");
    } catch (processorError) {
      console.error("[commitChanges] Local processing failed:", processorError);
      return {
        success: false,
        error: `Local processing failed: ${processorError instanceof Error ? processorError.message : String(processorError)}`,
      };
    }

    // If local processing succeeded, save to Pocketbase API
    const record = await pb.collection("pricebookChanges").create({
      book: options.bookId,
      org: orgId,
      changeset: changes,
    });

    return {
      success: true,
      recordId: record.id,
    };
  } catch (error) {
    console.error("[commitChanges] Error:", error);
    return {
      success: false,
      error: error instanceof Error ? error.message : String(error),
    };
  }
}