drop sequence seq_customer_key;
create sequence seq_customer_key start 1 increment 1;

drop table Customer;
create table Customer 
(
	customer_id 		int4 		not null default nextval('seq_customer_key'),
	customer_title		varchar(3)	not null,
	first_name	 		varchar(50)	not null,
	middle_initial 		char(1),
	last_name		 	varchar(50) not null,
	street_address		varchar(70) not null,
	city				varchar(50) not null,
	state				varchar(50) not null,
	post_code			varchar(12) not null,
	country				varchar(50) not null,
	home_phone			varchar(20) not null,	
	alt_phone			varchar(20),		
	email				varchar(70) not null unique,			
	password			varchar(12) not null,				
	customer_status		char(1) 	not null default 'a',					
	create_timestamp	timestamp 	not null default now(),	
	last_update 		timestamp 	not null default now(),
	updated_by			varchar(12) not null,
	primary key			(customer_id)
);

drop sequence seq_auditcustomer_key;
create sequence seq_auditcustomer_key start 1 increment 1;
	
drop table AuditCustomer;
create table AuditCustomer 
(
	audit_key_id 			int4 		default nextval('seq_auditcustomer_key') not null,
	customer_id 			int4,
	customer_title			varchar(3),
	first_name	 			varchar(50),
	middle_initial 			char(1),
	last_name		 		varchar(50),
	street_address			varchar(70),
	city					varchar(50),
	state					varchar(50),
	post_code				varchar(12),
	country					varchar(50),
	home_phone				varchar(20),	
	alt_phone				varchar(20),		
	email					varchar(70),			
	password				varchar(12),				
	customer_status			char(1),					
	create_timestamp		timestamp,	
	last_update 			timestamp,
	updated_by				varchar(12),
	audit_log_time 			timestamp 	default now(),
	audit_operation_type 	char(1),
	primary key				(audit_key_id)
);

drop function tp_customer_a_ins();
create function tp_customer_a_ins() 
returns opaque as
	'begin
		insert into AuditCustomer 
			(customer_id,
			 customer_title,
			 first_name,
			 middle_initial,
			 last_name,
			 street_address,
			 city,
			 state,
			 post_code,
			 country,
			 home_phone,	
			 alt_phone,		
			 email,			
			 password,				
			 customer_status,					
			 create_timestamp,	
			 last_update,
			 updated_by,
			 audit_operation_type)
		values
			(new.customer_id,
			 new.customer_title,
			 new.first_name,
			 new.middle_initial,
			 new.last_name,
			 new.street_address,
			 new.city,
			 new.state,
			 new.post_code,
			 new.country,
			 new.home_phone,	
			 new.alt_phone,		
			 new.email,			
			 new.password,				
			 new.customer_status,					
			 new.create_timestamp,	
			 new.last_update,
			 new.updated_by,
			 ''i'');
		return new;
	end;'
language 'plpgsql'; 
create trigger tr_customer_a_ins 
after insert on Customer
for each row execute procedure tp_customer_a_ins();

drop function tp_customer_a_upd();
create function tp_customer_a_upd() 
returns opaque as
	'begin
		insert into AuditCustomer 
			(customer_id,
			 customer_title,
			 first_name,
			 middle_initial,
			 last_name,
			 street_address,
			 city,
			 state,
			 post_code,
			 country,
			 home_phone,	
			 alt_phone,		
			 email,			
			 password,				
			 customer_status,					
			 create_timestamp,	
			 last_update,
			 updated_by,
			 audit_operation_type)
		values
			(new.customer_id,
			 new.customer_title,
			 new.first_name,
			 new.middle_initial,
			 new.last_name,
			 new.street_address,
			 new.city,
			 new.state,
			 new.post_code,
			 new.country,
			 new.home_phone,	
			 new.alt_phone,		
			 new.email,			
			 new.password,				
			 new.customer_status,					
			 new.create_timestamp,	
			 new.last_update,
			 new.updated_by,
			 ''u'');
		return new;
	end;'
language 'plpgsql'; 
create trigger tr_customer_a_upd 
after update on Customer
for each row execute procedure tp_customer_a_upd();

drop function tp_customer_a_del();
create function tp_customer_a_del() 
returns opaque as
	'begin
		insert into AuditCustomer 
			(customer_id,
			 customer_title,
			 first_name,
			 middle_initial,
			 last_name,
			 street_address,
			 city,
			 state,
			 post_code,
			 country,
			 home_phone,	
			 alt_phone,		
			 email,			
			 password,				
			 customer_status,					
			 create_timestamp,	
			 last_update,
			 updated_by,
			 audit_operation_type)
		values
			(old.customer_id,
			 old.customer_title,
			 old.first_name,
			 old.middle_initial,
			 old.last_name,
			 old.street_address,
			 old.city,
			 old.state,
			 old.post_code,
			 old.country,
			 old.home_phone,	
			 old.alt_phone,		
			 old.email,			
			 old.password,				
			 old.customer_status,					
			 old.create_timestamp,	
			 old.last_update,
			 old.updated_by,
			 ''d'');
		return old;
	end;'
language 'plpgsql'; 
create trigger tr_customer_a_del 
after delete on Customer
for each row execute procedure tp_customer_a_del();
