---

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


==((ПРАВКИ))

INSERT INTO Manufacturers (ManufacturerName, CountryID)
VALUES
(N'Benelli', 4),
(N'FN Herstal', 3),
(N'Accuracy International', 5);

INSERT INTO Weapons (ModelName, TypeID, ManufacturerID, CaliberID)
VALUES
(N'Glock-18',   1, 2, 2), -- Пистолет
(N'AWP',        2, 3, 1), -- Винтовка
(N'AK-47',      3, 1, 1), -- Автомат
(N'XM1014',     4, 4, 3), -- Дробовик
(N'M249',       5, 5, 1), -- Пулемет
(N'Drone Unit', 6, 6, 2), -- Дроны
(N'HE Grenade', 7, 7, 3); -- Граната




CREATE TABLE Characteristics (
    ID                      INT IDENTITY(1,1) PRIMARY KEY,
    WeaponID                INT            NOT NULL,
    CharacteristicName      NVARCHAR(100)  NOT NULL,
    CharacteristicValue     NVARCHAR(100)  NOT NULL,
    CONSTRAINT FK_Characteristics_Weapon
        FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);


INSERT INTO Characteristics (WeaponID, CharacteristicName, CharacteristicValue)
SELECT w.ID, v.CharacteristicName, v.CharacteristicValue
FROM Weapons w
JOIN (VALUES
    -- Glock-18
    (N'Glock-18', N'Модель',                          N'Glock-18'),
    (N'Glock-18', N'Длина (мм)',                       N'186'),
    (N'Glock-18', N'Прицельная дальность (м)',         N'20'),
    (N'Glock-18', N'Масса (кг)',                       N'0.65'),
    (N'Glock-18', N'Тип ствола',                       N'Нарезной'),
    (N'Glock-18', N'Скорострельность (выстр/мин)',     N'1200 (burst)'),
    (N'Glock-18', N'Урон (тело)',                      N'30'),
    (N'Glock-18', N'Режим огня',                       N'Одиночный/очереди по 3'),

    -- AWP
    (N'AWP', N'Модель',                                N'AWP'),
    (N'AWP', N'Длина (мм)',                            N'1100'),
    (N'AWP', N'Прицельная дальность (м)',              N'2000+'),
    (N'AWP', N'Масса (кг)',                            N'6.6'),
    (N'AWP', N'Тип ствола',                            N'Нарезной'),
    (N'AWP', N'Скорострельность (выстр/мин)',          N'41'),
    (N'AWP', N'Урон (тело)',                           N'115'),
    (N'AWP', N'Режим огня',                            N'Одиночный'),

    -- AK-47
    (N'AK-47', N'Модель',                              N'AK-47'),
    (N'AK-47', N'Длина (мм)',                          N'943'),
    (N'AK-47', N'Прицельная дальность (м)',            N'400'),
    (N'AK-47', N'Масса (кг)',                          N'4.78'),
    (N'AK-47', N'Тип ствола',                          N'Нарезной'),
    (N'AK-47', N'Скорострельность (выстр/мин)',        N'600'),
    (N'AK-47', N'Урон (тело)',                         N'36'),
    (N'AK-47', N'Режим огня',                          N'Автоматический/одиночный'),

    -- XM1014
    (N'XM1014', N'Модель',                             N'XM1014'),
    (N'XM1014', N'Длина (мм)',                         N'1010'),
    (N'XM1014', N'Прицельная дальность (м)',           N'3'),
    (N'XM1014', N'Масса (кг)',                         N'3.8'),
    (N'XM1014', N'Тип ствола',                         N'Гладкоствольный'),
    (N'XM1014', N'Скорострельность (выстр/мин)',       N'171'),
    (N'XM1014', N'Урон (тело)',                        N'20'),
    (N'XM1014', N'Режим огня',                         N'Полуавтоматический'),

    -- M249
    (N'M249', N'Модель',                               N'M249'),
    (N'M249', N'Длина (мм)',                           N'1030'),
    (N'M249', N'Прицельная дальность (м)',             N'1800'),
    (N'M249', N'Масса (кг)',                           N'10'),
    (N'M249', N'Тип ствола',                           N'Нарезной'),
    (N'M249', N'Скорострельность (выстр/мин)',         N'750'),
    (N'M249', N'Урон (тело)',                          N'32'),
    (N'M249', N'Режим огня',                           N'Автоматический'),

    -- Drone Unit (осмысленные характеристики)
    (N'Drone Unit', N'Модель',                         N'Drone Unit'),
    (N'Drone Unit', N'Масса (кг)',                     N'0.8'),
    (N'Drone Unit', N'Дальность связи (м)',            N'500'),
    (N'Drone Unit', N'Время полёта (мин)',             N'15'),
    (N'Drone Unit', N'Тип управления',                 N'Дистанционное'),
    (N'Drone Unit', N'Полезная нагрузка',              N'Есть'),
    (N'Drone Unit', N'Режим полёта',                   N'Автономный/ручной'),
    (N'Drone Unit', N'Скорость (км/ч)',                N'60'),

    -- HE Grenade (без "длины ствола" ??)
    (N'HE Grenade', N'Модель',                         N'HE Grenade'),
    (N'HE Grenade', N'Масса (кг)',                     N'0.4'),
    (N'HE Grenade', N'Радиус поражения (м)',           N'5'),
    (N'HE Grenade', N'Урон (тело)',                    N'115 (взрыв)'),
    (N'HE Grenade', N'Тип боеприпаса',                 N'Осколочно-фугасный'),
    (N'HE Grenade', N'Время задержки (с)',             N'3'),
    (N'HE Grenade', N'Начальная скорость',             N'Бросок рукой'),
    (N'HE Grenade', N'Режим применения',               N'Одноразовый')

) v(ModelName, CharacteristicName, CharacteristicValue)
ON w.ModelName = v.ModelName;




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)
);

