DROP TABLE IF EXISTS qcp5inspectionreportrecommendation;
DROP TABLE IF EXISTS qcp5inspectionreport;

CREATE TABLE "qcp5inspectionreportrecommendation"( 
    "id"                     bigserial PRIMARY KEY,
    "consignment_no"         VARCHAR NOT NULL,
    "consignment_status"     VARCHAR NOT NULL,
    "target"                 VARCHAR  NULL,
    "documentdate"           VARCHAR  NULL,
    "documentnumber"         VARCHAR  NULL,
    "instructions"           VARCHAR  NULL,
    "rejectionreason"        VARCHAR  NULL,
    "rejectionweight"        FLOAT    NULL,
    "passedweight"           FLOAT    NULL,
    "user_id"                INT NOT NULL,
    "exportcompany_id"       INT NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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

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


CREATE TABLE "suspensionreport"( 
    "id"                     bigserial PRIMARY KEY,
    "packhouse_id"           INT NULL,
    "exportcompany_id"       INT NULL,
    "user_id"                INT NOT NULL,
    "report"                 VARCHAR NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "suspensionreport" ADD FOREIGN KEY ("packhouse_id") REFERENCES "packhouses" ("id");
ALTER TABLE "suspensionreport" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "suspensionreport" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");