database-schema

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)
-- Dumped by pg_dump version 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)

-- Started on 2021-08-30 18:14:10 PDT

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 212 (class 1255 OID 33045)
-- Name: delete_deposit(bigint); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.delete_deposit(in_deposit_id bigint)
    LANGUAGE plpgsql
    AS $$
DECLARE
	this_deposit deposit_table%ROWTYPE;
BEGIN
	SELECT * INTO this_deposit FROM deposit_table WHERE deposit_id=in_deposit_id;

	UPDATE company_table 
	SET deposit_count = deposit_count - 1 
	WHERE company_id=this_deposit.company_id;

	UPDATE company_table 
	SET deposit_total = deposit_total - this_deposit.value 
	WHERE company_id=this_deposit.company_id;

	DELETE FROM deposit_table WHERE deposit_id=in_deposit_id;
END;
$$;


--
-- TOC entry 225 (class 1255 OID 33046)
-- Name: delete_expense(bigint); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.delete_expense(in_expense_id bigint)
    LANGUAGE plpgsql
    AS $$
DECLARE
	this_expense expense_table%ROWTYPE;
BEGIN
	SELECT * INTO this_expense FROM expense_table WHERE expense_id=in_expense_id;

	UPDATE company_table 
	SET expense_count = expense_count - 1 
	WHERE company_id=this_expense.company_id;

	UPDATE company_table 
	SET expense_total = expense_total - (this_expense.quantity*this_expense.price_per_unit) 
	WHERE company_id=this_expense.company_id;

	DELETE FROM expense_table WHERE expense_id=in_expense_id;
END;
$$;


--
-- TOC entry 226 (class 1255 OID 33047)
-- Name: delete_transaction(bigint); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.delete_transaction(in_transaction_id bigint)
    LANGUAGE plpgsql
    AS $$
DECLARE
	this_transaction transaction_table%ROWTYPE;
BEGIN
	SELECT * INTO this_transaction FROM transaction_table WHERE transaction_id=in_transaction_id;

	UPDATE company_table 
	SET transaction_count = transaction_count - 1 
	WHERE company_id=this_transaction.company_id;

	UPDATE company_table 
	SET transaction_total = transaction_total - (this_transaction.quantity*this_transaction.price_per_unit) 
	WHERE company_id=this_transaction.company_id;

	UPDATE company_table 
	SET product_count = product_count - this_transaction.quantity
	WHERE company_id=this_transaction.company_id;
	
	IF this_transaction.payment_method = 'square' THEN
		UPDATE business
		SET square_total = square_total - this_transaction.quantity
		WHERE company_id=this_transaction.company_id;
	END IF;

	DELETE FROM transaction_table WHERE transaction_id=in_transaction_id;
END;
$$;


--
-- TOC entry 228 (class 1255 OID 33048)
-- Name: insert_deposit(text, text, money, text); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.insert_deposit(in_company_id text, in_user_id text, in_value money, in_description text)
    LANGUAGE plpgsql
    AS $$
BEGIN
INSERT INTO deposit_table (user_id, company_id, value, description) 
VALUES (in_user_id, in_company_id, in_value, in_description);

UPDATE company_table 
SET deposit_count = deposit_count + 1 
WHERE company_id=in_company_id;

UPDATE company_table 
SET deposit_total = deposit_total + in_value 
WHERE company_id=in_company_id;
END;
$$;


--
-- TOC entry 227 (class 1255 OID 33049)
-- Name: insert_expense(text, text, text, text, numeric, date, text, money, text); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.insert_expense(in_user_id text, in_company_id text, in_product text, in_company text, in_quantity numeric, in_date date, in_payment text, in_price money, in_description text)
    LANGUAGE plpgsql
    AS $$
BEGIN
INSERT INTO expense_table (user_id, company_id, product, company, quantity, date, payment_method, price_per_unit, description) 
VALUES (in_user_id, in_company_id, in_product, in_company, in_quantity, in_date, in_payment, in_price, in_description);

