-- ----------
-- tpcw_schema1_pgsql.sql
-- 
--  PostgreSQL specific database object definitions for the
--  PHP implementation of the TPC-W ECommerce benchmark v1.8.
--
--	This is part 2 of the schema, adding primary and foreign keys,
--	indexes, permissions and stored procedures.
-- ----------


-- ----------
-- COUNTRY
-- ----------
alter table tpcw_country 
	add primary key (co_id);

grant select on tpcw_country to apache;

-- ----------
-- AUTHOR
-- ----------
alter table tpcw_author
	add primary key (a_id);

grant select on tpcw_author to apache;

-- ----------
-- ADDRESS
-- ----------
alter table tpcw_address
	add primary key (addr_id);

alter table tpcw_address
	add constraint "Illegal Country ID in Address"
			foreign key (addr_co_id)
			references tpcw_country (co_id);

create index tpcw_address_lookup_idx on tpcw_address (addr_street1);

grant select, insert, update, delete on tpcw_address to apache;


-- ----------
-- ADDR_ID_SEQ
-- ----------
select setval('tpcw_addr_id_seq', max(addr_id)) from tpcw_address;
grant select, update on tpcw_addr_id_seq to apache;

-- ----------
-- ITEM
-- ----------
alter table tpcw_item
	add primary key (i_id);

alter table tpcw_item
	add constraint "Illegal Author ID in Item"
			foreign key (i_a_id)
			references tpcw_author (a_id);

create index tpcw_item_i_subject_idx on tpcw_item (i_subject);
create index tpcw_item_i_a_id_idx on tpcw_item (i_a_id);

grant select, insert, update, delete on tpcw_item to apache;

-- ----------
-- CUSTOMER
-- ----------
alter table tpcw_customer
	add primary key (c_id);

alter table tpcw_customer
	add constraint "Illegal Address ID in Customer"
			foreign key (c_addr_id)
			references tpcw_address (addr_id);

create unique index tpcw_customer_c_uname_idx on tpcw_customer (c_uname);

grant select, insert, update on tpcw_customer to apache;

-- ----------
-- C_ID_SEQ
-- ----------
select setval('tpcw_c_id_seq', max(c_id)) from tpcw_customer;
grant select, update on tpcw_c_id_seq to apache;

-- ----------
-- ORDERS
-- ----------
alter table tpcw_orders
	add primary key (o_id);

alter table tpcw_orders
	add constraint "Illegal Customer ID in Order"
			foreign key (o_c_id)
			references tpcw_customer (c_id);

alter table tpcw_orders
	add constraint "Illegal Address ID in Order.o_bill_addr_id"
			foreign key (o_bill_addr_id)
			references tpcw_address (addr_id);

alter table tpcw_orders
	add constraint "Illegal Address ID in Order.o_ship_addr_id"
			foreign key (o_ship_addr_id)
			references tpcw_address (addr_id);

create index tpcw_orders_o_date_idx on tpcw_orders (o_date);
cluster tpcw_orders_o_date_idx on tpcw_orders;
create index tpcw_orders_o_c_id_idx on tpcw_orders (o_c_id, o_date, o_id);

grant select, insert on tpcw_orders to apache;

-- ----------
-- O_ID_SEQ
-- ----------
select setval('tpcw_o_id_seq', max(o_id)) from tpcw_orders;
grant select, update on tpcw_o_id_seq to apache;

-- ----------
-- ORDER_LINE
-- ----------
alter table tpcw_order_line 
	add primary key (ol_id, ol_o_id);

alter table tpcw_order_line 
	add constraint "Illegal Order ID in OrderLine"
			foreign key (ol_o_id)
			references tpcw_orders (o_id);

alter table tpcw_order_line 
	add constraint "Illegal Item ID in OrderLine"
			foreign key (ol_i_id)
			references tpcw_item (i_id);

-- create index tpcw_order_line_ol_i_id_idx on tpcw_order_line (ol_i_id);
create index tpcw_order_line_ol_o_id_idx on tpcw_order_line (ol_o_id);
cluster tpcw_order_line_ol_o_id_idx on tpcw_order_line;

