-- Enums
CREATE TYPE public.order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');
CREATE TYPE public.item_condition AS ENUM ('new', 'used', 'refurbished');

-- Composite type
CREATE TYPE public.address AS (
    street text,
    city   text,
    zip    text
);

-- Tables
CREATE TABLE public.users (
    id         uuid        NOT NULL DEFAULT gen_random_uuid(),
    email      text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
);

CREATE TABLE public.products (
    id         bigint                NOT NULL,
    name       text                  NOT NULL,
    condition  public.item_condition  NOT NULL DEFAULT 'new',
    search_vec tsvector
);

CREATE TABLE public.orders (
    id         bigint               NOT NULL,
    user_id    uuid                 NOT NULL,
    status     public.order_status  NOT NULL DEFAULT 'pending',
    total      numeric(10,2)        NOT NULL DEFAULT 0,
    created_at timestamptz          NOT NULL DEFAULT now()
);

CREATE TABLE public.order_items (
    id         bigint  NOT NULL,
    order_id   bigint  NOT NULL REFERENCES public.orders(id),
    product_id bigint  NOT NULL,
    quantity   integer NOT NULL DEFAULT 1
);

-- Comments
COMMENT ON TABLE  public.users        IS 'registered users';
COMMENT ON COLUMN public.users.email  IS 'unique login email';
COMMENT ON TABLE  public.orders       IS 'customer orders';

-- Alter table: FK constraints, add/drop column
ALTER TABLE public.orders
    ADD CONSTRAINT orders_user_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);

ALTER TABLE public.order_items
    ADD CONSTRAINT items_product_fkey FOREIGN KEY (product_id) REFERENCES public.products(id);

ALTER TABLE public.users ADD COLUMN bio text;
ALTER TABLE public.users DROP COLUMN bio;

-- Functions: trigger stub, parameterised query with user-defined param + return types
CREATE FUNCTION public.set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RETURN NEW; END $$;

CREATE FUNCTION public.active_orders(p_user_id uuid, p_status public.order_status)
RETURNS SETOF public.orders LANGUAGE sql AS $$
    SELECT * FROM public.orders WHERE user_id = p_user_id AND status = p_status
$$;

COMMENT ON FUNCTION public.set_updated_at() IS 'stamps updated_at on mutation';

-- View: join across two tables
CREATE VIEW public.order_summary AS
    SELECT o.id, o.status, o.total, u.email
    FROM   public.orders o
    JOIN   public.users  u ON u.id = o.user_id;

COMMENT ON VIEW public.order_summary IS 'denormalised order rows with user email';
COMMENT ON TYPE public.order_status  IS 'lifecycle states for an order';

-- Trigger
CREATE TRIGGER orders_updated_at
    BEFORE UPDATE ON public.orders
    FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Policy: USING + WITH CHECK
CREATE POLICY orders_isolation ON public.orders
    USING     (user_id = current_setting('app.current_user_id')::uuid)
    WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

-- Indexes: btree column, unique, multi-column, partial, expression, GIN
CREATE INDEX        idx_orders_user_id     ON public.orders   (user_id);
CREATE UNIQUE INDEX idx_users_email        ON public.users    (email);
CREATE INDEX        idx_orders_user_status ON public.orders   (user_id, status);
CREATE INDEX        idx_orders_pending     ON public.orders   (created_at) WHERE status = 'pending';
CREATE INDEX        idx_users_email_lower  ON public.users    (lower(email));
CREATE INDEX        idx_products_search    ON public.products USING gin (search_vec);
