DDL скрипта

DROP TABLE IF EXISTS BATCHES;
DROP TABLE IF EXISTS CLIENTS;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS ORDER_BATCHES;
DROP TABLE IF EXISTS PAYMENTS;
DROP TABLE IF EXISTS PRODUCERS;
DROP TABLE IF EXISTS PRODUCTS;
DROP TABLE IF EXISTS TRANSPORTS;
DROP TABLE IF EXISTS USERS;

CREATE TABLE USERS (
  id UUID PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  is_admin BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE CLIENTS (
  id UUID PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  country TEXT NOT NULL,
  registration_number TEXT NOT NULL,
  tax_code TEXT NOT NULL,
  contact_person TEXT NOT NULL,
  phone_number TEXT NOT NULL,
  billing_address TEXT NOT NULL,
  shipping_address TEXT NOT NULL
);

CREATE TABLE PRODUCERS (
  id UUID PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  country TEXT NOT NULL,
  phone_number TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE PRODUCTS (
  id UUID PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  hs_code TEXT NOT NULL,
  price FLOAT NOT NULL,
  unit_of_measure TEXT NOT NULL,
  producer_id UUID REFERENCES PRODUCERS(id)
);

CREATE TABLE BATCHES (
  id UUID PRIMARY KEY NOT NULL,
  batch_code TEXT NOT NULL,
  production_date DATE NOT NULL,
  expiration_date DATE NOT NULL,
  net_weight FLOAT NOT NULL,
  gross_weight FLOAT NOT NULL,
  units_per_batch INTEGER NOT NULL,
  product_id UUID REFERENCES PRODUCTS(id)
);

CREATE TABLE TRANSPORTS (
  id UUID PRIMARY KEY NOT NULL,
  name TEXT NOT NULL,
  departure_point TEXT NOT NULL,
  arrival_point TEXT NOT NULL,
  estimated_departure_date DATE NOT NULL,
  estimated_arrival_date DATE NOT NULL,
  incoterm TEXT NOT NULL,
  insurance_conditions TEXT NOT NULL
);

CREATE TABLE ORDERS (
  id UUID PRIMARY KEY NOT NULL,
  date DATE NOT NULL,
  status TEXT NOT NULL,
  estimated_delivery_date DATE NOT NULL,
  buyer_id UUID REFERENCES CLIENTS(id),
  receiver_id UUID REFERENCES CLIENTS(id),
  transport_id UUID REFERENCES TRANSPORTS(id)
);

CREATE TABLE PAYMENTS (
  id UUID PRIMARY KEY NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  currency TEXT NOT NULL,
  due_date DATE NOT NULL,
  exchange_rate FLOAT NOT NULL,
  payment_date DATE NOT NULL,
  payment_method TEXT NOT NULL,
  payment_status TEXT NOT NULL,
  order_id UUID REFERENCES ORDERS(id)
);

CREATE TABLE ORDER_BATCHES (
  id UUID PRIMARY KEY NOT NULL,
  quantity INTEGER NOT NULL,
  price_per_unit FLOAT NOT NULL,
  total_price FLOAT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE,
  updated_at TIMESTAMP WITH TIME ZONE,
  order_id UUID REFERENCES ORDERS(id),
  batch_id UUID REFERENCES BATCHES(id)
);

DML скрипта

INSERT INTO users (id, name, email, password, is_admin) VALUES
  (gen_random_uuid(), 'Alice Admin', 'alice@example.com', 'password_hash_1', TRUE),
  (gen_random_uuid(), 'Bob Buyer', 'bob@example.com', 'password_hash_2', FALSE),
  (gen_random_uuid(), 'Eve Employee', 'eve@example.com', 'password_hash_3', FALSE);

INSERT INTO producers (id, name, address, country, phone_number, email) VALUES
  (gen_random_uuid(), 'Fresh Farms Ltd', '123 Farm Rd', 'US', '+1-555-0100', 'contact@freshfarms.example'),
  (gen_random_uuid(), 'Oceanic Produce', '456 Sea Ave', 'ES', '+34-600-123-456', 'sales@oceanic.example');

WITH p AS (SELECT id FROM producers ORDER BY name LIMIT 1)
INSERT INTO products (id, name, description, hs_code, price, unit_of_measure, producer_id)
SELECT gen_random_uuid(), 'Apples', 'Red apples - grade A', '080810', 1.25, 'kg', id FROM p;

WITH p2 AS (SELECT id FROM producers ORDER BY name DESC LIMIT 1)
INSERT INTO products (id, name, description, hs_code, price, unit_of_measure, producer_id)
SELECT gen_random_uuid(), 'Frozen Shrimp', 'IQF shrimp 20/30', '030617', 8.50, 'kg', id FROM p2;

INSERT INTO batches (id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch, product_id) 
SELECT gen_random_uuid(), 'BATCH-A1', CURRENT_DATE - INTERVAL '30 days', CURRENT_DATE + INTERVAL '335 days', 100.0, 105.0, 1000, id FROM products WHERE name='Apples' LIMIT 1;

INSERT INTO batches (id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch, product_id) 
SELECT gen_random_uuid(), 'BATCH-S1', CURRENT_DATE - INTERVAL '10 days', CURRENT_DATE + INTERVAL '355 days', 500.0, 510.0, 200, id FROM products WHERE name='Frozen Shrimp' LIMIT 1;

INSERT INTO clients (id, name, country, registration_number, tax_code, contact_person, phone_number, billing_address, shipping_address) VALUES
  (gen_random_uuid(), 'Global Retailer', 'US', 'REG-1001', 'TAX-1001', 'John Buyer', '+1-555-0200', '100 Market St', '200 Warehouse Rd'),
  (gen_random_uuid(), 'Local Bistro', 'ES', 'REG-2002', 'TAX-2002', 'Maria Chef', '+34-600-234-567', 'Calle 12', 'Calle 13');

INSERT INTO transports (id, name, departure_point, arrival_point, estimated_departure_date, estimated_arrival_date, incoterm, insurance_conditions) VALUES
  (gen_random_uuid(), 'Ocean Freight 1', 'Valencia Port', 'New York Port', CURRENT_DATE + INTERVAL '7 days', CURRENT_DATE + INTERVAL '21 days', 'FOB', 'Standard cargo insurance'),
  (gen_random_uuid(), 'Truck Line A', 'Warehouse A', 'Retail Hub', CURRENT_DATE + INTERVAL '1 day', CURRENT_DATE + INTERVAL '2 days', 'DAP', 'No additional insurance');

WITH buyer AS (SELECT id FROM clients ORDER BY name LIMIT 1),
     receiver AS (SELECT id FROM clients ORDER BY name DESC LIMIT 1),
     transport AS (SELECT id FROM transports ORDER BY name LIMIT 1)
INSERT INTO orders (id, date, status, estimated_delivery_date, buyer_id, receiver_id, transport_id)
SELECT gen_random_uuid(), CURRENT_DATE, 'created', CURRENT_DATE + INTERVAL '14 days', buyer.id, receiver.id, transport.id
FROM buyer, receiver, transport;

WITH o AS (SELECT id FROM orders LIMIT 1)
INSERT INTO payments (id, amount, currency, due_date, exchange_rate, payment_date, payment_method, payment_status, order_id)
SELECT gen_random_uuid(), 1250.00, 'USD', CURRENT_DATE + INTERVAL '30 days', 1.0, NULL, 'bank_transfer', 'pending', id FROM o;

WITH o AS (SELECT id FROM orders LIMIT 1),
     b1 AS (SELECT id FROM batches WHERE batch_code='BATCH-A1' LIMIT 1),
     b2 AS (SELECT id FROM batches WHERE batch_code='BATCH-S1' LIMIT 1)
INSERT INTO order_batches (id, quantity, price_per_unit, total_price, created_at, updated_at, order_id, batch_id)
SELECT gen_random_uuid(), 100, 1.25, 125.00, now(), NULL, o.id, b1.id FROM o, b1
UNION ALL
SELECT gen_random_uuid(), 50, 8.50, 425.00, now(), NULL, o.id, b2.id FROM o, b2;

DBeaver ER Дијаграм