CREATE FUNCTION public.set_current_timestamp_updated_at ()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
DECLARE
  _new record;
BEGIN
  _new := NEW;
  _new. "updated_at" = NOW();
  RETURN _new;
END;
$$;

-- Create an "url" Postgres type that is an alias for "text"
-- Which validates the input is an URL
CREATE DOMAIN url AS text CHECK (VALUE ~ 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#()?&//=]*)');

COMMENT ON DOMAIN url IS 'Match URLs (http or https)';


CREATE TABLE "address" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    user_id int NOT NULL,
    city text NOT NULL,
    state text NOT NULL,
    -- Use text for zipcode to handle ZIP+4 extended zipcodes
    zipcode text NOT NULL,
    address_line_one text NOT NULL,
    address_line_two text
);

COMMENT ON TABLE address IS 'A physical billing/shipping address, attached to a user account';


CREATE TABLE "site_admin" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    name text NOT NULL,
    email text NOT NULL UNIQUE,
    password text NOT NULL,
    refresh_token text UNIQUE
);

COMMENT ON TABLE "site_admin" IS 'Someone administrative capabilities on the site';

COMMENT ON COLUMN "site_admin"."password" IS 'A bcrypt-hashed version of the admin password, compared against securely in the JWT Auth API handler for sign-in';


CREATE TABLE "order" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    user_id int NOT NULL,
    billing_address_id int NOT NULL,
    shipping_address_id int NOT NULL,
    is_shipped boolean DEFAULT FALSE NOT NULL,
    order_total numeric,
    status text DEFAULT 'CREATED' NOT NULL
);

COMMENT ON TABLE "order" IS 'An order from a customer, containing one or more products and quantities';


CREATE TABLE "order_product" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    order_id int NOT NULL,
    product_id int NOT NULL,
    quantity int NOT NULL
);

COMMENT ON TABLE "order_product" IS 'A product belonging to a customer order, along with a quantity';


CREATE TABLE order_status (
  status text PRIMARY KEY
);

INSERT INTO order_status (
  status)
VALUES (
  'CREATED'),
(
  'PAID'),
(
  'SHIPPED'),
(
  'DELIVERED'),
(
  'CANCELLED'),
(
  'REFUNDED');
CREATE TABLE product (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    name text NOT NULL,
    -- Do we want description to be mandatory?
    description text,
    category_display_name text NOT NULL,
    brand text,
    price numeric NOT NULL,
    -- TODO: Make "product_image_url" table and one-to-many relationship from product -> product_image_url
    -- The Kaggle data just came with it in this format so we can hackily (ab)use JSONB for shitty relationships
    image_urls jsonb
);


-- Hasura Enum table containing product categories
-- Arguably, making categories an Enum might not be a best practice
--
-- For a few reasons:
--
-- Primarily that, because each change to the Enum table (insert/update/delete) modifies the GQL schema types
-- any sort of Client SDK containing the schema types has to be regenerated to stay up-to-date after any changes
--
-- And also because the metadata needs to be reloaded after modifications to the enum, since this can't be detected and reflected automatically yet
--
-- But, this is a great way to demo this feature and a semi-realistic usecase
CREATE TABLE product_category_enum (
    name text PRIMARY KEY,
    display_name text NOT NULL UNIQUE
);

INSERT INTO product_category_enum (
    display_name,
    name)
VALUES (
    'Home Furnishing',
    'home_furnishing'),
(
    'Computers',
    'computers'),
(
    'Baby Care',
    'baby_care'),
(
    'Wearable Smart Devices',
    'wearable_smart_devices'),
(
    'Furniture',
    'furniture'),
(
    'Home Entertainment',
    'home_entertainment'),
(
    'Home & Kitchen',
    'home_kitchen'),
(
    'Clothing',
    'clothing'),
(
    'Beauty and Personal Care',
    'beauty_and_personal_care'),
(
    'Sunglasses',
    'sunglasses'),
(
    'Tools & Hardware',
    'tools_hardware'),
(
    'Household Supplies',
    'household_supplies'),
(
    'Home Improvement',
    'home_improvement'),
(
    'Footwear',
    'footwear'),
(
    'Gaming',
    'gaming'),
(
    'Mobiles & Accessories',
    'mobiles_accessories'),
(
    'Sports & Fitness',
    'sports_fitness'),
(
    'Health & Personal Care Appliances',
    'health_personal_care_appliances'),
(
    'Home Decor & Festive Needs',
    'home_decor_festive_needs'),
(
    'Pens & Stationery',
    'pens_stationery'),
(
    'Watches',
    'watches'),
(
    'Food & Nutrition',
    'food_nutrition'),
(
    'Kitchen & Dining',
    'kitchen_dining'),
(
    'Pet Supplies',
    'pet_supplies'),