grant select, insert on tpcw_order_line to apache;

-- ----------
-- CC_XACTS
-- ----------
alter table tpcw_cc_xacts
	add primary key (cx_o_id);

alter table tpcw_cc_xacts
	add constraint "Illegal Order ID in CC_xacts"
			foreign key (cx_o_id)
			references tpcw_orders (o_id);

alter table tpcw_cc_xacts
	add constraint "Illegal Country ID in CC_xacts"
			foreign key (cx_co_id)
			references tpcw_country (co_id);

grant select, insert on tpcw_cc_xacts to apache;


-- ----------
-- FUNCTION tpcw_bestsellers (subject)
--
--	Select the 50 best sellers in a subject according 2.13.3.1
--
--  Description from TPC-W 2.13:
-- 
--  Of the entire set of (I_ID, I_TITLE) pairs for items on the
--  selected subject, the first 50 pairs (or less if the entire
--  set contains less than 50 pairs) are obtained. The following
--  search predicate is used (where <string> is the content of
--  SUBJECT_STRING):
-- 
--  I_SUBJECT =  <string>  for the order lines of the 3,333 most
--  recent orders (where O_ID = OL_O_ID) based on O_DATE and
--  sorted by descending sum(OL_QTY) grouped on OL_I_ID
-- ----------
create or replace function tpcw_bestsellers (varchar)
returns setof record
as '
declare
    p_subject	alias for $1;
	row			record;
begin
    for row in select i_id, i_title, a_fname, a_lname
			from tpcw_order_line join tpcw_item on ol_i_id = i_id
				join tpcw_author on i_a_id = a_id
			where ol_o_id in (select o_id from tpcw_orders
					order by o_date desc limit 3333)
			and i_subject = p_subject
			group by i_id, i_title, a_fname, a_lname
			order by sum(ol_qty) desc limit 50
	loop
		return next row;
	end loop;
	return;
end;
' language plpgsql;

-- ----------
-- FUNCTION tpcw_update_item (i_id, new_cost, new_imate, new_thumbnail)
--
--	Update the targeted item and select new values for the i_related[1-5]
--	columns according to TPC-W 2.16.3.3.
-- ----------
create or replace function tpcw_update_item (bigint, numeric, text, text)
returns setof bigint
as '
declare
	p_i_id		alias for $1;
	p_new_cost	alias for $2;
	p_new_image	alias for $3;
	p_new_thumb	alias for $4;
	v_i_id1		bigint;
	v_i_id2		bigint;
	v_i_id3		bigint;
	v_i_id4		bigint;
	v_i_id5		bigint;
	v_qty		bigint;
	v_curs		cursor (k_i_id bigint) is
		select ol_i_id, sum(ol_qty) from tpcw_order_line
		where ol_o_id in (select o_id from tpcw_orders
			where o_id in (select o_id from tpcw_orders
							order by o_date desc limit 10000)
			and o_c_id in (select o_c_id from tpcw_order_line
							join tpcw_orders on ol_o_id = o_id
							where ol_o_id in (select o_id from tpcw_orders
									order by o_date desc limit 10000)
							and ol_i_id = k_i_id
							group by o_c_id))
		and ol_i_id <> k_i_id
		group by ol_i_id
		order by sum(ol_qty) desc, ol_i_id limit 5;
