shipping.sql

drop table if exists shipments;
create table shipments (
  id serial primary key not null,
  sender_address_id integer not null,
  sender_contact_id integer not null,
  receiver_address_id integer not null,
  receiver_contact_id integer not null,
  created_at timestamp not null default current_timestamp,
  units integer not null,
  zar_value numeric not null
);

drop table if exists shipment_addresses;
create table shipment_addresses (
  id serial primary key not null,
  line1 varchar(100),
  line2 varchar(100),
  line3 varchar(100),
  line4 varchar(100)
);

insert into shipment_addresses (line1, line2, line3, line4)
select
  md5(random()::text) rand_string,
  md5(random()::text) rand_string,
  md5(random()::text) rand_string,
  md5(random()::text) rand_string
from
  generate_series(1, 100000) t (rownum);

drop table if exists shipping_contacts;
create table shipping_contacts (
  id serial primary key not null,
  line1 varchar(100),
  line2 varchar(100),
  line3 varchar(100),
  line4 varchar(100)
);

insert into shipping_contacts (line1, line2, line3, line4)
select
  md5(random()::text) rand_string,
  md5(random()::text) rand_string,
  md5(random()::text) rand_string,
  md5(random()::text) rand_string
from
  generate_series(1, 100000) t (rownum);

insert into shipments (sender_address_id, sender_contact_id, receiver_address_id, receiver_contact_id, units, zar_value)
select
  floor(random() * 100000 + 1)::int,
  floor(random() * 100000 + 1)::int,
  floor(random() * 100000 + 1)::int,
  floor(random() * 100000 + 1)::int,
  floor(random() * 100 + 1)::int,
  random() * 100000 + 1
from
  generate_series(1, 5000000) t (rownum);

alter table shipments
  add constraint fk_sender_address_id foreign key (sender_address_id) references shipment_addresses (id);

alter table shipments
  add constraint fk_receiver_address_id foreign key (receiver_address_id) references shipment_addresses (id);

alter table shipments
  add constraint fk_sender_contact_id foreign key (sender_contact_id) references shipping_contacts (id);

alter table shipments
  add constraint fk_receiver_contact_id foreign key (receiver_contact_id) references shipping_contacts (id);
  
create index idx_shipments_created_at on shipments(created_at desc);  

select *
  from shipments as "shipment"
  left join "shipment_addresses" as "sender_address"
    on "sender_address"."id" = "shipment"."sender_address_id"
  left join "shipping_contacts" as "sender_contact"
    on "sender_contact"."id" = "shipment"."sender_contact_id"
  left join "shipment_addresses" as "receiver_address"
    on "receiver_address"."id" = "shipment"."receiver_address_id"
  left join "shipping_contacts" as "receiver_contact"
    on "receiver_contact"."id" = "shipment"."receiver_contact_id"
 order by shipment.created_at desc
 limit 1000;