(
    'Jewellery',
    'jewellery'),
(
    'Cameras & Accessories',
    'cameras_accessories'),
(
    'Automotive',
    'automotive'),
(
    'eBooks',
    'e_books'),
(
    'Toys & School Supplies',
    'toys_school_supplies'),
(
    'Eyewear',
    'eyewear'),
(
    'Automation & Robotics',
    'automation_robotics'),
(
    'Bags, Wallets & Belts',
    'bags_wallets_belts');


CREATE TABLE "product_review" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    user_id int NOT NULL,
    product_id int NOT NULL,
    rating int NOT NULL CHECK (rating >= 1 AND rating <= 5),
    -- 5,000 characters = 800 words or 1.5 pages single-spaced (based on 6-char word avg)
    comment text NOT NULL CHECK (char_length(comment) <= 5000),
    -- Only allow each person to leave one review per product they've purchased
    CONSTRAINT one_review_per_person_and_product UNIQUE (user_id, product_id)
);

COMMENT ON TABLE "product_review" IS 'A review for a product which a customer has purchased before';


CREATE TABLE "user" (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL,
    updated_at timestamptz DEFAULT now() NOT NULL,
    name text NOT NULL,
    email text NOT NULL UNIQUE,
    password text NOT NULL,
    -- refresh_token really should be NOT NULL but the seed data doesn't have them
    refresh_token text UNIQUE
);

COMMENT ON TABLE "user" IS 'Someone with an account on the site, who uses it to make purchases';

COMMENT ON COLUMN "user"."password" IS 'A bcrypt-hashed version of the user password, compared against securely in the JWT Auth API handler for sign-in';


ALTER TABLE ONLY public.address
    ADD CONSTRAINT address_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user (id);


ALTER TABLE ONLY public.order
    ADD CONSTRAINT order_billing_address_id_fkey FOREIGN KEY
    (billing_address_id) REFERENCES public.address (id);

ALTER TABLE ONLY public.order
    ADD CONSTRAINT order_shipping_address_id_fkey FOREIGN KEY
    (shipping_address_id) REFERENCES public.address (id);

ALTER TABLE ONLY public.order
    ADD CONSTRAINT order_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user (id);

ALTER TABLE ONLY public.order
    ADD CONSTRAINT order_status_fkey FOREIGN KEY (status) REFERENCES
    public.order_status (status);


/*Functions*/
CREATE OR REPLACE FUNCTION public.gen_order_total ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    STABLE
    AS $function$
DECLARE
    sumtotal numeric;
BEGIN
    SELECT
        TRUNC(SUM(p.price), 2) INTO STRICT sumtotal
    FROM
        public.order o
        INNER JOIN public.order_product op ON (o.id = op.order_id)
        INNER JOIN public.product p ON (op.product_id = p.id)
    WHERE
        o.id = OLD.id
    GROUP BY
        o.id;
    NEW.order_total := sumtotal;
    RETURN NEW;
EXCEPTION
    WHEN no_data_found THEN
        RAISE NOTICE 'No products found for %', OLD.id;
    RETURN NEW;
END;

$function$;


ALTER TABLE ONLY public.order_product
    ADD CONSTRAINT order_id_fkey FOREIGN KEY (order_id) REFERENCES public.order (id);

ALTER TABLE ONLY public.order_product
    ADD CONSTRAINT product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product (id);


ALTER TABLE ONLY public.product
    ADD CONSTRAINT category_display_name_fk FOREIGN KEY (category_display_name) REFERENCES public.product_category_enum (display_name);


ALTER TABLE ONLY public.product_review
    ADD CONSTRAINT user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user (id);

ALTER TABLE ONLY public.product_review
    ADD CONSTRAINT product_id_fkey FOREIGN KEY (product_id) REFERENCES public.product (id);


CREATE TRIGGER set_address_updated_at
    BEFORE UPDATE ON public.address
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();
CREATE TRIGGER set_site_admin_updated_at
    BEFORE UPDATE ON public.site_admin
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();
CREATE TRIGGER set_order_updated_at
    BEFORE UPDATE ON public.order
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();

CREATE TRIGGER sum_order
    BEFORE INSERT OR UPDATE ON public.order
    FOR EACH ROW
    EXECUTE PROCEDURE public.gen_order_total ();
CREATE TRIGGER set_order_product_updated_at
    BEFORE UPDATE ON public.order_product
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();
CREATE TRIGGER set_product_updated_at
    BEFORE UPDATE ON public.product
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();

CREATE TRIGGER set_product_review_updated_at
    BEFORE UPDATE ON public.product_review
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();
CREATE TRIGGER set_user_updated_at
    BEFORE UPDATE ON public.user
    FOR EACH ROW
    EXECUTE FUNCTION public.set_current_timestamp_updated_at ();