if not(exists(select * from systable where UCase(table_name)=UCase('Invoice_Data')))
then
  create table "Invoice_Data"
  (
    "id" integer not null default autoincrement,
    "cargo_id" integer not null,
    "source_syscode" integer,
    "import_document" varchar(64),
    "import_timestamp" varchar(24),
    "process_a_timestamp" varchar(24),
    "process_b_timestamp" varchar(24),
    "supplier_customer_no" numeric(7,0),
    "invoice_no" numeric(7,0),
    "invoice_type" integer,
    "invoice_date" varchar(24),
    "supplier_order_no" varchar(20),
    "invoice_amount" numeric(11,2),
    "currency_code" varchar(3),
    "items_count" integer,
    primary key ("id")
  );
end if;

GO

if not(exists(select * from systable where UCase(table_name)=UCase('Item_Data')))
then
  create table "Item_Data"
  (
    "id" integer not null default autoincrement,
	"invoice_id" integer not null,
    "import_invoice" numeric(7,0),
    "item_order_index" integer,
    "supplier_item_no" varchar(13),
	"navision_item_no" varchar(13),
	"supplier_qty" numeric(13,3),
    "qty" numeric(13,3),
	"price_syscode" varchar(2),
    "qty_factor" numeric(13,3),
	"supplier_price" numeric(11,2),
    "price" numeric(11,2),
	"supplier_name" varchar(30),
    "barcode" varchar(13),
    "reference_code" varchar(14),
    "qty_adjust" numeric(13,3),
	"qty_damaged" numeric(13,3),
	"good_before_date" varchar(24),
    "vendor_batch_code" varchar(24),
	"navision_code" varchar(24),
    primary key ("id")
  );
end if;

go

if not(exists(select * from systable where UCase(table_name)=UCase('item_cargo')))
then
  create table "item_cargo"
  (
    "id" integer not null default autoincrement,
	"item_id" integer,
	"order_no" numeric(7,0),
	"dispatch_note_position" numeric(5,0),
	"supplier_item_no" varchar(13),
	"supplier_qty" numeric(13,3),
	"box_no_from" numeric(6,0),
	"box_no_to" numeric(6,0),
	"no_boxes" numeric(6,0),
	"pallet_barcode" varchar(20),
    primary key ("id")
  );
end if;
go

if not(exists(select * from sysforeignkey where UCase(role)=UCase('invoiceitem_invoice'))) then
ALTER TABLE "DBA"."Item_Data"
	ADD FOREIGN KEY "invoiceitem_invoice" ("invoice_id") 
	REFERENCES "DBA"."Invoice_Data" ("id") on update cascade on delete cascade
end if;
go


if not(exists(select * from sysforeignkey where UCase(role)=UCase('itemcargo_item'))) then
ALTER TABLE "DBA"."item_cargo"
	ADD FOREIGN KEY "itemcargo_item" ("item_id") 
	REFERENCES "DBA"."Item_Data" ("id") on update cascade on delete cascade
end if;
go