begin
	open v_curs (p_i_id);

	fetch v_curs into v_i_id1, v_qty;
	if not found then
		-- need to make them up entirely
		v_i_id1 := p_i_id + 7;
		if not exists (select true from tpcw_item where i_id = v_i_id1) then
			v_i_id1 := v_i_id1 % 10;
		end if;
		return next v_i_id1;

		v_i_id2 := v_i_id1 + 7;
		if not exists (select true from tpcw_item where i_id = v_i_id2) then
			v_i_id2 := v_i_id2 % 10;
		end if;
		return next v_i_id2;

		v_i_id3 := v_i_id2 + 7;
		if not exists (select true from tpcw_item where i_id = v_i_id3) then
			v_i_id3 := v_i_id3 % 10;
		end if;
		return next v_i_id3;

		v_i_id4 := v_i_id3 + 7;
		if not exists (select true from tpcw_item where i_id = v_i_id4) then
			v_i_id4 := v_i_id4 % 10;
		end if;
		return next v_i_id4;

		v_i_id5 := v_i_id4 + 7;
		if not exists (select true from tpcw_item where i_id = v_i_id5) then
			v_i_id5 := v_i_id5 % 10;
		end if;
		return next v_i_id5;
	else
		-- found at least one, return as many as there are in
		-- the result set and from the last increment by one
		-- skipping duplicates.
		return next v_i_id1;

		fetch v_curs into v_i_id2, v_qty;
		if not found then
			v_i_id2 := v_i_id1 + 1;
			loop
				if exists (select true from tpcw_item where i_id = v_i_id2) then
					if v_i_id2 <> p_i_id
							and v_i_id2 <> v_i_id1
					then
						exit;
					end if;
					v_i_id2 := v_i_id2 + 1;
				else
					v_i_id2 = 1;
				end if;
			end loop;
		end if;
		return next v_i_id2;

		fetch v_curs into v_i_id3, v_qty;
		if not found then
			v_i_id3 := v_i_id2 + 1;
			loop
				if exists (select true from tpcw_item where i_id = v_i_id3) then
					if v_i_id3 <> p_i_id
							and v_i_id3 <> v_i_id1
							and v_i_id3 <> v_i_id2
					then
						exit;
					end if;
					v_i_id3 := v_i_id3 + 1;
				else
					v_i_id3 = 1;
				end if;
			end loop;
		end if;
		return next v_i_id3;

		fetch v_curs into v_i_id4, v_qty;
		if not found then
			v_i_id4 := v_i_id3 + 1;
			loop
				if exists (select true from tpcw_item where i_id = v_i_id4) then
					if v_i_id4 <> p_i_id
							and v_i_id4 <> v_i_id1
							and v_i_id4 <> v_i_id2
							and v_i_id4 <> v_i_id3
					then
						exit;
					end if;
					v_i_id4 := v_i_id4 + 1;
				else
					v_i_id4 = 1;
				end if;
			end loop;
		end if;
		return next v_i_id4;

		fetch v_curs into v_i_id5, v_qty;
		if not found then
			v_i_id5 := v_i_id4 + 1;
			loop
				if exists (select true from tpcw_item where i_id = v_i_id5) then
					if v_i_id5 <> p_i_id
							and v_i_id5 <> v_i_id1
							and v_i_id5 <> v_i_id2
							and v_i_id5 <> v_i_id3
							and v_i_id5 <> v_i_id4
					then
						exit;
					end if;
					v_i_id5 := v_i_id5 + 1;
				else
					v_i_id5 = 1;
				end if;
			end loop;
		end if;
		return next v_i_id5;
	end if;

	update tpcw_item 
		set i_cost		= p_new_cost,
			i_image		= p_new_image,
			i_thumbnail	= p_new_thumb,
			i_related1	= v_i_id1,
			i_related2	= v_i_id2,
			i_related3	= v_i_id3,
			i_related4	= v_i_id4,
			i_related5	= v_i_id5
		where i_id = p_i_id;

	close v_curs;
	return;
end;
' language plpgsql;


-- ----------
-- FUNCTION tpcw_store_address (street1, street2, city, state, zip, country)
--
--	Lookup an address record, add a new one if not found.
-- ----------
create or replace function tpcw_store_address (varchar, varchar,
		varchar, varchar, varchar, varchar)
returns record
as '
declare
    p_street1	alias for $1;
    p_street2	alias for $2;
    p_city		alias for $3;
    p_state		alias for $4;
    p_zip		alias for $5;
    p_country	alias for $6;
	v_co_id		integer;
	v_addr_id	bigint;
	v_result	record;
