set role "speaker-to-postgres";
update member set member_name = substring(member_name from E'(.*) \\(') where member_name like '%(%)';
update member set member_name = substring(member_name from '.*, (.*)') || ' ' || substring(member_name from '(.*), ') where member_name like '%,%';
update member set member_pseudo = true where member_initials in ('CHECKOUTRS', 'CHECKOUTTZ', 'THE_ZGT');
update member set member_name='' where member_initials = member_name;

create table member_name (
       member_name_id integer default nextval('id_seq') primary key,
       member_id integer not null references member,
       member_name text not null);

create trigger member_name_insert
       before insert or update or delete on member_name
       for each row execute procedure collateral_update('member');
create trigger member_name_log
       before insert or update or delete on member_name
       for each row execute procedure log_row();

grant insert, delete, select on member_name to keyholders;

insert into member_name (member_id, member_name)
 select member_id, member_initials from member where member_initials != '';
insert into member_name (member_id, member_name)
 select member_id, member_name from member where member_name != '';

alter table member drop column member_name;
alter table member drop column member_initials;

insert into member(member_pseudo) values (true);
insert into member_name(member_id, member_name) select currval('id_seq'), 'CASH';

create table member_magic (
       member_name text unique primary key,
       member_id integer not null references member);

grant select on member_magic to keyholders;

insert into member_magic (member_name, member_id) select member_name, member_id from member_name where member_name='CASH';
insert into member_magic (member_name, member_id) select member_name, member_id from member_name where member_name='BLUEBELLCOMM';
insert into member_magic (member_name, member_id) select member_name, member_id from member_name where member_name='NEWSHELF';

create table member_email (
       member_email_id integer default nextval('id_seq') primary key,
       member_id integer not null references member,
       member_email text not null);

create trigger member_email_insert
       before insert or update or delete on member_email
       for each row execute procedure collateral_update('member');
create trigger member_email_log
       before insert or update or delete on member_email
       for each row execute procedure log_row();

grant insert, delete, select on member_email to keyholders;

create table address_type (
       address_type char(1) not null primary key,
       address_type_description text);

grant select on address_type to keyholders;

insert into address_type values ('?', 'Unspecified');
insert into address_type values ('P', 'Permanent');
insert into address_type values ('T', 'Term');
insert into address_type values ('M', 'MIT Work');

create table member_address (
       member_address_id integer default nextval('id_seq') primary key,
       member_id integer not null references member,
       member_address text not null,
       address_type char(1) default '?' references address_type not null);

create trigger member_address_insert
       before insert or update or delete on member_address
       for each row execute procedure collateral_update('member');
create trigger member_address_log
       before insert or update or delete on member_address
       for each row execute procedure log_row();

grant insert, delete, select on member_address to keyholders;

create table member_comment (
       member_comment_id integer default nextval('id_seq') primary key,
       member_id integer not null references member,
       member_comment text not null);

create trigger member_comment_insert
       before insert or update or delete on member_comment
       for each row execute procedure collateral_update('member');
create trigger member_comment_log
       before insert or update or delete on member_comment
       for each row execute procedure log_row();

grant insert, delete, select on member_comment to keyholders;

create table transaction_type (
       transaction_type char(1) primary key,
       transaction_type_description text not null,
       transaction_type_basic boolean not null);

grant select on transaction_type to keyholders;

insert into transaction_type values ('F', 'Asssess Fine', true);
insert into transaction_type values ('P', 'Payment', true);
insert into transaction_type values ('D', 'Donation for Fine Credit', true);
insert into transaction_type values ('K', 'Asssess Keyfine', true);
insert into transaction_type values ('M', 'Membership', false);
insert into transaction_type values ('R', 'Reimbursement', false);
insert into transaction_type values ('V', 'Void Previous', false);
insert into transaction_type values ('O', 'Other', false);
insert into transaction_type values ('L', 'LHE', false);

create table transaction (
       transaction_id integer default nextval('id_seq') not null primary key,
       transaction_amount numeric not null,
       member_id integer references member not null,
       transaction_type char references transaction_type not null,
       transaction_description text not null,

       transaction_created timestamp with time zone default current_timestamp not null,
       transaction_created_by varchar(64) default current_user not null,
       transaction_created_with varchar(64) default current_client());

create trigger transaction_log
       before update or delete on transaction for each row execute procedure log_row();
-- note no insert

grant insert, select on transaction to keyholders;

create table membership_type (
       membership_type char(1) primary key,
       membership_description text not null);

grant select on membership_type to keyholders;

insert into membership_type values ('P', 'Permanent');
insert into membership_type values ('L', 'Life');
insert into membership_type values ('Y', 'Year');
insert into membership_type values ('S', 'Summer');

create table membership (
       membership_id integer default nextval('id_seq') primary key,
       member_id integer not null references member,
       membership_expires timestamp with time zone,
       membership_type char(1) references membership_type default 'Y' not null,
       membership_payment integer references transaction(transaction_id),

       membership_created timestamp with time zone default current_timestamp not null,
       membership_created_by varchar(64) default current_user not null,
       membership_created_with varchar(64) default current_client() not null,
       membership_modified timestamp with time zone default current_timestamp not null,
       membership_modified_by varchar(64) default current_user not null,
       membership_modified_with varchar(64) default current_client() not null);

create trigger membership_insert
       before insert on membership for each row execute procedure insert_row_created_with();
create trigger membeship_update
       before update on membership for each row execute procedure update_row_modified();
create trigger membership_log
       before insert or update or delete on membership for each row execute procedure log_row();

grant insert, select on membership to keyholders;

create table fine_payment (
       checkout_id integer not null references checkout,
       transaction_id integer not null references transaction);

create trigger fine_payment_log
       before update or delete on fine_payment for each row execute procedure log_row();
-- note no insert

grant insert, select on fine_payment to keyholders;

create table balance_transfer (
       transaction_id1 integer not null references transaction(transaction_id),
       transaction_id2 integer not null references transaction(transaction_id));

create trigger balance_transfer_log
       before update or delete on balance_transfer for each row execute procedure log_row();
-- note no insert

grant insert, select on balance_transfer to keyholders;

create table timewarp (
       timewarp_id integer default nextval('id_seq') primary key,
       timewarp_start timestamp with time zone not null,
       timewarp_end timestamp with time zone not null,
       timewarp_modified timestamp with time zone default current_timestamp not null,
       timewarp_modified_by varchar(64) default current_user not null,
       timewarp_modified_with varchar(64) default 'SQL' not null,
       timewarp_created timestamp with time zone default current_timestamp not null,
       timewarp_created_by varchar(64) default current_user not null,
       timewarp_created_with varchar(64) default current_client());

create trigger timewarp_insert
       before insert on timewarp for each row execute procedure insert_row_created_with();
create trigger timewarp_update
       before update on timewarp for each row execute procedure update_row_modified();
create trigger timewarp_log
       before insert or update or delete on timewarp for each row execute procedure log_row();

grant select on timewarp to keyholders;
grant insert, update, delete on timewarp to "*chamber";

--insert into member(member_initials) values ('SURGERY');
--insert into member(member_initials) values ('BLUEBELLCOMM');
--insert into member(member_initials) values ('CHECKOUT');
--insert into member(member_initials) values ('NEWSHELF');
--insert into member(member_initials) values ('LIBCOMM');

