DDL скрипта

DROP TABLE IF EXISTS ORDER_BATCHES;
DROP TABLE IF EXISTS PAYMENTS;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS BATCHES;
DROP TABLE IF EXISTS PRODUCTS;
DROP TABLE IF EXISTS PRODUCERS;
DROP TABLE IF EXISTS CLIENTS;
DROP TABLE IF EXISTS USERS;
DROP TYPE IF EXISTS currency_enum;
DROP TYPE IF EXISTS payment_status_enum;
DROP TYPE IF EXISTS boolean_enum;

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

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

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

CREATE TABLE PRODUCTS (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  hs_code VARCHAR(50),
  price NUMERIC(14,4) NOT NULL DEFAULT 0,
  producer_id UUID REFERENCES PRODUCERS(id) ON DELETE SET NULL,
  unit_of_measure VARCHAR(50)
);

CREATE TABLE BATCHES (
  id UUID PRIMARY KEY,
  product_id UUID REFERENCES PRODUCTS(id) ON DELETE CASCADE,
  batch_code VARCHAR(100) NOT NULL,
  production_date DATE,
  expiration_date DATE,
  net_weight NUMERIC(12,4),
  gross_weight NUMERIC(12,4),
  units_per_batch INTEGER
);

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

CREATE TABLE ORDERS (
  id UUID PRIMARY KEY,
  date DATE NOT NULL,
  status VARCHAR(50),
  estimated_delivery_date DATE,
  buyer_id UUID REFERENCES CLIENTS(id) ON DELETE SET NULL,
  receiver_id UUID REFERENCES CLIENTS(id) ON DELETE SET NULL,
  transport_id UUID REFERENCES TRANSPORTS(id) ON DELETE SET NULL
);

CREATE TABLE PAYMENTS (
  id UUID PRIMARY KEY,
  order_id UUID UNIQUE REFERENCES ORDERS(id) ON DELETE CASCADE,
  amount NUMERIC(18,4) NOT NULL,
  currency VARCHAR(10) DEFAULT 'USD',
  due_date DATE,
  exchange_rate NUMERIC(18,8) DEFAULT 1.0,
  payment_date DATE,
  payment_method VARCHAR(100),
  payment_status VARCHAR(50)
);

CREATE TABLE ORDER_BATCHES (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES ORDERS(id) ON DELETE CASCADE,
  batch_id UUID REFERENCES BATCHES(id) ON DELETE RESTRICT,
  quantity INTEGER NOT NULL,
  price_per_unit NUMERIC(14,4) NOT NULL,
  total_price NUMERIC(18,4) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

DML скрипта

INSERT INTO USERS (id, name, email, password, is_admin) VALUES
('11111111-1111-1111-1111-111111111111', 'Alice Admin', 'alice@example.com', 'hashed_password_1', TRUE),
('22222222-2222-2222-2222-222222222222', 'Bob Buyer', 'bob@example.com', 'hashed_password_2', FALSE);

INSERT INTO CLIENTS (id, name, country, registration_number, tax_code, contact_person, phone_number, billing_address, shipping_address) VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Acme Imports', 'Italy', 'REG-IT-0001', 'TAXIT0001', 'Giulia Rossi', '+39 06 1234567', 'Via Roma 1, 00100 Roma, Italy', 'Port of Naples, Dock 5'),
('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Global Retailer', 'Germany', 'DE-REG-2020', 'DE-TAX-2020', 'Hans Müller', '+49 30 7654321', 'Hauptstrasse 10, 10115 Berlin, Germany', 'Warehouse 3, Berlin');

INSERT INTO PRODUCERS (id, name, address, country, phone_number, email) VALUES
('33333333-3333-3333-3333-333333333333', 'Producer Co.', 'Industrial Park 5, Zona A', 'Spain', '+34 91 1234567', 'sales@producerco.example'),
('44444444-4444-4444-4444-444444444444', 'Farm Fresh Ltd.', 'Countryside Road 12', 'France', '+33 1 23456789', 'contact@farmfresh.example');

INSERT INTO PRODUCTS (id, name, description, hs_code, price, producer_id, unit_of_measure) VALUES
('55555555-5555-5555-5555-555555555555', 'Organic Olive Oil', 'Extra virgin olive oil, cold pressed', '150900', 12.5000, '33333333-3333-3333-3333-333333333333', 'L'),
('66666666-6666-6666-6666-666666666666', 'Canned Tomatoes', 'San Marzano peeled tomatoes', '200210', 1.2000, '44444444-4444-4444-4444-444444444444', 'kg');

INSERT INTO BATCHES (id, product_id, batch_code, production_date, expiration_date, net_weight, gross_weight, units_per_batch) VALUES
('77777777-7777-7777-7777-777777777777', '55555555-5555-5555-5555-555555555555', 'OO-202509-A', '2025-06-01', '2027-06-01', 1000.0000, 1020.0000, 1000),
('88888888-8888-8888-8888-888888888888', '66666666-6666-6666-6666-666666666666', 'CT-202509-01', '2025-07-15', '2028-07-15', 500.0000, 510.0000, 500);

INSERT INTO TRANSPORTS (id, name, departure_point, arrival_point, estimated_departure_date, estimated_arrival_date, incoterm, insurance_conditions) VALUES
('99999999-9999-9999-9999-999999999999', 'Mediterranean Sea Freight', 'Port of Valencia', 'Port of Naples', '2025-09-20', '2025-09-25', 'FOB', 'Standard cargo insurance'),
('aaaaaaaa-0000-aaaa-0000-aaaaaaaa0000', 'Road Carrier EU', 'Berlin Warehouse', 'Acme Imports HQ', '2025-10-01', '2025-10-03', 'DAP', 'Carrier liability');

INSERT INTO ORDERS (id, date, status, estimated_delivery_date, buyer_id, receiver_id, transport_id) VALUES
('dddddddd-dddd-dddd-dddd-dddddddddddd', '2025-09-01', 'Pending', '2025-10-05', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '99999999-9999-9999-9999-999999999999'),
('eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', '2025-09-10', 'Confirmed', '2025-10-10', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-0000-aaaa-0000-aaaaaaaa0000');

INSERT INTO PAYMENTS (id, order_id, amount, currency, due_date, exchange_rate, payment_date, payment_method, payment_status) VALUES
('ffffffff-ffff-ffff-ffff-ffffffffffff', 'dddddddd-dddd-dddd-dddd-dddddddddddd', 12500.0000, 'EUR', '2025-10-01', 1.00000000, NULL, 'Wire Transfer', 'Pending'),
('11111111-aaaa-1111-aaaa-11111111aaaa', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', 600.0000, 'EUR', '2025-10-05', 1.00000000, '2025-09-15', 'Credit Card', 'Completed');

INSERT INTO ORDER_BATCHES (id, order_id, batch_id, quantity, price_per_unit, total_price, created_at, updated_at) VALUES
('22222222-2222-2222-2222-222222222222', 'dddddddd-dddd-dddd-dddd-dddddddddddd', '77777777-7777-7777-7777-777777777777', 200, 12.5000, 2500.0000, now(), now()),
('33333333-3333-3333-3333-333333333333', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', '88888888-8888-8888-8888-888888888888', 500, 1.2000, 600.0000, now(), now());