List Files | Just Commands | Repo | Logs

← back | Copy

export interface Change {
  collection: string;
  operation: string;
  data: Record<string, any>;
  id?: string; // for update/delete operations
}

interface Ref {
  collection: string;
  refIds: string[];
  targetField?: string; // Optional: specify field name to store resolved IDs (defaults to collection name)
}

import { SQLiteDBConnection } from "@capacitor-community/sqlite";
import { save as saveLog } from "@/framework/logs";

export interface ProcessingLogEntry {
  elapsed_ms: number;
  changeset_count: number;
  bytes: number;
  book_id: string | null;
}

export class ChangesetProcessor {
  private db: SQLiteDBConnection;
  private pendingLogs: ProcessingLogEntry[] = [];
  private allowedCollections = [
    "books",
    "menus",
    "offers",
    "menuTiers",
    "contentItems",
    "menuCopy",
    "warrantyCopy",
    "problems",
    "problemCategories",
    "problemTags",
    "checklists",
    "costsMaterial",
    "costsTime",
    "tierSets",
    "tiers",
    "formulas",
  ];

  constructor(db: SQLiteDBConnection) {
    this.db = db;
  }

  /**
   * Validates a changeset without committing any changes.
   * Returns validation result with any errors found.
   */
  async validateChangeset(
    changes: Change[],
    orgId: string,
    bookId?: string,
  ): Promise<{ valid: boolean; errors: string[] }> {
    const errors: string[] = [];

    for (const change of changes) {
      try {
        // Validate collection is allowed
        if (!this.allowedCollections.includes(change.collection)) {
          errors.push(`Collection "${change.collection}" is not allowed`);
          continue;
        }

        // Validate operation
        if (!["create", "update", "delete"].includes(change.operation)) {
          errors.push(`Invalid operation "${change.operation}"`);
          continue;
        }

        // For update/delete, verify record exists
        if (change.operation === "update" || change.operation === "delete") {
          if (!change.data.refId) {
            errors.push(`${change.operation} requires refId`);
            continue;
          }
          const findStmt = `SELECT id FROM ${change.collection} WHERE refId = '${this.escapeString(change.data.refId)}'`;
          const result = await this.db.query(findStmt);
          if (!result?.values?.length) {
            errors.push(`Record not found: ${change.collection} with refId "${change.data.refId}"`);
          }
        }

        // Validate refs can be resolved
        if (change.data.refs && Array.isArray(change.data.refs)) {
          for (const ref of change.data.refs as Ref[]) {
            for (const refId of ref.refIds || []) {
              const stmt = `SELECT id FROM ${ref.collection} WHERE refId = '${this.escapeString(refId)}'`;
              const result = await this.db.query(stmt);
              if (!result?.values?.length) {
                errors.push(`Reference not found: ${ref.collection} with refId "${refId}"`);
              }
            }
          }
        }
      } catch (e) {
        errors.push(`Validation error: ${e instanceof Error ? e.message : String(e)}`);
      }
    }

    return { valid: errors.length === 0, errors };
  }

  async processChangeset(
    changes: Change[],
    orgId: string,
    useTransaction: boolean = true,
    bookId?: string,
    createdDate?: string,
  ): Promise<void> {
    const startTime = performance.now();
    const changesJson = JSON.stringify(changes);
    const changesBytes = new TextEncoder().encode(changesJson).length;

    if (useTransaction) {
      // Pass transaction=false since we're managing our own transaction
      // (Capacitor SQLite defaults transaction=true, which auto-wraps each call)
      await this.db.execute("BEGIN TRANSACTION", false);
    }

    try {
      for (const change of changes) {
        await this.processChange(change, orgId, bookId);
      }

      // Save the changeset to pricebookChanges table
      await this.saveChangeset(changes, bookId, createdDate);

      if (useTransaction) {
        await this.db.execute("COMMIT", false);
      }

      const elapsedMs = performance.now() - startTime;

      // Accumulate log entry for later flushing
      this.pendingLogs.push({
        elapsed_ms: Math.round(elapsedMs),
        changeset_count: changes.length,
        bytes: changesBytes,
        book_id: bookId || null,
      });
    } catch (error) {
      if (useTransaction) {
        await this.db.execute("ROLLBACK", false);
      }
      // throw new Error(`Failed to process changeset: ${error}`);
      throw error;
    }
  }

  /**
   * Get all accumulated log entries without clearing them
   */
  getPendingLogs(): ProcessingLogEntry[] {
    return [...this.pendingLogs];
  }

