-- Need to test this script

CREATE ROLE UkayReader;
CREATE ROLE UkayAdmin;
CREATE DATABASE ukay;
GRANT CONNECT ON DATABASE ukay TO UkayReader;
GRANT USAGE ON SCHEMA public to UkayReader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO UkayReader;
CREATE USER UkayUser WITH PASSWORD 'abc123';

GRANT UkayReader TO UkayUser;
GRANT ALL PRIVILEGES ON DATABASE ukay TO UkayAdmin;

CREATE TYPE DeliveryType AS ENUM ('DELIVERY', 'PICK_UP_FROM_CUST', 'P_AND_D_PICKUP','P_AND_D_DELIVERY', 'PICK_UP', 'DELIVERY_EVEN_EXC');
CREATE TYPE ServiceType AS ENUM ('WHITE_GLOVES', 'THRESHOLD', 'PICK_UP', 'ROOM_OF_CHOICE');
CREATE TYPE ServiceStatus AS ENUM ('PENDING', 'IN_TRANSIT', 'DELIVERED', 'CANCELLED');

CREATE TABLE IF NOT EXISTS public.delivery
(
    deliveryid character varying(255) COLLATE pg_catalog."default" NOT NULL,
    routeid character varying(255) NOT NULL,
    recipientname character varying(255) COLLATE pg_catalog."default",
    receipientphone character varying(50) COLLATE pg_catalog."default",
    addressline1 character varying(50) COLLATE pg_catalog."default",
    addressline2 character varying(50) COLLATE pg_catalog."default",
    receipientcity character varying(50) COLLATE pg_catalog."default",
    receipientstate character varying(50) COLLATE pg_catalog."default",
    receipientpostalcode integer,
    receipientcountry character varying(50) COLLATE pg_catalog."default",
    quantity integer,
    itemname character varying(255) COLLATE pg_catalog."default",
    deliverytype deliverytype NOT NULL,
    servicetype servicetype NOT NULL,
    deliverystatus servicestatus NOT NULL,
    drivernotes text COLLATE pg_catalog."default",
    proofofdelivery bytea,
    CONSTRAINT delivery_pkey PRIMARY KEY (deliveryid)
);

CREATE TABLE IF NOT EXISTS public.drivers
(
    driverid integer NOT NULL,
    routeid character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT drivers_pkey PRIMARY KEY (driverid)
);

CREATE TABLE IF NOT EXISTS public.routes
(
    routeid character varying(255) COLLATE pg_catalog."default" NOT NULL,
    deliveryid character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT routes_pkey PRIMARY KEY (routeid),
    CONSTRAINT deliveryid UNIQUE (deliveryid)
);

ALTER TABLE IF EXISTS public.drivers
    ADD FOREIGN KEY (routeid)
    REFERENCES public.routes (routeid) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE
    NOT VALID;


ALTER TABLE IF EXISTS public.routes
    ADD FOREIGN KEY (deliveryid)
    REFERENCES public.delivery (deliveryid) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE
    NOT VALID;

