CREATE TABLE "packinglist"( 
    "id"                     bigserial PRIMARY KEY,
    "status"                 VARCHAR NOT NULL,
    "user_id"                INT NOT NULL,
    "exportcompany_id"       INT NOT NULL,
    "order_id"               INT NOT NULL,
    "updated_by"             INT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "packinglist" ADD FOREIGN KEY ("order_id") REFERENCES "orders" ("id");

CREATE TABLE "packinglist_others"( 
    "id"                        bigserial PRIMARY KEY,
    "packinglist_id"            INT NOT NULL,
    "productname"               VARCHAR NOT NULL,
    "totalboxes"                INT NOT NULL,
    "boxweight"                 FLOAT NOT NULL,
    "productnetweight"          FLOAT NOT NULL,
    "totalproductnetweight"     FLOAT NOT NULL,
    "totalproductgrossweight"   FLOAT NOT NULL,
    "created_at" TIMESTAMPTZ    NOT NULL DEFAULT(now())
);

ALTER TABLE "packinglist_others" ADD FOREIGN KEY ("packinglist_id") REFERENCES "packinglist" ("id");

CREATE TABLE "packinglist_products"( 
    "id"                      bigserial PRIMARY KEY,
    "packinglist_id"          INT NOT NULL,
    "batchno"                 INT NULL,
    "receptionform_id"        INT NOT NULL,
    "order_id"                INT NOT NULL,
    "traceability_code"       VARCHAR NOT NULL,
    "product_id"              INT NOT NULL,
    "boxweight"               FLOAT NULL,
    "totalboxes"              INT NULL,
    "productnetweight"        FLOAT NULL,
    "totalproductnetweight"   FLOAT NULL,
    "totalproductgrossweight" FLOAT NULL,
    "created_at"              TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "packinglist_products" ADD FOREIGN KEY ("packinglist_id") REFERENCES "packinglist" ("id");
ALTER TABLE "packinglist_products" ADD FOREIGN KEY ("product_id") REFERENCES  "crops" ("id");
ALTER TABLE "packinglist_products" ADD FOREIGN KEY ("receptionform_id") REFERENCES  "receptionform" ("id");
ALTER TABLE "packinglist_products" ADD FOREIGN KEY ("order_id") REFERENCES  "orders" ("id");

CREATE TABLE "packinglist_batch"( 
    "id"                      bigserial PRIMARY KEY,
    "batchno"                 VARCHAR NOT NULL,
    "packinglist_id"          INT NOT NULL,
    "order_id"                INT NOT NULL,
    "receptionform_id"        INT NOT NULL,
    "traceability_code"       VARCHAR NOT NULL,
    "product_id"              INT NOT NULL,
    "totalboxes"              INT NULL,
    "boxweight"               FLOAT NULL,
    "productnetweight"        FLOAT NULL,
    "totalproductnetweight"   FLOAT NULL,
    "totalproductgrossweight" FLOAT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "packinglist_batch" ADD FOREIGN KEY ("packinglist_id") REFERENCES "packinglist" ("id");
ALTER TABLE "packinglist_batch" ADD FOREIGN KEY ("product_id") REFERENCES  "crops" ("id");
ALTER TABLE "packinglist_batch" ADD FOREIGN KEY ("order_id")   REFERENCES  "orders" ("id");
ALTER TABLE "packinglist_batch" ADD FOREIGN KEY ("receptionform_id")   REFERENCES  "receptionform" ("id");