  /**
   * Flush all accumulated log entries to the database
   * Call this after all processing is complete (e.g., after login/register flow)
   */
  async flushLogs(): Promise<void> {
    if (this.pendingLogs.length === 0) return;

    // Aggregate all pending logs into a single summary
    const totalElapsedMs = this.pendingLogs.reduce((sum, log) => sum + log.elapsed_ms, 0);
    const totalChangesets = this.pendingLogs.reduce((sum, log) => sum + log.changeset_count, 0);
    const totalBytes = this.pendingLogs.reduce((sum, log) => sum + log.bytes, 0);
    const batchCount = this.pendingLogs.length;

    try {
      await saveLog({
        log_type: 'process_changes',
        log_data: {
          elapsed_ms: totalElapsedMs,
          changeset_count: totalChangesets,
          bytes: totalBytes,
          batch_count: batchCount,
          entries: this.pendingLogs,
        },
        created_by: 'system',
        created_by_name: 'ChangesetProcessor',
      });
    } catch (logError) {
      console.error('[ChangesetProcessor] Failed to flush logs:', logError);
    }

    // Clear pending logs after flush
    this.pendingLogs = [];
  }

  private async saveChangeset(changes: Change[], bookId?: string, createdDate?: string): Promise<void> {
    const changesetJson = this.escapeString(JSON.stringify(changes));
    const bookValue = bookId ? `'${this.escapeString(bookId)}'` : 'NULL';

    // Use provided createdDate or let the database use CURRENT_TIMESTAMP default
    let stmt: string;
    if (createdDate) {
      const createdValue = `'${this.escapeString(createdDate)}'`;
      stmt = `INSERT INTO pricebookChanges (book, changeset, status, created) VALUES (${bookValue}, '${changesetJson}', 'success', ${createdValue})`;
    } else {
      stmt = `INSERT INTO pricebookChanges (book, changeset, status) VALUES (${bookValue}, '${changesetJson}', 'success')`;
    }

    try {
      await this.db.execute(stmt, false);
    } catch (error) {
      console.error('[ChangesetProcessor] Failed to save changeset to pricebookChanges:', error);
      // Don't throw - we don't want to fail the whole changeset just because logging failed
    }
  }

  private async processChange(change: Change, orgId: string, bookId?: string): Promise<void> {
    if (!this.allowedCollections.includes(change.collection)) {
      throw new Error(`Collection not allowed: ${change.collection}`);
    }

    switch (change.operation) {
      case "create":
        return this.createRecord(change.collection, change.data, orgId, bookId);
      case "update":
        return this.updateRecord(change.collection, change.data, orgId, bookId);
      case "delete":
        return this.deleteRecord(change.collection, change.data, orgId);
      default:
        throw new Error(`Unsupported operation: ${change.operation}`);
    }
  }

  private async createRecord(
    collection: string,
    data: Record<string, any>,
    orgId: string,
    bookId?: string,
  ): Promise<void> {
    // Generate a 15-character ID like PocketBase
    const id = this.generateId();
    const now = new Date().toISOString();

    // Process refs if they exist
    const processedData = { ...data };
    delete processedData.refs; // Remove refs from the data to be inserted

    // const globalCollections

    if (data.refs && Array.isArray(data.refs)) {
      for (const ref of data.refs as Ref[]) {
        const recordIds = await this.resolveRefIds(
          ref.collection,
          ref.refIds,
          orgId,
        );
        // Use targetField if specified, otherwise default to collection name
        const fieldName = ref.targetField || ref.collection;
        processedData[fieldName] = JSON.stringify(recordIds);
      }
    }

    // Add system fields
    processedData.id = id;
    processedData.org = orgId;
    if (bookId) {
      processedData.book = bookId;
    }
    processedData.created = now;
    processedData.updated = now;

    // Build insert statement with values embedded
    const fields = Object.keys(processedData);
    const values = fields
      .map((field) => this.escapeValue(processedData[field]))
      .join(", ");

    // Use INSERT OR IGNORE for contentItems to handle duplicates gracefully
    const insertType = collection === 'contentItems' ? 'INSERT OR IGNORE' : 'INSERT';
    const stmt = `${insertType} INTO ${collection} (${fields.join(", ")}) VALUES (${values})`;

    // console.log(stmt);

    try {
      // Pass transaction=false since we're inside a managed transaction
      await this.db.execute(stmt, false);
      // console.log(`Created record in ${collection}: ${id}`);
    } catch (error) {
      throw error;
      // throw new Error(`Failed to create record in ${collection}: ${error}`);
    }
  }

