--create database moria;
use moria;
drop table if exists modules;
create table modules ( id INT NOT NULL default '0',
name VARCHAR(50),
long_name VARCHAR(150),
description VARCHAR(250),
main_content VARCHAR(90),
author VARCHAR(50),
distribution VARCHAR(50),
category VARCHAR(30),
homepage VARCHAR(90),           
PRIMARY KEY (id)
) TYPE=MyISAM;

drop table if exists clients;
create table clients ( 
client_id INT NOT NULL AUTO_INCREMENT,
hostname VARCHAR(30) NOT NULL default 'hostname',
mac VARCHAR(17) NOT NULL default '00:01:02:03:04:05',
ip VARCHAR(15) NOT NULL default 'dhcp',
profile VARCHAR(30),
distribution VARCHAR(50),
administrator VARCHAR(50),
status VARCHAR(100),
PRIMARY KEY (client_id)
) TYPE=MyISAM;

drop table if exists profiles;
create table profiles (
profile_name VARCHAR(30),
filename VARCHAR(90) NOT NULL,
description VARCHAR(250),
distribution VARCHAR(50),
PRIMARY KEY (profile_name)
) TYPE=MyISAM;

drop table if exists groups;
create table groups (
profile_name VARCHAR(30) NOT NULL,
machine_id INT NOT NULL
) TYPE=MyISAM;

drop table if exists users;
create table users (
username VARCHAR(30) NOT NULL,
password VARCHAR(50) NOT NULL default 'invalid',
real_name VARCHAR(60),
comment VARCHAR(90),
PRIMARY KEY (username)
) TYPE=MyISAM;

drop table if exists permissions;
create table permissions (
perm_name VARCHAR(30) NOT NULL,
description VARCHAR(90),
creator VARCHAR(30),
PRIMARY KEY (perm_name)
) TYPE=MyISAM;



--INSERT INTO jobs (client, cliengroup, jobtype, priority, jobparam) VALUES ("boxa", "webserver", "pkgupdate", 2, "apache")
--INSERT INTO jobs (client, cliengroup, jobtype, priority, jobparam) VALUES ("boxa", "webserver", "cfgupdate", 3, "scp://server/etc/apache2/httpd2.conf")
--INSERT INTO jobs (client, cliengroup, jobtype, priority, jobparam) VALUES ("boxa", "webserver", "runscript", 4, "scp://server/etc/apache2/updatesomething.sh")

drop table if exists jobs;
create table jobs (
job_id INT NOT NULL,
client_id INT NOT NULL,
jobtype VARCHAR(30) NOT NULL,
priority INT NOT NULL default 5,
jobparam VARCHAR(300),
timecreated TIMESTAMP NOT NULL,
timetosend TIMESTAMP NOT NULL,
timesent TIMESTAMP,
timedone TIMESTAMP,
status VARCHAR(50) NOT NULL,
created_by VARCHAR(30) NOT NULL,
description VARCHAR(250),
PRIMARY KEY (job_id)
) TYPE=MyISAM;

-- by Blackace
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
  sessionid varchar(255) character set utf8 collate utf8_bin NOT NULL default '',
  expiration int(10) unsigned NOT NULL default '0',
  data text,
  PRIMARY KEY  (sessionid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

drop table if exists user_preferences;

grant all on moria.* to hobbit identified by 'hobbitpassword';
grant all on moria.* to hobbit@localhost identified by 'hobbitpassword';
