drop table IF EXISTS settings;
CREATE  TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) NOT NULL ,
  value VARCHAR(2000) NULL ,
  modification_timestamp TIMESTAMP 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(256) NULL ,
  roles INT NOT NULL default 0,
  first_name VARCHAR(100) NULL ,
  last_name VARCHAR(100) NULL ,
  last_login TIMESTAMP NULL ,
  lang VARCHAR(10) NULL ,
  email VARCHAR(150) NULL ,
  creation_timestamp TIMESTAMP NOT NULL default now(),  
  modification_timestamp TIMESTAMP 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 ,
  license_filename VARCHAR(100) NOT NULL ,
  description VARCHAR(500) NULL ,
  creation_timestamp TIMESTAMP NOT NULL default now(),  
  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 TIMESTAMP NULL default now(),  
  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 TIMESTAMP NOT NULL default now(),  
  PRIMARY KEY (id));

drop table IF EXISTS licensetype_metadata;
CREATE TABLE IF NOT EXISTS licensetype_metadata (
  license_type_id INT NOT NULL ,
  `key` VARCHAR(100) NOT NULL ,
  value VARCHAR(200) NULL ,
  mandatory BOOLEAN NOT NULL default true,
  PRIMARY KEY (license_type_id, `key`));

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 TIMESTAMP NOT NULL default now(),  
  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 ,
  init_valid_date DATE NOT NULL,
  end_valid_date DATE NOT NULL,
  status VARCHAR(2) NOT NULL default 'CR',
  comments VARCHAR(1024) NULL ,
  license_type_id INT NOT NULL,  
  organization_id INT NOT NULL,  
  license_preactivation BOOLEAN NOT NULL DEFAULT true,  
  preactivation_valid_period INT NOT NULL DEFAULT 7,  
  renew_valid_period INT NOT NULL DEFAULT 30,  
  created_by varchar(45) NULL ,  
  creation_timestamp TIMESTAMP NOT NULL default now(),
  PRIMARY KEY (id));
  
drop table IF EXISTS pack_metadata;
CREATE TABLE IF NOT EXISTS pack_metadata (
  pack_id INT NOT NULL ,
  `key` VARCHAR(100) NOT NULL ,
  value VARCHAR(200) NULL ,
  readonly BOOlEAN NOT NULL default false,
  mandatory BOOLEAN NOT NULL default true,
  PRIMARY KEY (pack_id, `key`));
  
  
drop table IF EXISTS license;
CREATE TABLE IF NOT EXISTS license (
  id INT NOT NULL auto_increment,
  code VARCHAR(100) NOT NULL ,
  code_suffix INT NULL ,
  request_data VARCHAR(1024) NULL ,
  request_data_hash VARCHAR(64) NULL ,
  license_data VARCHAR(1024) NULL ,
  pack_id INT NOT NULL,  
  full_name VARCHAR(150) NULL,  
  email VARCHAR(100) NULL,  
  comments VARCHAR(1024) NULL ,
  creation_timestamp TIMESTAMP NOT NULL default now(),  
  modification_timestamp TIMESTAMP NULL ,  
  last_access_timestamp TIMESTAMP NULL ,  
  expiration_date TIMESTAMP NULL ,  
  cancelled_by varchar(45) NULL ,  
  created_by varchar(45) NULL ,  
  status VARCHAR(2) NOT NULL default 'CR',  
  PRIMARY KEY (id),
  index(request_data_hash, pack_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,
  creation_timestamp TIMESTAMP NOT NULL default now(),  
  action VARCHAR(40) , 
  comments VARCHAR(512) ,
  PRIMARY KEY (id));
  
   
drop table IF EXISTS blocked_request;
CREATE TABLE IF NOT EXISTS blocked_request (
  hash VARCHAR(64) NOT NULL ,
  request_data VARCHAR(1024) NULL ,
  blocked_by varchar(45) NULL ,  
  creation_timestamp TIMESTAMP NOT NULL default now(),  
  PRIMARY KEY (hash));