  private async updateRecord(
    collection: string,
    data: Record<string, any>,
    orgId: string,
    bookId?: string,
  ): Promise<void> {
    if (!data.refId) {
      return;
    }

    // First, find the existing record by refId only (org check removed for client app)
    const findStmt = `SELECT id FROM ${collection} WHERE refId = '${this.escapeString(data.refId)}'`;
    const result = await this.db.query(findStmt);

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

    const recordId = result.values[0].id; // First row, first column (id)
    const now = new Date().toISOString();

    // Process refs if they exist
    const processedData = { ...data };
    delete processedData.refs; // Remove refs from the data to be updated

    if (data.refs && Array.isArray(data.refs)) {
      for (const ref of data.refs as Ref[]) {
        const recordIds = await this.resolveRefIds(
          ref.collection,
          ref.refIds,
          orgId,
        );
        // Use targetField if specified, otherwise default to collection name
        const fieldName = ref.targetField || ref.collection;
        processedData[fieldName] = JSON.stringify(recordIds);
      }
    }

    // Add system fields
    processedData.org = orgId;
    if (bookId) {
      processedData.book = bookId;
    }
    processedData.updated = now;

    // Build update statement
    const fields = Object.keys(processedData).filter((key) => key !== "id");
    const setClause = fields
      .map((field) => `${field} = ${this.escapeValue(processedData[field])}`)
      .join(", ");

    const stmt = `UPDATE ${collection} SET ${setClause} WHERE id = '${this.escapeString(recordId)}'`;

    try {
      // Pass transaction=false since we're inside a managed transaction
      await this.db.execute(stmt, false);
    } catch (error) {
      throw error;
      // throw new Error(`Failed to update record in ${collection}: ${error}`);
    }
  }

  private async deleteRecord(collection: string, data: Record<string, any>, orgId: string): Promise<void> {
    if (!data.refId) {
      return;
    }

    // Find the record by refId only (org check removed for client app)
    const findStmt = `SELECT id FROM ${collection} WHERE refId = '${this.escapeString(data.refId)}'`;
    const result = await this.db.query(findStmt);

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

    const recordId = result.values[0].id;
    const stmt = `DELETE FROM ${collection} WHERE id = '${this.escapeString(recordId)}'`;

    try {
      // Pass transaction=false since we're inside a managed transaction
      await this.db.execute(stmt, false);
      // console.log(`Deleted record from ${collection}: refId=${data.refId}`);
    } catch (error) {
      throw error;
      // throw new Error(`Failed to delete record from ${collection}: ${error}`);
    }
  }

  private async resolveRefIds(
    collection: string,
    refIds: string[],
    orgId: string,
  ): Promise<string[]> {
    const recordIds: string[] = [];

    // Look up by refId only (org check removed for client app)
    for (const refId of refIds) {
      const stmt = `SELECT id FROM ${collection} WHERE refId = '${this.escapeString(refId)}'`;
      const result = await this.db.query(stmt);

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

      recordIds.push(result.values[0]["id"]); // First row, first column (id)
    }
    return recordIds;
  }

  private escapeValue(value: any): string {
    if (value === null || value === undefined) {
      return "NULL";
    }
    if (typeof value === "string") {
      return `'${this.escapeString(value)}'`;
    }
    if (typeof value === "number") {
      return value.toString();
    }
    if (typeof value === "boolean") {
      return value ? "1" : "0";
    }
    // For objects/arrays, stringify and escape
    return `'${this.escapeString(JSON.stringify(value))}'`;
  }

  private escapeString(str: string): string {
    // Escape single quotes by doubling them (SQLite standard)
    return str.replace(/'/g, "''");
  }

  private generateId(): string {
    // Generate a 15-character alphanumeric ID like PocketBase
    const chars = "abcdefghijklmnopqrstuvwxyz0123456789";
    let result = "";
    for (let i = 0; i < 15; i++) {
      result += chars.charAt(Math.floor(Math.random() * chars.length));
    }
    return result;
  }
}

// Usage example:
export async function applySpaceshipChanges(
  db: SQLiteDBConnection,
  orgId: string,
) {
  const processor = new ChangesetProcessor(db);

  // Import your spaceship data
  const spaceship = [
    {
      collection: "tierSets",
      operation: "create",
      data: {
        name: "Good/Better/Best",
        refId: "goodBetterBest",
      },
    },
    {
      collection: "tiers",
      operation: "create",
      data: {
        name: "Good",
        rank: 3,
        refId: "good",
        refs: [
          {
            collection: "tierSets",
            refIds: ["goodBetterBest"],
          },
        ],
      },
    },
    // ... rest of your spaceship data
  ];

  // Option 1: Let the processor handle the transaction
  await processor.processChangeset(spaceship, orgId);

  // Option 2: Handle transaction externally
  // Note: Pass transaction=false to execute() since we're managing our own transaction
  await db.execute("BEGIN TRANSACTION", false);
  try {
    await processor.processChangeset(spaceship, orgId, false);
    await db.execute("COMMIT", false);
  } catch (error) {
    await db.execute("ROLLBACK", false);
    throw error;
  }
}

export { ChangesetProcessor as default };