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 NULL ,
  full_name VARCHAR(100) NULL ,
  short_name VARCHAR(3) 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 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 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 (
  user_id INT NOT NULL,
  organization_id INT NOT NULL,  
  PRIMARY KEY (user_id, organization_id));
  
drop table IF EXISTS pack;
CREATE  TABLE IF NOT EXISTS pack (
  id INT NOT NULL,
  code VARCHAR(50) NOT NULL ,
  num_licenses INT NOT NULL ,
  license_type_id INT NOT NULL,  
  organization_id INT NOT NULL,  
  created_by varchar(45) NULL ,  
  creation_timestamp DATETIME NOT NULL ,  
  PRIMARY KEY (id));
  
drop table IF EXISTS license;
CREATE TABLE IF NOT EXISTS license (
  id INT NOT NULL,
  code VARCHAR(100) NOT NULL ,
  pack_id INT NOT NULL,  
  user_name INT NULL,  
  user_email INT NOT NULL,  
  creation_timestamp DATETIME NOT NULL ,  
  sent_timestamp DATETIME NULL ,  
  modification_timestamp DATETIME NULL ,  
  cancelation_timestamp DATETIME NULL ,  
  canceled_by varchar(45) NULL ,  
  created_by varchar(45) NULL ,  
  status VARCHAR(3) NULL ,  
  PRIMARY KEY (id));