cart/schema/schema_art_DDL.sql

-- CREATE SCHEMA
/*
-- DROP SCHEMA art CASCADE;

CREATE SCHEMA art AUTHORIZATION sherwinp;

*/

-- POSTGRESQL SQL SCHEMA DDL: art
SET search_path TO art;

-- art."document" definition

-- Drop table

-- DROP TABLE "document";

CREATE TABLE "document" (
	document_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	document_url varchar(254) NOT NULL,
	CONSTRAINT pk_document PRIMARY KEY (document_id)
);


-- art.zip definition

-- Drop table

-- DROP TABLE zip;

CREATE TABLE zip (
	zip varchar(5) NOT NULL,
	city varchar(16) NULL,
	state varchar(2) NULL,
	CONSTRAINT pk_zip PRIMARY KEY (zip),
	CONSTRAINT zip_check CHECK (regexp_like((zip)::text, '\d{5}'::text))
);


-- art.contact definition

-- Drop table

-- DROP TABLE contact;

CREATE TABLE contact (
	contact_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	first_name varchar(16) NOT NULL,
	last_name varchar(16) NOT NULL,
	phone_no varchar(12) NULL,
	email varchar(64) NOT NULL,
	modified_by int4 NULL,
	created_dt timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
	modified_dt timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CONSTRAINT pk_contact_id PRIMARY KEY (contact_id),
	CONSTRAINT contact_modified_by_fkey FOREIGN KEY (modified_by) REFERENCES contact(contact_id)
);


-- art."plan" definition

-- Drop table

-- DROP TABLE "plan";

CREATE TABLE "plan" (
	plan_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	description varchar(32) NULL,
	fk_document_id int4 NOT NULL,
	duration int4 NULL,
	price int4 NULL,
	modified_by int4 NULL,
	created_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	modified_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT pk_plan PRIMARY KEY (plan_id),
	CONSTRAINT plan_fk_document_id_fkey FOREIGN KEY (fk_document_id) REFERENCES "document"(document_id)
);


-- art.profile definition

-- Drop table

-- DROP TABLE profile;

CREATE TABLE profile (
	profile_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	fk_contact_id int4 NOT NULL,
	active bool DEFAULT false NULL,
	modified_by int4 NOT NULL,
	created_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	modified_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT pk_profile_id PRIMARY KEY (profile_id),
	CONSTRAINT profile_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id) ON DELETE CASCADE,
	CONSTRAINT profile_modified_by_fkey FOREIGN KEY (modified_by) REFERENCES profile(profile_id)
);


-- art.staff definition

-- Drop table

-- DROP TABLE staff;

CREATE TABLE staff (
	fk_profile_id int4 NOT NULL,
	"position" varchar(14) NOT NULL,
	CONSTRAINT check_position CHECK ((("position")::text = ANY ((ARRAY['Director'::character varying, 'Curator'::character varying, 'Art Handler'::character varying, 'Registrar'::character varying, 'Associate'::character varying])::text[]))),
	CONSTRAINT pk_staff PRIMARY KEY (fk_profile_id),
	CONSTRAINT staff_fk_profile_id_fkey FOREIGN KEY (fk_profile_id) REFERENCES profile(profile_id)
);


-- art."subscription" definition

-- Drop table

-- DROP TABLE "subscription";

CREATE TABLE "subscription" (
	fk_plan_id int4 NOT NULL,
	fk_contact_id int4 NOT NULL,
	modified_by int4 NULL,
	created_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	modified_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT pk_subscription PRIMARY KEY (fk_plan_id, fk_contact_id),
	CONSTRAINT subscription_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id),
	CONSTRAINT subscription_fk_plan_id_fkey FOREIGN KEY (fk_plan_id) REFERENCES "plan"(plan_id)
);


-- art.task_history definition

-- Drop table

-- DROP TABLE task_history;

CREATE TABLE task_history (
	task_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	assigned_to_profile_id int4 NOT NULL,
	created_by_profile_id int4 NOT NULL,
	related_entity_type varchar(32) NULL,
	related_entity_id int4 NULL,
	task_type varchar(32) NOT NULL,
	task_title varchar(64) NOT NULL,
	description varchar(128) NULL,
	fk_document_id int4 NULL,
	status varchar(16) DEFAULT 'pending'::character varying NULL,
	due_date timestamp NULL,
	completed_at timestamp NULL,
	created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT chk_status CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'in_progress'::character varying, 'completed'::character varying, 'cancelled'::character varying])::text[]))),
	CONSTRAINT task_history_pkey PRIMARY KEY (task_id),
	CONSTRAINT task_history_fk_document_id_fkey FOREIGN KEY (fk_document_id) REFERENCES "document"(document_id)
);


