--
-- data model for on-line store (ArsDigita Shoppe)
-- 
-- created by philg@mit.edu on June 3, 1998
-- 

-- we store the ISO code in lower case, e.g,. 'us'

create table sh_country_codes (
	iso		char(2) not null primary key,
	country_name	varchar(150)
);

create sequence sh_product_id_sequence;

-- can't ever delete a product because it might have been ordered
-- and therefore the row in sh_orders would be hosed
-- so we flag it 

create table sh_products (
	product_id	integer primary key,
	short_name	varchar(100) not null,
	description	varchar(4000),
	price		number not null,
	-- we use three digits because Cybercash does
	currency	char(3) default 'USD',
	-- a soft good is something like a computer program
	-- that can be downloaded immediately
	soft_good_p	char(1) check (soft_good_p in ('t','f')),
	-- text (HTML format) to display after successful purchase
	display_after	varchar(4000),
	for_sale_p	char(1) default 't' check (for_sale_p in ('t','f'))
);

-- as a minor measure of auditing, 
-- we store the IP address of the client that placed the order
-- we want to store the IP address as a text string, separated by
-- dots.  So you'd think that we'd need 15 chars (e.g., for
-- 255.255.255.255).  But we don't want to have to bash this
-- for IPv6 too much.  So...
-- ftp://ftp.isi.edu/internet-drafts/draft-ietf-ipngwg-addr-arch-v2-06.txt
-- says that the preferred representation for these 128-bit addresses
-- is x:x:x:x:x:x:x:x where "x" is four hex digits.  So that means we
-- need 8x4+7=39 chars
-- but they also allow x:x:x:x:x:x:**old-style IP address***
-- so that is 6x4+5+15=44 chars
-- we'll make it varchar(50)!

create sequence sh_order_id_sequence;


