CREATE TABLE "qcp1" (
      "id"                  bigserial PRIMARY KEY,
      "receptionform_id"    INT NOT NULL,
      "exportcompany_id"    INT NOT NULL, 
      "user_id"             INT NOT NULL,
      "weightpassed"        FLOAT NOT NULL,
      "status"              VARCHAR NOT NULL,
      "created_at"          TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp1" ADD FOREIGN KEY ("receptionform_id") REFERENCES "receptionform" ("id");
ALTER TABLE "qcp1" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp1" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");


CREATE TABLE "qcp1_attributes"( 
    "id"                    bigserial PRIMARY KEY,
    "qcp1_id"               INT NOT NULL,
    "qualityattribute_id"   INT NOT NULL,
    "qtyremoved"            FLOAT NOT NULL,
    "qtytaken"              FLOAT NOT NULL,
    "other"                 VARCHAR NULL,
    "percentage"            FLOAT NOT NULL,
    "traceabilitycode"      VARCHAR NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

      
ALTER TABLE "qcp1_attributes" ADD FOREIGN KEY ("qualityattribute_id") REFERENCES "qualityattributes" ("id");
ALTER TABLE "qcp1_attributes" ADD FOREIGN KEY ("qcp1_id") REFERENCES "qcp1" ("id");

CREATE TABLE "qcp1_recommendation"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp1_id"                INT NOT NULL,
    "status"                 VARCHAR NOT NULL,
    "netweightrejected"      FLOAT NOT NULL,
    "netweightpassed"        FLOAT NULL,
    "other"                  VARCHAR NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp1_recommendation" ADD FOREIGN KEY ("qcp1_id") REFERENCES "qcp1" ("id");

CREATE TABLE "qcp2" (
     "id"                   bigserial PRIMARY KEY,
     "batchno"              VARCHAR NOT NULL,
     "reception_id"         INT NOT NULL,
     "exportcompany_id"     INT NOT NULL, 
     "user_id"              INT NOT NULL, 
     "weightpassed"         FLOAT NOT NULL,
     "status"               VARCHAR NOT NULL,
     "created_at"           TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp2" ADD FOREIGN KEY ("reception_id") REFERENCES "receptionform" ("id");
ALTER TABLE "qcp2" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp2" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
CREATE TABLE "qcp2_attributes"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp2_id"                INT   NOT NULL,
    "qualityattribute_id"    INT   NOT NULL,
    "qtyremoved"             FLOAT NOT NULL,
    "qtytaken"               FLOAT NOT NULL,
    "percentage"             FLOAT NOT NULL,
    "other"                  VARCHAR NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp2_attributes" ADD FOREIGN KEY ("qualityattribute_id") REFERENCES "qualityattributes" ("id");
ALTER TABLE "qcp2_attributes" ADD FOREIGN KEY ("qcp2_id") REFERENCES "qcp2" ("id");

CREATE TABLE "qcp2_recommendation"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp2_id"                INT NOT NULL,
    "status"                 VARCHAR NOT NULL,
    "netweightrejected"      FLOAT NOT NULL,
    "netweightpassed"        FLOAT NULL,
    "other"                  VARCHAR NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp2_recommendation" ADD FOREIGN KEY ("qcp2_id") REFERENCES "qcp2" ("id");

CREATE TABLE "qcp3" (
     "id"                   bigserial PRIMARY KEY,
     "batchno"              VARCHAR NOT NULL,
     "reception_id"         INT NOT NULL,
     "exportcompany_id"     INT NOT NULL, 
     "weightpassed"         FLOAT NOT NULL,
     "user_id"              INT NOT NULL, 
     "status"               VARCHAR NOT NULL,
     "created_at"           TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp3" ADD FOREIGN KEY ("reception_id") REFERENCES "receptionform" ("id");
ALTER TABLE "qcp3" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "qcp3" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

CREATE TABLE "qcp3_attributes"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp3_id"                INT NOT NULL,
    "qualityattribute_id"    INT NOT NULL,
    "qtyremoved"             FLOAT NOT NULL,
    "qtytaken"               FLOAT NOT NULL,
    "percentage"             FLOAT NOT NULL,
    "other"                  VARCHAR NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp3_attributes" ADD FOREIGN KEY ("qualityattribute_id") REFERENCES "qualityattributes" ("id");
ALTER TABLE "qcp3_attributes" ADD FOREIGN KEY ("qcp3_id") REFERENCES "qcp3" ("id");

CREATE TABLE "qcp3_recommendation"( 
    "id"                     bigserial PRIMARY KEY,
    "qcp3_id"                INT NOT NULL,
    "status"                 VARCHAR NOT NULL,
    "netweightrejected"      FLOAT NOT NULL,
    "netweightpassed"        FLOAT NULL,
    "reasonforrejection"     VARCHAR NULL,
    "other"                  VARCHAR NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "qcp3_recommendation" ADD FOREIGN KEY ("qcp3_id") REFERENCES "qcp3" ("id");

