---

-- ============================================================
-- 1. БАЗОВЫЕ СПРАВОЧНИКИ
-- ============================================================

CREATE TABLE Countries (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    CountryName NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Positions (
    ID           INT IDENTITY(1,1) PRIMARY KEY,
    PositionName NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE WeaponTypes (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    TypeName    NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Calibers (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    CaliberName NVARCHAR(20) NOT NULL UNIQUE
);

-- ============================================================
-- 2. СУЩНОСТИ 2-ГО УРОВНЯ
-- ============================================================

CREATE TABLE Manufacturers (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ManufacturerName    NVARCHAR(100) NOT NULL,
    CountryID           INT           NOT NULL,
    CONSTRAINT FK_Manufacturers_Country FOREIGN KEY (CountryID) REFERENCES Countries(ID)
);

CREATE TABLE Suppliers (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    SupplierName    NVARCHAR(100) NOT NULL,
    CountryID       INT           NOT NULL,
    ContactPhone    NVARCHAR(20)  NOT NULL,
    CONSTRAINT FK_Suppliers_Country FOREIGN KEY (CountryID) REFERENCES Countries(ID)
);

CREATE TABLE Employees (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    FirstName       NVARCHAR(50)  NOT NULL,
    LastName        NVARCHAR(50)  NOT NULL,
    PositionID      INT           NOT NULL,
    EmployeeNumber  NVARCHAR(20)  NOT NULL UNIQUE,
    HireDate        DATE          NOT NULL,
    CONSTRAINT FK_Employees_Position FOREIGN KEY (PositionID) REFERENCES Positions(ID)
);

-- ============================================================
-- 3. ОСНОВНЫЕ СУЩНОСТИ И СКЛАД
-- ============================================================

CREATE TABLE Weapons (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    ModelName       NVARCHAR(100)   NOT NULL,
    TypeID          INT             NOT NULL,
    ManufacturerID  INT             NOT NULL,
    CaliberID       INT             NOT NULL,
    CONSTRAINT FK_Weapons_Type         FOREIGN KEY (TypeID)         REFERENCES WeaponTypes(ID),
    CONSTRAINT FK_Weapons_Manufacturer FOREIGN KEY (ManufacturerID) REFERENCES Manufacturers(ID),
    CONSTRAINT FK_Weapons_Caliber      FOREIGN KEY (CaliberID)      REFERENCES Calibers(ID)
);

-- НОВАЯ ТАБЛИЦА: СКЛАД (ТЕКУЩИЕ ОСТАТКИ)
CREATE TABLE Stock (
    WeaponID INT PRIMARY KEY,
    Quantity INT NOT NULL DEFAULT 0 CHECK (Quantity >= 0),
    CONSTRAINT FK_Stock_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);

CREATE TABLE PriceHistory (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    WeaponID        INT             NOT NULL,
    Price           DECIMAL(10,2)   NOT NULL CHECK (Price > 0),
    EffectiveDate   DATE            NOT NULL,
    CONSTRAINT FK_PriceHistory_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);

CREATE TABLE Clients (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    FirstName       NVARCHAR(50)  NOT NULL,
    LastName        NVARCHAR(50)  NOT NULL,
    PassportNumber  NVARCHAR(20)  NOT NULL UNIQUE,
    Phone           NVARCHAR(20)  NOT NULL
);

CREATE TABLE Licenses (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    ClientID        INT             NOT NULL,
    LicenseNumber   NVARCHAR(30)    NOT NULL UNIQUE,
    IssueDate       DATE            NOT NULL,
    ExpiryDate      DATE            NOT NULL,
    CONSTRAINT FK_Licenses_Client FOREIGN KEY (ClientID) REFERENCES Clients(ID),
    CONSTRAINT CHK_LicenseDates CHECK (ExpiryDate > IssueDate)
);

-- ============================================================
-- 4. ОПЕРАЦИИ: ПОСТАВКИ
-- ============================================================

CREATE TABLE Deliveries (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    SupplierID          INT             NOT NULL,
    EmployeeID          INT             NOT NULL,
    DeliveryDate        DATE            NOT NULL,
    CONSTRAINT FK_Deliveries_Supplier FOREIGN KEY (SupplierID) REFERENCES Suppliers(ID),
    CONSTRAINT FK_Deliveries_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

CREATE TABLE DeliveryDetails (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    DeliveryID          INT             NOT NULL,
    WeaponID            INT             NOT NULL,
    Quantity            INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice           DECIMAL(10,2)   NOT NULL CHECK (UnitPrice > 0),
    CONSTRAINT FK_DeliveryDetails_Delivery FOREIGN KEY (DeliveryID) REFERENCES Deliveries(ID) ON DELETE CASCADE,
    CONSTRAINT FK_DeliveryDetails_Weapon   FOREIGN KEY (WeaponID)   REFERENCES Weapons(ID)
);

-- ============================================================
-- 5. ОПЕРАЦИИ: ПРОДАЖИ
-- ============================================================

CREATE TABLE Sales (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    ClientID    INT     NOT NULL,
    LicenseID   INT     NOT NULL,
    EmployeeID  NOT NULL,
    SaleDate    DATE    NOT NULL,
    CONSTRAINT FK_Sales_Client   FOREIGN KEY (ClientID)   REFERENCES Clients(ID),
    CONSTRAINT FK_Sales_License  FOREIGN KEY (LicenseID)  REFERENCES Licenses(ID),
    CONSTRAINT FK_Sales_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

CREATE TABLE SaleDetails (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    SaleID      INT     NOT NULL,
    WeaponID    INT     NOT NULL,
    Quantity    INT     NOT NULL CHECK (Quantity > 0),
    SalePrice   DECIMAL(10,2) NOT NULL CHECK (SalePrice > 0),
    CONSTRAINT FK_SaleDetails_Sale   FOREIGN KEY (SaleID)   REFERENCES Sales(ID) ON DELETE CASCADE,
    CONSTRAINT FK_SaleDetails_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);
GO

-- ============================================================
-- ТРИГГЕРЫ ДЛЯ АВТОМАТИЗАЦИИ СКЛАДА (STOCK)
-- ============================================================

-- 1. Триггер на приход товара: увеличивает остаток в таблице Stock
CREATE TRIGGER trg_DeliveryDetails_UpdateStock
ON DeliveryDetails
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Обновляем, если запись об оружии уже есть на складе
    UPDATE s
    SET s.Quantity = s.Quantity + i.Quantity
    FROM Stock s
    JOIN inserted i ON s.WeaponID = i.WeaponID;

    -- Добавляем, если этой модели оружия еще ни разу не было на складе
    INSERT INTO Stock (WeaponID, Quantity)
    SELECT i.WeaponID, i.Quantity
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM Stock s WHERE s.WeaponID = i.WeaponID);
END;
GO

-- 2. Триггер на продажу товара: проверяет наличие и уменьшает остаток
CREATE TRIGGER trg_SaleDetails_UpdateStock
ON SaleDetails
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Проверка: хватает ли товара?
    IF EXISTS (
        SELECT 1
        FROM inserted i
        LEFT JOIN Stock s ON i.WeaponID = s.WeaponID
        WHERE ISNULL(s.Quantity, 0) < i.Quantity
    )
    BEGIN
        RAISERROR('Ошибка: Недостаточно товара на складе для совершения продажи.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Если всё ок, списываем количество
    UPDATE s
    SET s.Quantity = s.Quantity - i.Quantity
    FROM Stock s
    JOIN inserted i ON s.WeaponID = i.WeaponID;
END;
GO

-- ============================================================
-- ЗАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ
-- ============================================================

-- Справочники
INSERT INTO Countries (CountryName) VALUES ('Россия'), ('Австрия'), ('США'), ('Италия'), ('Германия');
INSERT INTO Positions (PositionName) VALUES ('Продавец-консультант'), ('Кладовщик');
INSERT INTO WeaponTypes (TypeName) VALUES ('Пистолет'), ('Винтовка'), ('Автомат');
INSERT INTO Calibers (CaliberName) VALUES ('5.45x39'), ('9x19'), ('12/76');

-- Производители
INSERT INTO Manufacturers (ManufacturerName, CountryID) VALUES 
('Kalashnikov Concern', 1), 
('Glock GmbH', 2);

-- Поставщики и сотрудники
INSERT INTO Suppliers (SupplierName, CountryID, ContactPhone) VALUES ('АрмТорг', 1, '+7-495-111');
INSERT INTO Employees (FirstName, LastName, PositionID, EmployeeNumber, HireDate) VALUES ('Иван', 'Петров', 1, 'EMP-001', '2020-03-15');

-- Оружие
INSERT INTO Weapons (ModelName, TypeID, ManufacturerID, CaliberID) VALUES 
('АК-74', 3, 1, 1), 
('Glock 17', 1, 2, 2);

-- Клиенты
INSERT INTO Clients (FirstName, LastName, PassportNumber, Phone) VALUES ('Андрей', 'Волков', 'MD123456', '+373-69');
INSERT INTO Licenses (ClientID, LicenseNumber, IssueDate, ExpiryDate) VALUES (1, 'ЛИЦ-001', '2023-01-15', '2026-01-15');

-- ПОСТАВКА (Триггер сам добавит данные в таблицу Stock)
INSERT INTO Deliveries (SupplierID, EmployeeID, DeliveryDate) VALUES (1, 1, '2024-01-10');
INSERT INTO DeliveryDetails (DeliveryID, WeaponID, Quantity, UnitPrice) VALUES 
(1, 1, 10, 32000.00), -- Приехало 10 АК-74
(1, 2, 5, 25000.00);  -- Приехало 5 Глоков

-- ПРОДАЖА (Триггер проверит остаток в Stock и уменьшит его)
INSERT INTO Sales (ClientID, LicenseID, EmployeeID, SaleDate) VALUES (1, 1, 1, '2024-02-01');
INSERT INTO SaleDetails (SaleID, WeaponID, Quantity, SalePrice) VALUES 
(1, 1, 2, 45000.00); -- Продали 2 АК-74 (Останется 8)

-- ПРОВЕРКА СКЛАДА
SELECT * FROM Stock;