---

-- 1. Категории
CREATE TABLE Categories (
    CategoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryName TEXT NOT NULL,
    Description TEXT
);

-- 2. Товары
CREATE TABLE Goods (
    GoodsID INTEGER PRIMARY KEY AUTOINCREMENT,
    GoodsName TEXT NOT NULL,
    DetailedTracking TEXT
);

-- 3. Покупки
CREATE TABLE Spendings (
    SpendingID INTEGER PRIMARY KEY AUTOINCREMENT,
    CategoryID INTEGER NOT NULL,
    ShopName TEXT,
    TotalSum NUMERIC,
    SpendingDate TEXT,  -- дата как 'YYYY-MM-DD'
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 4. Детализация покупок
CREATE TABLE Goods_Spendings (
    GoodsSpendingID INTEGER PRIMARY KEY AUTOINCREMENT,
    SpendingID INTEGER NOT NULL,
    GoodsID INTEGER NOT NULL,
    Quantity INTEGER,
    Amount NUMERIC,
    FOREIGN KEY (SpendingID) REFERENCES Spendings(SpendingID),
    FOREIGN KEY (GoodsID) REFERENCES Goods(GoodsID)
);