schema.sql

--
-- Name: users; Type: TABLE; Schema: public;
--

CREATE TABLE public.users (
    email character varying NOT NULL,
    fullname character varying,
    profile_pic character varying,
    created_at timestamp without time zone
);



--
-- Name: vendors; Type: TABLE; Schema: public;
--

CREATE TABLE public.vendors (
    id integer NOT NULL,
    user_email character varying,
    name character varying,
    email character varying,
    phone_no character varying,
    rating double precision,
    created_at timestamp without time zone
);



--
-- Name: vendors_id_seq; Type: SEQUENCE; Schema: public;
--

CREATE SEQUENCE public.vendors_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: vendors_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
--

ALTER SEQUENCE public.vendors_id_seq OWNED BY public.vendors.id;


--
-- Name: order_items; Type: TABLE; Schema: public;
--

CREATE TABLE public.order_items (
    id integer NOT NULL,
    order_ref character varying,
    product_id integer,
    quantity integer,
    price_at_order double precision,
    created_at timestamp without time zone
);



--
-- Name: order_items_id_seq; Type: SEQUENCE; Schema: public;
--

CREATE SEQUENCE public.order_items_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: order_items_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
--

ALTER SEQUENCE public.order_items_id_seq OWNED BY public.order_items.id;


--
-- Name: products; Type: TABLE; Schema: public;
--

CREATE TABLE public.products (
    id integer NOT NULL,
    owner_email character varying,
    name character varying,
    sku character varying,
    unit_price double precision,
    stock_level integer,
    created_at timestamp without time zone
);



--
-- Name: products_id_seq; Type: SEQUENCE; Schema: public;
--

CREATE SEQUENCE public.products_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: products_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
--

ALTER SEQUENCE public.products_id_seq OWNED BY public.products.id;


--
-- Name: purchase_orders; Type: TABLE; Schema: public;
--

CREATE TABLE public.purchase_orders (
    ref_no character varying NOT NULL,
    vendor_id integer,
    owner_email character varying,
    total_amount double precision,
    status character varying,
    order_date timestamp without time zone,
    expected_date timestamp without time zone,
    created_at timestamp without time zone
);




--
-- Name: order_items id; Type: DEFAULT; Schema: public;
--

ALTER TABLE ONLY public.order_items ALTER COLUMN id SET DEFAULT nextval('public.order_items_id_seq'::regclass);


--
-- Name: products id; Type: DEFAULT; Schema: public;
--

ALTER TABLE ONLY public.products ALTER COLUMN id SET DEFAULT nextval('public.products_id_seq'::regclass);


--
-- Name: vendors id; Type: DEFAULT; Schema: public;
--

ALTER TABLE ONLY public.vendors ALTER COLUMN id SET DEFAULT nextval('public.vendors_id_seq'::regclass);


--
-- Name: products _owner_email_product_sku_uc; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT _owner_email_product_sku_uc UNIQUE (owner_email, sku);


--
-- Name: vendors _user_email_vendor_email_uc; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT _user_email_vendor_email_uc UNIQUE (user_email, email);


--
-- Name: vendors _user_email_vendor_phone_uc; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT _user_email_vendor_phone_uc UNIQUE (user_email, phone_no);


--
-- Name: order_items order_items_pkey; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_pkey PRIMARY KEY (id);


--
-- Name: products products_pkey; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_pkey PRIMARY KEY (id);


--
-- Name: purchase_orders purchase_orders_pkey; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.purchase_orders
    ADD CONSTRAINT purchase_orders_pkey PRIMARY KEY (ref_no);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (email);


--
-- Name: vendors vendors_pkey; Type: CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT vendors_pkey PRIMARY KEY (id);


--
-- Name: ix_products_id; Type: INDEX; Schema: public;
--

CREATE INDEX ix_products_id ON public.products USING btree (id);


--
-- Name: ix_purchase_orders_ref_no; Type: INDEX; Schema: public;
--

CREATE INDEX ix_purchase_orders_ref_no ON public.purchase_orders USING btree (ref_no);


--
-- Name: ix_users_email; Type: INDEX; Schema: public;
--

CREATE INDEX ix_users_email ON public.users USING btree (email);


--
-- Name: ix_vendors_id; Type: INDEX; Schema: public;
--

CREATE INDEX ix_vendors_id ON public.vendors USING btree (id);


--
-- Name: order_items order_items_order_ref_fkey; Type: FK CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.order_items
    ADD CONSTRAINT order_items_order_ref_fkey FOREIGN KEY (order_ref) REFERENCES public.purchase_orders(ref_no);


--
-- Name: order_items order_items_product_id_fkey; Type: FK CONSTRAINT; Schema: public;
--

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


--
-- Name: products products_owner_email_fkey; Type: FK CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.products
    ADD CONSTRAINT products_owner_email_fkey FOREIGN KEY (owner_email) REFERENCES public.users(email);


--
-- Name: purchase_orders purchase_orders_owner_email_fkey; Type: FK CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.purchase_orders
    ADD CONSTRAINT purchase_orders_owner_email_fkey FOREIGN KEY (owner_email) REFERENCES public.users(email);


--
-- Name: purchase_orders purchase_orders_vendor_id_fkey; Type: FK CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.purchase_orders
    ADD CONSTRAINT purchase_orders_vendor_id_fkey FOREIGN KEY (vendor_id) REFERENCES public.vendors(id);


--
-- Name: vendors vendors_user_email_fkey; Type: FK CONSTRAINT; Schema: public;
--

ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT vendors_user_email_fkey FOREIGN KEY (user_email) REFERENCES public.users(email);