ERROR 1114 (HY000): The table 'table name' is full

[Kuldeep Singh]

While creating Database schema [only Table creation without any data in table using following commend] on mysql cluster i am facing following error:

mysql>\. schema.sql

ERROR 1114 (HY000): The table 'time_translations' is full
Total tables in schema: ~34

**********************************************************
*********Info related to MySql Cluster:****************
***********************************************************
Server version: 5.6.11-ndb-7.3.2-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)
ENGINE TYPE: NDBCLUSTER
Replica: 2

OS: Red Hat Enterprise Linux Server release 6.0 (Santiago)
MemTotal: 32881364 kB [~32GB]

uname -a
Linux globladel16 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

************My.cnf file:**************************
[mysqld]
ndbcluster
ndb-connectstring=localhost
thread_cache_size=250
max_heap_table_size=2048M
tmp_table_size=2048M
max_connections=250
wait_timeout=7200
query_cache_type=ON
query_cache_size=1024M
ndb_cluster_connection_pool=2
ndb_autoincrement_prefetch_sz=1024

[mysql_cluster]
ndb-connectstring=localhost

***********************Config.ini**********************
[ndbd default]
NoOfReplicas=2 # Number of replicas
DataMemory=2048M # How much memory to allocate for data storage
IndexMemory=2048M # How much memory to allocate for index storage
TransactionDeadlockDetectionTimeout=10000 # Time to wait for query execution by another node before aborting the transaction
MaxNoOfTables=4096

### Management node configuration section ###
[ndb_mgmd]
NodeId=1
hostname=localhost # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files

### Data node configuration section ###
[ndbd]
hostname=localhost # Hostname or IP address
datadir=/var/lib/mysql-cluster/data # Directory for this data node.s data files

[ndbd]
hostname=localhost # Hostname or IP address
datadir=/var/lib/mysql-cluster/data # Directory for this data node.s data files

### First MySQL Server configuration section ###
[mysqld]
NodeId=50
hostname=localhost # Hostname or IP address

[mysqld]
NodeId=51
hostname=localhost # Hostname or IP address


