f1c12fd81a
All checks were successful
Build and Push Docker Image / build-and-push (push) Successful in 1m23s
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
20 lines
1010 B
SQL
20 lines
1010 B
SQL
-- Отдельная таблица записей об остатках (создаётся при каждом выполнении и переносе)
|
||
CREATE TABLE shopping_volume_records (
|
||
id SERIAL PRIMARY KEY,
|
||
item_id INTEGER NOT NULL REFERENCES shopping_items(id) ON DELETE CASCADE,
|
||
user_id INTEGER NOT NULL REFERENCES users(id),
|
||
action_type VARCHAR(20) NOT NULL,
|
||
volume_remaining NUMERIC(10,4),
|
||
volume_purchased NUMERIC(10,4),
|
||
daily_consumption NUMERIC(10,4),
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_shopping_volume_records_item_id ON shopping_volume_records(item_id);
|
||
|
||
-- Создаём начальные записи для всех существующих товаров (остаток 0, дата = created_at)
|
||
INSERT INTO shopping_volume_records (item_id, user_id, action_type, volume_remaining, volume_purchased, created_at)
|
||
SELECT id, user_id, 'create', 0, 0, created_at
|
||
FROM shopping_items
|
||
WHERE deleted = FALSE;
|