CREATE TABLE "modesoftransport" (
    "id" bigserial PRIMARY KEY,
    "topackhouse"                                     BOOLEAN NOT NULL DEFAULT true,
    "toexit"                                          BOOLEAN NOT NULL DEFAULT true,
    "todestination"                                   BOOLEAN NOT NULL DEFAULT true,
    "motdescription"                                  VARCHAR UNIQUE NOT NULL,
    "status"                                          VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"                                      TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "countries" (
    "id"                                    bigserial PRIMARY KEY,
    "countrycode"                           VARCHAR  UNIQUE NOT NULL,
    "countryname"                           VARCHAR  NOT NULL,
    "created_at"                            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "zones" (
    "id"                                    bigserial PRIMARY KEY,
    "zone"                                  VARCHAR   NOT NULL,
    "district_id"                           INT       NOT NULL,
    "created_at"                            TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "zones" ADD FOREIGN KEY ("district_id") REFERENCES "districts" ("id");

CREATE TABLE "airlines" (
    "id" bigserial PRIMARY KEY,
    "airlinecode"                                  varchar  UNIQUE NOT NULL,
    "airlinename"                                  varchar  NOT NULL,
    "modeoftransport_id"                           INTEGER  NOT NULL,
    "status"                                       VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"                                   TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "airlines" ADD FOREIGN KEY ("modeoftransport_id") REFERENCES "modesoftransport" ("id");

CREATE TABLE "exitpoints" (
    "id"                 bigserial PRIMARY KEY,
    "expcode"            VARCHAR NOT NULL,     
    "modeoftransport_id" INT     NOT NULL,                             
    "expdescription"     VARCHAR NOT NULL,
    "village_id"         INT     NOT NULL,
    "parish_id"          INT     NOT NULL,
    "district_id"        INT     NOT NULL,
    "expwhatsapp"        VARCHAR NOT NULL,
    "longtitude"         FLOAT NOT NULL,
    "latitude"           FLOAT NOT NULL,
    "status"             VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"         TIMESTAMPTZ   NOT NULL DEFAULT(now())
);

ALTER TABLE "exitpoints" ADD FOREIGN KEY ("modeoftransport_id") REFERENCES "modesoftransport" ("id");
ALTER TABLE "exitpoints" ADD FOREIGN KEY ("village_id")  REFERENCES "villages" ("id");
ALTER TABLE "exitpoints" ADD FOREIGN KEY ("parish_id")   REFERENCES "parishes" ("id");
ALTER TABLE "exitpoints" ADD FOREIGN KEY ("district_id") REFERENCES "districts" ("id");

CREATE TABLE "user_zones"(
    "id"                        bigserial PRIMARY KEY,
    "user_id"                   INTEGER NOT NULL,
    "district_id"               INT NOT NULL,
    "zone_id"                   INT NOT NULL,
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "user_zones" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "user_zones" ADD FOREIGN KEY ("district_id") REFERENCES "districts" ("id");
ALTER TABLE "user_zones" ADD FOREIGN KEY ("zone_id") REFERENCES "zones" ("id");

CREATE TABLE "exithandlers" (
    "id" bigserial PRIMARY KEY,
    "exhcode"                                  varchar  UNIQUE NOT NULL,
    "exhdescription"                           varchar  NOT NULL,
    "exitpoint"                                INTEGER  NOT NULL,
    "status"                                   VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"                               TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "exithandlers" ADD FOREIGN KEY ("exitpoint") REFERENCES "exitpoints" ("id");

CREATE TABLE "consignmentreasonrejections" (
    "id"                                   bigserial PRIMARY KEY,
    "crrcode"                              VARCHAR  UNIQUE NOT NULL,
    "crrdescription"                       VARCHAR  NOT NULL,
    "created_at"                           TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "consignmentrejections" (
    "id" bigserial PRIMARY KEY,
    "cjcode"                              VARCHAR  UNIQUE NOT NULL,
    "cjdescription"                       VARCHAR  NOT NULL,
    "created_at"                          TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "consignmentdoctypes" (
    "id" bigserial PRIMARY KEY,
    "cdcode"                            varchar UNIQUE NOT NULL,
    "cddescription"                     varchar NOT NULL,
    "created_at"                        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "packagetypes" (
    "id" bigserial PRIMARY KEY,
    "pgcode"                            varchar UNIQUE NOT NULL,
    "pgdescription"                     varchar NOT NULL,
    "created_at"                        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "reasonrejections" (
    "id" bigserial PRIMARY KEY,
    "reasoncode"                        varchar UNIQUE NOT NULL,
    "reasondescription"                 varchar NOT NULL,
    "created_at"                        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "rejections" (
    "id" bigserial PRIMARY KEY,
    "rejectioncode"                     varchar UNIQUE NOT NULL,
    "rejectiondescription"              varchar NOT NULL,
    "created_at"                        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "productdoctypes" (
    "id" bigserial PRIMARY KEY,
    "pddocumentcode"                    varchar UNIQUE NOT NULL,
    "pddocumentdescription"             varchar NOT NULL,
    "created_at"                        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "crops" (
    "id"                        bigserial PRIMARY KEY,
    "variant"                   VARCHAR NOT NULL,
    "cropname"                  varchar UNIQUE  NOT NULL,
    "crophscode"                varchar UNIQUE  NOT NULL,
    "cic_code"                  varchar NOT NULL,
    "riskstatus"                varchar NOT NULL,
    "status"                    VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "clients" (
    "id"                        bigserial PRIMARY KEY,
    "companyname"               varchar UNIQUE NOT NULL,
    "country_id"                INT NOT NULL,
    "exportcompany_id"          INT NOT NULL,
    "contactphone"              varchar NOT NULL,
    "contactname"               varchar NOT NULL,
    "contactemail"              varchar NOT NULL,
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "clients" ADD FOREIGN KEY ("country_id") REFERENCES "countries" ("id");
ALTER TABLE "clients" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "plannedconsignments" (
    "id"                 VARCHAR PRIMARY KEY,
    "start_date"         VARCHAR NOT NULL,
    "end_date"           VARCHAR NOT NULL,
    "status"             VARCHAR NOT NULL DEFAULT 'Pending',
    "exportcompany_id"   INT NOT NULL,
    "user_id"            INT NOT NULL,
    "created_at"         TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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


CREATE TABLE "orders" (
    "id"                        bigserial PRIMARY KEY,
    "client_id"                 INT NOT NULL, 
    "exportcompany_id"          INT NOT NULL,
    "consignment_no"            VARCHAR NULL,
    "status"                    VARCHAR NOT NULL DEFAULT 'Active',
    "user_id"                   INT NOT NULL,
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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

CREATE TABLE "order_crops" (
    "id"             bigserial PRIMARY KEY,
    "order_id"       INT NOT NULL,
    "crop_id"        INT NOT NULL,
    "weight"         FLOAT NOT NULL,
    "weightreceived" FLOAT NULL,
    "status"         VARCHAR NOT NULL DEFAULT 'Active',
    "created_at"     TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "order_crops" ADD FOREIGN KEY ("order_id") REFERENCES "orders" ("id");
ALTER TABLE "order_crops" ADD FOREIGN KEY ("crop_id") REFERENCES "crops" ("id");

CREATE TABLE "farmers" (
    "id"                       bigserial PRIMARY KEY,
    "fmcode"                   varchar UNIQUE NOT NULL,
    "fmid"                     varchar UNIQUE NOT NULL,
    "fmidtype"                 varchar NOT NULL,
    "fmname"                   varchar NOT NULL,
    "fmphoto"                  varchar NULL,
    "fmphone"                  varchar NOT NULL,
    "fmemail"                  varchar UNIQUE NOT NULL,
    "status"                   VARCHAR NOT NULL DEFAULT 'Active',
    "isverified"               BOOLEAN NOT NULL DEFAULT false,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

CREATE TABLE "farmer_contacts" (
    "id"                       bigserial PRIMARY KEY,
    "farmer_id"                INT NOT NULL,
    "contactname"              varchar NOT NULL,
    "contactphone"             varchar NOT NULL,
    "contactemail"             varchar NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "farmer_contacts" ADD FOREIGN KEY ("farmer_id") REFERENCES "farmers" ("id");

CREATE TABLE "farmer_farms" (
    "id"                       bigserial PRIMARY KEY,
    "farmer_id"                INT NOT NULL,
    "crop_id"                  INT NOT NULL,
    "traceability_code"        varchar UNIQUE NOT NULL,
    "harvestbalance"           FLOAT NULL,
    "district_id"              INT NOT NULL,
    "subcounty_id"             INT NOT NULL,
    "parish_id"                INT NOT NULL,
    "village_id"               INT NOT NULL,
    "acreage"                  INT NOT NULL,
    "lat"                      float   NOT NULL,
    "long"                     float   NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "farmer_farms" ADD FOREIGN KEY ("farmer_id") REFERENCES "farmers" ("id");
ALTER TABLE "farmer_farms" ADD FOREIGN KEY ("crop_id") REFERENCES "crops" ("id");
ALTER TABLE "farmer_farms" ADD FOREIGN KEY ("district_id") REFERENCES "districts" ("id");
ALTER TABLE "farmer_farms" ADD FOREIGN KEY ("parish_id") REFERENCES "parishes" ("id");
ALTER TABLE "farmer_farms" ADD FOREIGN KEY ("village_id") REFERENCES "villages" ("id");

CREATE TABLE "farmer_produce" (
    "id"                       bigserial PRIMARY KEY,
    "farmer_id"                INT NOT NULL,
    "crop_id"                  INT NOT NULL,
    "plantingdate"             DATE NOT NULL,
    "acreage"                  INT  NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "farmer_produce" ADD FOREIGN KEY ("farmer_id") REFERENCES "farmers" ("id");
ALTER TABLE "farmer_produce" ADD FOREIGN KEY ("crop_id") REFERENCES "crops" ("id");

CREATE TABLE "receptionform" (
    "id"                       bigserial PRIMARY KEY,
    "supplier_id"              INT       NOT NULL,
    "traceability_code"        varchar   NOT NULL,
    "order_id"                 INT       NOT NULL,
    "crop_id"                  INT       NOT NULL,
    "batches"                  INT       NOT NULL,
    "netweight"                FLOAT       NOT NULL,
    "phytostatus"              VARCHAR   NOT NULL,
    "status"                   VARCHAR   NOT NULL,
    "modeoftransport_id"       INT       NOT NULL,
    "quantityrejected"         FLOAT       NOT NULL,
    "previousharvestbalance"   FLOAT       NOT NULL,
    "finalharvestbalance"      FLOAT       NOT NULL,
    "driversname"              VARCHAR   NOT NULL,    
    "carnumberplate"           VARCHAR   NOT NULL,
    "deliverytime"             DATE      NOT NULL,
    "score"                    INT       NOT NULL, 
    "user_id"                  INT       NOT NULL,
    "packhouse_id"             INT       NOT NULL,
    "exportcompany_id"         INT       NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);


ALTER TABLE "receptionform" ADD FOREIGN KEY ("modeoftransport_id") REFERENCES "modesoftransport" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("packhouse_id") REFERENCES "packhouses" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("crop_id")   REFERENCES "crops" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("order_id")  REFERENCES "orders" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("user_id")   REFERENCES "users" ("id");
ALTER TABLE "receptionform" ADD FOREIGN KEY ("supplier_id")   REFERENCES "farmers" ("id");


CREATE TABLE "receptionform_qrcodes" (
    "id"                       bigserial PRIMARY KEY,
    "receptionform_id"         INT       NOT NULL,
    "qrcode"                   VARCHAR   NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "receptionform_qrcodes" ADD FOREIGN KEY ("receptionform_id") REFERENCES "receptionform" ("id");

CREATE TABLE "redflags"(
      "id"                bigserial PRIMARY KEY,
      "crop_id"           INT NOT NULL,
      "farmer_id"         INT NOT NULL,
      "reason"            VARCHAR NOT NULL,
      "user_id"           INT NOT NULL,
      "stage"             VARCHAR NOT NULL,
      "traceability_code" VARCHAR NOT NULL,
      "isactive"          BOOLEAN NOT NULL DEFAULT true,
      "created_at"        TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "redflags" ADD FOREIGN KEY ("crop_id") REFERENCES   "crops" ("id");
ALTER TABLE "redflags" ADD FOREIGN KEY ("farmer_id") REFERENCES "farmers" ("id");
ALTER TABLE "redflags" ADD FOREIGN KEY ("user_id") REFERENCES  "users" ("id");

CREATE TABLE "qualityattributes"(
      "id"            bigserial PRIMARY KEY,
      "description"   VARCHAR NOT NULL,    
      "qcp1"          BOOLEAN NOT NULL DEFAULT true,
      "qcp2"          BOOLEAN NOT NULL DEFAULT true,
      "qcp3"          BOOLEAN NOT NULL DEFAULT true,
      "qcp4"          BOOLEAN NOT NULL DEFAULT true,
      "qcp5"          BOOLEAN NOT NULL DEFAULT true,
      "receptionform" BOOLEAN NOT NULL DEFAULT true,
      "status"        VARCHAR NOT NULL DEFAULT 'Active',
      "created_at"    TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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

ALTER TABLE "reception_attributes" ADD FOREIGN KEY ("qualityattribute_id") REFERENCES "qualityattributes" ("id");
ALTER TABLE "reception_attributes" ADD FOREIGN KEY ("receptionform_id") REFERENCES "receptionform" ("id");



CREATE TABLE "reception_documents"(
        "id"                            bigserial PRIMARY KEY,
        "scoutingrecords"               VARCHAR NULL,
        "productharvestslip"            VARCHAR NULL,
        "sprayingrecord"                VARCHAR NULL,
        "destinationrecord"             VARCHAR NULL,
        "receptionform_id"              INT NOT NULL,
        "description"                   VARCHAR NULL,
        "created_at"                    TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "reception_documents" ADD FOREIGN KEY ("receptionform_id") REFERENCES "receptionform" ("id");
CREATE TABLE "plannedconsignments_tpdetails" (
    "id"                     bigserial PRIMARY KEY,
    "plannedconsignments_id" VARCHAR NOT NULL,
    "country_id"             INT  NOT NULL,
    "modesoftransport_id"    INT  NOT NULL,
    "exit_date"              DATE NOT NULL,
    "exitpoint_id"           INT  NOT NULL,
    "airline_id"             INT  NULL,
    "exithandler_id"         INT  NULL,
    "created_at"             TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("country_id") REFERENCES "countries" ("id");
ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("modesoftransport_id") REFERENCES "modesoftransport" ("id");
ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("exitpoint_id") REFERENCES "exitpoints" ("id");
ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("airline_id") REFERENCES "airlines" ("id");
ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("exithandler_id") REFERENCES "exithandlers" ("id");
ALTER TABLE "plannedconsignments_tpdetails" ADD FOREIGN KEY ("plannedconsignments_id") REFERENCES "plannedconsignments" ("id");

CREATE TABLE "plannedconsignments_documents" (
    "id"                             bigserial PRIMARY KEY,
    "plc_id"                         VARCHAR NOT NULL,
    "airway_bill"                    VARCHAR NULL,
    "mrl"                            VARCHAR NULL,
    "phytosanitary_certificate"      VARCHAR NULL,
    "other"                          VARCHAR NULL,
    "created_at"                     TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "plannedconsignments_documents" ADD FOREIGN KEY ("plc_id") REFERENCES "plannedconsignments" ("id");

CREATE TABLE "plannedconsignments_otherdocuments" (
    "id"                             bigserial PRIMARY KEY,
    "plannedconsignments_id"         VARCHAR NOT NULL,
    "name"                           VARCHAR NOT NULL,
    "description"                    VARCHAR NOT NULL,
    "created_at"                     TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "plannedconsignments_otherdocuments" ADD FOREIGN KEY ("plannedconsignments_id") REFERENCES "plannedconsignments" ("id");



CREATE TABLE "user_packhouse"(
    "id"                    bigserial PRIMARY KEY,
    "user_id"               INTEGER NOT NULL,
    "packhouse_id"          INTEGER NOT NULL,
    "created_at"            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "user_packhouse" ADD FOREIGN KEY ("packhouse_id") REFERENCES "packhouses" ("id");
ALTER TABLE "user_packhouse" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");


CREATE TABLE "user_exporter"(
    "id"                        bigserial PRIMARY KEY,
    "user_id"                   INTEGER NOT NULL,
    "exportcompany_id"          INTEGER NOT NULL,
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "user_exporter" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "user_exporter" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

CREATE TABLE "packhouse_exporter"(
    "id"                        bigserial PRIMARY KEY,
    "packhouse_id"              INTEGER NOT NULL,
    "exportcompany_id"          INTEGER NOT NULL,
    "created_at"                TIMESTAMPTZ NOT NULL DEFAULT(now())
);
ALTER TABLE "packhouse_exporter" ADD FOREIGN KEY ("packhouse_id") REFERENCES "packhouses" ("id");
ALTER TABLE "packhouse_exporter" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