UPDATE company_table 
SET expense_count = expense_count + 1 
WHERE company_id=in_company_id;

UPDATE company_table 
SET expense_total = expense_total + (in_quantity*in_price) 
WHERE company_id=in_company_id;
END;
$$;


--
-- TOC entry 229 (class 1255 OID 33053)
-- Name: insert_transaction(text, text, text, text, date, text, numeric, money); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.insert_transaction(in_user_id text, in_company_id text, in_customer text, in_product text, in_date date, in_payment text, in_quantity numeric, in_price money)
    LANGUAGE plpgsql
    AS $$
BEGIN
INSERT INTO transaction_table (user_id, company_id, customer, date, product, payment_method, quantity, price_per_unit) 
VALUES (in_user_id, in_company_id, in_customer, in_date, in_product, in_payment, in_quantity, in_price);

UPDATE company_table 
SET transaction_count = transaction_count + 1 
WHERE company_id=in_company_id;

UPDATE company_table 
SET transaction_total = transaction_total + (in_quantity*in_price) 
WHERE company_id=in_company_id;

UPDATE company_table 
SET product_count = product_count + in_quantity 
WHERE company_id=in_company_id;

IF in_payment::text = 'square' then
	UPDATE company_table
	SET square_total = square_total + (in_quantity*in_price)
	WHERE company_id=in_company_id; 
END IF;
END;
$$;


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 203 (class 1259 OID 32954)
-- Name: company_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.company_table (
    company_id text NOT NULL,
    section text,
    instructor text,
    profit_goal money DEFAULT 500 NOT NULL,
    stretch_profit_goal money DEFAULT 750 NOT NULL,
    profit money GENERATED ALWAYS AS ((transaction_total - expense_total)) STORED NOT NULL,
    expense_total money DEFAULT 0 NOT NULL,
    transaction_total money DEFAULT 0 NOT NULL,
    deposit_total money DEFAULT 0 NOT NULL,
    square_total money DEFAULT 0 NOT NULL,
    expense_count integer DEFAULT 0 NOT NULL,
    transaction_count integer DEFAULT 0 NOT NULL,
    deposit_count integer DEFAULT 0 NOT NULL,
    product_count integer DEFAULT 0 NOT NULL
);


--
-- TOC entry 211 (class 1259 OID 33041)
-- Name: company_view; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.company_view AS
 SELECT company_table.company_id,
    company_table.section,
    company_table.instructor,
    (company_table.square_total)::numeric AS square_total,
    (company_table.deposit_total)::numeric AS deposit_total,
    (company_table.expense_total)::numeric AS expense_total,
    (company_table.transaction_total)::numeric AS transaction_total,
    company_table.transaction_count,
    company_table.expense_count,
    company_table.deposit_count,
    company_table.product_count,
    (company_table.profit)::numeric AS profit,
    (company_table.profit_goal)::numeric AS profit_goal,
    (company_table.stretch_profit_goal)::numeric AS stretch_profit_goal
   FROM public.company_table;


--
-- TOC entry 205 (class 1259 OID 32975)
-- Name: deposit_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.deposit_table (
    deposit_id bigint NOT NULL,
    company_id text NOT NULL,
    user_id text DEFAULT 'none'::text NOT NULL,
    value money NOT NULL,
    description text,
    date date DEFAULT CURRENT_DATE NOT NULL
);


--
-- TOC entry 204 (class 1259 OID 32973)
-- Name: deposit_table_deposit_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.deposit_table ALTER COLUMN deposit_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.deposit_table_deposit_id_seq
    START WITH 0
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 20000
    CACHE 1
    CYCLE
);


--
-- TOC entry 207 (class 1259 OID 32993)
-- Name: expense_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.expense_table (
    expense_id bigint NOT NULL,
    company_id text NOT NULL,
    user_id text NOT NULL,
    product text NOT NULL,
    company text NOT NULL,
    payment_method text NOT NULL,
    description text NOT NULL,
    quantity numeric NOT NULL,
    price_per_unit money NOT NULL,
    date date DEFAULT CURRENT_DATE NOT NULL,
    total money GENERATED ALWAYS AS ((price_per_unit * (quantity)::double precision)) STORED NOT NULL
);


