---

CREATE DATABASE ComputerPark_ISP3241k;
GO
USE ComputerPark_ISP3241k;
GO

CREATE TABLE Employees (
    EmployeeID  INT IDENTITY(1,1) PRIMARY KEY,
    FullName    NVARCHAR(150) NOT NULL,
    Position    NVARCHAR(100),
    Department  NVARCHAR(100),
    Office      NVARCHAR(20),
    Phone       NVARCHAR(30),
    Email       NVARCHAR(100)
);

CREATE TABLE EquipmentTypes (
    TypeID  INT IDENTITY(1,1) PRIMARY KEY,
    Name    NVARCHAR(50) NOT NULL UNIQUE   -- Laptop, System Unit, Monitor, Printer, UPS
);

CREATE TABLE EquipmentModels (
    ModelID        INT IDENTITY(1,1) PRIMARY KEY,
    TypeID         INT NOT NULL REFERENCES EquipmentTypes(TypeID),
    Manufacturer   NVARCHAR(50) NOT NULL,
    ModelName      NVARCHAR(100) NOT NULL,
    Specifications NVARCHAR(500)
);

CREATE TABLE Suppliers (
    SupplierID    INT IDENTITY(1,1) PRIMARY KEY,
    Name          NVARCHAR(150) NOT NULL,
    ContactPerson NVARCHAR(100),
    Phone         NVARCHAR(30),
    Email         NVARCHAR(100)
);

CREATE TABLE Equipment (
    EquipmentID          INT IDENTITY(1,1) PRIMARY KEY,
    InventoryNumber      NVARCHAR(30) NOT NULL UNIQUE,
    SerialNumber         NVARCHAR(50),
    TypeID               INT NOT NULL REFERENCES EquipmentTypes(TypeID),
    ModelID              INT NOT NULL REFERENCES EquipmentModels(ModelID),
    SupplierID           INT NOT NULL REFERENCES Suppliers(SupplierID),
    PurchaseDate         DATE NOT NULL,
    WarrantyStartDate    DATE,
    WarrantyEndDate      DATE,
    Cost                 DECIMAL(12,2) NOT NULL CHECK (Cost >= 0),
    Status               NVARCHAR(20) NOT NULL DEFAULT 'In Stock'
        CHECK (Status IN ('In Use', 'In Stock', 'In Repair', 'Written Off')),
    CurrentOwnerID       INT NULL REFERENCES Employees(EmployeeID),  -- NULL if in stock
    CONSTRAINT CK_Warranty CHECK (WarrantyEndDate IS NULL
        OR WarrantyEndDate >= WarrantyStartDate)
);

CREATE TABLE Maintenance (
    RecordID          INT IDENTITY(1,1) PRIMARY KEY,
    EquipmentID       INT NOT NULL REFERENCES Equipment(EquipmentID),
    RequestDate       DATE NOT NULL,
    CompletionDate    DATE NULL,
    ProblemDescription NVARCHAR(500),
    WorkPerformed     NVARCHAR(500),
    WorkCost          DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (WorkCost >= 0),
    PerformerID       INT NULL REFERENCES Employees(EmployeeID),  -- in-house sysadmin
    ExternalPerformer NVARCHAR(150) NULL,                          -- external company
    CONSTRAINT CK_MaintenanceDates CHECK (CompletionDate IS NULL
        OR CompletionDate >= RequestDate)
);

CREATE TABLE Components (
    ComponentID      INT IDENTITY(1,1) PRIMARY KEY,
    SystemUnitID     INT NOT NULL REFERENCES Equipment(EquipmentID),
    ComponentType    NVARCHAR(30) NOT NULL
        CHECK (ComponentType IN ('CPU', 'RAM', 'SSD', 'HDD', 'GPU', 'Other')),
    Model            NVARCHAR(100),
    SerialNumber     NVARCHAR(50),
    InstallationDate DATE
);
GO

-- Indexes on foreign keys
CREATE INDEX IX_Equipment_Owner      ON Equipment(CurrentOwnerID);
CREATE INDEX IX_Equipment_Status     ON Equipment(Status);
CREATE INDEX IX_Maintenance_Equip    ON Maintenance(EquipmentID);
CREATE INDEX IX_Components_Unit      ON Components(SystemUnitID);
GO

-- Views for management
CREATE VIEW v_EquipmentByStatus AS
SELECT Status, COUNT(*) AS Quantity, SUM(Cost) AS TotalCost
FROM Equipment
GROUP BY Status;
GO

CREATE VIEW v_MaintenanceCosts AS
SELECT e.EquipmentID, e.InventoryNumber, m.ModelName,
       COUNT(s.RecordID) AS RequestCount,
       ISNULL(SUM(s.WorkCost), 0) AS TotalSpent
FROM Equipment e
JOIN EquipmentModels m ON m.ModelID = e.ModelID
LEFT JOIN Maintenance s ON s.EquipmentID = e.EquipmentID
GROUP BY e.EquipmentID, e.InventoryNumber, m.ModelName;
GO

CREATE VIEW v_ExpiringWarranty AS
SELECT e.InventoryNumber, t.Name AS EquipmentType, m.ModelName,
       e.WarrantyEndDate,
       DATEDIFF(DAY, GETDATE(), e.WarrantyEndDate) AS DaysLeft,
       emp.FullName AS Owner
FROM Equipment e
JOIN EquipmentTypes t ON t.TypeID = e.TypeID
JOIN EquipmentModels m ON m.ModelID = e.ModelID
LEFT JOIN Employees emp ON emp.EmployeeID = e.CurrentOwnerID
WHERE e.Status <> 'Written Off'
  AND e.WarrantyEndDate BETWEEN GETDATE() AND DATEADD(DAY, 90, GETDATE());
GO