---

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

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

-- 3. Покупки
CREATE TABLE Spendings (
    SpendingID INT AUTO_INCREMENT PRIMARY KEY,
    CategoryID INT NOT NULL,
    ShopName VARCHAR(100),
    TotalSum DECIMAL(10,2),
    SpendingDate DATE,
    CONSTRAINT fk_spendings_category
        FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 4. Детализация покупок
CREATE TABLE Goods_Spendings (
    GoodsSpendingID INT AUTO_INCREMENT PRIMARY KEY,
    SpendingID INT NOT NULL,
    GoodsID INT NOT NULL,
    Quantity INT,
    Amount DECIMAL(10,2),
    CONSTRAINT fk_goods_spendings_spending
        FOREIGN KEY (SpendingID) REFERENCES Spendings(SpendingID),
    CONSTRAINT fk_goods_spendings_goods
        FOREIGN KEY (GoodsID) REFERENCES Goods(GoodsID)
);