CREATE TABLE Category (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100) NOT NULL UNIQUE
);

DROP TABLE equipment CASCADE;
CREATE TABLE Equipment (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(200) NOT NULL,
    manufacturer VARCHAR(200),
    passport_data TEXT,
    category_id INT NOT NULL REFERENCES Category(id) ON DELETE RESTRICT
);
DROP TABLE Employee CASCADE;
CREATE TABLE Employee (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    full_name VARCHAR(200) NOT NULL,
    position VARCHAR(100),
    phone VARCHAR(20)
);

DROP TABLE Supplier CASCADE;
CREATE TABLE Supplier (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    address TEXT NOT NULL
);

CREATE TABLE Warehouse (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    address TEXT NOT NULL
);

-- Фактические таблицы
DROP TABLE Purchase CASCADE;
CREATE TABLE Purchase (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    equipment_id INT NOT NULL REFERENCES Equipment(id) ON DELETE RESTRICT,
    supplier_id INT NOT NULL REFERENCES Supplier(id),
    warehouse_id INT NOT NULL REFERENCES Warehouse(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price_per_unit NUMERIC(12,2) NOT NULL CHECK (price_per_unit >= 0),
    purchase_date DATE NOT NULL,
    notes TEXT
);

DROP TABLE WriteOff CASCADE;
CREATE TABLE WriteOff (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    equipment_id INT NOT NULL REFERENCES Equipment(id) ON DELETE RESTRICT,
    employee_id INT NOT NULL REFERENCES Employee(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    reason VARCHAR(255),
    write_off_date DATE NOT NULL
);

-- Индексы
CREATE INDEX idx_purchase_equip ON Purchase(equipment_id);
CREATE INDEX idx_writeoff_equip ON WriteOff(equipment_id);

-- Представление с расчётным атрибутом: текущий остаток каждого оборудования
CREATE VIEW EquipmentStock AS
SELECT 
    e.id,
    e.name,
    e.manufacturer,
    e.passport_data,
    c.name AS category,
    COALESCE(SUM(p.quantity), 0) - COALESCE(SUM(w.quantity), 0) AS current_quantity
FROM Equipment e
LEFT JOIN Category c ON e.category_id = c.id
LEFT JOIN Purchase p ON e.id = p.equipment_id
LEFT JOIN WriteOff w ON e.id = w.equipment_id
GROUP BY e.id, c.name;

-- Детализация закупок (для отчётов)
CREATE VIEW PurchaseDetails AS
SELECT 
    p.id,
    e.name AS equipment,
    s.address AS supplier,
    w.address AS warehouse,
    p.quantity,
    p.price_per_unit,
    p.quantity * p.price_per_unit AS total_cost,
    p.purchase_date,
    p.notes
FROM Purchase p
JOIN Equipment e ON p.equipment_id = e.id
JOIN Supplier s ON p.supplier_id = s.id
JOIN Warehouse w ON p.warehouse_id = w.id;

-- Детализация списаний
CREATE VIEW WriteOffDetails AS
SELECT 
    wo.id,
    e.name AS equipment,
    emp.full_name AS employee,
    wo.quantity,
    wo.reason,
    wo.write_off_date
FROM WriteOff wo
JOIN Equipment e ON wo.equipment_id = e.id
JOIN Employee emp ON wo.employee_id = emp.id;