CREATE TABLE "users" (
    "id"           bigserial PRIMARY KEY,
    "fullname"     varchar NOT NULL,
    "nin"          varchar UNIQUE NULL,
    "regno"        varchar UNIQUE NULL,
    "stamp"        varchar NULL,
    "signature"    varchar NULL,
    "phone"        varchar UNIQUE NOT NULL,
    "status"       varchar DEFAULT 'Active',
    "user_type"    varchar NOT NULL, 
    "email"        varchar UNIQUE NOT NULL,
    "password"     varchar NOT NULL,
    "created_at"   TIMESTAMPTZ NOT NULL DEFAULT(now())
);
CREATE TABLE "districts" (
    "id"                                    bigserial PRIMARY KEY,
    "name"                                  VARCHAR   UNIQUE NOT NULL,
    "code"                                  VARCHAR   UNIQUE NOT NULL,
    "created_at"                            TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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

CREATE TABLE "parishes" (
    "id"                 bigserial PRIMARY KEY,
    "parish"             VARCHAR NOT NULL,
    "subcounty_id"       INT NOT NULL,
    "code"               VARCHAR NOT NULL,
    "created_at"         TIMESTAMPTZ   NOT NULL DEFAULT(now())
);
ALTER TABLE "parishes" ADD FOREIGN KEY ("subcounty_id") REFERENCES "subcounties" ("id");

CREATE TABLE "villages" (
    "id"                 bigserial PRIMARY KEY,
    "village"            VARCHAR NOT NULL,
    "code"               VARCHAR NOT NULL,
    "parish_id"          INT NOT NULL,
    "created_at"         TIMESTAMPTZ   NOT NULL DEFAULT(now())
);
ALTER TABLE "villages" ADD FOREIGN KEY ("parish_id") REFERENCES "parishes" ("id");

CREATE TABLE "exportcompanies" (
    "id"                       bigserial PRIMARY KEY,
    "expname"                  varchar UNIQUE NOT NULL,
    "expregno"                 varchar UNIQUE NOT NULL,
    "exprexno"                 varchar UNIQUE NULL,
    "expaddress1"              varchar NOT NULL,  
    "expdistrict_id"           INT NOT NULL,
    "expsubcounty_id"          INT NOT NULL,
    "expvillage_id"            INT NOT NULL,
    "expparish_id"             INT NOT NULL,
    "registrationstatus"       varchar NOT NULL,
    "validitydate"             date NOT NULL,
    "expcompanyphone"          varchar UNIQUE NOT NULL,
    "expemail"                 varchar UNIQUE NOT NULL,
    "created_at"               TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "exportcompanies" ADD FOREIGN KEY ("expdistrict_id") REFERENCES "districts" ("id");
ALTER TABLE "exportcompanies" ADD FOREIGN KEY ("expvillage_id") REFERENCES "villages" ("id");
ALTER TABLE "exportcompanies" ADD FOREIGN KEY ("expparish_id") REFERENCES "parishes" ("id");

CREATE TABLE "roles"( 
    "id"               bigserial PRIMARY KEY,
    "name"             varchar UNIQUE NOT NULL,
    "exportcompany_id" INT NULL,
    "description"      varchar NOT NULL,
    "created_at"       TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "roles" ADD FOREIGN KEY ("exportcompany_id") REFERENCES "exportcompanies" ("id");

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

ALTER TABLE "user_roles" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");
ALTER TABLE "user_roles" ADD FOREIGN KEY ("role_id") REFERENCES "roles" ("id");



CREATE TABLE "role_permissions" (
      "id" bigserial PRIMARY KEY,
      "role_id" INTEGER NOT NULL,
      "permissions" varchar NOT NULL,
      "created_at" TIMESTAMPTZ NOT NULL DEFAULT(now())
);

ALTER TABLE "role_permissions" ADD FOREIGN KEY ("role_id") REFERENCES "roles" ("id");

CREATE TABLE "packhouses" (
    "id"                 bigserial PRIMARY KEY,
    "phname"             VARCHAR UNIQUE NOT NULL,
    "phregno"            VARCHAR UNIQUE NOT NULL,
    "contactname"        VARCHAR NOT NULL,
    "contactphone"       VARCHAR NOT NULL,
    "contactemail"       VARCHAR NOT NULL,
    "streetaddress"      VARCHAR NOT NULL,
    "district_id"        INT NOT NULL,
    "parish_id"          INT NOT NULL,
    "village_id"         INT NOT NULL,
    "long"               FLOAT   NOT NULL,
    "lat"                FLOAT   NOT NULL,
    "regstatus"          VARCHAR NOT NULL,
    "expirydate"         DATE    NOT NULL,
    "created_at"         TIMESTAMPTZ NOT NULL DEFAULT(now())
);

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