CREATE DATABASE  IF NOT EXISTS zoomscript ;

USE zoomscript;

CREATE TABLE process_info (
  job_id varchar(200) NOT NULL,
  asset_id int(11) DEFAULT NULL,
  username varchar(45) DEFAULT NULL,
  start_time bigint(50) DEFAULT NULL,
  end_time bigint(50) DEFAULT NULL,
  status_str varchar(45) DEFAULT NULL,
  filename varchar(1000) DEFAULT NULL,
  size_of_asset bigint(50) DEFAULT NULL,
  type varchar(45) DEFAULT NULL,
  category varchar(100) DEFAULT NULL,
  sub_category varchar(100) DEFAULT NULL,
  error_code longtext,
  machine_id varchar(200) DEFAULT NULL,
  PRIMARY KEY (job_id)
);

CREATE TABLE asset_job_info(
	job_id int NOT NULL,
	asset_id int NOT NULL,
	status_str varchar(100) DEFAULT NULL,
	start_time bigint DEFAULT NULL,
	end_time bigint DEFAULT NULL,
	current_milestone int DEFAULT NULL,
	current_milestone_start_time bigint DEFAULT NULL,
	filename varchar(255) Default NULL,
	size_of_asset bigint Default NULL,
    PRIMARY KEY(job_id,asset_id)
	);


	CREATE TABLE error_info(
	error_code int NOT NULL,
	severity int NOT NULL,
	possible_resolutions varchar(100) Default NULL,
	descriptions varchar(250) Default NULL,
	PRIMARY KEY(error_code)
	);

	CREATE TABLE failure_info(
	job_id int NOT NULL,
	error_code int NOT NULL,
	occurence_time bigint Default NULL
);

CREATE TABLE job_info(
	job_id int NOT NULL,
	script_type_id int NOT NULL,
	username varchar(250) DEFAULT NULL,
	start_time bigint DEFAULT NULL,
	end_time bigint DEFAULT NULL,
	status_str varchar(10) DEFAULT NULL,
	type varchar(10) DEFAULT NULL,
PRIMARY KEY(job_id)
);

CREATE TABLE milestone_info(
	milestone_id int  NOT NULL,
	milestone_label varchar(100) NOT NULL,
	descriptions varchar(250) NOT NULL,
PRIMARY KEY (milestone_id)
);

CREATE TABLE script_milestone(
	script_id int NOT NULL,
	milestone_id int NOT NULL,
	milestone_time_interval_ratio int NOT NULL,
	single_group varchar(6) NOT NULL,
	static_dynamic varchar(7) NOT NULL,
	rolling_avg float NOT NULL,
	milestone_order int NOT NULL
);

CREATE TABLE script_type_info(
	script_type_id int NOT NULL,
	category varchar(100) NOT NULL,
	sub_category varchar(100) NOT NULL
);