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

ALTER TABLE "qcp5" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp5" ADD FOREIGN KEY ("packinglist_id") REFERENCES  "packinglist" ("id");
CREATE TABLE "qcp5_products"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp5_id"                INT NOT NULL,
    "traceability_code"      VARCHAR NOT NULL,
    "status"                 VARCHAR NOT NULL,
    "product_id"             INT NOT NULL,
	"totalboxes"             INT NOT NULL,
    "boxweight"              FLOAT NULL,
    "productnetweight"       FLOAT NULL,
    "totalproductnetweight"  FLOAT NULL,
    "totalproductgrossweight"FLOAT NULL,
    "sampledboxes"           INT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp5_products" ADD FOREIGN KEY ("product_id") REFERENCES "crops" ("id");
ALTER TABLE "qcp5_products" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");

CREATE TABLE "qcp5_others"( 
    "id"                      bigserial PRIMARY KEY,
    "qcp5_id"                 INT NOT NULL,
    "packinglist_id"          INT NOT NULL,
    "product_name"            VARCHAR NOT NULL,
    "noofboxes"               INT NOT NULL,
    "boxweight"               FLOAT NULL,
    "productnetweight"        FLOAT NULL,
    "totalproductnetweight"   FLOAT NULL,
    "totalproductgrossweight" FLOAT NULL,
    "sampledboxes"            INT NULL,
    "status"                  VARCHAR NOT NULL,
    "user_id"                 INT NOT NULL,
    "exportcompany_id"        INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp5_others" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");
ALTER TABLE "qcp5_others" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5_others" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp5_others" ADD FOREIGN KEY ("packinglist_id") REFERENCES "packinglist" ("id");
    
CREATE TABLE "qcp5_attributes"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp5_id"               INT NOT NULL,
    "qcp5_product_id"       INT NOT NULL,
    "qualityattributes_id"  INT NOT NULL,
    "user_id"               INT NOT NULL,
    "exportcompany_id"      INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp5_attributes" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");
ALTER TABLE "qcp5_attributes" ADD FOREIGN KEY ("qcp5_product_id") REFERENCES "qcp5_products" ("id");
ALTER TABLE "qcp5_attributes" ADD FOREIGN KEY ("qualityattributes_id") REFERENCES  "qualityattributes" ("id");
ALTER TABLE "qcp5_attributes" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5_attributes" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp5_other_attributes"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp5_id"               INT NOT NULL,
    "qcp5_other_id"         INT NOT NULL,
    "other"                 VARCHAR NOT NULL,
    "qualityattributes_id"  INT NOT NULL,
    "user_id"               INT NOT NULL,
    "exportcompany_id"      INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "qcp5_other_attributes" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");
ALTER TABLE "qcp5_other_attributes" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5_other_attributes" ADD FOREIGN KEY ("qcp5_other_id") REFERENCES  "qcp5_others" ("id");
ALTER TABLE "qcp5_other_attributes" ADD FOREIGN KEY ("qualityattributes_id") REFERENCES  "qualityattributes" ("id");
ALTER TABLE "qcp5_other_attributes" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp5_recommendations"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp5_id"               INT NOT NULL,
    "qcp5_product_id"       INT NOT NULL,
    "status"                VARCHAR NOT NULL,
    "reason"                VARCHAR NULL,
    "weight_rejected"       FLOAT NOT NULL,
    "weight_passed"         FLOAT NOT NULL,
    "user_id"               INT NOT NULL,
    "exportcompany_id"      INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp5_recommendations" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");
ALTER TABLE "qcp5_recommendations" ADD FOREIGN KEY ("qcp5_product_id") REFERENCES "qcp5_products" ("id");
ALTER TABLE "qcp5_recommendations" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5_recommendations" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp5_otherrecommendations"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp5_id"               INT NOT NULL,
    "qcp5_others_id"        INT NOT NULL,
    "status"                VARCHAR NOT NULL,
    "reason"                VARCHAR NULL,
    "weight_rejected"       FLOAT NOT NULL,
    "weight_passed"         FLOAT NOT NULL,
    "user_id"               INT NOT NULL,
    "exportcompany_id"      INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp5_otherrecommendations" ADD FOREIGN KEY ("qcp5_id") REFERENCES "qcp5" ("id");
ALTER TABLE "qcp5_otherrecommendations" ADD FOREIGN KEY ("qcp5_others_id") REFERENCES "qcp5_others" ("id");
ALTER TABLE "qcp5_otherrecommendations" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp5_otherrecommendations" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");