--
-- TOC entry 206 (class 1259 OID 32991)
-- Name: expense_table_expense_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.expense_table ALTER COLUMN expense_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.expense_table_expense_id_seq
    START WITH 0
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 20000
    CACHE 5
    CYCLE
);


--
-- TOC entry 209 (class 1259 OID 33010)
-- Name: transaction_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.transaction_table (
    transaction_id bigint NOT NULL,
    company_id text NOT NULL,
    user_id text NOT NULL,
    product text NOT NULL,
    payment_method text NOT NULL,
    customer text NOT NULL,
    quantity numeric NOT NULL,
    price_per_unit money NOT NULL,
    date date DEFAULT CURRENT_DATE NOT NULL,
    total money GENERATED ALWAYS AS ((price_per_unit * (quantity)::double precision)) STORED NOT NULL
);


--
-- TOC entry 208 (class 1259 OID 33008)
-- Name: transaction_table_transaction_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.transaction_table ALTER COLUMN transaction_id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.transaction_table_transaction_id_seq
    START WITH 0
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 20000
    CACHE 1
    CYCLE
);


--
-- TOC entry 210 (class 1259 OID 33025)
-- Name: user_has_company; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.user_has_company (
    user_id text NOT NULL,
    company_id text NOT NULL
);


--
-- TOC entry 202 (class 1259 OID 32946)
-- Name: user_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.user_table (
    user_id text NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    section text,
    role integer NOT NULL
);


--
-- TOC entry 2856 (class 2606 OID 32972)
-- Name: company_table company_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.company_table
    ADD CONSTRAINT company_pkey PRIMARY KEY (company_id);


--
-- TOC entry 2858 (class 2606 OID 32984)
-- Name: deposit_table deposit_table_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.deposit_table
    ADD CONSTRAINT deposit_table_pkey PRIMARY KEY (deposit_id);


--
-- TOC entry 2860 (class 2606 OID 33002)
-- Name: expense_table expense_table_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.expense_table
    ADD CONSTRAINT expense_table_pkey PRIMARY KEY (expense_id);


--
-- TOC entry 2862 (class 2606 OID 33019)
-- Name: transaction_table transaction_table_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.transaction_table
    ADD CONSTRAINT transaction_table_pkey PRIMARY KEY (transaction_id);


--
-- TOC entry 2854 (class 2606 OID 32953)
-- Name: user_table user_table_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_table
    ADD CONSTRAINT user_table_pkey PRIMARY KEY (user_id);


--
-- TOC entry 2866 (class 2606 OID 33031)
-- Name: user_has_company company_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_has_company
    ADD CONSTRAINT company_id FOREIGN KEY (company_id) REFERENCES public.company_table(company_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 2863 (class 2606 OID 32985)
-- Name: deposit_table deposit_table_cid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.deposit_table
    ADD CONSTRAINT deposit_table_cid_fkey FOREIGN KEY (company_id) REFERENCES public.company_table(company_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 2864 (class 2606 OID 33003)
-- Name: expense_table expense_cid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.expense_table
    ADD CONSTRAINT expense_cid_fkey FOREIGN KEY (company_id) REFERENCES public.company_table(company_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 2865 (class 2606 OID 33054)
-- Name: transaction_table transaction_table_cid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.transaction_table
    ADD CONSTRAINT transaction_table_cid_fkey FOREIGN KEY (company_id) REFERENCES public.company_table(company_id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;


--
-- TOC entry 2867 (class 2606 OID 33036)
-- Name: user_has_company user_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.user_has_company
    ADD CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES public.user_table(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


-- Completed on 2021-08-30 18:14:18 PDT

--
-- PostgreSQL database dump complete
--