CREATE TABLE "consignment_queries"( 
    "id"                     bigserial PRIMARY KEY,
    "plannedconsignment_id"  VARCHAR NOT NULL,
    "reason"                 VARCHAR NOT NULL,
    "user_id"                INT NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "consignment_queries" ADD FOREIGN KEY ("plannedconsignment_id") REFERENCES "plannedconsignments" ("id");
ALTER TABLE "consignment_queries" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

CREATE TABLE "trackhygiene"( 
    "id"                     bigserial PRIMARY KEY,
    "type"                   VARCHAR NOT NULL,
    "requirement"            VARCHAR NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "trackhygienereport"( 
    "id"                     bigserial PRIMARY KEY,
    "truckimage"             VARCHAR  NULL,
    "consignment_no"         VARCHAR  NULL,
    "vehicleregno"           VARCHAR  NULL,
    "date"                   DATE     NULL,
    "detergent"              VARCHAR  NULL,
    "refrigerationstatus"    VARCHAR  NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "trackhygienereport" ADD FOREIGN KEY ("consignment_no") REFERENCES "plannedconsignments" ("id");

CREATE TABLE "trackhygienereport_checklist"( 
    "id"                     bigserial PRIMARY KEY,
    "trackhygienereport_id"  INT NOT NULL,
    "trackhygiene_id"        INT NOT NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "trackhygienereport_checklist" ADD FOREIGN KEY ("trackhygienereport_id") REFERENCES "trackhygienereport" ("id");
ALTER TABLE "trackhygienereport_checklist" ADD FOREIGN KEY ("trackhygiene_id") REFERENCES "trackhygiene" ("id");