create table sh_orders (
        -- Goes into table at confirmation time:
	order_id	integer primary key,
	product_id	integer not null references sh_products,
	confirmed_date	date,
	ip_address	varchar(50) not null,
	order_state	varchar(50) not null,
	first_names	varchar(100) not null,
	last_name	varchar(100) not null,
	email		varchar(100) not null,
	-- credit card auth stuff
	name_on_card	varchar(100),
	billing_zip_code	char(5),
	-- shipping address for non-soft goods, might be useful
	-- as invoice mailing address for soft goods
	line1		varchar(100),
	line2		varchar(100),
	city		varchar(100),
	state		varchar(100),
	postal_code	varchar(20),
	country_code	char(2) references sh_country_codes(iso),
	phone_number	varchar(20),
	price_charged	number,
	currency	char(3) default 'USD',
	-- Goes into table at authorization time (columns named
	-- cc_* refer to output from CyberCash):
	authorized_date	date,
	cc_auth_status	varchar(100),
	cc_auth_txn_id	varchar(100),
	cc_auth_errloc	varchar(100),
	cc_auth_errmsg	varchar(200),
	cc_auth_aux_msg	varchar(200),
	cc_auth_auth_code	varchar(100),
	cc_auth_action_code	char(3),
	cc_auth_avs_code	varchar(3),
	-- processor-specific; not obviously useful for cybercash
	-- perhaps useful when looking at statements from 
	-- merchant's bank 
	cc_auth_ref_code	varchar(100),
	-- Goes into table at shipping time:
	shipped_date	date,
	-- Goes into table at postauth time (i.e., when orders are marked
	-- for batching):
	postauth_date	date,
	cc_post_status	varchar(100),
	cc_post_txn_id	varchar(100),
	cc_post_errloc	varchar(100),
	cc_post_errmsg	varchar(200),
	cc_post_aux_msg	varchar(200),
	cc_post_auth_code	varchar(100),
	cc_post_action_code	char(3),
	cc_post_avs_code	varchar(3),
	cc_post_ref_code	varchar(100),
	-- Goes into table after querying for "settled" transaction type:
	cc_sett_date	date,
	cc_sett_status	varchar(100),
	cc_sett_txn_id	varchar(100),
	cc_sett_auth_code	varchar(100),
	cc_sett_batch_id	varchar(100),
	cc_sett_action_code	char(3),
	cc_sett_avs_code	varchar(3),
	cc_sett_ref_code	varchar(100),
	-- Goes into table at return time (i.e. when we use
	-- the API message "return" to mark the orders for return).
	-- tried_to_return_date exists in case CyberCash doesn't
	-- respond to our return attempt (in which case we can
	-- retry later).  
	-- Important note: "return" has no implicit connection with 
	-- the product being received back (that would recorded in
	-- the received_back_date column). 
	tried_to_return_date  date,
	return_date	date,
	refunded_amount		number,
	cc_retn_status	varchar(100),
	cc_retn_txn_id	varchar(100),
	cc_retn_errloc	varchar(100),
	cc_retn_errmsg	varchar(200),
	cc_retn_aux_msg	varchar(200),
	cc_retn_auth_code	varchar(100),
	cc_retn_action_code	char(3),
	cc_retn_avs_code	varchar(3),
	cc_retn_ref_code	varchar(100),
	-- Goes into table after querying for "setlret" transaction type
	-- (for returns that have been settled):
	-- NOTE: I'm assuming that CyberCash is automatically settling
	-- orders of type "markret" as it is orders of type "marked", since
	-- we are in auto settle mode.  We will find out shortly.
	cc_sret_date	date,
	cc_sret_status	varchar(100),
	cc_sret_txn_id	varchar(100),
	cc_sret_auth_code	varchar(100),
	cc_sret_batch_id	varchar(100),
	cc_sret_action_code	char(3),
	cc_sret_avs_code	varchar(3),
	cc_sret_ref_code	varchar(100),
	-- Goes into table when voiding a "marked" transaction
	-- The CyberCash manual states that all of the standard
	-- output fields are returned, although I've only witnessed
	-- aux-msg, Mstatus, MErrMsg, and MErrLoc
	tried_to_void_marked_date  date,
	void_marked_date	date,
	cc_vdmk_status	varchar(100),
	cc_vdmk_txn_id	varchar(100),
	cc_vdmk_errloc	varchar(100),
	cc_vdmk_errmsg	varchar(200),
	cc_vdmk_aux_msg	varchar(200),
	cc_vdmk_auth_code	varchar(100),
	cc_vdmk_action_code	char(3),
	cc_vdmk_avs_code	varchar(3),
	cc_vdmk_ref_code	varchar(100),
	-- Goes into table when voiding a "markret" transaction:
	tried_to_void_markret_date  date,
	void_markret_date	date,
	cc_vdrn_status	varchar(100),
	cc_vdrn_txn_id	varchar(100),
	cc_vdrn_errloc	varchar(100),
	cc_vdrn_errmsg	varchar(200),
	cc_vdrn_aux_msg	varchar(200),
	cc_vdrn_auth_code	varchar(100),
	cc_vdrn_action_code	char(3),
	cc_vdrn_avs_code	varchar(3),
	cc_vdrn_ref_code	varchar(100),
	-- Goes into table at various times not associated
	-- with state changes:
	-- not NULL if returned to merchant
        received_back_date      date,
        -- did we go through a merchant-initiated refund?
	-- did the consumer initiate a dispute from his end?
        disputed_p              char(1) check (disputed_p in ('t','f')),
        -- date on which we discovered the dispute
        dispute_discovery_date  date,
        -- if the consumer's bank got his money back from us forcibly
        charged_back_p          char(1) check (charged_back_p in ('t','f')),
	comments	varchar(4000)
); 


-- for accounting reports, etc., we only want to report orders that
-- got through to Cybercash

create or replace view sh_orders_reportable 
as
select * from sh_orders
where order_state not in ('confirmed','failed_authorization','void');

-- log all transactions with status other that success or
-- success-duplicate and all attempted transactions that got no 
-- response from CyberCash so that shoppe administrators
-- can view any potential problems

create sequence sh_problem_id_sequence;

create table sh_problems_log (
	problem_id	integer primary key,
	order_id	integer references sh_orders,
	problem_date	date,
	txn_type	varchar(30),
	txn_status	varchar(30),
	error_message	varchar(200)
);

