664 lines
19 KiB
JavaScript
664 lines
19 KiB
JavaScript
const pool = require("../db/pool");
|
|
|
|
function normalizeItemName(itemName) {
|
|
return String(itemName || "").trim().toLowerCase();
|
|
}
|
|
|
|
function toPositiveInteger(value, fallback = 1) {
|
|
const numberValue = Number(value);
|
|
return Number.isInteger(numberValue) && numberValue > 0 ? numberValue : fallback;
|
|
}
|
|
|
|
const ACTIVE_ADDED_BY_USERS_SQL = `
|
|
(
|
|
SELECT ARRAY_AGG(
|
|
active_added_by_users.user_label
|
|
ORDER BY active_added_by_users.last_added_on DESC, active_added_by_users.user_label
|
|
)
|
|
FROM (
|
|
SELECT
|
|
COALESCE(NULLIF(TRIM(u.display_name), ''), NULLIF(TRIM(u.name), ''), u.username) AS user_label,
|
|
MAX(active_history.added_on) AS last_added_on
|
|
FROM (
|
|
SELECT
|
|
hlh.*,
|
|
COALESCE(
|
|
SUM(hlh.quantity) OVER (
|
|
PARTITION BY hlh.household_list_id
|
|
ORDER BY hlh.added_on DESC, hlh.id DESC
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
|
|
),
|
|
0
|
|
) AS newer_quantity
|
|
FROM household_list_history hlh
|
|
WHERE hlh.household_list_id = hl.id
|
|
) active_history
|
|
JOIN users u ON active_history.added_by = u.id
|
|
WHERE active_history.newer_quantity < GREATEST(hl.quantity, 0)
|
|
GROUP BY user_label
|
|
) active_added_by_users
|
|
) AS added_by_users`;
|
|
|
|
async function getHouseholdStoreItemByNormalizedName(householdId, storeLocationId, normalizedName) {
|
|
const result = await pool.query(
|
|
`SELECT id, name, normalized_name, image_id
|
|
FROM household_store_items
|
|
WHERE household_id = $1
|
|
AND store_location_id = $2
|
|
AND normalized_name = $3`,
|
|
[householdId, storeLocationId, normalizedName]
|
|
);
|
|
|
|
return result.rows[0] || null;
|
|
}
|
|
|
|
async function createItemImage({
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
householdListId = null,
|
|
imageScope,
|
|
imageBuffer,
|
|
mimeType,
|
|
userId = null,
|
|
}) {
|
|
if (!imageBuffer || !mimeType) {
|
|
return null;
|
|
}
|
|
|
|
const result = await pool.query(
|
|
`INSERT INTO household_item_images
|
|
(
|
|
household_id,
|
|
store_location_id,
|
|
household_store_item_id,
|
|
household_list_id,
|
|
image_scope,
|
|
image,
|
|
mime_type,
|
|
created_by
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
|
RETURNING id`,
|
|
[
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
householdListId,
|
|
imageScope,
|
|
imageBuffer,
|
|
mimeType,
|
|
userId,
|
|
]
|
|
);
|
|
|
|
return result.rows[0].id;
|
|
}
|
|
|
|
exports.ensureHouseholdStoreItem = async (householdId, storeLocationId, itemName) => {
|
|
const normalizedName = normalizeItemName(itemName);
|
|
let item = await getHouseholdStoreItemByNormalizedName(
|
|
householdId,
|
|
storeLocationId,
|
|
normalizedName
|
|
);
|
|
|
|
if (item) {
|
|
return item;
|
|
}
|
|
|
|
const result = await pool.query(
|
|
`INSERT INTO household_store_items
|
|
(household_id, store_location_id, name, normalized_name, updated_at)
|
|
VALUES ($1, $2, $3, $4, NOW())
|
|
RETURNING id, name, normalized_name, image_id`,
|
|
[householdId, storeLocationId, normalizedName, normalizedName]
|
|
);
|
|
|
|
return result.rows[0];
|
|
};
|
|
|
|
exports.getHouseholdStoreList = async (householdId, storeLocationId, includeHistory = true) => {
|
|
const result = await pool.query(
|
|
`SELECT
|
|
hl.id,
|
|
hl.household_store_item_id AS item_id,
|
|
hl.household_store_item_id,
|
|
hsi.name AS item_name,
|
|
hl.quantity,
|
|
hl.bought,
|
|
hl.notes,
|
|
ENCODE(COALESCE(list_img.image, hl.custom_image, catalog_img.image, hsi.custom_image), 'base64') AS item_image,
|
|
COALESCE(list_img.mime_type, hl.custom_image_mime_type, catalog_img.mime_type, hsi.custom_image_mime_type) AS image_mime_type,
|
|
${includeHistory ? `${ACTIVE_ADDED_BY_USERS_SQL},` : "NULL AS added_by_users,"}
|
|
hl.modified_on AS last_added_on,
|
|
hic.item_type,
|
|
hic.item_group,
|
|
COALESCE(slz.name, hic.zone) AS zone,
|
|
slz.sort_order AS zone_sort_order
|
|
FROM household_lists hl
|
|
JOIN household_store_items hsi ON hsi.id = hl.household_store_item_id
|
|
LEFT JOIN household_item_images list_img ON list_img.id = hl.image_id
|
|
LEFT JOIN household_item_images catalog_img ON catalog_img.id = hsi.image_id
|
|
LEFT JOIN household_item_classifications hic
|
|
ON hic.household_id = hl.household_id
|
|
AND hic.store_location_id = hl.store_location_id
|
|
AND hic.household_store_item_id = hl.household_store_item_id
|
|
LEFT JOIN store_location_zones slz ON slz.id = hic.zone_id
|
|
WHERE hl.household_id = $1
|
|
AND hl.store_location_id = $2
|
|
AND hl.bought = FALSE
|
|
ORDER BY slz.sort_order ASC NULLS LAST, hsi.name ASC`,
|
|
[householdId, storeLocationId]
|
|
);
|
|
return result.rows;
|
|
};
|
|
|
|
exports.getItemByName = async (householdId, storeLocationId, itemName) => {
|
|
const normalizedName = normalizeItemName(itemName);
|
|
const result = await pool.query(
|
|
`SELECT
|
|
hl.id,
|
|
hl.household_id,
|
|
hl.store_location_id,
|
|
hl.household_store_item_id AS item_id,
|
|
hl.household_store_item_id,
|
|
hsi.name AS item_name,
|
|
hl.quantity,
|
|
hl.bought,
|
|
hl.notes,
|
|
ENCODE(COALESCE(list_img.image, hl.custom_image, catalog_img.image, hsi.custom_image), 'base64') AS item_image,
|
|
COALESCE(list_img.mime_type, hl.custom_image_mime_type, catalog_img.mime_type, hsi.custom_image_mime_type) AS image_mime_type,
|
|
${ACTIVE_ADDED_BY_USERS_SQL},
|
|
hl.modified_on AS last_added_on,
|
|
hic.item_type,
|
|
hic.item_group,
|
|
COALESCE(slz.name, hic.zone) AS zone,
|
|
slz.sort_order AS zone_sort_order
|
|
FROM household_lists hl
|
|
JOIN household_store_items hsi ON hsi.id = hl.household_store_item_id
|
|
LEFT JOIN household_item_images list_img ON list_img.id = hl.image_id
|
|
LEFT JOIN household_item_images catalog_img ON catalog_img.id = hsi.image_id
|
|
LEFT JOIN household_item_classifications hic
|
|
ON hic.household_id = hl.household_id
|
|
AND hic.store_location_id = hl.store_location_id
|
|
AND hic.household_store_item_id = hl.household_store_item_id
|
|
LEFT JOIN store_location_zones slz ON slz.id = hic.zone_id
|
|
WHERE hl.household_id = $1
|
|
AND hl.store_location_id = $2
|
|
AND hsi.normalized_name = $3`,
|
|
[householdId, storeLocationId, normalizedName]
|
|
);
|
|
return result.rows[0] || null;
|
|
};
|
|
|
|
exports.addOrUpdateItem = async (
|
|
householdId,
|
|
storeLocationId,
|
|
itemName,
|
|
quantity,
|
|
userId,
|
|
imageBuffer = null,
|
|
mimeType = null,
|
|
notes = undefined
|
|
) => {
|
|
const nextQuantity = toPositiveInteger(quantity);
|
|
const householdStoreItem = await exports.ensureHouseholdStoreItem(
|
|
householdId,
|
|
storeLocationId,
|
|
itemName
|
|
);
|
|
const listResult = await pool.query(
|
|
`SELECT id, bought, quantity
|
|
FROM household_lists
|
|
WHERE household_id = $1
|
|
AND store_location_id = $2
|
|
AND household_store_item_id = $3`,
|
|
[householdId, storeLocationId, householdStoreItem.id]
|
|
);
|
|
|
|
if (listResult.rowCount > 0) {
|
|
const listId = listResult.rows[0].id;
|
|
const previousQuantity = toPositiveInteger(listResult.rows[0].quantity, 0);
|
|
const wasBought = Boolean(listResult.rows[0].bought);
|
|
const historyQuantity =
|
|
!wasBought && nextQuantity > previousQuantity
|
|
? nextQuantity - previousQuantity
|
|
: nextQuantity;
|
|
|
|
let imageId = null;
|
|
if (imageBuffer && mimeType) {
|
|
imageId = await createItemImage({
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId: householdStoreItem.id,
|
|
householdListId: listId,
|
|
imageScope: "list",
|
|
imageBuffer,
|
|
mimeType,
|
|
userId,
|
|
});
|
|
}
|
|
|
|
if (imageId) {
|
|
await pool.query(
|
|
`UPDATE household_lists
|
|
SET quantity = $1,
|
|
bought = FALSE,
|
|
image_id = $2,
|
|
custom_image = NULL,
|
|
custom_image_mime_type = NULL,
|
|
notes = COALESCE($3, notes),
|
|
modified_on = NOW()
|
|
WHERE id = $4`,
|
|
[nextQuantity, imageId, notes, listId]
|
|
);
|
|
} else {
|
|
await pool.query(
|
|
`UPDATE household_lists
|
|
SET quantity = $1,
|
|
bought = FALSE,
|
|
notes = COALESCE($2, notes),
|
|
modified_on = NOW()
|
|
WHERE id = $3`,
|
|
[nextQuantity, notes, listId]
|
|
);
|
|
}
|
|
|
|
return {
|
|
listId,
|
|
itemId: householdStoreItem.id,
|
|
householdStoreItemId: householdStoreItem.id,
|
|
itemName: householdStoreItem.name,
|
|
quantity: nextQuantity,
|
|
previousQuantity,
|
|
historyQuantity,
|
|
wasBought,
|
|
isNew: false,
|
|
};
|
|
}
|
|
|
|
const insert = await pool.query(
|
|
`INSERT INTO household_lists
|
|
(household_id, store_location_id, household_store_item_id, quantity, added_by, notes)
|
|
VALUES ($1, $2, $3, $4, $5, $6)
|
|
RETURNING id`,
|
|
[householdId, storeLocationId, householdStoreItem.id, nextQuantity, userId, notes || null]
|
|
);
|
|
|
|
if (imageBuffer && mimeType) {
|
|
const imageId = await createItemImage({
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId: householdStoreItem.id,
|
|
householdListId: insert.rows[0].id,
|
|
imageScope: "list",
|
|
imageBuffer,
|
|
mimeType,
|
|
userId,
|
|
});
|
|
|
|
await pool.query(
|
|
`UPDATE household_lists
|
|
SET image_id = $1,
|
|
custom_image = NULL,
|
|
custom_image_mime_type = NULL
|
|
WHERE id = $2`,
|
|
[imageId, insert.rows[0].id]
|
|
);
|
|
}
|
|
|
|
return {
|
|
listId: insert.rows[0].id,
|
|
itemId: householdStoreItem.id,
|
|
householdStoreItemId: householdStoreItem.id,
|
|
itemName: householdStoreItem.name,
|
|
quantity: nextQuantity,
|
|
previousQuantity: 0,
|
|
historyQuantity: nextQuantity,
|
|
wasBought: false,
|
|
isNew: true,
|
|
};
|
|
};
|
|
|
|
exports.setBought = async (listId, bought, quantityBought = null) => {
|
|
const item = await pool.query(
|
|
`SELECT id, household_id, store_location_id, household_store_item_id, quantity, bought
|
|
FROM household_lists
|
|
WHERE id = $1`,
|
|
[listId]
|
|
);
|
|
|
|
if (!item.rows[0]) return null;
|
|
|
|
const current = item.rows[0];
|
|
const currentQuantity = toPositiveInteger(current.quantity, 0);
|
|
|
|
if (bought === false) {
|
|
await pool.query(
|
|
"UPDATE household_lists SET bought = FALSE, modified_on = NOW() WHERE id = $1",
|
|
[listId]
|
|
);
|
|
return {
|
|
...current,
|
|
eventType: "ITEM_UNBOUGHT",
|
|
quantityDelta: null,
|
|
quantityAfter: currentQuantity,
|
|
};
|
|
}
|
|
|
|
const requestedQuantity = toPositiveInteger(quantityBought, 0);
|
|
if (requestedQuantity > 0) {
|
|
const boughtQuantity = Math.min(requestedQuantity, currentQuantity);
|
|
const remainingQuantity = currentQuantity - boughtQuantity;
|
|
|
|
if (remainingQuantity <= 0) {
|
|
await pool.query(
|
|
"UPDATE household_lists SET bought = TRUE, modified_on = NOW() WHERE id = $1",
|
|
[listId]
|
|
);
|
|
} else {
|
|
await pool.query(
|
|
"UPDATE household_lists SET quantity = $1, modified_on = NOW() WHERE id = $2",
|
|
[remainingQuantity, listId]
|
|
);
|
|
}
|
|
|
|
return {
|
|
...current,
|
|
eventType: "ITEM_BOUGHT",
|
|
quantityDelta: -boughtQuantity,
|
|
quantityAfter: Math.max(remainingQuantity, 0),
|
|
};
|
|
}
|
|
|
|
await pool.query(
|
|
"UPDATE household_lists SET bought = TRUE, modified_on = NOW() WHERE id = $1",
|
|
[listId]
|
|
);
|
|
|
|
return {
|
|
...current,
|
|
eventType: "ITEM_BOUGHT",
|
|
quantityDelta: -currentQuantity,
|
|
quantityAfter: 0,
|
|
};
|
|
};
|
|
|
|
exports.addHistoryRecord = async (
|
|
listId,
|
|
householdStoreItemId,
|
|
quantity,
|
|
userId,
|
|
storeLocationId = null
|
|
) => {
|
|
await pool.query(
|
|
`INSERT INTO household_list_history
|
|
(household_list_id, store_location_id, household_store_item_id, quantity, added_by, added_on)
|
|
VALUES (
|
|
$1,
|
|
COALESCE($5, (SELECT store_location_id FROM household_lists WHERE id = $1)),
|
|
$2,
|
|
$3,
|
|
$4,
|
|
NOW()
|
|
)`,
|
|
[listId, householdStoreItemId, quantity, userId, storeLocationId]
|
|
);
|
|
};
|
|
|
|
exports.recordItemEvent = async ({
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
householdListId = null,
|
|
actorUserId = null,
|
|
eventType,
|
|
quantityDelta = null,
|
|
quantityAfter = null,
|
|
metadata = {},
|
|
}) => {
|
|
await pool.query(
|
|
`INSERT INTO household_item_events
|
|
(
|
|
household_id,
|
|
store_location_id,
|
|
household_store_item_id,
|
|
household_list_id,
|
|
actor_user_id,
|
|
event_type,
|
|
quantity_delta,
|
|
quantity_after,
|
|
metadata
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)`,
|
|
[
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
householdListId,
|
|
actorUserId,
|
|
eventType,
|
|
quantityDelta,
|
|
quantityAfter,
|
|
JSON.stringify(metadata || {}),
|
|
]
|
|
);
|
|
};
|
|
|
|
exports.getSuggestions = async (query, householdId, storeLocationId) => {
|
|
const result = await pool.query(
|
|
`SELECT DISTINCT
|
|
hsi.name AS item_name,
|
|
CASE WHEN hl.id IS NOT NULL AND hl.bought = FALSE THEN 0 ELSE 1 END AS sort_order
|
|
FROM household_store_items hsi
|
|
LEFT JOIN household_lists hl
|
|
ON hl.household_store_item_id = hsi.id
|
|
AND hl.household_id = $2
|
|
AND hl.store_location_id = $3
|
|
WHERE hsi.household_id = $2
|
|
AND hsi.store_location_id = $3
|
|
AND hsi.name ILIKE $1
|
|
ORDER BY sort_order, hsi.name
|
|
LIMIT 10`,
|
|
[`%${query}%`, householdId, storeLocationId]
|
|
);
|
|
return result.rows;
|
|
};
|
|
|
|
exports.getRecentlyBoughtItems = async (householdId, storeLocationId) => {
|
|
const result = await pool.query(
|
|
`SELECT
|
|
hl.id,
|
|
hl.household_store_item_id AS item_id,
|
|
hl.household_store_item_id,
|
|
hsi.name AS item_name,
|
|
hl.quantity,
|
|
hl.bought,
|
|
ENCODE(COALESCE(list_img.image, hl.custom_image, catalog_img.image, hsi.custom_image), 'base64') AS item_image,
|
|
COALESCE(list_img.mime_type, hl.custom_image_mime_type, catalog_img.mime_type, hsi.custom_image_mime_type) AS image_mime_type,
|
|
${ACTIVE_ADDED_BY_USERS_SQL},
|
|
hl.modified_on AS last_added_on
|
|
FROM household_lists hl
|
|
JOIN household_store_items hsi ON hsi.id = hl.household_store_item_id
|
|
LEFT JOIN household_item_images list_img ON list_img.id = hl.image_id
|
|
LEFT JOIN household_item_images catalog_img ON catalog_img.id = hsi.image_id
|
|
WHERE hl.household_id = $1
|
|
AND hl.store_location_id = $2
|
|
AND hl.bought = TRUE
|
|
AND hl.modified_on >= NOW() - INTERVAL '24 hours'
|
|
ORDER BY hl.modified_on DESC`,
|
|
[householdId, storeLocationId]
|
|
);
|
|
return result.rows;
|
|
};
|
|
|
|
exports.getZoneByName = async (householdId, storeLocationId, zoneName) => {
|
|
const result = await pool.query(
|
|
`SELECT id, name, sort_order
|
|
FROM store_location_zones
|
|
WHERE household_id = $1
|
|
AND store_location_id = $2
|
|
AND normalized_name = $3
|
|
AND is_active = TRUE`,
|
|
[householdId, storeLocationId, normalizeItemName(zoneName)]
|
|
);
|
|
return result.rows[0] || null;
|
|
};
|
|
|
|
exports.getClassification = async (householdId, storeLocationId, itemId) => {
|
|
const result = await pool.query(
|
|
`SELECT
|
|
hic.item_type,
|
|
hic.item_group,
|
|
COALESCE(slz.name, hic.zone) AS zone,
|
|
hic.confidence,
|
|
hic.source
|
|
FROM household_item_classifications hic
|
|
LEFT JOIN store_location_zones slz ON slz.id = hic.zone_id
|
|
WHERE hic.household_id = $1
|
|
AND hic.store_location_id = $2
|
|
AND hic.household_store_item_id = $3`,
|
|
[householdId, storeLocationId, itemId]
|
|
);
|
|
return result.rows[0] || null;
|
|
};
|
|
|
|
exports.upsertClassification = async (householdId, storeLocationId, itemId, classification) => {
|
|
const { item_type, item_group, zone, confidence, source } = classification;
|
|
const zoneRecord = zone ? await exports.getZoneByName(householdId, storeLocationId, zone) : null;
|
|
|
|
const result = await pool.query(
|
|
`INSERT INTO household_item_classifications
|
|
(
|
|
household_id,
|
|
store_location_id,
|
|
household_store_item_id,
|
|
item_type,
|
|
item_group,
|
|
zone,
|
|
zone_id,
|
|
confidence,
|
|
source
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
|
|
ON CONFLICT (household_id, store_location_id, household_store_item_id)
|
|
DO UPDATE SET
|
|
item_type = EXCLUDED.item_type,
|
|
item_group = EXCLUDED.item_group,
|
|
zone = EXCLUDED.zone,
|
|
zone_id = EXCLUDED.zone_id,
|
|
confidence = EXCLUDED.confidence,
|
|
source = EXCLUDED.source,
|
|
updated_at = NOW()
|
|
RETURNING *`,
|
|
[
|
|
householdId,
|
|
storeLocationId,
|
|
itemId,
|
|
item_type,
|
|
item_group,
|
|
zone,
|
|
zoneRecord?.id || null,
|
|
confidence,
|
|
source,
|
|
]
|
|
);
|
|
return result.rows[0];
|
|
};
|
|
|
|
exports.deleteClassification = async (householdId, storeLocationId, itemId) => {
|
|
const result = await pool.query(
|
|
`DELETE FROM household_item_classifications
|
|
WHERE household_id = $1
|
|
AND store_location_id = $2
|
|
AND household_store_item_id = $3`,
|
|
[householdId, storeLocationId, itemId]
|
|
);
|
|
return result.rowCount > 0;
|
|
};
|
|
|
|
exports.updateItem = async (listId, itemName, quantity, notes) => {
|
|
const existing = await pool.query(
|
|
`SELECT id, household_id, store_location_id, household_store_item_id, quantity, notes
|
|
FROM household_lists
|
|
WHERE id = $1`,
|
|
[listId]
|
|
);
|
|
|
|
if (existing.rowCount === 0) {
|
|
return null;
|
|
}
|
|
|
|
const updates = [];
|
|
const values = [listId];
|
|
let paramCount = 1;
|
|
|
|
if (quantity !== undefined) {
|
|
paramCount += 1;
|
|
updates.push(`quantity = $${paramCount}`);
|
|
values.push(quantity);
|
|
}
|
|
|
|
if (notes !== undefined) {
|
|
paramCount += 1;
|
|
updates.push(`notes = $${paramCount}`);
|
|
values.push(notes);
|
|
}
|
|
|
|
updates.push("modified_on = NOW()");
|
|
|
|
const result = await pool.query(
|
|
`UPDATE household_lists SET ${updates.join(", ")} WHERE id = $1 RETURNING *`,
|
|
values
|
|
);
|
|
|
|
return {
|
|
previous: existing.rows[0],
|
|
updated: result.rows[0],
|
|
};
|
|
};
|
|
|
|
exports.deleteItem = async (listId) => {
|
|
const result = await pool.query(
|
|
`DELETE FROM household_lists
|
|
WHERE id = $1
|
|
RETURNING id, household_id, store_location_id, household_store_item_id, quantity`,
|
|
[listId]
|
|
);
|
|
return result.rows[0] || null;
|
|
};
|
|
|
|
exports.setCatalogItemImage = async (
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
imageBuffer,
|
|
mimeType,
|
|
userId = null
|
|
) => {
|
|
const imageId = await createItemImage({
|
|
householdId,
|
|
storeLocationId,
|
|
householdStoreItemId,
|
|
imageScope: "catalog",
|
|
imageBuffer,
|
|
mimeType,
|
|
userId,
|
|
});
|
|
|
|
await pool.query(
|
|
`UPDATE household_store_items
|
|
SET image_id = $1,
|
|
custom_image = NULL,
|
|
custom_image_mime_type = NULL,
|
|
updated_at = NOW()
|
|
WHERE household_id = $2
|
|
AND store_location_id = $3
|
|
AND id = $4`,
|
|
[imageId, householdId, storeLocationId, householdStoreItemId]
|
|
);
|
|
|
|
return imageId;
|
|
};
|