DB Schema

-- Extensions

CREATE EXTENSION IF NOT EXISTS citext SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS moddatetime SCHEMA extensions;

-- Custom types

CREATE DOMAIN email AS citext
    CHECK (value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$');

-- Tables

CREATE TABLE IF NOT EXISTS categories
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS products
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    image text NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    characteristics jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_visible boolean NOT NULL DEFAULT false,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS product_variants
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    product_id integer NOT NULL,
    stock integer NOT NULL,
    price integer NOT NULL,
    sales integer NOT NULL DEFAULT 0,
    characteristics jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
    CHECK (price > 0),
    CHECK (stock >= 0),
    CHECK (sales >= 0)
);

CREATE TABLE IF NOT EXISTS product_categories
(
    category_id integer NOT NULL,
    product_id integer NOT NULL,
    PRIMARY KEY (category_id, product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS carts
(
    user_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS cart_items
(
    user_id uuid NOT NULL,
    variant_id integer NOT NULL,
    quantity integer NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, variant_id),
    FOREIGN KEY (user_id) REFERENCES carts (user_id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants (id) ON DELETE RESTRICT,
    CHECK (quantity > 0)
);

CREATE TABLE IF NOT EXISTS orders
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    user_id uuid NOT NULL,
    payment_id uuid,
    recipient_email email NOT NULL,
    recipient_name text NOT NULL,
    recipient_contact text NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS order_items
(
    order_id integer NOT NULL,
    variant_id integer NOT NULL,
    quantity integer NOT NULL,
    PRIMARY KEY (order_id, variant_id),
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants (id) ON DELETE RESTRICT,
    CHECK (quantity > 0)
);

-- Views

CREATE OR REPLACE VIEW products_view AS
    SELECT *, (
        SELECT sum(product_variants.sales) AS sum
        FROM product_variants
        WHERE product_variants.product_id = products.id
    ) AS sales, (
        SELECT min(product_variants.price) AS min
        FROM product_variants
        WHERE product_variants.product_id = products.id
    ) AS min_price
    FROM products;

-- RLS

ALTER TABLE IF EXISTS categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS products ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS product_variants ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS product_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS carts ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS cart_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE products_view OWNER TO authenticated;
CREATE POLICY "Read access for everyone" ON categories FOR SELECT USING (true);
CREATE POLICY "Read access for everyone" ON products FOR SELECT USING ((is_visible = true));
CREATE POLICY "Read access for everyone" ON product_variants FOR SELECT USING (true);
CREATE POLICY "Read access for everyone" ON product_categories FOR SELECT USING (true);
CREATE POLICY "Full access for the owner" ON carts FOR ALL USING ((auth.uid() = user_id));
CREATE POLICY "Full access for the owner" ON cart_items FOR ALL USING ((auth.uid() = user_id));
CREATE POLICY "Read access for the owner" ON orders FOR SELECT USING ((auth.uid() = user_id));
CREATE POLICY "Read access for the owner" ON order_items FOR SELECT USING (
    (EXISTS ( SELECT 1 FROM orders WHERE (orders.id = order_items.order_id)))
);

-- Indexes

CREATE INDEX ON product_variants (product_id);
CREATE INDEX ON orders (user_id);

-- Triggers

CREATE OR REPLACE FUNCTION validate_product()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $$
BEGIN
  if new.is_visible and not exists (select 1 from product_variants pv where pv.product_id = new.id) then
    raise exception 'Visible product with id = % must have at least 1 variant', new.id;
  end if;
  return new;
END;
$$;

CREATE TRIGGER product_validation
    BEFORE INSERT OR UPDATE
    ON products
    FOR EACH ROW
    EXECUTE FUNCTION validate_product();

CREATE TRIGGER handle_updated_at
    BEFORE UPDATE
    ON products
    FOR EACH ROW
    EXECUTE FUNCTION extensions.moddatetime('updated_at');

CREATE TRIGGER handle_updated_at
    BEFORE UPDATE
    ON product_variants
    FOR EACH ROW
    EXECUTE FUNCTION extensions.moddatetime('updated_at');

CREATE TRIGGER handle_updated_at
    BEFORE UPDATE
    ON carts
    FOR EACH ROW
    EXECUTE FUNCTION extensions.moddatetime('updated_at');

CREATE TRIGGER handle_updated_at
    BEFORE UPDATE
    ON cart_items
    FOR EACH ROW
    EXECUTE FUNCTION extensions.moddatetime('updated_at');

-- RPC

CREATE OR REPLACE FUNCTION set_cart(cart_input jsonb)
    RETURNS void
    LANGUAGE 'sql'
AS $$
  delete from carts where user_id = auth.uid();
  insert into carts (user_id) values (auth.uid());
  insert into cart_items (user_id, variant_id, quantity)
    select
      auth.uid() as user_id,
      (item->'variantId')::int as variant_id,
      (item->'quantity')::int as quantity
    from jsonb_array_elements(cart_input->'items') as item;
$$;

CREATE OR REPLACE FUNCTION merge_cart(cart_input jsonb)
    RETURNS void
    LANGUAGE 'sql'
AS $$
  insert into carts (user_id)
    values (auth.uid())
    on conflict (user_id) do nothing;
  insert into cart_items (user_id, variant_id, quantity)
    select
      auth.uid() as user_id,
      (item->'variantId')::int as variant_id,
      (item->'quantity')::int as quantity
    from jsonb_array_elements(cart_input->'items') as item
    on conflict (user_id, variant_id) do update set quantity = excluded.quantity;
$$;

CREATE OR REPLACE FUNCTION place_order(
	recipient_input jsonb)
    RETURNS void
    LANGUAGE 'sql'
    SECURITY DEFINER
AS $$
  with placed_order as (
    insert into orders (user_id, recipient_name, recipient_email, recipient_contact)
      select
        user_id,
        recipient_input->>'name',
        (recipient_input->>'email')::email,
        recipient_input->>'contact'
      from carts
      where user_id = auth.uid()
      returning id, user_id
  )
  insert into order_items (order_id, variant_id, quantity)
    select o.id, i.variant_id, i.quantity
    from placed_order as o join cart_items as i
    on o.user_id = i.user_id;
  delete from carts where user_id = auth.uid();
$$;

CREATE OR REPLACE FUNCTION payment_succeeded(order_id_input integer)
    RETURNS void
    LANGUAGE 'sql'
AS $$
  with items as (
    select variant_id, quantity
    from orders as o
    join order_items as i
    on o.id = order_id_input and o.id = i.order_id
  )
  update product_variants
  set sales = sales + quantity
  from items
  where product_variants.id = items.variant_id;
$$;

-- Dummy data

insert into categories (name)
values
  ('Frontend'),
  ('Backend');

insert into products (image, name, description)
values
  ('nextjs-logo', 'Next.js', 'Awesome Open Source React Framework'),
  ('supabase-logo', 'Supabase', 'Awesome Open Source BaaS');

insert into product_variants (product_id, stock, price)
values
  (1, 999, 100),
  (2, 999, 100);

insert into product_categories (category_id, product_id)
values
  (1, 1),
  (2, 1),
  (2, 2);

update products set is_visible = true;