-- ИСПРАВЛЕНИЕ 1: добавлен суррогатный первичный ключ ID,
-- WeaponID сохранён как UNIQUE NOT NULL (гарантирует 1:1 с Weapons)
CREATE TABLE Stock (
    ID       INT IDENTITY(1,1) PRIMARY KEY,        -- добавлен ID
    WeaponID INT NOT NULL UNIQUE,                   -- уникальность сохранена
    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)
);

-- Вьюшка для получения актуальной цены (последняя запись по дате)
GO
CREATE VIEW vw_CurrentPrice AS
    SELECT w.ID AS WeaponID, w.ModelName, ph.Price, ph.EffectiveDate
    FROM Weapons w
    CROSS APPLY (
        SELECT TOP 1 Price, EffectiveDate
        FROM PriceHistory
        WHERE WeaponID = w.ID
        ORDER BY EffectiveDate DESC
    ) ph;
GO

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. ОПЕРАЦИИ: ПРОДАЖИ
-- ============================================================

-- ИСПРАВЛЕНИЕ 2: добавлен тип INT для EmployeeID (был синтаксическая ошибка)
CREATE TABLE Sales (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    ClientID    INT     NOT NULL,
    LicenseID   INT     NOT NULL,
    EmployeeID  INT     NOT NULL,    -- исправлено: добавлен тип данных INT
    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

-- ============================================================
-- ИНДЕКСЫ НА ВНЕШНИЕ КЛЮЧИ
-- (SQL Server не создаёт их автоматически, в отличие от MySQL)
-- ============================================================

CREATE INDEX IX_Manufacturers_CountryID    ON Manufacturers(CountryID);
CREATE INDEX IX_Suppliers_CountryID        ON Suppliers(CountryID);
CREATE INDEX IX_Employees_PositionID       ON Employees(PositionID);
CREATE INDEX IX_Weapons_TypeID             ON Weapons(TypeID);
CREATE INDEX IX_Weapons_ManufacturerID     ON Weapons(ManufacturerID);
CREATE INDEX IX_Weapons_CaliberID          ON Weapons(CaliberID);
CREATE INDEX IX_PriceHistory_WeaponID      ON PriceHistory(WeaponID);
CREATE INDEX IX_Licenses_ClientID          ON Licenses(ClientID);
CREATE INDEX IX_Deliveries_SupplierID      ON Deliveries(SupplierID);
CREATE INDEX IX_Deliveries_EmployeeID      ON Deliveries(EmployeeID);
CREATE INDEX IX_DeliveryDetails_DeliveryID ON DeliveryDetails(DeliveryID);
CREATE INDEX IX_DeliveryDetails_WeaponID   ON DeliveryDetails(WeaponID);
CREATE INDEX IX_Sales_ClientID             ON Sales(ClientID);
CREATE INDEX IX_Sales_LicenseID            ON Sales(LicenseID);
CREATE INDEX IX_Sales_EmployeeID           ON Sales(EmployeeID);
CREATE INDEX IX_SaleDetails_SaleID         ON SaleDetails(SaleID);
CREATE INDEX IX_SaleDetails_WeaponID       ON SaleDetails(WeaponID);
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
        -- ИСПРАВЛЕНИЕ 3: THROW вместо устаревшего RAISERROR
        THROW 50001, N'Ошибка: недостаточно товара на складе для совершения продажи.', 1;
    END;

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

-- ============================================================
-- ТРИГГЕР: ПРОВЕРКА ПРИНАДЛЕЖНОСТИ ЛИЦЕНЗИИ КЛИЕНТУ
-- ИСПРАВЛЕНИЕ 4: лицензия должна принадлежать клиенту из той же продажи
-- ============================================================

CREATE TRIGGER trg_Sales_CheckLicenseOwner
ON Sales
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Licenses l ON i.LicenseID = l.ID
        WHERE l.ClientID <> i.ClientID
    )
    BEGIN
        THROW 50002, N'Ошибка: лицензия не принадлежит указанному клиенту.', 1;
    END;
