oze2

-- Tworzenie tabeli klientów
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20),
    address VARCHAR(255)
);

-- Tworzenie tabeli urządzeń OZE
CREATE TABLE Renewable_Energy_Devices (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6),
    installation_date DATE,
    last_maintenance_date DATE
);

-- Tworzenie tabeli zgłoszeń awarii
CREATE TABLE Device_Failures (
    failure_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    failure_start DATETIME,
    failure_end DATETIME,
    failure_reason NVARCHAR(255),
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

-- Tworzenie tabeli napraw
CREATE TABLE Repairs (
    repair_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    repair_start DATETIME,
    repair_end DATETIME,
    repair_description NVARCHAR(255),
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

-- Tworzenie tabeli zmian serwisowych
CREATE TABLE Service_Shifts (
    shift_id INT PRIMARY KEY IDENTITY,
    shift_date DATE,
    shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
    start_time TIME,
    end_time TIME
);




-- Wstawianie 500 przykładowych danych do tabeli urządzeń OZE
DECLARE @Counter INT = 1;

WHILE @Counter <= 500
BEGIN
    DECLARE @DeviceId NVARCHAR(26) = CONCAT('Device_', @Counter);
    DECLARE @InstallationDate DATE = DATEADD(DAY, -RAND() * 365, GETDATE()); -- Losowa data instalacji z ostatniego roku
    DECLARE @LastMaintenanceDate DATE = DATEADD(DAY, -RAND() * 180, GETDATE()); -- Losowa data ostatniego przeglądu z ostatnich 6 miesięcy
    DECLARE @DeviceStatus INT = CAST(RAND() * 6 AS INT); -- Losowy status urządzenia

    INSERT INTO Renewable_Energy_Devices (device_id, device_status, installation_date, last_maintenance_date)
    VALUES (@DeviceId, @DeviceStatus, @InstallationDate, @LastMaintenanceDate);

    SET @Counter = @Counter + 1;
END;


-- Wstawianie danych do tabeli zmian serwisowych (poniedziałek - sobota)
INSERT INTO Service_Shifts (shift_date, shift_type, start_time, end_time) VALUES
('2024-04-01', 1, '06:00:00', '14:00:00'),
('2024-04-01', 2, '14:00:00', '22:00:00'),
('2024-04-01', 3, '22:00:00', '06:00:00'),
('2024-04-02', 1, '06:00:00', '14:00:00'),
('2024-04-02', 2, '14:00:00', '22:00:00'),
('2024-04-02', 3, '22:00:00', '06:00:00'),
('2024-04-03', 1, '06:00:00', '14:00:00'),
('2024-04-03', 2, '14:00:00', '22:00:00'),
('2024-04-03', 3, '22:00:00', '06:00:00'),
('2024-04-04', 1, '06:00:00', '14:00:00'),
('2024-04-04', 2, '14:00:00', '22:00:00'),
('2024-04-04', 3, '22:00:00', '06:00:00'),
('2024-04-05', 1, '06:00:00', '14:00:00'),
('2024-04-05', 2, '14:00:00', '22:00:00'),
('2024-04-05', 3, '22:00:00', '06:00:00'),
('2024-04-06', 1, '06:00:00', '14:00:00'),
('2024-04-06', 2, '14:00:00', '22:00:00'),
('2024-04-06', 3, '22:00:00', '06:00:00');

--############################################################################

-- Wstawianie 100 przykładowych danych do tabeli klientów
DECLARE @Counter INT = 1;

WHILE @Counter <= 100
BEGIN
    DECLARE @Name VARCHAR(255) = 'Customer' + CAST(@Counter AS VARCHAR);
    DECLARE @Email VARCHAR(255) = 'customer' + CAST(@Counter AS VARCHAR) + '@example.com';
    DECLARE @Phone VARCHAR(20) = '123-456-789' + CAST(@Counter AS VARCHAR);
    DECLARE @Address VARCHAR(255) = 'Address' + CAST(@Counter AS VARCHAR);

    INSERT INTO Customers (customer_id, name, email, phone, address)
    VALUES (@Counter, @Name, @Email, @Phone, @Address);

    SET @Counter = @Counter + 1;
END;

-- Wstawianie 1000 przykładowych danych do tabeli zgłoszeń awarii
DECLARE @Counter INT = 1;

WHILE @Counter <= 1000
BEGIN
    DECLARE @FailureStart DATETIME = DATEADD(DAY, -RAND() * 365, GETDATE()); -- Losowa data rozpoczęcia awarii z ostatniego roku
    DECLARE @FailureEnd DATETIME = DATEADD(HOUR, RAND() * 24, @FailureStart); -- Losowa data zakończenia awarii w ciągu 24 godzin od rozpoczęcia
    DECLARE @FailureReason NVARCHAR(255) = 'Failure reason ' + CAST(@Counter AS NVARCHAR); -- Powód awarii

    INSERT INTO Device_Failures (failure_start, failure_end, failure_reason)
    VALUES (@FailureStart, @FailureEnd, @FailureReason);

    SET @Counter = @Counter + 1;
END;



-- Wstawianie 1000 przykładowych danych do tabeli napraw bez uwzględnienia pola device_id
DECLARE @Counter INT = 1;

WHILE @Counter <= 1000
BEGIN
    DECLARE @RepairStart DATETIME = DATEADD(DAY, -RAND() * 365, GETDATE()); -- Losowa data rozpoczęcia naprawy z ostatniego roku
    DECLARE @RepairEnd DATETIME = DATEADD(HOUR, RAND() * 24, @RepairStart); -- Losowa data zakończenia naprawy w ciągu 24 godzin od rozpoczęcia
    DECLARE @RepairDescription NVARCHAR(255) = 'Repair description ' + CAST(@Counter AS NVARCHAR); -- Opis naprawy

    INSERT INTO Repairs (repair_start, repair_end, repair_description)
    VALUES (@RepairStart, @RepairEnd, @RepairDescription);

    SET @Counter = @Counter + 1;
END;








--#############################################################################

-- 1. Na jakiej zmianie dane urządzenie OZE uległo awarii
SELECT df.device_id, ss.shift_id
FROM Device_Failures df
INNER JOIN Service_Shifts ss ON df.failure_start BETWEEN CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.start_time
                               AND DATEADD(DAY, IIF(ss.start_time >= ss.end_time, 1, 0), CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.end_time)

-- 2. Na jakiej zmianie urządzenie OZE zostało naprawione
SELECT r.device_id, ss.shift_id
FROM Repairs r
INNER JOIN Service_Shifts ss ON r.repair_end BETWEEN CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.start_time
                               AND DATEADD(DAY, IIF(ss.start_time >= ss.end_time, 1, 0), CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.end_time)

-- 3. Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu
SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
GROUP BY df.device_id

-- 4. Jaki był czas przestoju danego urządzenia OZE w ciągu zadanego okresu czasu nie wliczając w to czasu kiedy firma serwisująca nie pracowała (weekendy)
SELECT df.device_id, SUM(DATEDIFF(HOUR, df.failure_start, df.failure_end)) AS downtime_hours
FROM Device_Failures df
INNER JOIN Service_Shifts ss ON df.failure_start BETWEEN CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.start_time
                               AND DATEADD(DAY, IIF(ss.start_time >= ss.end_time, 1, 0), CONVERT(DATETIME, CONVERT(DATE, ss.shift_date)) + ss.end_time)
WHERE DATEPART(WEEKDAY, df.failure_start)