SQL & DDL: DDL FINAL SKRIPTA.txt

-- Drop tables and views if exist
DROP TABLE IF EXISTS aktivniprodukti CASCADE;
DROP VIEW IF EXISTS aktivniprodukti CASCADE;

-- Customer
CREATE TABLE customer (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  CustomerSurName VARCHAR(100),
  Email VARCHAR(100),
  Address VARCHAR(255),
  CustomerContact VARCHAR(20),
  Password VARCHAR(255) NOT NULL
);

-- CustomerFeedback
CREATE TABLE customerfeedback (
  FeedbackID INT PRIMARY KEY,
  CustomerID INT,
  ProductID INT,
  Rating INT CHECK (Rating BETWEEN 1 AND 5),
  Comment TEXT,
  FeedbackDate DATE,
  TransactionID INT,
  CONSTRAINT customerfeedback_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
  CONSTRAINT customerfeedback_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
  CONSTRAINT customerfeedback_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
);

-- Employee
CREATE TABLE employee (
  EmployeeID INT PRIMARY KEY,
  EmployeeName VARCHAR(100),
  EmployeeSurName VARCHAR(100),
  Position VARCHAR(100),
  Department VARCHAR(100),
  Email VARCHAR(100),
  Password VARCHAR(100)
);

-- Maintenance
CREATE TABLE maintenance (
  MainID INT PRIMARY KEY,
  EmployeeID INT,
  ProductID INT,
  MainDate DATE,
  Description TEXT,
  Cost NUMERIC(10,2),
  Status VARCHAR(10) DEFAULT 'Pending',
  StartTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  EndTime TIMESTAMP,
  CONSTRAINT maintenance_status_check CHECK (Status IN ('Pending', 'Completed')),
  CONSTRAINT maintenance_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
  CONSTRAINT maintenance_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
);

-- Procurement
CREATE TABLE procurement (
  TransactionID INT PRIMARY KEY,
  EmployeeID INT,
  CustomerID INT,
  ProductID INT,
  ProcurementDate DATE,
  Quantity INT,
  Status VARCHAR(10) DEFAULT 'Pending',
  Notified BOOLEAN DEFAULT FALSE,
  GroupID VARCHAR(255),
  CONSTRAINT procurement_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
  CONSTRAINT procurement_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
  CONSTRAINT procurement_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
  CONSTRAINT procurement_status_check CHECK (Status IN ('Pending', 'Approved', 'Rejected'))
);

-- Product
CREATE TABLE product (
  ProductID INT PRIMARY KEY,
  Model VARCHAR(100),
  Price NUMERIC(10,2),
  LicensePlate VARCHAR(50),
  Status VARCHAR(15) NOT NULL,
  CONSTRAINT product_status_check CHECK (Status IN ('available', 'sold', 'rented', 'maintenance'))
);

-- T_Type
CREATE TABLE t_type (
  TransactionID INT PRIMARY KEY,
  Type VARCHAR(10) NOT NULL,
  Duration INT,
  MonthlyPay NUMERIC(10,2),
  TotalPrice NUMERIC(10,2),
  CONSTRAINT t_type_type_check CHECK (Type IN ('Rent', 'Buy')),
  CONSTRAINT t_type_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
);

-- Trailer
CREATE TABLE trailer (
  ProductID INT PRIMARY KEY,
  Capacity NUMERIC(10,2),
  CONSTRAINT trailer_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
);

-- Truck
CREATE TABLE truck (
  ProductID INT PRIMARY KEY,
  HP INT,
  CONSTRAINT truck_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
);

-- Views (Customer views product)
CREATE TABLE views (
  CustomerID INT NOT NULL,
  ProductID INT NOT NULL,
  PRIMARY KEY (CustomerID, ProductID),
  CONSTRAINT views_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
  CONSTRAINT views_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
);

-- Wallet
CREATE TABLE wallet (
  WalletID SERIAL PRIMARY KEY,
  CustomerID INT NOT NULL,
  Balance NUMERIC(10,2) NOT NULL DEFAULT 0.00,
  CardNumber VARCHAR(16),
  ExpiryDate DATE,
  CVV VARCHAR(4),
  CardHolderName VARCHAR(100),
  CONSTRAINT wallet_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID)
);

-- View aktivniprodukti (PostgreSQL syntax)
CREATE OR REPLACE VIEW aktivniprodukti AS
SELECT product.ProductID,
       product.Model,
       product.Price,
       product.LicensePlate
FROM product
WHERE product.Status = 'available';