CREATE TABLE usr (
    uid			SERIAL PRIMARY KEY,
    login		VARCHAR(32) UNIQUE NOT NULL,
    passwd		VARCHAR(64) NOT NULL,
    autosave	TEXT
);

CREATE INDEX usr_login ON usr(login);

CREATE TABLE savegame (
    sid			SERIAL PRIMARY KEY,
    uid			INTEGER REFERENCES usr(uid)
			    ON DELETE CASCADE
			    ON UPDATE CASCADE,
    slot		INTEGER NOT NULL,
    savedata		TEXT
);

CREATE INDEX savegame_uid ON savegame(uid);

CREATE TABLE params (
    param	VARCHAR(16) PRIMARY KEY,
    value	INTEGER
);

INSERT INTO params(param,value)
    VALUES ('wordsize',4),
	   ('maxobj',10),
	   ('maxsave',5);

CREATE TABLE location (
    rid			SERIAL PRIMARY KEY,
    roomdesc		TEXT
);

INSERT INTO location(rid,roomdesc)
    VALUES (0,'Start Room');

CREATE TABLE vocab (
    wid		INTEGER NOT NULL,
    word	VARCHAR(32) PRIMARY KEY
);

CREATE INDEX vocab_wid ON vocab(wid);

CREATE TABLE msg (
    mid			SERIAL PRIMARY KEY,
    message		TEXT
);

CREATE TABLE smsg (
    smid		INTEGER PRIMARY KEY,
    message		TEXT
);

COPY smsg (smid, message) FROM stdin;
0	Everything is dark. I can't see
1	I can also see :-
2	I await your command.
3	I'm ready for your instructions.
4	Tell me what to do
5	Give me your command.
6	I don't understand that. Try some different words.
7	I can't go in that direction
8	I can't
9	I have with me :-
10	(worn)
11	Nothing at all
12	Do you really want to quit now ?
13	End of game. Do you want to try again ?
14	Bye. Have a nice day
15	OK.
16	Press any key to continue
17	You have taken
18	turn
19	s
20	.
21	You have scored
22	%
23	I'm not wearing it.
24	I can't. My hands are  full.
25	I already have it.
26	It's not here.
27	I can't carry any more.
28	I don't have it.
29	I'm  already wearing it.
30	Yes
31	No
\.

CREATE TABLE obj (
    objid		SERIAL PRIMARY KEY,
    objdesc		TEXT,
    startloc	INTEGER DEFAULT -1,
    wid			INTEGER DEFAULT NULL
);

INSERT INTO  obj(objid,objdesc,startloc)
    VALUES (0,'Source of Light',-1);

CREATE TABLE move (
    rid			INTEGER REFERENCES location(rid)
			    ON DELETE CASCADE
			    ON UPDATE CASCADE,
    wid			INTEGER,
    newroom		INTEGER REFERENCES location(rid)
			    ON DELETE CASCADE
			    ON UPDATE CASCADE,
    PRIMARY KEY (rid,wid)
);

CREATE TABLE action (
    aid			SERIAL PRIMARY KEY,
    tbl			INTEGER NOT NULL CHECK (tbl IN (0,1)),
    wid1		INTEGER,
    wid2		INTEGER,
    pgm			TEXT
);

CREATE INDEX action_tbl ON action(tbl);
CREATE INDEX action_wid1 ON action(wid1);
CREATE INDEX action_wid2 ON action(wid2);

