OZE - tylko tworzenie tabel

-- 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),
    device_power DECIMAL(18,0),
    company_id INT
);


-- Tworzenie tabeli zgłoszeń awarii

CREATE TABLE Device_Failures (
    failure_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    shift_number INT,
    failure_start DATETIME,
    failure_end DATETIME,
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

-- 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 zamówień

CREATE TABLE Customers_Orders (
    customer_order_id INT PRIMARY KEY,
    customer_id INT,
    customer_order_date_start DATETIME,
    customer_order_date_end DATETIME,
    customer_order_amount_of_energy DECIMAL(18,0),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);


-- Tworzenie tabeli firm serwisowych

CREATE TABLE Servicing_Companies (
    company_id INT PRIMARY KEY,
    name VARCHAR(255),
    working_hours VARCHAR(255)
);

-- Wstawianie danych do tabeli firm serwisowych

INSERT INTO Servicing_Companies (company_id, name, working_hours)
VALUES (1, 'Company Y', '6:00-14:00, 14:00-22:00, 22:00-6:00');


----------
ALTER TABLE dbo.Renewable_Energy_Devices
ADD CONSTRAINT FK_OZE_SC FOREIGN KEY (company_id) REFERENCES Servicing_Companies(company_id);
----------

-- Tworzenie tabeli zmian serwisowych

CREATE TABLE Servicing_Shifts (
    shift_number INT PRIMARY KEY IDENTITY,
    shift_date DATE,
    shift_type INT CHECK (shift_type >= 1 AND shift_type <= 3),
    start_time TIME,
    end_time TIME
);



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