END;
GO

-- ============================================================
-- ТРИГГЕР: ПРОВЕРКА СРОКА ДЕЙСТВИЯ ЛИЦЕНЗИИ ПРИ ПРОДАЖЕ
-- ИСПРАВЛЕНИЕ 5: нельзя продавать по просроченной лицензии
-- ============================================================

CREATE TRIGGER trg_Sales_CheckLicenseExpiry
ON Sales
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Licenses l ON i.LicenseID = l.ID
        WHERE l.ExpiryDate < i.SaleDate
    )
    BEGIN
        THROW 50003, N'Ошибка: срок действия лицензии истёк.', 1;
    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'), ('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 PriceHistory (WeaponID, Price, EffectiveDate) VALUES
(1, 30000.00, '2023-01-01'),
(1, 32000.00, '2024-01-01'),
(2, 22000.00, '2023-01-01'),
(2, 25000.00, '2024-01-01');

-- Клиенты
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 Glock 17

-- Продажа (триггер проверит остаток, принадлежность лицензии и её срок)
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 w.ModelName, s.Quantity
FROM Stock s
JOIN Weapons w ON s.WeaponID = w.ID;

-- Актуальные цены (через вьюшку)
SELECT * FROM vw_CurrentPrice;

-- Продажи с данными клиента и лицензии
SELECT 
    sa.ID AS SaleID,
    sa.SaleDate,
    c.FirstName + ' ' + c.LastName AS Client,
    l.LicenseNumber,
    l.ExpiryDate,
    w.ModelName,
    sd.Quantity,
    sd.SalePrice
FROM Sales sa
JOIN Clients c    ON sa.ClientID  = c.ID
JOIN Licenses l   ON sa.LicenseID = l.ID
JOIN SaleDetails sd ON sa.ID      = sd.SaleID
JOIN Weapons w    ON sd.WeaponID  = w.ID;