Hướng dẫn đồng bộ Google Sheets

class ApiSheet {
  constructor(sheetId, listModel, apiConfig = null) {
    this.sheetId = sheetId;
    this.listModel = listModel || {}; // Now optional - can be empty
    this.cache = CacheService.getScriptCache();
    this.cacheExpiration = 21600;
    this.apiConfig = apiConfig;
  }

  /**
   * Helper to parse JSON safely
   */
  parse(str) {
    try {
      if (typeof str === "object" && str !== null) return str;
      if (typeof str === "string") return JSON.parse(str);
      return null;
    } catch (e) {
      return null;
    }
  }

  /**
   * Helper to stringify JSON
   */
  stringify(data) {
    return typeof data === "string" ? data : JSON.stringify(data);
  }

  /**
   * Standard JSON Response
   */
  sendContent(payload) {
    return ContentService.createTextOutput(this.stringify(payload)).setMimeType(
      ContentService.MimeType.JSON,
    );
  }

  /**
   * Get Model Instance - Now supports dynamic tables
   */
  getModel(tableName) {
    const db = DBV2.Connection(this.sheetId);
    
    // First check hardcoded listModel
    let columns = this.listModel[tableName];

    // If not in listModel, check _schema sheet
    if (!columns) {
      columns = db.getSchema(tableName);
    }

    if (!columns) {
      throw new Error(`Table '${tableName}' not found. Use createTable first.`);
    }

    return db.createTable(tableName, columns);
  }

  /**
   * Caching Strategy: "Table Versioning"
   */
  getTableVersion(tableName) {
    const key = `v_${tableName}`;
    let v = this.cache.get(key);
    if (!v) {
      v = "1";
      this.cache.put(key, v, 21600);
    }
    return v;
  }

  incrementTableVersion(tableName) {
    const key = `v_${tableName}`;
    let v = Number(this.getTableVersion(tableName));
    v++;
    this.cache.put(key, v.toString(), 21600);
  }

  getCacheKey(tableName, method, options) {
    const v = this.getTableVersion(tableName);
    const optStr = options
      ? JSON.stringify(options, Object.keys(options).sort())
      : "null";
    return `${tableName}_v${v}_${method}_${optStr}`;
  }

  /**
   * Service Dispatcher - Now includes dynamic table methods
   */
  service(tableName, method, options) {
    // Special methods that don't require table
    if (method === "createTable") {
      return this.handleCreateTable(tableName, options);
    }
    if (method === "listTables") {
      return this.handleListTables();
    }
    if (method === "getTableSchema") {
      return this.handleGetTableSchema(tableName);
    }
    if (method === "dropTable") {
      return this.handleDropTable(tableName);
    }

    const model = this.getModel(tableName);

    const handlers = {
      findOne: () => this.handleRead(tableName, "findOne", model, options),
      findByPk: () => {
        return this.handleRead(tableName, "findByPk", model, options?.id);
      },
      findAll: () => this.handleRead(tableName, "findAll", model, options),
      findAndCountAll: () =>
        this.handleRead(tableName, "findAndCountAll", model, options),

      create: () => this.handleWrite(tableName, "create", model, options),
      updateById: () => this.handleUpdate(tableName, model, options),
      deleteById: () => this.handleDelete(tableName, model, options),
    };

    if (handlers[method]) {
      return handlers[method]();
    } else {
      throw new Error(`Method '${method}' not supported.`);
    }
  }

  // --- New Dynamic Table Handlers ---

  handleCreateTable(tableName, options) {
    if (!tableName)
      throw new Error("Missing 'table' parameter for createTable");

    const columns = options?.columns || options;
    if (!columns || !Array.isArray(columns) || columns.length === 0) {
      throw new Error("Missing or invalid 'columns' array for createTable");
    }

    const db = DBV2.Connection(this.sheetId);

    // Check if table already exists
    const existingSchema = db.getSchema(tableName);
    if (existingSchema) {
      throw new Error(`Table '${tableName}' already exists`);
    }

    // Create the table
    const table = db.createTable(tableName, columns);

    return {
      table: tableName,
      columns: columns,
      message: `Table '${tableName}' created successfully`,
    };
  }

  handleListTables() {
    const db = DBV2.Connection(this.sheetId);
    return db.listAllTables();
  }

  handleGetTableSchema(tableName) {
    if (!tableName) throw new Error("Missing 'table' parameter");

    const db = DBV2.Connection(this.sheetId);
    const schema = db.getSchema(tableName);

    if (!schema) {
      throw new Error(`Table '${tableName}' not found`);
    }

    return {
      table: tableName,
      columns: schema,
    };
  }

  handleDropTable(tableName) {
    if (!tableName) throw new Error("Missing 'table' parameter for dropTable");

    const ss = SpreadsheetApp.openById(this.sheetId);
    const sheet = ss.getSheetByName(tableName);

    if (!sheet) {
      throw new Error(`Table '${tableName}' not found`);
    }

    // Delete the sheet
    ss.deleteSheet(sheet);

    // Remove from _schema
    const schemaSheet = ss.getSheetByName("_schema");
    if (schemaSheet) {
      const data = schemaSheet.getDataRange().getValues();
      for (let i = 1; i < data.length; i++) {
        if (data[i][0] === tableName) {
          schemaSheet.deleteRow(i + 1);
          break;
        }
      }
    }

    this.incrementTableVersion(tableName);

    return {
      table: tableName,
      message: `Table '${tableName}' dropped successfully`,
    };
  }

  // --- Existing Handlers ---