*******************Following text is our schema.sql ***********
*****************************************************************
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `claroNginDb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `claroNginDb` ;

-- -----------------------------------------------------
-- Table `claroNginDb`.`routing_type_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`routing_type_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`routing_type_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`routing_type_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_routing_type_name` ON `claroNginDb`.`routing_type_dictionary` (`routing_type_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`announcements`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`announcements` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`announcements` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`ann_name` VARCHAR(50) NOT NULL ,
`ann_file_path` VARCHAR(100) NOT NULL ,
`info` VARCHAR(100) NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_ann_name` ON `claroNginDb`.`announcements` (`ann_name` ASC) ;

CREATE UNIQUE INDEX `udx_ann_file_path` ON `claroNginDb`.`announcements` (`ann_file_path` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`error_actions_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`error_actions_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`error_actions_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`error_action_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_error_actions_dictionary` ON `claroNginDb`.`error_actions_dictionary` (`error_action_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`agents`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`agents` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`agents` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`agent_name` VARCHAR(50) NOT NULL ,
`primary_no_user_part` VARCHAR(30) NOT NULL ,
`primary_no_domain_part` VARCHAR(50) NOT NULL ,
`busy_action_ref` INT UNSIGNED NOT NULL ,
`busy_no_user_part` VARCHAR(30) NULL ,
`busy_no_domain_part` VARCHAR(50) NULL ,
`nr_action_ref` INT UNSIGNED NOT NULL ,
`nr_no_user_part` VARCHAR(30) NULL ,
`nr_no_domain_part` VARCHAR(50) NULL ,
`othr_err_action_ref` INT UNSIGNED NOT NULL ,
`othr_err_no_user_part` VARCHAR(30) NULL ,
`othr_err_no_domain_part` VARCHAR(50) NULL ,
`queue_max_length` INT UNSIGNED NULL ,
`queue_retry_time` INT UNSIGNED NULL ,
`queue_max_wait_time` INT UNSIGNED NULL ,
`queue_max_attempts` INT UNSIGNED NULL DEFAULT 3 ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
`welcome_ann_ref` INT UNSIGNED NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_primary_no_user_part_primary_no_domain_part` ON `claroNginDb`.`agents` (`primary_no_user_part` ASC, `primary_no_domain_part` ASC) ;

CREATE UNIQUE INDEX `udx_agent_name` ON `claroNginDb`.`agents` (`agent_name` ASC) ;

ALTER TABLE `claroNginDb`.`agents` ADD CONSTRAINT `fk_agents_busy_action_ref_error_actions_dictionary_id` FOREIGN KEY (`busy_action_ref` ) REFERENCES `claroNginDb`.`error_actions_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`agents` ADD CONSTRAINT `fk_agents_nr_action_ref_error_actions_dictionary_id` FOREIGN KEY (`nr_action_ref` ) REFERENCES `claroNginDb`.`error_actions_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`agents` ADD CONSTRAINT `fk_agents_othr_err_action_ref_error_actions_dictionary_id` FOREIGN KEY (`othr_err_action_ref` ) REFERENCES `claroNginDb`.`error_actions_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`agents` ADD CONSTRAINT `fk_welcome_ann_ref_announcements_id` FOREIGN KEY (`welcome_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;


-- -----------------------------------------------------
-- Table `claroNginDb`.`service_numbers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`service_numbers` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`service_numbers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`service_number` VARCHAR(30) NOT NULL ,
`customer_name` VARCHAR(50) NOT NULL ,
`service_enable_flag` TINYINT(1) NOT NULL DEFAULT 1 ,
`routing_type_ref` INT UNSIGNED NOT NULL ,
`screening_flag` TINYINT(1) NOT NULL DEFAULT 0 ,
`queue_flag` TINYINT(1) NOT NULL DEFAULT 0 ,
`ivr_flag` TINYINT(1) NOT NULL DEFAULT 0 ,
`welcome_ann_ref` INT UNSIGNED NULL ,
`agent_ref` INT UNSIGNED NULL ,
`info` VARCHAR(100) NULL ,
`cac_formatter` VARCHAR(30) NULL ,
`cac_prefix` VARCHAR(10) NULL ,
`call_limit_value` INT UNSIGNED NULL ,
`call_limit_rel_ref` INT UNSIGNED NULL ,
`call_limit_counter` INT UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_service_number` ON `claroNginDb`.`service_numbers` (`service_number` ASC) ;

CREATE INDEX `idx_customer_name` ON `claroNginDb`.`service_numbers` (`customer_name` ASC) ;

ALTER TABLE `claroNginDb`.`service_numbers` ADD CONSTRAINT `fk_service_numbers_routing_type_ref_routing_type_dictionary_id` FOREIGN KEY (`routing_type_ref` ) REFERENCES `claroNginDb`.`routing_type_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`service_numbers` ADD CONSTRAINT `fk_service_numbers_welcome_ann_ref_announcemets_id` FOREIGN KEY (`welcome_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`service_numbers` ADD CONSTRAINT `fk_service_numbers_agent_ref_agents_id` FOREIGN KEY (`agent_ref` ) REFERENCES `claroNginDb`.`agents` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`service_numbers` ADD CONSTRAINT `fk_service_numbers_call_limit_rel_ref_release_info_id` FOREIGN KEY (`call_limit_rel_ref` ) REFERENCES `claroNginDb`.`release_info` (`id` );

-- -----------------------------------------------------
-- Table `claroNginDb`.`node_type_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`node_type_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`node_type_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`node_type_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_node_type_name` ON `claroNginDb`.`node_type_dictionary` (`node_type_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`branch_type_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`branch_type_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`branch_type_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`branch_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_branch_name` ON `claroNginDb`.`branch_type_dictionary` (`branch_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`nt_trees`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`nt_trees` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`nt_trees` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`service_number_ref` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_service_number_ref` ON `claroNginDb`.`nt_trees` (`service_number_ref` ASC) ;

ALTER TABLE `claroNginDb`.`nt_trees` ADD CONSTRAINT `fk_nt_trees_service_number_ref_service_numbers_id` FOREIGN KEY (`service_number_ref` ) REFERENCES `claroNginDb`.`service_numbers` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`nt_tree_nodes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`nt_tree_nodes` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`nt_tree_nodes` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nt_tree_ref` INT UNSIGNED NOT NULL ,
`node_ref` INT UNSIGNED NOT NULL ,
`branch_type_ref` INT UNSIGNED NOT NULL ,
`node_type_ref` INT UNSIGNED NOT NULL ,
`parent_node_ref` INT UNSIGNED NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_nt_tree_ref_branch_type_ref_parent_node_ref` ON `claroNginDb`.`nt_tree_nodes` (`nt_tree_ref` ASC, `branch_type_ref` ASC, `parent_node_ref` ASC) ;

CREATE INDEX `idx_nt_tree_ref` ON `claroNginDb`.`nt_tree_nodes` (`nt_tree_ref` ASC) ;

ALTER TABLE `claroNginDb`.`nt_tree_nodes` ADD CONSTRAINT `fk_nt_tree_nodes_node_type_ref_node_type_dictionary_id` FOREIGN KEY (`node_type_ref` ) REFERENCES `claroNginDb`.`node_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`nt_tree_nodes` ADD CONSTRAINT `fk_nt_tree_nodes_branch_type_ref_branch_type_dictionary_id` FOREIGN KEY (`branch_type_ref` ) REFERENCES `claroNginDb`.`branch_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`nt_tree_nodes` ADD CONSTRAINT `fk_nt_tree_nodes_parent_node_ref_nt_tree_nodes_id` FOREIGN KEY (`parent_node_ref` ) REFERENCES `claroNginDb`.`nt_tree_nodes` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`nt_tree_nodes` ADD CONSTRAINT `fk_nt_tree_nodes_nt_tree_ref_nt_trees_id` FOREIGN KEY (`nt_tree_ref` ) REFERENCES `claroNginDb`.`nt_trees` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`list_type_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`list_type_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`list_type_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`list_type_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_list_type_name` ON `claroNginDb`.`list_type_dictionary` (`list_type_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`generic_lists`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`generic_lists` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`generic_lists` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`list_type_ref` INT UNSIGNED NOT NULL ,
`list_name` VARCHAR(50) NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_list_name` ON `claroNginDb`.`generic_lists` (`list_name` ASC) ;

CREATE INDEX `idx_list_type_ref` ON `claroNginDb`.`generic_lists` (`list_type_ref` ASC) ;

ALTER TABLE `claroNginDb`.`generic_lists` ADD CONSTRAINT `fk_generic_lists_list_type_ref_list_type_dictionary_id` FOREIGN KEY (`list_type_ref` ) REFERENCES `claroNginDb`.`list_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`hunting_lists`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`hunting_lists` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`hunting_lists` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`hunting_list_name` VARCHAR(50) NOT NULL ,
`err_action_ref` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
`welcome_ann_ref` INT UNSIGNED NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_hunting_list_name` ON `claroNginDb`.`hunting_lists` (`hunting_list_name` ASC) ;

ALTER TABLE `claroNginDb`.`hunting_lists` ADD CONSTRAINT `fk_hunting_lists_err_action_ref_error_actions_dictionary_id` FOREIGN KEY (`err_action_ref` ) REFERENCES `claroNginDb`.`error_actions_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`hunting_lists` ADD CONSTRAINT `fk_hunting_lists_welcome_ann_ref_announcements_id_idx` FOREIGN KEY (`welcome_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`numbers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`numbers` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`numbers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`generic_list_ref` INT UNSIGNED NULL DEFAULT NULL ,
`hunting_list_ref` INT UNSIGNED NULL DEFAULT NULL ,
`number_user_part` VARCHAR(30) NOT NULL ,
`number_domain_part` VARCHAR(50) NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
`priority` INT UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_generic_list_ref_number_user_part_number_domain_part` ON `claroNginDb`.`numbers` (`generic_list_ref` ASC, `number_user_part` ASC, `number_domain_part` ASC) ;

CREATE UNIQUE INDEX `udx_hunting_list_ref_number_user_part_number_domain_part` ON `claroNginDb`.`numbers` (`hunting_list_ref` ASC, `number_user_part` ASC, `number_domain_part` ASC) ;

CREATE INDEX `idx_generic_list_ref` ON `claroNginDb`.`numbers` (`generic_list_ref` ASC) ;

CREATE INDEX `idx_hunting_list_ref` ON `claroNginDb`.`numbers` (`hunting_list_ref` ASC) ;

ALTER TABLE `claroNginDb`.`numbers` ADD CONSTRAINT `fk_numbers_generic_list_ref_generic_lists_id` FOREIGN KEY (`generic_list_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`numbers` ADD CONSTRAINT `fk_numbers_hunting_list_ref_hunting_lists_id` FOREIGN KEY (`hunting_list_ref` ) REFERENCES `claroNginDb`.`hunting_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`dates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`dates` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`dates` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`date_range_name` VARCHAR(50) NOT NULL ,
`start_date` DATE NOT NULL ,
`end_date` DATE NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_start_date_end_date` ON `claroNginDb`.`dates` (`group_ref` ASC, `start_date` ASC, `end_date` ASC) ;

CREATE INDEX `idx_group_ref` ON `claroNginDb`.`dates` (`group_ref` ASC) ;

CREATE UNIQUE INDEX `udx_group_ref_date_rang_name` ON `claroNginDb`.`dates` (`group_ref` ASC, `date_range_name` ASC) ;

ALTER TABLE `claroNginDb`.`dates` ADD CONSTRAINT `fk_dates_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`days_of_week`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`days_of_week` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`days_of_week` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`dow_name` VARCHAR(50) NOT NULL ,
`days` VARCHAR(7) NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_dow_name` ON `claroNginDb`.`days_of_week` (`dow_name` ASC) ;

-- -----------------------------------------------------
-- Table `claroNginDb`.`time_of_day`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`time_of_day` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`time_of_day` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`time_name` VARCHAR(50) NOT NULL ,
`start_time` TIME NOT NULL ,
`end_time` TIME NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_start_time_end_time` ON `claroNginDb`.`time_of_day` (`group_ref` ASC, `start_time` ASC, `end_time` ASC) ;

CREATE INDEX `idx_group_ref` ON `claroNginDb`.`time_of_day` (`group_ref` ASC) ;

CREATE UNIQUE INDEX `udx_group_ref_time_name` ON `claroNginDb`.`time_of_day` (`group_ref` ASC, `time_name` ASC) ;

ALTER TABLE `claroNginDb`.`time_of_day` ADD CONSTRAINT `fk_time_of_day_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`target_type_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`target_type_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`target_type_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`target_type_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_target_type_name` ON `claroNginDb`.`target_type_dictionary` (`target_type_name` ASC) ;

-- -----------------------------------------------------
-- Table `claroNginDb`.`quotas`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`quotas` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`quotas` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`quota` INT UNSIGNED NOT NULL ,
`call_counter` INT UNSIGNED NOT NULL DEFAULT 0 ,
`target_type` INT UNSIGNED NOT NULL ,
`target_ref` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_target_type_target_ref` ON `claroNginDb`.`quotas` (`group_ref` ASC, `target_ref` ASC, `target_type` ASC) ;

CREATE INDEX `idx_group_ref` ON `claroNginDb`.`quotas` (`group_ref` ASC) ;

CREATE INDEX `idx_agent_ref` ON `claroNginDb`.`quotas` (`target_ref` ASC) ;

ALTER TABLE `claroNginDb`.`quotas` ADD CONSTRAINT `fk_quotas_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`quotas` ADD CONSTRAINT `fk_quotas_target_type_ref_list_type_dictionary_id` FOREIGN KEY (`target_type` ) REFERENCES `claroNginDb`.`target_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`prompt_and_collect`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`prompt_and_collect` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`prompt_and_collect` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`ann_name` VARCHAR(50) NOT NULL ,
`ann_file_path` VARCHAR(100) NOT NULL ,
`min_digits` INT UNSIGNED NOT NULL DEFAULT 0 ,
`max_digits` INT UNSIGNED NOT NULL ,
`return_key` VARCHAR(5) NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_ann_name` ON `claroNginDb`.`prompt_and_collect` (`ann_name` ASC) ;

CREATE UNIQUE INDEX `udx_ann_file_path` ON `claroNginDb`.`prompt_and_collect` (`ann_file_path` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`ivr_menus`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`ivr_menus` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`ivr_menus` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`menu_name` VARCHAR(50) NOT NULL ,
`menu_ann_ref` INT UNSIGNED NOT NULL ,
`wrong_digit_ann_ref` INT UNSIGNED NULL ,
`option_timeout_ann_ref` INT UNSIGNED NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_menu_name` ON `claroNginDb`.`ivr_menus` (`menu_name` ASC) ;

ALTER TABLE `claroNginDb`.`ivr_menus` ADD CONSTRAINT `fk_ivr_menus_menu_ann_ref_prompt_and_collect_id` FOREIGN KEY (`menu_ann_ref` ) REFERENCES `claroNginDb`.`prompt_and_collect` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`ivr_menus` ADD CONSTRAINT `fk_ivr_menus_wrong_digit_ann_ref_announcemets_id` FOREIGN KEY (`wrong_digit_ann_ref` ) REFERENCES `claroNginDb`.`prompt_and_collect` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`ivr_menus` ADD CONSTRAINT `fk_iv_menus_option_timeout_ann_ref_announcemets_id` FOREIGN KEY (`option_timeout_ann_ref` ) REFERENCES `claroNginDb`.`prompt_and_collect` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`menu_actions_dictionary`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`menu_actions_dictionary` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`menu_actions_dictionary` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`menu_action_name` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_menu_action_name` ON `claroNginDb`.`menu_actions_dictionary` (`menu_action_name` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`ivr_menu_choices`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`ivr_menu_choices` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`ivr_menu_choices` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`ivr_menu_ref` INT UNSIGNED NOT NULL ,
`digit` VARCHAR(20) NOT NULL ,
`digit_action_ref` INT UNSIGNED NOT NULL ,
`target_ref` INT UNSIGNED NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_ivr_menu_ref_digit` ON `claroNginDb`.`ivr_menu_choices` (`ivr_menu_ref` ASC, `digit` ASC) ;

ALTER TABLE `claroNginDb`.`ivr_menu_choices` ADD CONSTRAINT `fk_ivr_menu_choices_ivr_menu_ref_ivr_menus_id` FOREIGN KEY (`ivr_menu_ref` ) REFERENCES `claroNginDb`.`ivr_menus` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`ivr_menu_choices` ADD CONSTRAINT `fk_ivr_menu_choices_digit_action_ref_menu_actions_dictionary_id` FOREIGN KEY (`digit_action_ref` ) REFERENCES `claroNginDb`.`menu_actions_dictionary` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`ivrs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`ivrs` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`ivrs` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`ivr_name` VARCHAR(50) NOT NULL ,
`main_menu_ref` INT UNSIGNED NOT NULL ,
`max_wrong_attempts` INT UNSIGNED NOT NULL DEFAULT 3 ,
`wrong_attempts_ann_ref` INT UNSIGNED NOT NULL ,
`max_levels` INT UNSIGNED NOT NULL DEFAULT 5 ,
`max_levels_ann_ref` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_ivr_name` ON `claroNginDb`.`ivrs` (`ivr_name` ASC) ;

ALTER TABLE `claroNginDb`.`ivrs` ADD CONSTRAINT `fk_ivrs_main_menu_ref_ivr_menus_id` FOREIGN KEY (`main_menu_ref` ) REFERENCES `claroNginDb`.`ivr_menus` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`ivrs` ADD CONSTRAINT `fk_ivrs_max_levels_ann_ref_announcemets_id` FOREIGN KEY (`max_levels_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`ivrs` ADD CONSTRAINT `fk_ivrs_wrong_attempts_ann_ref_announcemets_id` FOREIGN KEY (`wrong_attempts_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`release_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`release_info` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`release_info` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`rel_name` VARCHAR(50) NOT NULL ,
`rel_cause` INT UNSIGNED NULL ,
`rel_ann_flag` TINYINT(1) NOT NULL DEFAULT 0 ,
`rel_ann_ref` INT UNSIGNED NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_rel_name` ON `claroNginDb`.`release_info` (`rel_name` ASC) ;

ALTER TABLE `claroNginDb`.`release_info` ADD CONSTRAINT `fk_release_info_rel_ann_ref_announcemets_id` FOREIGN KEY (`rel_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`screening_params`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`screening_params` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`screening_params` (
`service_number_ref` INT UNSIGNED NOT NULL ,
`bw_list_ref` INT UNSIGNED NULL ,
`catg_list_ref` INT UNSIGNED NULL DEFAULT 0 ,
`date_ref` INT UNSIGNED NULL ,
`pub_holiday_ref` INT UNSIGNED NULL ,
`pvt_holiday_ref` INT UNSIGNED NULL ,
`dow_ref` INT UNSIGNED NULL ,
`tod_ref` INT UNSIGNED NULL ,
`gl_flag` TINYINT(1) NOT NULL DEFAULT 0 ,
`err_ann_ref` INT UNSIGNED NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`service_number_ref`)) ENGINE=NDBCLUSTER;

ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_service_number_ref_service_numbers_id` FOREIGN KEY (`service_number_ref` ) REFERENCES `claroNginDb`.`service_numbers` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_bw_list_ref_generic_lists_id` FOREIGN KEY (`bw_list_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_date_ref_generic_lists_id` FOREIGN KEY (`date_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_pub_holiday_ref_generic_lists_id` FOREIGN KEY (`pub_holiday_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_pvt_holiday_ref_generic_lists_id` FOREIGN KEY (`pvt_holiday_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_dow_ref_days_of_week_id` FOREIGN KEY (`dow_ref` ) REFERENCES `claroNginDb`.`days_of_week` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_tod_ref_generic_lists_id` FOREIGN KEY (`tod_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_err_ann_ref_announcements_id` FOREIGN KEY (`err_ann_ref`) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`screening_params` ADD CONSTRAINT `fk_screening_params_catg_list_ref_generic_lists_id` FOREIGN KEY (`catg_list_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`grey_params`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`grey_params` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`grey_params` (
`service_number_ref` INT UNSIGNED NOT NULL ,
`gl_ref` INT UNSIGNED NOT NULL ,
`gl_ann_ref` INT UNSIGNED NOT NULL ,
`gl_reenter_ann_ref` INT UNSIGNED NOT NULL ,
`gl_reject_ann_ref` INT UNSIGNED NOT NULL ,
`gl_min_pin_length` INT UNSIGNED NOT NULL DEFAULT 8 ,
`gl_max_pin_length` INT UNSIGNED NOT NULL DEFAULT 20 ,
`gl_max_attempts` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`service_number_ref`)) ENGINE=NDBCLUSTER;

ALTER TABLE `claroNginDb`.`grey_params` ADD CONSTRAINT `fk_grey_params_service_number_ref_service_numbers_id` FOREIGN KEY (`service_number_ref`) REFERENCES `claroNginDb`.`service_numbers` (`id`) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`grey_params` ADD CONSTRAINT `fk_grey_params_gl_ref_generic_lists_id` FOREIGN KEY (`gl_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`grey_params` ADD CONSTRAINT `fk_grey_params_gl_ann_ref_prompt_and_collect_id` FOREIGN KEY (`gl_ann_ref` ) REFERENCES `claroNginDb`.`prompt_and_collect` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`grey_params` ADD CONSTRAINT `fk_grey_params_gl_reenter_ann_ref_prompt_and_collect_id` FOREIGN KEY (`gl_reenter_ann_ref` ) REFERENCES `claroNginDb`.`prompt_and_collect` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`grey_params` ADD CONSTRAINT `fk_grey_params_gl_reject_ann_ref_announcements_id` FOREIGN KEY (`gl_reject_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`grey_numbers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`grey_numbers` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`grey_numbers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`clg_no_user_part` VARCHAR(30) NOT NULL ,
`clg_no_domain_part` VARCHAR(50) NOT NULL ,
`passwd` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_clg_no_user_part_clg_no_domain_part` ON `claroNginDb`.`grey_numbers` (`group_ref` ASC, `clg_no_user_part` ASC, `clg_no_domain_part` ASC) ;

CREATE INDEX `idx_group_ref` ON `claroNginDb`.`grey_numbers` (`group_ref` ASC) ;

ALTER TABLE `claroNginDb`.`grey_numbers` ADD CONSTRAINT `fk_grey_numbers_gn_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`categories`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`categories` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`categories` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`category_name` VARCHAR(50) NOT NULL ,
`category_action` TINYINT(1) NULL ,
`info` VARCHAR(100) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_category_name` ON `claroNginDb`.`categories` (`category_name` ASC, `group_ref` ASC) ;

ALTER TABLE `claroNginDb`.`categories` ADD CONSTRAINT `fk_categories_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`error_params`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`error_params` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`error_params` (
`service_number_ref` INT UNSIGNED NOT NULL ,
`generic_error_rel_cause` INT UNSIGNED NOT NULL DEFAULT 503 ,
`agent_busy_rel_cause` INT UNSIGNED NULL DEFAULT 486 ,
`agent_busy_ann_ref` INT UNSIGNED NULL ,
`no_reply_timer` INT UNSIGNED NOT NULL DEFAULT 10 ,
`agent_nr_rel_cause` INT UNSIGNED NULL DEFAULT 503 ,
`agent_nr_ann_ref` INT UNSIGNED NULL ,
`agent_othr_err_rel_cause` INT UNSIGNED NULL DEFAULT 503 ,
`agent_othr_err_ann_ref` INT UNSIGNED NULL ,
`agent_queue_ann_ref` INT UNSIGNED NULL ,
`hl_err_rel_cause` INT UNSIGNED NULL DEFAULT 503 ,
`hl_err_ann_ref` INT UNSIGNED NULL ,
`agent_queue_full_ann_ref` INT UNSIGNED NULL ,
`agent_queue_timeout_ann_ref` INT UNSIGNED NULL ,
PRIMARY KEY (`service_number_ref`)) ENGINE=NDBCLUSTER;

ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_agent_busy_ann_ref_announcemets_id` FOREIGN KEY (`agent_busy_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_agent_nr_ann_ref_announcemets_id` FOREIGN KEY (`agent_nr_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_agent_othr_err_ann_ref_announcemets_id` FOREIGN KEY (`agent_othr_err_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_agent_service_number_ref_service_numbers_id` FOREIGN KEY (`service_number_ref` ) REFERENCES `claroNginDb`.`service_numbers` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_queue_ann_ref_announcements_id` FOREIGN KEY (`agent_queue_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_hl_err_ann_ref_announcements_id` FOREIGN KEY (`hl_err_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_queue_timeout_ann_ref_announcements_id` FOREIGN KEY (`agent_queue_timeout_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`error_params` ADD CONSTRAINT `fk_error_params_queue_full_ann_ref_announcements_id` FOREIGN KEY (`agent_queue_full_ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
-- -----------------------------------------------------
-- Table `claroNginDb`.`user_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`user_info` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`user_info` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(25) NOT NULL ,
`first_name` VARCHAR(30) NOT NULL ,
`lastname` VARCHAR(30) NULL ,
`email` VARCHAR(50) NULL ,
`password` VARCHAR(30) NOT NULL ,
`phone_number` VARCHAR(30) NULL ,
`rights` VARCHAR(30) NOT NULL ,
`counter` INT(2) NOT NULL DEFAULT 0 ,
`role` VARCHAR(20) NULL ,
`active_flag` TINYINT(1) NOT NULL DEFAULT 1 ,
`info` VARCHAR(45) NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_username` ON `claroNginDb`.`user_info` (`username` ASC) ;


-- -----------------------------------------------------
-- Table `claroNginDb`.`mass_provisioning_history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`mass_provisioning_history` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`mass_provisioning_history` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_ref` INT UNSIGNED NOT NULL ,
`status` VARCHAR(30) NOT NULL ,
`start_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`end_time` TIMESTAMP NULL ,
`input_file` VARCHAR(100) NOT NULL ,
`output_file` VARCHAR(100) NULL ,
`info` VARCHAR(100) NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

ALTER TABLE `claroNginDb`.`mass_provisioning_history` ADD CONSTRAINT `fk_mass_provisioning_history_user_ ref_user_info_ id` FOREIGN KEY (`user_ref` ) REFERENCES `claroNginDb`.`user_info` (`id` ) ON DELETE RESTRICT ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`user_history`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`user_history` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`user_history` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_name` VARCHAR(25) NOT NULL ,
`entity_name` VARCHAR(50) NULL DEFAULT NULL ,
`entity_id` VARCHAR(10) NULL DEFAULT NULL ,
`table_name` VARCHAR(50) NOT NULL ,
`operation_type` VARCHAR(20) NOT NULL ,
`result` VARCHAR(20) NOT NULL ,
`timestamp` VARCHAR(50) NOT NULL ,
PRIMARY KEY (`id`) ) ENGINE=NDBCLUSTER;

CREATE INDEX `idx_user_name` ON `claroNginDb`.`user_history` (`user_name` ASC) ;

-- -----------------------------------------------------
-- Table `claroNginDb`.`origin_translations`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`origin_translations` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`origin_translations` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`group_ref` INT UNSIGNED NOT NULL ,
`origin_ref` INT UNSIGNED NULL ,
`target_type_ref` INT UNSIGNED NOT NULL ,
`target_ref` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_origin_ref` ON `claroNginDb`.`origin_translations` (`group_ref` ASC, `origin_ref` ASC) ;

ALTER TABLE `claroNginDb`.`origin_translations` ADD CONSTRAINT `fk_origin_translations_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`origin_translations` ADD CONSTRAINT `fk_origin_translations_target_type_ref_list_type_dictionary_id` FOREIGN KEY (`target_type_ref` ) REFERENCES `claroNginDb`.`target_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`origin_translations` ADD CONSTRAINT `fk_origin_translations_origin_ref_generic_lists_id` FOREIGN KEY (`origin_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`time_translations`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`time_translations` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`time_translations` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`group_ref` INT UNSIGNED NOT NULL ,
`date_ref` INT UNSIGNED NULL ,
`dow_ref` INT UNSIGNED NULL ,
`time_ref` INT UNSIGNED NULL ,
`target_type_ref` INT UNSIGNED NOT NULL ,
`target_ref` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE INDEX `fk_time_translations_dow_ref_day_of_week_id_idx` ON `claroNginDb`.`time_translations` (`dow_ref` ASC) ;

CREATE UNIQUE INDEX `udx_group_ref_date_ref_dow_ref_time_ref` ON `claroNginDb`.`time_translations` (`group_ref` ASC, `date_ref` ASC, `dow_ref` ASC, `time_ref` ASC) ;

ALTER TABLE `claroNginDb`.`time_translations` ADD CONSTRAINT `fk_time_translations_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`time_translations` ADD CONSTRAINT `fk_time_translations_target_type_ref_list_type_dictionary_id` FOREIGN KEY (`target_type_ref` ) REFERENCES `claroNginDb`.`target_type_dictionary` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`time_translations` ADD CONSTRAINT `fk_time_translations_date_ref_generic_lists_id` FOREIGN KEY (`date_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`time_translations` ADD CONSTRAINT `fk_time_translations_time_ref_generic_lists_id` FOREIGN KEY (`time_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`time_translations` ADD CONSTRAINT `fk_time_translations_dow_ref_day_of_week_id` FOREIGN KEY (`dow_ref` ) REFERENCES `claroNginDb`.`days_of_week` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- -----------------------------------------------------
-- Table `claroNginDb`.`blackwhite`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `claroNginDb`.`blackwhite` ;

CREATE TABLE IF NOT EXISTS `claroNginDb`.`blackwhite` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`group_ref` INT UNSIGNED NOT NULL ,
`bl_flag` TINYINT(1) NOT NULL DEFAULT 1 ,
`bw_list_ref` INT UNSIGNED NOT NULL ,
`bw_list_exception_ref` INT UNSIGNED NULL ,
`priority` INT UNSIGNED NOT NULL DEFAULT 0 ,
`ann_ref` INT UNSIGNED NOT NULL ,
`info` VARCHAR(100) NULL ,
PRIMARY KEY (`id`)) ENGINE=NDBCLUSTER;

CREATE UNIQUE INDEX `udx_group_ref_bl_flag_bw_list_ref_bw_list_exception_ref` ON `claroNginDb`.`blackwhite` (`group_ref` ASC, `bw_list_ref` ASC, `bw_list_exception_ref` ASC) ;

CREATE INDEX `idx_group_ref` ON `claroNginDb`.`blackwhite` (`group_ref` ASC) ;

ALTER TABLE `claroNginDb`.`blackwhite` ADD CONSTRAINT `fk_blackwhite_group_ref_generic_lists_id` FOREIGN KEY (`group_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`blackwhite` ADD CONSTRAINT `fk_blackwhite_bw_list_ref_generic_lists_id` FOREIGN KEY (`bw_list_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`blackwhite` ADD CONSTRAINT `fk_blackwhite_bw_list_exception_ref_generic_lists_id` FOREIGN KEY (`bw_list_exception_ref` ) REFERENCES `claroNginDb`.`generic_lists` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `claroNginDb`.`blackwhite` ADD CONSTRAINT `fk_blackwhite_ann_ref_ann_ref_id` FOREIGN KEY (`ann_ref` ) REFERENCES `claroNginDb`.`announcements` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`routing_type_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`routing_type_dictionary` (`id`, `routing_type_name`) VALUES (NULL, 'Simple');
INSERT INTO `claroNginDb`.`routing_type_dictionary` (`id`, `routing_type_name`) VALUES (NULL, 'Tree');
INSERT INTO `claroNginDb`.`routing_type_dictionary` (`id`, `routing_type_name`) VALUES (NULL, 'No Translation');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`announcements`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`announcements` (`id`, `ann_name`, `ann_file_path`, `info`) VALUES (NULL, 'Default_Busy_Announcement', '/test/default-busy-announcement.wav', NULL);
INSERT INTO `claroNginDb`.`announcements` (`id`, `ann_name`, `ann_file_path`, `info`) VALUES (NULL, 'Default_NoReply_Announcement', '/test/default-no-reply-announcement.wav', NULL);
INSERT INTO `claroNginDb`.`announcements` (`id`, `ann_name`, `ann_file_path`, `info`) VALUES (NULL, 'Default_Other_Error_Announcement', '/test/default-other-error-announcement.wav', NULL);
INSERT INTO `claroNginDb`.`announcements` (`id`, `ann_name`, `ann_file_path`, `info`) VALUES (NULL, 'Default_Hunting_List_Announcement', '/test/default-hunting-list-announcement.wav', NULL);

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`error_actions_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`error_actions_dictionary` (`id`, `error_action_name`) VALUES (NULL, 'Release');
INSERT INTO `claroNginDb`.`error_actions_dictionary` (`id`, `error_action_name`) VALUES (NULL, 'Announcement');
INSERT INTO `claroNginDb`.`error_actions_dictionary` (`id`, `error_action_name`) VALUES (NULL, 'Queue');
INSERT INTO `claroNginDb`.`error_actions_dictionary` (`id`, `error_action_name`) VALUES (NULL, 'Forward');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`node_type_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Date Criteria');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Holiday List Criteria');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Day Of Week Criteria');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Time Of Day Criteria');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Call Hunting');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Call Distribution');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Agent');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Announcement & Release');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'IVR');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Category Criteria');
INSERT INTO `claroNginDb`.`node_type_dictionary` (`id`, `node_type_name`) VALUES (NULL, 'Route On Origin');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`branch_type_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`branch_type_dictionary` (`id`, `branch_name`) VALUES (NULL, 'Root');
INSERT INTO `claroNginDb`.`branch_type_dictionary` (`id`, `branch_name`) VALUES (NULL, 'OK');
INSERT INTO `claroNginDb`.`branch_type_dictionary` (`id`, `branch_name`) VALUES (NULL, 'NOK');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`list_type_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Date List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Public Holiday List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Private Holiday List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'TOD List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Call Distribution List ');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Black & White List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Black & White Exception List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Grey List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Category Screening List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Province List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Area List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Locality List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Route On Origin List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Route On Date & Time List');
INSERT INTO `claroNginDb`.`list_type_dictionary` (`id`, `list_type_name`) VALUES (NULL, 'Category Routing List');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`target_type_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Agent');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'IVR');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Hunting List');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Call Distribution');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Route On Origin List');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Route On Date & Time List');
INSERT INTO `claroNginDb`.`target_type_dictionary` (`id`, `target_type_name`) VALUES (NULL, 'Announcement & Exit');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`menu_actions_dictionary`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Previous Menu');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Next Menu');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Main Menu');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Agent');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Call Hunting');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Call Distribution');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Route On Origin');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Route On Date & Time');
INSERT INTO `claroNginDb`.`menu_actions_dictionary` (`id`, `menu_action_name`) VALUES (NULL, 'Announcement & Exit');

COMMIT;

-- -----------------------------------------------------
-- Data for table `claroNginDb`.`user_info`
-- -----------------------------------------------------
START TRANSACTION;
USE `claroNginDb`;
INSERT INTO `claroNginDb`.`user_info` (`id`, `username`, `first_name`, `lastname`, `email`, `password`, `phone_number`, `rights`, `counter`, `role`, `active_flag`, `info`) VALUES (NULL, 'super', 'Super', 'User', '[e-mail]', 'Sr3U+5LPoGO/Tj09wNMzXw==', '1234567890', '1111', 0, 'admin', 1, 'Claro super user');

COMMIT;