-- art.userrole definition

-- Drop table

-- DROP TABLE userrole;

CREATE TABLE userrole (
	fk_profile_id int4 NOT NULL,
	rolename varchar(16) NULL,
	passwordhash varchar(256) DEFAULT NULL::character varying NULL,
	CONSTRAINT check_role CHECK (((rolename)::text = ANY ((ARRAY['SYSTEM'::character varying, 'CONTACT'::character varying, 'CUSTOMER'::character varying, 'STAFF'::character varying, 'SUPPLIER'::character varying])::text[]))),
	CONSTRAINT pk_userrole PRIMARY KEY (fk_profile_id),
	CONSTRAINT userrole_fk_profile_id_fkey FOREIGN KEY (fk_profile_id) REFERENCES profile(profile_id) ON DELETE CASCADE
);


-- art.address definition

-- Drop table

-- DROP TABLE address;

CREATE TABLE address (
	zip varchar(5) NOT NULL,
	fk_contact_id int4 NOT NULL,
	street varchar(32) NOT NULL,
	CONSTRAINT pk_address PRIMARY KEY (zip, fk_contact_id),
	CONSTRAINT address_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id) ON DELETE CASCADE,
	CONSTRAINT address_zip_fkey FOREIGN KEY (zip) REFERENCES zip(zip)
);


-- art.authorize definition

-- Drop table

-- DROP TABLE authorize;

CREATE TABLE authorize (
	authorization_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	fk_contact_id int4 NULL,
	authorization_status varchar(16) NOT NULL,
	authorization_date timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	expiration_date timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	fk_document_id int4 NULL,
	modified_by int4 NULL,
	created_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	modified_dt timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT authorize_pkey PRIMARY KEY (authorization_id),
	CONSTRAINT check_authorization_status CHECK (((authorization_status)::text = ANY ((ARRAY['pending'::character varying, 'approved'::character varying, 'rejected'::character varying, 'expired'::character varying])::text[]))),
	CONSTRAINT authorize_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id),
	CONSTRAINT authorize_fk_document_id_fkey FOREIGN KEY (fk_document_id) REFERENCES "document"(document_id)
);


-- art.inventory definition

-- Drop table

-- DROP TABLE inventory;

CREATE TABLE inventory (
	inventory_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	fk_contact_id int4 NOT NULL,
	fk_authorization_id int4 NULL,
	sku varchar(100) NOT NULL,
	product_description varchar(100) NOT NULL,
	unit_cost numeric(10, 2) NOT NULL,
	lead_time_days int4 NOT NULL,
	product_name varchar(100) NOT NULL,
	unit_of_measure varchar(50) NOT NULL,
	base_price numeric(10, 2) NOT NULL,
	quantity_available int4 DEFAULT 0 NOT NULL,
	created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	fk_document_id int4 NULL,
	CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
	CONSTRAINT supplier_unique_sku UNIQUE (fk_contact_id, sku),
	CONSTRAINT inventory_fk_authorization_id_fkey FOREIGN KEY (fk_authorization_id) REFERENCES authorize(authorization_id),
	CONSTRAINT inventory_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id),
	CONSTRAINT inventory_fk_document_id_fkey FOREIGN KEY (fk_document_id) REFERENCES "document"(document_id)
);


-- art.invoice definition

-- Drop table

-- DROP TABLE invoice;

CREATE TABLE invoice (
	invoice_id uuid DEFAULT gen_random_uuid() NOT NULL,
	fk_plan_id int4 NULL,
	fk_contact_id int4 NULL,
	invoicedate date NULL,
	amountpaid numeric(10, 2) DEFAULT 0 NULL,
	CONSTRAINT invoice_pkey PRIMARY KEY (invoice_id),
	CONSTRAINT invoice_fk_contact_id_fkey FOREIGN KEY (fk_contact_id) REFERENCES contact(contact_id),
	CONSTRAINT invoice_fk_plan_id_fkey FOREIGN KEY (fk_plan_id) REFERENCES "plan"(plan_id)
);


-- art.order_items definition

-- Drop table

-- DROP TABLE order_items;

