#
#  dbtables.sql
#
#  mysql -u yourusername -D yourdatabasename < dbtables.sql
#  
#  This file will drop all previous AstraNOS tabels and 
#  recreate them from scratch. 
#  Please not that you will loose all data if you run this script
#  on a previous existing AstraNOS installation.
#
#  Table structure for users table
#
DROP TABLE IF EXISTS users;

CREATE TABLE users (
 idx int not null auto_increment primary key,
 username   varchar(30) unique,
 password   varchar(32),
 userid     varchar(32),
 userlevel  tinyint(1) unsigned not null,
 visits     int(24) unsigned,
 email      varchar(50),
 timestamp  int(11) unsigned not null,
 last_time  int(11) unsigned not null,
 disk_quota int(16) unsigned,
 restore_session tinyint(1),
 background varchar(32)
);


#
#  Table structure for active users table
#
DROP TABLE IF EXISTS active_users;

CREATE TABLE active_users (
 username varchar(30) primary key,
 timestamp int(11) unsigned not null
);


#
#  Table structure for active guests table
#
DROP TABLE IF EXISTS active_guests;

CREATE TABLE active_guests (
 ip varchar(15) primary key,
 timestamp int(11) unsigned not null
);


#
#  Table structure for banned users table
#
DROP TABLE IF EXISTS banned_users;

CREATE TABLE banned_users (
 username varchar(30) primary key,
 timestamp int(11) unsigned not null
);


#
# Icons on a users desktop
#
DROP TABLE IF EXISTS UserIcons;

CREATE TABLE UserIcons (
 idx        int not null auto_increment primary key,
 user_idx   int references users(idx),
 icon_text  varchar(64),
 icon_color varchar(10),
 icon       text,
 x_pos      int,
 y_pos      int,
 win_x      int,
 win_y      int,
 win_width  int,
 win_height int,
 execute    text,
 params     text
);

#
# Buttons of the panel (FishEyeButton)
#
DROP TABLE IF EXISTS FishEyeButtons;

CREATE TABLE FishEyeButtons (
 idx          int not null auto_increment primary key,
 user_idx     int references users(idx),
 button_pos   tinyint,
 button_text  varchar(64),
 button_color varchar(10),
 icon         text,
 win_x        int,
 win_y        int,
 win_width    int,
 win_height   int,
 execute      text,
 params       text
);

#
# Lists all currently available applications
# app_data could hold E.g. the link to a page 
# for iFrames
#
DROP TABLE IF EXISTS avail_apps;

CREATE TABLE avail_apps ( 
 idx int not null auto_increment primary key,
 app_name varchar(32),
 app_data varchar(64),
 x_pos  int,
 y_pos  int,
 width  int,
 height int
);

#
# user_apps holds the applications the user 
# has elected to use.
# app_data is allows to set specific data
# to the application.
#
DROP TABLE IF EXISTS user_apps;

CREATE TABLE user_apps (
 idx int not null auto_increment primary key,
 user_idx int references users(idx),
 app_name varchar(32),
 app_data varchar(64),
 x_pos  int,
 y_pos  int,
 width  int,
 height int
);