drop table IF EXISTS settings;
CREATE  TABLE IF NOT EXISTS settings (
  key VARCHAR(100) NOT NULL ,
  value VARCHAR(2000) NULL ,
  timestamp DATETIME NOT NULL DEFAULT now() ,
  PRIMARY KEY (key) );
 
drop table IF EXISTS user;
CREATE  TABLE IF NOT EXISTS user (
  username VARCHAR(45) NOT NULL ,
  password VARCHAR(100) NULL ,
  roles INT NOT NULL default 0,
  first_name VARCHAR(100) NULL ,
  last_name VARCHAR(100) NULL ,
  last_login DATETIME NULL ,
  lang VARCHAR(10) NULL ,
  creation_timestamp DATETIME NULL ,  
  modification_timestamp DATETIME NULL ,  
  PRIMARY KEY (username));

drop table IF EXISTS application;
CREATE TABLE IF NOT EXISTS application (
  id INT NOT NULL auto_increment,
  name VARCHAR(45) NOT NULL ,
  description VARCHAR(500) NULL ,
  creation_timestamp DATETIME NULL ,  
  PRIMARY KEY (id));

drop table IF EXISTS application_metadata;
CREATE TABLE IF NOT EXISTS application_metadata (
  application_id INT NOT NULL ,
  key VARCHAR(100) NOT NULL ,
  value VARCHAR(200) NULL ,
  mandatory BOOLEAN NOT NULL default true,
  creation_timestamp DATETIME NOT NULL ,  
  PRIMARY KEY (application_id, key));


drop table IF EXISTS license_type;
CREATE  TABLE IF NOT EXISTS license_type (
  id INT NOT NULL auto_increment,
  code VARCHAR(10) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  description VARCHAR(100) NULL ,
  application_id INT NULL ,
  creation_timestamp DATETIME NULL ,  
  PRIMARY KEY (id));

drop table IF EXISTS licensetype_metadata;
CREATE TABLE IF NOT EXISTS licensetype_metadata (
  id INT NOT NULL auto_increment,
  license_type_id INT NOT NULL ,
  key VARCHAR(100) NOT NULL ,
  value VARCHAR(200) NULL ,
  PRIMARY KEY (id));

drop table IF EXISTS organization;
CREATE  TABLE IF NOT EXISTS organization (
  id INT NOT NULL auto_increment,
  code VARCHAR(10) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  description VARCHAR(100) NULL ,
  org_parent_id INT NULL ,
  creation_timestamp DATETIME NULL ,  
  PRIMARY KEY (id));
  
drop table IF EXISTS user_organization;
CREATE  TABLE IF NOT EXISTS user_organization (
  username VARCHAR(45) NOT NULL,
  organization_id INT NOT NULL,  
  PRIMARY KEY (username, organization_id));
  
drop table IF EXISTS pack;
CREATE  TABLE IF NOT EXISTS pack (
  id INT NOT NULL auto_increment,
  code VARCHAR(50) NOT NULL ,
  num_licenses INT NOT NULL ,
  comments VARCHAR(1024) NULL ,
  license_type_id INT NOT NULL,  
  organization_id INT NOT NULL,  
  license_preactivation BOOLEAN NOT NULL DEFAULT true,  
  created_by varchar(45) NULL ,  
  creation_timestamp DATETIME NOT NULL ,  
  PRIMARY KEY (id));
  
drop table IF EXISTS pack_metadata;
CREATE TABLE IF NOT EXISTS pack_metadata (
  id INT NOT NULL auto_increment,
  pack_id INT NOT NULL ,
  key VARCHAR(100) NOT NULL ,
  value VARCHAR(200) NULL ,
  readonly BOOlEAN NOT NULL default false,
  PRIMARY KEY (id));
  
  
drop table IF EXISTS license;
CREATE TABLE IF NOT EXISTS license (
  id INT NOT NULL auto_increment,
  code VARCHAR(100) NOT NULL ,
  request_data VARCHAR(1024) NULL ,
  license_data VARCHAR(1024) NULL ,
  pack_id INT NOT NULL,  
  full_name VARCHAR(150) NULL,  
  email VARCHAR(100)  NOT NULL,  
  comments VARCHAR(1024) NULL ,
  creation_timestamp DATETIME NOT NULL ,  
  modification_timestamp DATETIME NULL ,  
  last_access_timestamp DATETIME NULL ,  
  expiration_date DATETIME NULL ,  
  canceled_by varchar(45) NULL ,  
  created_by varchar(45) NULL ,  
  status INT NOT NULL default 0,  
  PRIMARY KEY (id));
  
  
drop table IF EXISTS license_history;
CREATE TABLE IF NOT EXISTS license_history (
  id INT NOT NULL auto_increment,
  license_id INT NOT NULL,
  username VARCHAR(45) NOT NULL,
  timestamp DATETIME NOT NULL ,
  action VARCHAR(40) , 
  comments VARCHAR(512) ,
  PRIMARY KEY (id));