CREATE TABLE BOTS (
	ID            INTEGER NOT NULL,
	BOTNAME       VARCHAR(60),
	PACKAGENAME   VARCHAR(40),
	DESCRIPTION   VARCHAR(2048),
	AUTHORUSERID  INTEGER NOT NULL,
	UPLOADDATE    DATE,
	DERIVEDFROM   VARCHAR(60),
	DOWNLOADS     INTEGER,
	SOURCE        BLOB,
	CLASS         BLOB
);


CREATE TABLE NEWS (
	ID              INTEGER NOT NULL,
	TITLE           VARCHAR(120),
	TEXT            BLOB,
	AUTHORUSERID    INTEGER NOT NULL,
	SUBMITDATE      DATE NOT NULL,
	APPROVALUSERID  INTEGER,
	RATING          INTEGER,
	VOTES           INTEGER,
	DURATIONDAYS    INTEGER
);


CREATE TABLE USERS (
	ID            INTEGER NOT NULL,
	USERNAME      VARCHAR(30),
	PASSWORD      VARCHAR(30),
	EMAIL         VARCHAR(60)
);


CREATE TABLE GROUPS (
	ID            INTEGER NOT NULL,
	GROUPNAME     VARCHAR(60),
	DESCRIPTION   VARCHAR(1024),
	ADMINUSERID   INTEGER
);


CREATE TABLE CATEGORIES (
	ID            INTEGER NOT NULL,
	CATEGORYNAME  VARCHAR(60),
	DESCRIPTION   VARCHAR(1024),
	TYPEID        INTEGER,
	ADMINONLY     INTEGER
);


CREATE TABLE TYPES (
	ID            INTEGER NOT NULL,
	TYPENAME      VARCHAR(20)
);


CREATE TABLE USERSGROUPS (
	USERID        INTEGER NOT NULL,
	GROUPID       INTEGER NOT NULL
);


CREATE TABLE BOTSGROUPS (
	BOTID         INTEGER NOT NULL,
	GROUPID       INTEGER NOT NULL,
	SOURCEACCESS  INTEGER,
	CLASSACCESS   INTEGER
);


CREATE TABLE BOTSCATEGORIES (
	BOTID         INTEGER NOT NULL,
	CATEGORYID    INTEGER NOT NULL
);


CREATE TABLE NEWSCATEGORIES (
	NEWSID        INTEGER NOT NULL,
	CATEGORYID    INTEGER NOT NULL
);



INSERT INTO BOTS VALUES (1,'Tracker3','dan','Savage little bot - when it isnt just a sitting duck.',2,null,'Tracker',0,null,null);
INSERT INTO BOTS VALUES (2,'SnackBot',null,'Shifty little fella.  Real hard to target.',3,null,'CrazyBot',0,null,null);
INSERT INTO BOTS VALUES (3,'JazzBot3','gpc','Very sophisticated tracking skills.',1,null,'Tracker',0,null,null);

INSERT INTO BOTSGROUPS VALUES (1,1,0,1);
INSERT INTO BOTSGROUPS VALUES (1,7,1,1);
INSERT INTO BOTSGROUPS VALUES (2,1,1,1);
INSERT INTO BOTSGROUPS VALUES (3,6,1,1);

INSERT INTO BOTSCATEGORIES VALUES (1,13);
INSERT INTO BOTSCATEGORIES VALUES (1,15);
INSERT INTO BOTSCATEGORIES VALUES (1,20);
INSERT INTO BOTSCATEGORIES VALUES (2,15);
INSERT INTO BOTSCATEGORIES VALUES (3,15);
INSERT INTO BOTSCATEGORIES VALUES (3,13);

INSERT INTO USERS VALUES (1,'robocode','robocode','robocode@dev.null');
INSERT INTO USERS VALUES (2,'Dan Lynn','Dan Lynn','robodan@dev.null');
INSERT INTO USERS VALUES (3,'Jason','Jason','jason@dev.null');

