Hello from MCP server
// TODO: db.run() supports paramterized queries...
import { TypedPocketBase } from "@/pocketbase-types";
import type {
BooksRecord,
CostsMaterialRecord,
MenusRecord,
OffersRecord,
OrganizationsRecord,
} from "@/pocketbase-types.ts";
import type { OffersExpanded } from "@/types.ts";
import { SQLiteDBConnection } from "@capacitor-community/sqlite";
import initCollectionProblems from "./collections/problems";
import initCollectionSessions from "./collections/sessions";
const NEVER = new Date(Date.UTC(0, 0, 0, 0, 0, 0)).toISOString();
export async function createCollections(
db: SQLiteDBConnection,
pb: TypedPocketBase,
saveDb: () => Promise<void>,
) {
const problems = initCollectionProblems(db);
const sessions = initCollectionSessions(db, saveDb);
const pricebookChanges = {
getLastDate: async function (bookId: string) {
const r = await db.query(
`
select * from pricebookChanges where book = '${bookId}'
order by created desc limit 1
`,
);
if (r.values?.length == 0) {
return NEVER;
}
return r.values![0].created;
},
saveChange: async function (bookId: string, created: string) {
const stmt = `
insert into pricebookChanges (book, created)
values (?, ?);
`;
await db.run(stmt, [bookId, created]);
saveDb();
},
};
const appStatus = {
updateKey: async function (key: string, value: string) {
const stmt = `
INSERT INTO appStatus (key, value)
VALUES (?, ?)
ON CONFLICT(key)
DO UPDATE SET value = excluded.value;
`;
await db.run(stmt, [key, value]);
await saveDb();
},
setRefreshNow: async function () {
const now = new Date().toISOString();
await this.updateKey("lastRefresh", now);
},
setSyncNow: async function () {
const now = new Date().toISOString();
await this.updateKey("lastSync", now);
},
// TODO: We can factor out common code here if we want to
lastRefresh: async function () {
const last = await db.query(
"select * from appStatus where key='lastRefresh'",
);
if (last.values?.length == 0) {
await this.setRefreshNow();
return NEVER;
} else {
return last.values![0].value;
}
},
lastSync: async function () {
const last = await db.query(
"select * from appStatus where key='lastSync'",
);
if (last.values?.length == 0) {
await this.setSyncNow();
return NEVER;
} else {
return last.values![0].value;
}
},
};
const currencies = {
refresh: async function () {
const currencies = await pb.collection("currencies").getFullList();
if (currencies.length > 0) {
await db.execute("delete from currencies");
}
const stmt = `
insert into currencies (id, name, refId, symbol, created, updated) values (
?, ?, ?, ?, ?, ?)`;
for (const c of currencies) {
const values = [c.id, c.name, c.refId, c.symbol, c.created, c.updated];
await db.run(stmt, values);
}
this.refreshRates();
},
refreshRates: async function () {
// TODO: we should let the organization set a rate fixed event, but for
// now just choose the most recent
const rateFixedR = await pb
.collection("currencyRateFixed")
.getList(1, 1, {
sort: "-created",
});
const rateFixed = rateFixedR.items[0];
const rates = await pb.collection("currencyRates").getFullList({
filter: `currencyRateFixed = '${rateFixed.id}'`,
expand: "baseCurrency, quoteCurrency",
});
const stmt = `
insert into currencyRates (baseCurrency, quoteCurrency, rate, name, created) values (?, ?, ?, ?, ?)
ON CONFLICT(quoteCurrency)
DO UPDATE SET rate = excluded.rate, name = excluded.name, created = excluded.created;
`;
for (const rate of rates) {
const values = [
(rate.expand as any).baseCurrency.refId,
(rate.expand as any).quoteCurrency.refId,
rate.rate,
rateFixed.name || null,
rate.created || null,
];
await db.run(stmt, values);
await saveDb();
}
},
getAll: async function () {
const currenciesResponse = await db.query("select * from currencies");
return currenciesResponse.values;
},
getRates: async function () {
const currenciesResponse = await db.query("select * from currencyRates");
return currenciesResponse.values;
},
};
const books = {
getAll: async function () {
const booksResponse = await db.query(`select * from books`);
return booksResponse.values;
},
refresh: async function () {
if (pb.authStore.record?.activeOrg) {
const allBooks = await pb.collection("books").getFullList({
filter: `org = "${pb.authStore.record.activeOrg}"`,
expand: "parent",
});
if (allBooks.length > 0) {
await db.execute("delete from books");
}
// TODO: use Date.now() or the date from the API response or something.
for (const book of allBooks) {
let refId = book.refId;
if ((book.expand as any).parent) {
refId = (book.expand as any).parent.refId;
}
const stmt = `
INSERT INTO books (id, name, org, parent, refId, created, updated)
VALUES (
?,
?,
'clientApp',
NULL,
?,
'2025-08-08T10:30:00.000Z',
'2025-08-08T10:30:00.000Z'
);
`;
const values = [book.id, book.name, refId];
try {
// await db.beginTransaction();
await db.run(stmt, values);
await saveDb();
// await db.commitTransaction();
} catch (err) {
console.error(err);
// await db.rollbackTransaction();
}
}
}
},
};
const menuCopy = {
byId: async function (menuCopyId: string) {
const q = `select * from menuCopy where id = '${menuCopyId}'`;
const result = await db.query(q);
const r = result.values?.[0];
if (!r) return null;
const contentIds = JSON.parse(r.contentItems || '[]');
const items = [];
if (contentIds) {
for (const contentId of contentIds) {
const q2 = `select * from contentItems where id = '${contentId}'`;
const r2 = (await db.query(q2)).values;
if (r2) {
items.push(r2[0]);
}
}
}
r.contentItems = items;
return r;
},
};
const menus = {
byBookId: async function (bookId: string) {
const query = `select * from menus where json_extract(books, '$[0]') = '${bookId}'`;
const menus = (await db.query(query)).values;
return menus;
},
// adding this 'byId' in addition to 'byMenuId' so we can move away from
// the nested queries. Actually... if we always just have `select * from
// <collection> where id = <id>` we can get rid of a lot of this code.
byId: async function (id: string) {
const toParse = ["books"];
const r = await db.query(`select * from menus where id = '${id}'`);
if (r.values) {
const record = r.values[0];
for (const key of toParse) {
try {
record[key] = JSON.parse(record[key]);
} catch {
// Ignore parse errors for optional JSON fields
}
}
return record;
}
},
byMenuId: async function (menuId: string) {
const menuQuery = `select * from menus where id = '${menuId}' limit 1`;
const menuResult = await db.query(menuQuery);
const menu = menuResult.values?.[0];
if (!menu) return null;
const q = `select * from menuTiers where json_extract(menus, '$[0]') = '${menuId}'`;
const tiersResult = await db.query(q);
const tiers = tiersResult.values;
menu.tiers = [];
if (tiers) {
for (const tier of tiers) {
const tierId = JSON.parse(tier.tiers);
const tierQ = `select * from tiers where id = '${tierId}' limit 1`;
const tierResult = await db.query(tierQ);
const tierData = tierResult.values?.[0];
if (!tierData) continue;
const menuCopyIds = JSON.parse(tier.menuCopy);
const contentItemIds = JSON.parse(tier.contentItems);
tierData.contentItems = [];
for (const itemId of contentItemIds) {
const item = await contentItems.byId(itemId);
tierData.contentItems.push(item);
}
const menuCopyList = [];
if (menuCopyIds) {
for (const menuCopyId of menuCopyIds) {
const copy = await menuCopy.byId(menuCopyId);
menuCopyList.push(copy);
}
}
const offerId = JSON.parse(tier.offers);
tierData.menuCopy = menuCopyList;
tierData.offer = await offers.byOfferId(offerId);
menu.tiers.push(tierData);
}
}
menu.tiers.sort((a: { rank: number }, b: { rank: number }) => a.rank - b.rank);
return menu;
},
};
const contentItems = {
byId: async function (itemId: string) {
const query = `
select * from contentItems where id='${itemId}'
`;
const result = await db.query(query);
return result.values?.[0];
},
};
const offers = {
byMenuId: async function (menuId: string) {
const query = `
SELECT *
FROM offers
WHERE EXISTS (
SELECT 1
FROM json_each(menus)
WHERE json_each.value = '${menuId}'
);
`;
return (await db.query(query)).values;
},
byOfferId: async function (
offerId: string,
): Promise<OffersExpanded | null> {
const query = `
select * from offers where id="${offerId}"
`;
const offerResponse = (await db.query(query)).values;
console.log('[offers.byOfferId] offerId:', offerId, 'response:', offerResponse);
if (offerResponse) {
const offer = offerResponse[0];
console.log('[offers.byOfferId] offer.techHandbook:', offer.techHandbook);
if (offer.costsMaterial) {
const costs = JSON.parse(offer.costsMaterial)
.map((id: string) => `"${id}"`)
.join(", ");
const queryCosts = `SELECT * FROM costsMaterial WHERE id IN (${costs})`;
offer.costsMaterial = (await db.query(queryCosts)).values;
}
if (offer.costsTime) {
const costs = JSON.parse(offer.costsTime)
.map((id: string) => `"${id}"`)
.join(", ");
const queryCosts = `SELECT * FROM costsTime WHERE id IN (${costs})`;
offer.costsTime = (await db.query(queryCosts)).values;
}
if (offer.techHandbook) {
const ids = JSON.parse(offer.techHandbook)
.map((id: string) => `"${id}"`)
.join(", ");
const queryItems = `SELECT * FROM contentItems WHERE id IN (${ids})`;
const contentItemsResult = (await db.query(queryItems)).values;
console.log('[offers.byOfferId] techHandbook IDs:', offer.techHandbook, 'contentItems found:', contentItemsResult?.length || 0);
offer.techHandbookExpanded = contentItemsResult;
} else {
console.log('[offers.byOfferId] No techHandbook field on offer');
}
return offer as OffersExpanded;
}
return null;
},
};
const costsMaterial = {
byIds: async function (ids: string[]) {
const joined = ids.map((id) => `"${id}"`).join(", ");
const query = `SELECT * FROM costsMaterial WHERE id IN (${joined})`;
return (await db.query(query)).values;
},
};
const organizations = {
refresh: async function () {
if (pb.authStore.record?.activeOrg) {
try {
// Fetch the user's active organization - PocketBase will handle permissions
const org = await pb.collection("organizations").getOne(pb.authStore.record.activeOrg) as OrganizationsRecord & { variables?: Record<string, any> };
// Clear existing data and insert the active org
await db.execute("delete from organizations");
const stmt = `
insert into organizations (id, name, org, owner, currency, variables, created, updated)
values (?, ?, ?, ?, ?, ?, ?, ?)`;
const values = [
org.id,
org.name,
org.org,
org.owner,
org.currency,
JSON.stringify(org.variables || {}),
org.created,
org.updated,
];
await db.run(stmt, values);
await saveDb();
} catch (error) {
console.error('Error refreshing organization data:', error);
// If user doesn't have permission to view org, fail gracefully
}
}
},
getCurrentVariables: async function () {
if (pb.authStore.record?.activeOrg) {
const query = `select * from organizations where id = '${pb.authStore.record.activeOrg}' limit 1`;
const result = await db.query(query);
if (result.values && result.values.length > 0) {
const org = result.values[0];
return org.variables ? JSON.parse(org.variables) : {};
}
}
return {};
},
};
const changeMessages = {
save: async function (
pocketbaseId: string,
bookId: string,
orgId: string,
changeset: any[],
created: string,
status: string = 'success',
errorMessage?: string
) {
const stmt = `
INSERT INTO changeMessages (pocketbase_id, book, org, changeset, status, error_message, created, processed_at)
VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'));
`;
await db.run(stmt, [pocketbaseId, bookId, orgId, JSON.stringify(changeset), status, errorMessage || null, created]);
await saveDb();
},
getAll: async function (limit: number = 100) {
const r = await db.query(`
SELECT * FROM changeMessages
ORDER BY created DESC
LIMIT ${limit}
`);
return (r.values || []).map((row: any) => ({
...row,
changeset: JSON.parse(row.changeset || '[]')
}));
},
clear: async function () {
await db.run(`DELETE FROM changeMessages`);
await saveDb();
},
update: async function (id: number, changeset: any[]) {
const stmt = `UPDATE changeMessages SET changeset = ? WHERE id = ?`;
await db.run(stmt, [JSON.stringify(changeset), id]);
await saveDb();
}
};
return {
appStatus,
books,
changeMessages,
costsMaterial,
currencies,
menus,
offers,
organizations,
pricebookChanges,
problems,
sessions,
};
}