CREATE TABLE customer (
  CustomerID integer PRIMARY KEY,
  CustomerName varchar(100),
  CustomerSurName varchar(100),
  Email varchar(100),
  Address varchar(255),
  CustomerContact varchar(20),
  Password varchar(255) NOT NULL
);

CREATE TABLE employee (
  EmployeeID integer PRIMARY KEY,
  EmployeeName varchar(100),
  EmployeeSurName varchar(100),
  Position varchar(100),
  Department varchar(100),
  Email varchar(100),
  Password varchar(100)
);

CREATE TABLE procurement (
  TransactionID integer PRIMARY KEY,
  EmployeeID integer,
  CustomerID integer,
  ProcurementDate date,
  Quantity integer,
  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)
);

CREATE TABLE wallet (
  WalletID serial PRIMARY KEY,
  CustomerID integer 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)
);

CREATE TABLE procurement_request (
  RequestID serial PRIMARY KEY,
  CustomerID integer NOT NULL,
  WalletID integer NOT NULL,
  ProcurementID integer NOT NULL,
  Quantity integer DEFAULT 1,
  RequestedAt timestamp DEFAULT CURRENT_TIMESTAMP,
  Status varchar(10) DEFAULT 'Pending',
  PaymentMethod varchar(20),
  PaymentStatus varchar(10) DEFAULT 'Reserved',
  MonthlyPay numeric(10,2),
  TotalPrice numeric(10,2),
  Duration integer,
  CardID integer,
  TransactionType varchar(10) DEFAULT 'Buy',
  GroupID varchar(36),
  CONSTRAINT procurement_request_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
  CONSTRAINT wallet_fk FOREIGN KEY (WalletID) REFERENCES wallet(WalletID),
  CONSTRAINT procurement_fk FOREIGN KEY (ProcurementID) REFERENCES procurement(TransactionID),
  CONSTRAINT procurement_request_status_check CHECK (Status IN ('Pending','Approved','Rejected')),
  CONSTRAINT procurement_request_paymentstatus_check CHECK (PaymentStatus IN ('Reserved','Charged','Refunded')),
  CONSTRAINT procurement_request_transactiontype_check CHECK (TransactionType IN ('Buy','Rent'))
);

CREATE TABLE product (
  ProductID integer PRIMARY KEY,
  ProcurementID integer NOT NULL,
  ProcurementRequestID integer NOT NULL,
  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')),
  CONSTRAINT procurement_fk FOREIGN KEY(ProcurementID) REFERENCES procurement(TransactionID), 
  CONSTRAINT procurement_request_fk FOREIGN KEY(ProcurementRequestID) REFERENCES procurement_request(RequestID) 
);

CREATE TABLE maintenance (
  MainID integer PRIMARY KEY,
  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'))
);

CREATE TABLE trailer (
  ProductID integer PRIMARY KEY,
  Capacity numeric(10,2),
  CONSTRAINT trailer_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID)
);

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

CREATE TABLE views (
  CustomerID integer NOT NULL,
  ProductID integer 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)
);

CREATE TABLE customerfeedback (
  FeedbackID integer PRIMARY KEY,
  CustomerID integer,
  Rating integer CHECK (Rating BETWEEN 1 AND 5),
  Comment text,
  FeedbackDate date,
  TransactionID integer,
  CONSTRAINT feedback_customer_fk FOREIGN KEY (CustomerID) REFERENCES customer(CustomerID),
  CONSTRAINT feedback_transaction_fk FOREIGN KEY (TransactionID) REFERENCES procurement(TransactionID)
);

CREATE TABLE t_type (
  TransactionID integer PRIMARY KEY,
  Type varchar(10) NOT NULL,
  Duration integer,
  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)
);


CREATE TABLE product_maintenance (
  ProductID integer NOT NULL,
  MaintenanceID integer NOT NULL,
  CONSTRAINT product_maintenance_id PRIMARY KEY (ProductID, MaintenanceID),
  CONSTRAINT product_maintenance_product_fk FOREIGN KEY (ProductID) REFERENCES product(ProductID),
  CONSTRAINT product_maintenance_maintenance_fk FOREIGN KEY (MaintenanceID) REFERENCES maintenance(MainID)
);

CREATE TABLE employee_maintenance (
  EmployeeID integer NOT NULL,
  MaintenanceID integer NOT NULL,
  CONSTRAINT employee_maintenance_id PRIMARY KEY (EmployeeID, MaintenanceID),
  CONSTRAINT employee_maintenance_employee_fk FOREIGN KEY (EmployeeID) REFERENCES employee(EmployeeID),
  CONSTRAINT employee_maintenance_maintenance_fk FOREIGN KEY (MaintenanceID) REFERENCES maintenance(MainID)
);