INSERT INTO GROUPS VALUES (1,'Public','Anything associated with the public group is available to everyone.',0);
INSERT INTO GROUPS VALUES (2,'Admin','Must be a member of this group to perform site maintenance.',0);
INSERT INTO GROUPS VALUES (3,'NewsAdmin','This group can perform admin tasks on news items.',0);
INSERT INTO GROUPS VALUES (4,'NewsWriter','This group can submit news items.',0);
INSERT INTO GROUPS VALUES (5,'TipsAdmin','This group can perform admin tasks on tips.',0);

INSERT INTO GROUPS VALUES (6,'Bots-R-Us','We are not toying around!',2);
INSERT INTO GROUPS VALUES (7,'DeveloperForge','Ok - so we are web developers - not bot developers!',2);

INSERT INTO USERSGROUPS VALUES (1,1);
INSERT INTO USERSGROUPS VALUES (1,6);
INSERT INTO USERSGROUPS VALUES (1,7);
INSERT INTO USERSGROUPS VALUES (2,1);
INSERT INTO USERSGROUPS VALUES (2,2);
INSERT INTO USERSGROUPS VALUES (2,7);
INSERT INTO USERSGROUPS VALUES (3,1);
INSERT INTO USERSGROUPS VALUES (3,2);

INSERT INTO TYPES VALUES (1,'Bot');
INSERT INTO TYPES VALUES (2,'Code Snippet');
INSERT INTO TYPES VALUES (3,'Tip');
INSERT INTO TYPES VALUES (4,'News');
INSERT INTO TYPES VALUES (5,'Link');
INSERT INTO TYPES VALUES (6,'Role');

INSERT INTO CATEGORIES VALUES (1,'Movement','Tips & strategies concerning the bot movement.',3,0);
INSERT INTO CATEGORIES VALUES (2,'Scanning','Tips & strategies for keeping track of the other bots.',3,0);
INSERT INTO CATEGORIES VALUES (3,'Bot Collision','Tips & strategies avoiding or causing collision damage.',3,0);
INSERT INTO CATEGORIES VALUES (4,'Wall Collision','What to do when your bot is up against the wall.',3,0);
INSERT INTO CATEGORIES VALUES (5,'Hit By Bullet','What works best? Ignore? Run? Turn and attack?',3,0);
INSERT INTO CATEGORIES VALUES (6,'General Design','What overall design concepts work best?',3,0);

INSERT INTO CATEGORIES VALUES (7,'Site News','General news about site updates.',4,0);
INSERT INTO CATEGORIES VALUES (8,'Events','Information about events hosted by the site.',4,0);

INSERT INTO CATEGORIES VALUES (9,'Web Sites','Links to sites devoted to RoboCode.',5,0);
INSERT INTO CATEGORIES VALUES (10,'Discussion Sites','NewsGroup and other discussion forums dedicated to RoboCode.',5,0);
INSERT INTO CATEGORIES VALUES (11,'Tutorials','RoboCode tutorials available on the web.',5,0);

INSERT INTO CATEGORIES VALUES (12,'Original Bots','The bots which came with the original download of RoboCode.',1,1);
INSERT INTO CATEGORIES VALUES (13,'Showcase Bots','Bots identified by RoboCode Repository as exceptional.',1,1);
INSERT INTO CATEGORIES VALUES (14,'Extends Robot','Bots which extend the Robot class instead of AdvancedRobot.',1,0);
INSERT INTO CATEGORIES VALUES (15,'Extends AdvancedRobot','Bots which extend the AdvancedRobot class.',1,0);
INSERT INTO CATEGORIES VALUES (16,'Rammers','Attack by ramming into opponents.',1,0);
INSERT INTO CATEGORIES VALUES (17,'Avoiders','Generally fast and agile, keep moving to avoid being hit.',1,0);
INSERT INTO CATEGORIES VALUES (18,'Trackers','Track and follow a bot during while attacking.',1,0);
INSERT INTO CATEGORIES VALUES (19,'Walls','Often follow the walls.',1,0);
INSERT INTO CATEGORIES VALUES (20,'Multimode','Switch between modes of attack or defense on the fly.',1,0);
INSERT INTO CATEGORIES VALUES (21,'Advance Targeting','Uses advanced math or algorithms to fire ahead of target.',1,0);