CREATE TABLE "qcp4"( 
    "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 "qcp4" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp4" ADD FOREIGN KEY ("packinglist_id") REFERENCES    "packinglist" ("id");

CREATE TABLE "qcp4_products"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp4_id"                INT NOT NULL,
    "traceability_code"      VARCHAR NOT NULL,
    "product_id"             INT NOT NULL,
	"totalboxes"             INT NOT NULL,
    "noofboxes"              INT NOT NULL,
    "boxweight"              FLOAT NULL,
    "productnetweight"       FLOAT NULL,
    "totalproductnetweight"  FLOAT NULL,
    "totalproductgrossweight"FLOAT NULL,
    "status"                 VARCHAR NOT NULL,
    "sampledboxes"           INT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp4_products" ADD FOREIGN KEY ("product_id") REFERENCES "crops" ("id");
ALTER TABLE "qcp4_products" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");


CREATE TABLE "qcp4_others"( 
    "id"                      bigserial PRIMARY KEY,
    "qcp4_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 "qcp4_others" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");
ALTER TABLE "qcp4_others" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4_others" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp4_others" ADD FOREIGN KEY ("packinglist_id") REFERENCES "packinglist" ("id");
    
CREATE TABLE "qcp4_attributes"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp4_id"               INT NOT NULL,
    "qualityattributes_id"  INT NOT NULL,
    "qcp4_product_id"       INT NOT NULL,
    "user_id"               INT NOT NULL,
    "exportcompany_id"      INT NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp4_attributes" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");
ALTER TABLE "qcp4_attributes" ADD FOREIGN KEY ("qcp4_product_id") REFERENCES "qcp4_products" ("id");
ALTER TABLE "qcp4_attributes" ADD FOREIGN KEY ("qualityattributes_id") REFERENCES  "qualityattributes" ("id");
ALTER TABLE "qcp4_attributes" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4_attributes" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp4_other_attributes"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp4_id"               INT NOT NULL,
    "qcp4_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 "qcp4_other_attributes" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");
ALTER TABLE "qcp4_other_attributes" ADD FOREIGN KEY ("qcp4_other_id") REFERENCES "qcp4_others" ("id");
ALTER TABLE "qcp4_other_attributes" ADD FOREIGN KEY ("qualityattributes_id") REFERENCES "qualityattributes" ("id");
ALTER TABLE "qcp4_other_attributes" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4_other_attributes" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp4_recommendations"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp4_id"               INT NOT NULL,
    "status"                VARCHAR NOT NULL,
    "qcp4_product_id"       INT 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 "qcp4_recommendations" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");
ALTER TABLE "qcp4_recommendations" ADD FOREIGN KEY ("qcp4_product_id") REFERENCES "qcp4_products" ("id");
ALTER TABLE "qcp4_recommendations" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4_recommendations" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "qcp4_otherrecommendations"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp4_id"               INT NOT NULL,
    "qcp4_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 "qcp4_otherrecommendations" ADD FOREIGN KEY ("qcp4_id") REFERENCES "qcp4" ("id");
ALTER TABLE "qcp4_otherrecommendations" ADD FOREIGN KEY ("qcp4_others_id") REFERENCES "qcp4_others" ("id");
ALTER TABLE "qcp4_otherrecommendations" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");
ALTER TABLE "qcp4_otherrecommendations" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
