drop owned by "speaker-to-postgres" cascade;
drop database if exists mitsfs;
drop role if exists "speaker-to-postgres";
create role "speaker-to-postgres" superuser createrole createdb noinherit;
grant "speaker-to-postgres" to :USER;
set role "speaker-to-postgres";
create database mitsfs owner="speaker-to-postgres" encoding='UTF8';
drop role if exists libcomm;
drop role if exists mancinicomm;
drop role if exists panthercomm;
drop role if exists hasslecomm;
drop role if exists "*chamber";
drop role if exists keyholders;
drop role if exists surgeon;
drop role if exists system;
create role libcomm;
grant libcomm to :USER;
create role "*chamber" createrole noinherit;
grant libcomm to "*chamber" with admin option;
create role panthercomm;
create role hasslecomm;
create role keyholders role panthercomm, hasslecomm admin "*chamber";
grant panthercomm to libcomm;
create role mancinicomm;
grant mancinicomm to panthercomm;
create role surgeon;
create role system noinherit;
grant system to postgres;
grant system to wheel;
grant system to "speaker-to-postgres";
--grant system to phrogg;
\c mitsfs
set role "speaker-to-postgres";
-- end preamble
create language plpythonu;


drop function if exists current_client() cascade;
create function current_client() returns text as $$
    return GD.setdefault('mitsfs.client','SQL')
$$ language plpythonu;

drop function if exists set_client(text) cascade;
create function set_client(text) returns text as $$
    GD['mitsfs.client']=args[0]
    return GD['mitsfs.client']
$$ language plpythonu;

-- drop function stash_td() cascade;
-- create function stash_td() returns trigger as $$
--    GD['TD'] = dict(TD)
--     return 'OK'
-- $$ language plpythonu;
-- create trigger stash before update or insert or delete on x for each row execute procedure stash_td();

-- create function pypeek(text) returns text as $$
--    import pprint
--    return pprint.pformat(eval(args[0]))
-- $$ language plpythonu;

drop function if exists get_gd() cascade;
create function get_gd() returns text as $$
    import pprint
    return pprint.pformat(GD)
$$ language plpythonu;

drop function if exists update_row_modified() cascade;
create function update_row_modified() returns trigger as $$
    if 'relname_plan' not in SD:
        SD['relname_plan'] = plpy.prepare('select relname from pg_class where oid=$1', ['oid'])
    relname_plan = SD['relname_plan']
    relid = TD['relid']
    relid_map = GD.setdefault('relid_map', {})
    if relid in relid_map:
        name = relid_map[relid]
    else:
        name = plpy.execute(relname_plan, [relid])[0]['relname']
        relid_map[relid] = name
    if 'user_plan' not in SD:
        SD['user_plan'] = plpy.prepare('select current_user')
    user_plan = SD['user_plan']
    current_user = plpy.execute(user_plan)[0]['current_user']
    for arg in ('_created', '_created_by', '_created_with'): 
        TD['new'][name + arg] = TD['old'][name + arg]
    TD['new'][name + '_modified'] = 'now'
    TD['new'][name + '_modified_by'] = current_user
    TD['new'][name + '_modified_with'] = GD.setdefault('mitsfs.client','SQL')
    return 'MODIFY'
$$ language plpythonu;

drop function if exists insert_row_created_with() cascade;
create function insert_row_created_with() returns trigger as $$
    if 'relname_plan' not in SD:
        SD['relname_plan'] = plpy.prepare('select relname from pg_class where oid=$1', ['oid'])
    relname_plan = SD['relname_plan']
    relid = TD['relid']
    relid_map = GD.setdefault('relid_map', {})
    if relid in relid_map:
        name = relid_map[relid]
    else:
        name = plpy.execute(relname_plan, [relid])[0]['relname']
        relid_map[relid] = name
    TD['new'][name + '_created_with'] = GD.setdefault('mitsfs.client','SQL')
    TD['new'][name + '_modified_with'] = GD.setdefault('mitsfs.client','SQL')
    return 'MODIFY'
$$ language plpythonu;

drop function if exists collateral_update() cascade;
create function collateral_update() returns trigger as $$
    target = TD['args'][0]
    if target not in SD:
        SD[target] = plpy.prepare('update %s set %s_modified = current_timestamp where %s_id=$1' % (target, target, target), ['int4'])
    if TD['event'] != 'DELETE':
        row = TD['new']
    else:
        row = TD['old']
    plpy.execute(SD[target], [row[target+'_id']])
    return 'OK'
$$ language plpythonu;

drop function if exists log_row() cascade;
create function log_row() returns trigger as $$
    GD['TD'] = dict(TD)

    if 'relname_plan' not in SD:
        SD['relname_plan'] = plpy.prepare('select relname from pg_class where oid=$1', ['oid'])
    relname_plan = SD['relname_plan']
    relid = TD['relid']
    relid_map = GD.setdefault('relid_map', {})
    if relid in relid_map:
        name = relid_map[relid]
    else:
        name = plpy.execute(relname_plan, [relid])[0]['relname']
        relid_map[relid] = name

    event = TD['event']
    change = '%s %s\n' % (event, name)

    if TD['args'] is not None:
        id_column = TD['args'][0]
    else:
        id_column = name + '_id'
    filtercolumns = [name + i for i in ('_created',
                                        '_created_by',
                                        '_created_with',
                                        '_modified',
                                        '_modified_by',
                                        '_modified_with',)]

    if event == 'INSERT':
        log = [(column, str(value))
               for (column, value) in TD['new'].items()
               if column not in filtercolumns]
        obj_id = TD['new'][id_column]
    elif event == 'DELETE':
        log = [(column, str(value))
               for (column, value) in TD['old'].items()
               if column not in filtercolumns]
        obj_id = TD['old'][id_column]
    elif event == 'UPDATE':
        log = []
        for ((column, oldvalue), (othercolumn, newvalue)) in zip(TD['old'].items(), TD['new'].items()):
            if column not in filtercolumns and oldvalue != newvalue:
                log.append((column, str(oldvalue)))
                log.append((column, str(newvalue)))
        obj_id = TD['new'][id_column]

    for (column, value) in log:
        change += '%s %d\n' % (column, len(value.split('\n')))
        change += '%s\n' % value
    
    if 'log_plan' not in SD:
        SD['log_plan'] = plpy.prepare("INSERT INTO LOG (obj_id, relid, client, change) VALUES ($1,$2,$3,$4)", ['int4', 'oid', 'text', 'text'])
    log_plan = SD['log_plan']

    if log:
        plpy.execute(log_plan, [obj_id, relid, GD.setdefault('mitsfs.client','SQL'), change])
    return 'OK'
$$ language plpythonu;

reset role;
