-- DB Schema for IceMUD lib

drop table dirs; drop sequence dirs_seq;
drop table files; drop sequence files_seq;
drop table fileinfo; drop sequence fileinfo_seq;
drop table debug_log; drop sequence debug_log_seq;
drop table world_log; drop sequence world_log_seq;
drop table adm_log; drop sequence adm_log_seq;
--drop table progs; drop sequence progs_seq;
drop table chars; drop sequence chars_seq;
drop table accounts; drop sequence accounts_seq;


CREATE TABLE "accounts" (
	"id" bigint NOT NULL,
	"uname" text,
	"pword" text,
	"enabled" boolean,
	"lasttime" bigint,
	"lasthostaddr" text,
	"lasthostname" text,
	"lastmethod" text,
	"email1" text,
	"email2" text,
	"application" text,
	"awaiting_app" boolean,
	"activitylog" text,
	Constraint "accounts_pkey" Primary Key ("id")
);

create sequence accounts_seq;

create table chars ( 
	id bigint,
	acct bigint,
	name text,
	iswiz int,
	enabled int,
	primary key (id)
);

create sequence chars_seq;

--create table progs (
--	id bigint,
--	code text,
--	deps text,
--	lastchanged bigint,
--	lastcompiled bigint,
--	primary key (id)
--);
--
--create sequence progs_seq;

create table debug_log (
	id bigint,
	thetime bigint,
	event text,
	module text,
	backtrace text,
	whichacct bigint,
	whichchar bigint,
	severity int,
	primary key(id)
);

create sequence debug_log_seq;

create table adm_log (
	id bigint,
	thetime bigint,
	event text,
	whichacct bigint,
	whichchar bigint,
	severity int,
	primary key(id)
);

create sequence adm_log_seq;

create table world_log (
	id bigint,
	thetime bigint,
	event text,
	primary key(id)
);

create sequence world_log_seq;

create table dirs (
	id bigint,
	parent bigint default '0',
	name text not null,
	primary key(id),
	unique(name)
);

create sequence dirs_seq;

create table files (
	id bigint not null,
	parent bigint default '0',
	filename text not null,
	contents bytea,
	primary key(id),
	foreign key(parent) references dirs(id)
);

create sequence files_seq;

create table fileinfo (
	id bigint not null,
	owner bigint not null,
	gid bigint,
	acl text,
	reverse_acl smallint default '0',
	created bigint,
	modified bigint,
	accessed bigint,
	audited bigint default '0',
	auditor text,
	foreign key(id) references files(id)
);

create sequence fileinfo_seq;

insert into accounts (id, uname, enabled) values ('1', 'nemesis', '1');
insert into chars (id, acct, name, iswiz, enabled) values ('1', '1', 'Nemesis', '1', '1');

insert into accounts (id, uname, enabled) values ('2', 'witwerg', '1');
insert into chars (id, acct, name, iswiz, enabled) values ('2', '2', 'Witwerg', '1', '1');

insert into accounts (id, uname, pword, enabled) values ('3', 'test', 'test', '1');
insert into chars (id, acct, name, iswiz, enabled) values ('3', '3', 'Test', '0', '1');

insert into accounts (id, uname, enabled) values ('4', 'taerndil', '1');
insert into chars (id, acct, name, iswiz, enabled) values ('4', '4', 'Taerndil', '0', '1');

insert into dirs (id, name) values ('1', 'text');
insert into files (id, parent, filename, contents) values ('1', '1', 'issue', '
=====================================

 %%%%%%%
  \'%%%\'          @@   @
   %%%           @@@ @@@
   %%%   ### ### @@@@@@@ @@ @@ @@@,
  ,%%%, #   ##   @@ @ @@ @@ @@ @ @@
%%%%%%%%%## ### @@@   @@@\'@@@\' @@@\'
       - the dawn of ages -

=====================================
');

insert into dirs (id, name) values ('2', '/std');
