CREATE TABLE "inspectionreport"( 
    "id"                     bigserial PRIMARY KEY,
    "consignment_no"         VARCHAR NOT NULL,
    "notes"                  VARCHAR NOT NULL,
    "instruction"            VARCHAR NOT NULL,
    "user_id"                INT NOT NULL,
    "exportcompany_id"       INT NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "inspectionreport" ADD FOREIGN KEY ("consignment_no") REFERENCES "plannedconsignments" ("id");
ALTER TABLE "inspectionreport" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "inspectionreport" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "inspectionreportrecommendation"( 
    "id"                     bigserial PRIMARY KEY,
    "consignment_no"         VARCHAR NOT NULL,
    "consignment_status"     VARCHAR NOT NULL,
    "comment"                VARCHAR  NULL,
    "phytocertificate"       VARCHAR  NULL,
    "user_id"                INT NOT NULL,
    "exportcompany_id"       INT NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "inspectionreportrecommendation" ADD FOREIGN KEY ("consignment_no") REFERENCES "plannedconsignments" ("id");
ALTER TABLE "inspectionreportrecommendation" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "inspectionreportrecommendation" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");