---

-- ============================================================
-- 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 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), -- Цена закупа 1 штуки
    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  INT     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), -- Фиксируем цену продажи за 1 шт
    CONSTRAINT FK_SaleDetails_Sale   FOREIGN KEY (SaleID)   REFERENCES Sales(ID) ON DELETE CASCADE,
    CONSTRAINT FK_SaleDetails_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);
GO

-- ============================================================
-- ПРЕДСТАВЛЕНИЯ И ТРИГГЕРЫ (УЧЕТ ОСТАТКОВ)
-- ============================================================

-- Динамический расчет остатков на основе таблиц деталей
CREATE VIEW v_CurrentStock AS
SELECT 
    w.ID AS WeaponID,
    w.ModelName,
    ISNULL(d.DeliveredQty, 0) - ISNULL(s.SoldQty, 0) AS CurrentStock
FROM Weapons w
LEFT JOIN (SELECT WeaponID, SUM(Quantity) AS DeliveredQty FROM DeliveryDetails GROUP BY WeaponID) d ON w.ID = d.WeaponID
LEFT JOIN (SELECT WeaponID, SUM(Quantity) AS SoldQty FROM SaleDetails GROUP BY WeaponID) s ON w.ID = s.WeaponID;
GO

-- Триггер: Запрет продажи в минус (переписан под таблицу SaleDetails)
CREATE TRIGGER trg_SaleDetails_CheckStock
ON SaleDetails
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN v_CurrentStock v ON i.WeaponID = v.WeaponID
        WHERE v.CurrentStock < 0 
    )
    BEGIN
        RAISERROR('Недостаточно товара на складе для совершения продажи. Транзакция отменена.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
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'), ('.357 Mag'), ('12/76');

-- Заполняем производителей (ссылки на страны)
-- 1-РФ, 2-Австрия, 3-США, 4-Италия, 5-Германия, 6-Сербия, 7-Молдова
INSERT INTO Manufacturers (ManufacturerName, CountryID) VALUES
('Kalashnikov Concern',  1),
('Glock GmbH',          2),
('Smith & Wesson',      3),
('Beretta',             4),
('Mossberg',            3);

-- Поставщики
INSERT INTO Suppliers (SupplierName, CountryID, ContactPhone) VALUES
('АрмТорг',         1, '+7-495-111-22-33'),
('EuroArms Ltd',    5, '+49-30-555-66-77'),
('USDefense Co',    3, '+1-800-333-44-55');

-- Сотрудники (ссылки на должности)
INSERT INTO Employees (FirstName, LastName, PositionID, EmployeeNumber, HireDate) VALUES
('Иван', 'Петров', 1, 'EMP-001', '2020-03-15'),
('Алексей', 'Сидоров', 3, 'EMP-003', '2021-01-10');

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

-- Клиенты и лицензии
INSERT INTO Clients (FirstName, LastName, PassportNumber, Phone) VALUES
('Андрей', 'Волков', 'MD123456', '+373-69-111-111');

INSERT INTO Licenses (ClientID, LicenseNumber, IssueDate, ExpiryDate) VALUES
(1, 'ЛИЦ-2023-001', '2023-01-15', '2026-01-15');

-- Создаем 1 поставку (Заголовок)
INSERT INTO Deliveries (SupplierID, EmployeeID, DeliveryDate) VALUES (1, 2, '2024-01-10');
-- Наполняем эту поставку товарами (Детали - ID поставки = 1)
INSERT INTO DeliveryDetails (DeliveryID, WeaponID, Quantity, UnitPrice) VALUES
(1, 1, 10, 32000.00), -- 10 автоматов по 32к
(1, 2, 5,  25000.00); -- 5 глоков по 25к

-- Создаем 1 продажу (Заголовок)
INSERT INTO Sales (ClientID, LicenseID, EmployeeID, SaleDate) VALUES (1, 1, 1, '2024-02-01');
-- Клиент покупает сразу два разных товара в одном чеке! (Детали - ID продажи = 1)
INSERT INTO SaleDetails (SaleID, WeaponID, Quantity, SalePrice) VALUES
(1, 1, 2, 45000.00), -- Купил 2 автомата
(1, 2, 1, 35000.00); -- Купил 1 глок