CREATE PROCEDURE `korish_SimoneDesignInc`.`CREATE_CUST_DBS` (USER_ID BIGINT UNSIGNED)
BEGIN
DECLARE RETURN_CODE TINYINT DEFAULT -1;
DECLARE CREATE_CAMP_DB TINYINT DEFAULT -1;
DECLARE CAMP_ENABLED INT DEFAULT 0;
DECLARE NOTIFY_ENABLED INT DEFAULT 0;
DECLARE GIFT_ENABLED INT DEFAULT 0;
DECLARE FORMER_DB_CHECK VARCHAR(10);
#returns: -2 = invalid user_id, -1 = unexpected error, 0 = no new dbs needed, 1 = created all except camp db, 2 = created all dbs;
SELECT camp_db_enabled, camp_max, notify_max, item_max INTO CREATE_CAMP_DB, CAMP_ENABLED, NOTIFY_ENABLED, GIFT_ENABLED FROM MasterCustomerAccounts WHERE internal_id = USER_ID;
IF (FOUND_ROWS() = 0) THEN
SET RETURN_CODE = -2;
ELSE
IF (GIFT_ENABLED) THEN
#check, then if not exists create gift card balance table;
SET FORMER_DB_CHECK = NULL;
SET @new_table_name = CONCAT('ID_',USER_ID,'_GIFT');
SELECT TABLE_NAME INTO FORMER_DB_CHECK FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '@new_table_name';
IF FORMER_DB_CHECK IS NULL THEN
SET @query_body = CONCAT('
CREATE TABLE `',@new_table_name,'` (
`internal_id` SERIAL,
`av_name` VARCHAR(60) DEFAULT NULL,
`av_key` VARCHAR(36) DEFAULT NULL,
`balance` INT(10) UNSIGNED DEFAULT 0,
`last_modified` TIMESTAMP,
`creation_timestamp` TIMESTAMP,
');
PREPARE create_gift FROM @query_body;
EXECUTE create_gift;
DEALLOCATE PREPARE create_gift;
SET RETURN_CODE = 1;
ELSE
SET RETURN_CODE = 0;
END IF;
#check, then if not exists create gift card item table;
SET FORMER_DB_CHECK = NULL;
SET @new_table_name = CONCAT('ID_',USER_ID,'_ITEMS');
SELECT TABLE_NAME INTO FORMER_DB_CHECK FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '@new_table_name';
IF FORMER_DB_CHECK IS NULL THEN
SET @query_body = CONCAT('
CREATE TABLE `',@new_table_name,'` (
`internal_id` SERIAL,
`name` VARCHAR(255),
`price` INT(11) NOT NULL DEFAULT 9999,
`texture_key` VARCHAR(36),
`active` TINYINT(1) NOT NULL DEFAULT 1,
`modified_timestamp` TIMESTAMP,
');
PREPARE create_items FROM @query_body;
EXECUTE create_items;
DEALLOCATE PREPARE create_items;
SET RETURN_CODE = 1;
END IF;
ELSE
SET RETURN_CODE = 0;
END IF;
IF NOTIFY_ENABLED THEN
#check, then if not exists create customer notify table;
SET FORMER_DB_CHECK = NULL;
SET @new_table_name = CONCAT('ID_',USER_ID,'_NOTIFY');
SELECT TABLE_NAME INTO FORMER_DB_CHECK FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '@new_table_name';
IF FORMER_DB_CHECK IS NULL THEN
SET @query_body = CONCAT('
CREATE TABLE `',@new_table_name,'` (
`internal_id` SERIAL,
`av_name` VARCHAR(60),
`av_key` VARCHAR(36),
`auto_deliver` TINYINT(1) DEFAULT NULL,
`user_groups` BIGINT(20) UNSIGNED DEFAULT NULL,
`last_modified` TIMESTAMP,
`last_delivery` TIMESTAMP,
`creation_timestamp` TIMESTAMP,
');
PREPARE create_notify FROM @query_body;
EXECUTE create_notify;
DEALLOCATE PREPARE create_notify;
SET RETURN_CODE = 1;
END IF;
END IF;
#check, then if not exists create history table;
SET FORMER_DB_CHECK = NULL;
SET @new_table_name = CONCAT('ID_',USER_ID,'_HISTORY');
SELECT TABLE_NAME INTO FORMER_DB_CHECK FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '@new_table_name';
IF FORMER_DB_CHECK IS NULL THEN
SET @query_body = CONCAT('
CREATE TABLE `',@new_table_name,'` (
`internal_id` SERIAL,
`trans_id` BIGINT(20) UNSIGNED DEFAULT NULL,
`trans_type` TINYINT(4) DEFAULT NULL,
`user_id` BIGINT(20) UNSIGNED DEFAULT NULL,
`amount` INT(11) DEFAULT NULL,
`balance` INT(10) UNSIGNED DEFAULT 0,
`creation_timestamp` TIMESTAMP,
');
PREPARE create_history FROM @query_body;
EXECUTE create_history;
DEALLOCATE PREPARE create_history;
SET RETURN_CODE = 1;
END IF;
IF ((!GIFT_ENABLED && CAMP_ENABLED) || (GIFT_ENABLED && CREATE_CAMP_DB && CAMP_ENABLED)) THEN
#check, then if not exists create camping table;
SET FORMER_DB_CHECK = NULL;
SET @new_table_name = CONCAT('ID_',USER_ID,'_CAMP');
SELECT TABLE_NAME INTO FORMER_DB_CHECK FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME = '@new_table_name';
IF FORMER_DB_CHECK IS NULL THEN
SET @query_body = CONCAT('
CREATE TABLE `',@new_table_name,'` (
`internal_id` SERIAL,
`av_name` VARCHAR(60) DEFAULT NULL,
`av_key` VARCHAR(36) DEFAULT NULL,
`total_minutes` INT(10) UNSIGNED DEFAULT 0,
`last_modified` TIMESTAMP,
`creation_timestamp` TIMESTAMP,
');
PREPARE create_camp FROM @query_body;
EXECUTE create_camp;
DEALLOCATE PREPARE create_camp;
SET RETURN_CODE = 2;
END IF;
END IF;
END IF;
SELECT RETURN_CODE;
END