  handleRead(tableName, method, model, args) {
    const cacheKey = this.getCacheKey(tableName, method, args);
    const cached = this.cache.get(cacheKey);
    if (cached) {
      return JSON.parse(cached);
    }

    const resultObj = model[method](args);
    const data = resultObj.result;

    try {
      this.cache.put(cacheKey, JSON.stringify(data), this.cacheExpiration);
    } catch (e) {
      console.warn("Cache put failed (likely too large):", e);
    }

    return data;
  }

  handleWrite(tableName, method, model, data) {
    const res = model[method](data);
    this.incrementTableVersion(tableName);
    return res.result;
  }

  handleUpdate(tableName, model, options) {
    const id = options?.id;
    if (!id) throw new Error("Missing 'id' for updateById");

    const record = model.findByPk(id);
    if (!record.result) throw new Error("Record not found");

    Object.keys(options).forEach((k) => {
      if (k !== "id") record.result[k] = options[k];
    });

    record.save();
    this.incrementTableVersion(tableName);
    return record.result;
  }

  handleDelete(tableName, model, options) {
    const id = options?.id;
    if (!id) throw new Error("Missing 'id' for deleteById");

    const record = model.findByPk(id);
    if (record.result) {
      record.destroy();
      this.incrementTableVersion(tableName);
      return record.result;
    }
    return null;
  }

  /**
   * Check Permissions - Updated to include new methods
   */
  checkPermission(apiKey, tableName, method) {
    if (!this.apiConfig) return { allowed: true };

    if (!apiKey) return { allowed: false, error: "Missing API Key" };

    const perm = this.apiConfig[apiKey];
    if (!perm) return { allowed: false, error: "Invalid API Key" };

    if (perm.role === "admin") return { allowed: true };

    if (perm.role === "readonly") {
      const isRead =
        method.startsWith("find") ||
        method.startsWith("get") ||
        method === "listTables";
      if (!isRead) return { allowed: false, error: "Read-only access" };
    }

    if (
      perm.allowedTables &&
      tableName &&
      !perm.allowedTables.includes(tableName)
    ) {
      return { allowed: false, error: `Access to table '${tableName}' denied` };
    }

    if (perm.allowedMethods && !perm.allowedMethods.includes(method)) {
      return { allowed: false, error: `Method '${method}' denied` };
    }

    return { allowed: true };
  }

  /**
   * Main Request Processor
   */
  processRequest(e) {
    try {
      let method = e?.parameter?.method;
      let tableName = e?.parameter?.table;
      let options = null;
      let apiKey = e?.parameter?.apikey;

      if (e.postData && e.postData.contents) {
        const body = this.parse(e.postData.contents);
        if (body) {
          if (body.method) method = body.method;
          if (body.table) tableName = body.table;
          if (body.apikey) apiKey = body.apikey;
          if (body.options) options = body.options;
          else if (body.columns)
            options = body; // For createTable
          else if (!body.method) options = body;
        }
      }

      if (!options && e?.parameter?.options) {
        options = this.parse(decodeURIComponent(e.parameter.options));
      }

      // listTables doesn't require table name
      if (method === "listTables") {
        const access = this.checkPermission(apiKey, null, method);
        if (!access.allowed) {
          return this.sendContent({ status: false, error: access.error });
        }
        const data = this.service(null, method, options);
        return this.sendContent({ status: true, data: data });
      }

      if (!method) {
        return this.sendContent({
          status: false,
          error: "Missing 'method' parameter",
        });
      }

      // createTable, dropTable, getTableSchema need table name
      if (
        ["createTable", "dropTable", "getTableSchema"].includes(method) &&
        !tableName
      ) {
        return this.sendContent({
          status: false,
          error: "Missing 'table' parameter",
        });
      }

      // Other methods need both table and method
      if (
        !["createTable", "dropTable", "getTableSchema", "listTables"].includes(
          method,
        ) &&
        !tableName
      ) {
        return this.sendContent({
          status: false,
          error: "Missing 'table' parameter",
        });
      }

      const access = this.checkPermission(apiKey, tableName, method);
      if (!access.allowed) {
        return this.sendContent({ status: false, error: access.error });
      }

      const data = this.service(tableName, method, options);
      return this.sendContent({ status: true, data: data });
    } catch (e) {
      return this.sendContent({ status: false, error: e.toString() });
    }
  }

  doGet(e) {
    return this.processRequest(e);
  }
  doPost(e) {
    return this.processRequest(e);
  }
}
function newApiSheet(sheetId, listModel, apiConfig = null) {
  return new ApiSheet(sheetId, listModel, apiConfig);
}

const sheetId = "1CnTqy61126e0688V19K7xxxxxxxx";
const listModel = {
  // Example: Pre-define tables if needed
  // Products: ["name", "image", "description", "price", "manufacturer", "type", "quantity", "dosage", "substance", "category_ids"],
  // Orders: ["items", "address", "phone_number", "email", "bill_amount"],
  // Categories: ["name", "slug", "image_icon_url"],
};
const apiConfig = {
  test_admin_key: { role: "admin" }, // Full access - can create/drop tables
  test_read_key: {
    role: "readonly",
  }, // Read-only, can also list tables and get schemas
  // Custom role example:
  // custom_key: { role: "custom", allowedTables: ["Products"], allowedMethods: ["findAll", "findOne"] },
};

// ======================================================
// Initialize API
// listModel can be null/empty - tables can be created dynamically
// ======================================================
const app = newApiSheet(sheetId, listModel, apiConfig);

function doGet(e) {
  return app.doGet(e);
}

function doPost(e) {
  return app.doPost(e);
}