begin
	select co_id into v_co_id 
			from tpcw_country where co_name = p_country;
	if not found then 
		raise exception ''Unknown Country Name "%"'', p_country;
	end if;

	select addr_id into v_addr_id
			from tpcw_address
			where addr_street1	= p_street1
			and   addr_street2	= p_street2
			and   addr_city		= p_city
			and   addr_state	= p_state
			and   addr_zip		= p_zip
			and   addr_co_id	= v_co_id
			for update;
	if not found then
		v_addr_id = nextval(''tpcw_addr_id_seq'');
		insert into tpcw_address (
					addr_id, addr_street1, addr_street2,
					addr_city, addr_state, addr_zip, addr_co_id
				) values (
					v_addr_id, p_street1, p_street2,
					p_city, p_state, p_zip, v_co_id
				);
	end if;

	select v_addr_id as addr_id, v_co_id as co_id into v_result;
	return v_result;
end;
' language plpgsql;


-- ----------
-- FUNCTION tpcw_new_customer (c_id, uname, passwd,
--		fname, lname,
--		street1, street2, city, state, zip, country,
--		phone, email, discount, birthdate, data)
-- ----------
create or replace function tpcw_new_customer (bigint, varchar, varchar,
		varchar, varchar,
		varchar, varchar, varchar, varchar, varchar, varchar, 
		varchar, varchar, numeric, date, varchar)
returns record
as '
declare
	p_id		alias for $1;
	p_uname		alias for $2;
	p_passwd	alias for $3;
	p_fname		alias for $4;
	p_lname		alias for $5;
	p_street1	alias for $6;
	p_street2	alias for $7;
	p_city		alias for $8;
	p_state		alias for $9;
	p_zip		alias for $10;
	p_country	alias for $11;
	p_phone		alias for $12;
	p_email		alias for $13;
	p_discount	alias for $14;
	p_birthdate	alias for $15;
	p_data		alias for $16;
	v_addr_id	bigint;
	v_result	record;
begin
	select * into v_result from tpcw_store_address(p_street1, p_street2, 
			p_city, p_state, p_zip, p_country)
			as (addr_id bigint, co_id integer);

	insert into tpcw_customer (
				c_id, c_uname, c_passwd, c_fname, c_lname,
				c_addr_id, c_phone, c_email,
				c_since, c_last_visit,
				c_login, c_expiration,
				c_discount, c_balance, c_ytd_payment,
				c_birthdate, c_data
			) values (
				p_id, p_uname, p_passwd, p_fname, p_lname,
				v_result.addr_id, p_phone, p_email,
				CURRENT_DATE, CURRENT_DATE,
				CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + cast(''2 hours'' as interval),
				p_discount, ''0.00'', ''0.00'', 
				p_birthdate, p_data
			);
			
	return v_result;
end;
' language plpgsql;


-- ----------
-- FUNCTION tpcw_store_order_line (ol_id, ol_o_id, ol_i_id, ol_qty,
--								ol_discount, ol_comments);
-- ----------
create or replace function tpcw_store_order_line (integer, bigint,
		bigint, integer, numeric, varchar)
returns integer
as '
declare
    p_ol_id			alias for $1;
	p_ol_o_id		alias for $2;
	p_ol_i_id		alias for $3;
	p_ol_qty		alias for $4;
	p_ol_discount	alias for $5;
	p_ol_comments	alias for $6;
	v_i_stock		integer;
begin
	select i_stock into v_i_stock from tpcw_item
			where i_id = p_ol_i_id
			for update;
	if not found then
		raise exception ''Item % not found'', p_ol_i_id;
	end if;

	insert into tpcw_order_line (
			ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments
		) values (
			p_ol_id, p_ol_o_id, p_ol_i_id, p_ol_qty, p_ol_discount, p_ol_comments
		);

	if v_i_stock >= p_ol_qty + 10 then
		update tpcw_item set i_stock = v_i_stock - p_ol_qty
				where i_id = p_ol_i_id;
	else
		update tpcw_item set i_stock = v_i_stock - p_ol_qty + 21
				where i_id = p_ol_i_id;
	end if;
	
	return p_ol_id;
end;
' language plpgsql;


-- ----------
-- Finally analyze the database
-- ----------
analyze;


