--
-- Table schema for php_users for use on ffii database
-- transition step toward database structure described at http://a2e.de/i2p/ffiidb
--

drop table php_users;
create table php_users (
  uid varchar(8) unique references pnom,
  remind boolean,
  startdat timestamp,
  kredat timestamp,
  titol text );
grant all on table php_users to group knecht ;
comment on table php_users is 'extra information needed by a2e php_users user managment syste http://a2e.de/adv/php/users';
comment on column php_users.status is '0: unconfirmed, 1: enabled, -1: disabled' ;

drop table fotoref cascade;
create table fotoref (
   fotoref varchar(4) unique primary key,
   fotorrem text       
);
insert into fotoref values ( 'norm', 'passport photo' );
comment on table fotoref is 'classes of photos  used in case someone wants to register several here -- in the future this table may be replacable by an enum type' ;
comment on column fotoref.fotoref is 'class of photo, defaults to norm';
comment on column fotoref.fotorem is 'comment on class of photo';
grant all on table fotoref to group knecht ;

drop table fotos;
create table fotos (
  uid varchar(8) unique references pnom,
  fotoref varchar(4) references fotoref default 'norm',
  fotourl text,
  fotoht integer,
  fotowd integer,
  unique (uid, fotoref)
 );
comment on table fotos is 'information about photo of person' ;
grant all on table fotos to group knecht ;

drop table pgpref cascade; 
create table pgpref (
   pgpref varchar(4) unique primary key,
   pgprrem text
);
insert into pgpref values ( 'norm', 'main pgp key, usable to decrypt mail' );
comment on table pgpref is 'classes of pgp keys used in case someone wants to register several here -- in the future this table may be replacable by an enum type' ;
comment on column pgpref.pgpref is 'class of pgp key, defaults to norm';
comment on column pgpref.pgprrem is 'comment on class of pgp key';
grant all on table pgpref to group knecht ;

drop table pgp;
create table pgp (
  uid varchar(8) unique references pnom,
  pgpref varchar(4) references pgpref default 'norm',
  fingerpr text,
  pgpkey text,
  unique(uid, pgpref)
) ;
comment on table pgp is 'pgp keys of person' ;
grant all on table pgp to group knecht ;

drop table permdom cascade;
create table permdom (
   permdom varchar(16) unique primary key,
   permdrem text       
);
insert into permdom values ( 'php_users', 'default domain usesd by php_users' );
comment on table permdom is 'permission domain -- realm to which a permission may apply' ;
comment on column permdom.permdom is 'permission domain -- realm to which a permission may apply' ;
comment on column permdom.permdrem is 'explanatory comment on permdom';
grant all on table permdom to group knecht ;

drop table permvar cascade;
create table permvar (
   permvar varchar(24) unique primary key,
   permvrem text       
);
insert into permvar values ( 'list_users', 'action defined by majordojo php_users' );
insert into permvar values ( 'edit_self', 'action defined by majordojo php_users' );
insert into permvar values ( 'edit_users', 'action defined by majordojo php_users' );
insert into permvar values ( 'set_perm', 'action defined by majordojo php_users' );
insert into permvar values ( 'edit_perm', 'action defined by majordojo php_users' );
insert into permvar values ( 'change_other_pass', 'action defined by majordojo php_users' );
comment on table permvar is 'permission variable -- activity to which a permission may apply' ;
comment on column permvar.permvar is 'permission variable -- activity to which a permission may apply' ;
comment on column permvar.permvrem is 'explanatory comment on permvar';
grant all on table permvar to group knecht ;

drop table perms;
create table perms (
  uid varchar(8) references pnom,
  permdom varchar(16) references permdom,
  permvar varchar(24) references permvar,
  permval integer,
  permuid varchar(8) references pnom(uid),
  permrem text,
  permtmp timestamp default now(),
  unique (uid,permdom,permrem) ) ;

insert into perms values ('phm','php_users','list_users',1);
insert into perms values ('phm','php_users','edit_self',1);
insert into perms values ('phm','php_users','edit_users',1);
insert into perms values ('phm','php_users','set_perm',1);
insert into perms values ('phm','php_users','edit_perm',1);
insert into perms values ('phm','php_users','change_other_pass',1);

comment on table perms is 'permission granted by permuid to uid for conducting activity permvar in domain permdom at level permval' ;
comment on column perms.uid is 'person to whom permission is granted' ;
comment on column perms.permdom is 'domain of permitted activity' ;
comment on column perms.permvar is 'permitted activity' ;
comment on column perms.permval is 'extent to which activity is permitted, according to scale defined by permvar and explained in permvar.permvrem';
comment on column perms.permuid is 'permittant, i.e. person who granted the permission' ;
comment on column perms.permrem is 'explanatatory remarks by permittant' ;
grant all on table perms to group knecht ;

drop table logtoks;
create table logtoks ( uid varchar(8) references pnom, token text, newpass text, newmail text ) ;
comment on table logtoks is 'login tokens -- transient information stored and retrieved by php_users http://a2e.de/adv/php/users in order to ensure that the intended person is signing up or changing his authentication data' ;
comment on column logtoks.token is 'login token -- transient information stored and retrieved in order to ensure that the person who is trying to enter the site is the one who just had personal instructions sent to him' ;
comment on column logtoks.newmail is 'transient email address that will replace the real email address once the user has verified it';
comment on column logtoks.newpass is 'transient password that will replace the real password once the user has followed the instructions we mailed to him';
grant all on table logtoks to group knecht ;