CREATE TABLE order_items (
	order_item_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	fk_profile_id int4 NOT NULL,
	transaction_id varchar(32) NULL,
	created_at timestamp NOT NULL,
	quantity int4 DEFAULT 1 NOT NULL,
	unit_price numeric(10, 2) NOT NULL,
	line_total numeric(10, 2) NOT NULL,
	fk_inventory_id int4 NOT NULL,
	CONSTRAINT order_items_pkey PRIMARY KEY (order_item_id),
	CONSTRAINT order_items_fk_inventory_id_fkey FOREIGN KEY (fk_inventory_id) REFERENCES inventory(inventory_id),
	CONSTRAINT order_items_fk_profile_id_fkey FOREIGN KEY (fk_profile_id) REFERENCES profile(profile_id)
);


-- art.exhibit definition

-- Drop table

-- DROP TABLE exhibit;

CREATE TABLE exhibit (
	exhibit_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL,
	fk_inventory_id int4 NOT NULL,
	fk_plan_id int4 NULL,
	CONSTRAINT exhibit_pkey PRIMARY KEY (exhibit_id),
	CONSTRAINT exhibit_fk_inventory_id_fkey FOREIGN KEY (fk_inventory_id) REFERENCES inventory(inventory_id),
	CONSTRAINT exhibit_fk_plan_id_fkey FOREIGN KEY (fk_plan_id) REFERENCES "plan"(plan_id)
);


/* VIEW of available exhibit inventory */
CREATE OR REPLACE VIEW view_available_inventory
AS SELECT inventory.inventory_id,
    inventory.fk_contact_id,
    inventory.fk_authorization_id,
    inventory.sku,
    inventory.product_description,
    inventory.unit_cost,
    inventory.lead_time_days,
    inventory.product_name,
    inventory.unit_of_measure,
    inventory.base_price,
    inventory.quantity_available,
    inventory.created_at,
    inventory.updated_at,
    inventory.fk_document_id
   FROM art.inventory
  WHERE inventory.quantity_available > 0
INTERSECT
 SELECT exhibit.fk_inventory_id AS inventory_id,
    NULL::integer AS fk_contact_id,
    NULL::integer AS fk_authorization_id,
    NULL::character varying AS sku,
    NULL::character varying AS product_description,
    NULL::numeric AS unit_cost,
    NULL::integer AS lead_time_days,
    NULL::character varying AS product_name,
    NULL::character varying AS unit_of_measure,
    NULL::numeric AS base_price,
    NULL::integer AS quantity_available,
    NULL::timestamp without time zone AS created_at,
    NULL::timestamp without time zone AS updated_at,
    NULL::integer AS fk_document_id
   FROM art.exhibit;

CREATE OR REPLACE VIEW VIEW_SYSTEMROLE AS
	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  u.ROLENAME, p.ACTIVE  FROM
	USERROLE u
	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID AND u.ROLENAME = 'SYSTEM';

CREATE OR REPLACE VIEW VIEW_STAFF AS
	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  u.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME, s.POSITION FROM
	USERROLE u
	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID AND u.ROLENAME = 'STAFF'
	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID
    JOIN STAFF s on s.FK_PROFILE_ID = p.PROFILE_ID;

CREATE OR REPLACE VIEW VIEW_SUPPLIER AS
	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  u.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME FROM
	USERROLE u
	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID AND u.ROLENAME = 'SUPPLIER'
	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID;

CREATE OR REPLACE VIEW VIEW_CUSTOMER AS
	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  u.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME FROM
	USERROLE u
	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID AND u.ROLENAME = 'CUSTOMER'
	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID;

-- Trigger Function Paid Invoice, generates subscription
CREATE OR REPLACE FUNCTION invoice_paid_subscription()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO SUBSCRIPTION (FK_PLAN_ID, FK_CONTACT_ID) VALUES (NEW.FK_PLAN_ID, NEW.FK_CONTACT_ID);
    RETURN NEW;
END;
$function$
;
-- Trigger on invoice
create trigger paid_subscription after
insert
    on invoice for each row
    execute function invoice_paid_subscription();

CREATE OR REPLACE FUNCTION ORDER_ITEMS_reserved()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE INVENTORY SET QUANTITY_AVAILABLE = QUANTITY_AVAILABLE - NEW.QUANTITY WHERE INVENTORY_ID= NEW.FK_INVENTORY_ID;
    RETURN NEW;
END;
$function$
;
-- Trigger on ORDER_ITEMS customer ordered items reservation
CREATE TRIGGER ORDER_ITEMS_reserved AFTER
INSERT
     ON ORDER_ITEMS FOR EACH ROW
     execute function ORDER_ITEMS_reserved();