USE partsltd_prod;

-- Permanent Temp Tables
DROP TABLE IF EXISTS partsltd_prod.tmp_ph_Calc_User;
DROP TABLE IF EXISTS partsltd_prod.tmp_core_Msg_Error;
DROP TABLE IF EXISTS partsltd_prod.tmp_ph_User;
DROP TABLE IF EXISTS partsltd_prod.tmp_ph_User_Role_Link;


-- Permanent Tables
DROP TABLE IF EXISTS partsltd_prod.PH_Contact_Form_Temp;
DROP TABLE IF EXISTS partsltd_prod.PH_Contact_Form_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Contact_Form;

DROP TABLE IF EXISTS partsltd_prod.PH_Contact_Form_Change_Set;

DROP TABLE IF EXISTS partsltd_prod.PH_Calc_User_Temp;

DROP TABLE IF EXISTS partsltd_prod.PH_User_Role_Link_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_User_Role_Link;

DROP TABLE IF EXISTS partsltd_prod.PH_Role_Permission_Link_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Role_Permission_Link;

DROP TABLE IF EXISTS partsltd_prod.PH_Role_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Role;

DROP TABLE IF EXISTS partsltd_prod.PH_User_Temp;
DROP TABLE IF EXISTS partsltd_prod.PH_User_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_User;

DROP TABLE IF EXISTS partsltd_prod.PH_Permission_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Permission;

DROP TABLE IF EXISTS partsltd_prod.PH_Permission_Group_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Permission_Group;

DROP TABLE IF EXISTS partsltd_prod.PH_Access_Level_Audit;
DROP TABLE IF EXISTS partsltd_prod.PH_Access_Level;

DROP TABLE IF EXISTS partsltd_prod.PH_User_Change_Set;

DROP TABLE IF EXISTS partsltd_prod.CORE_Split_Key_Value_Pair_Csv_Temp;
DROP TABLE IF EXISTS partsltd_prod.CORE_Split_Temp;

DROP TABLE IF EXISTS partsltd_prod.CORE_Msg_Error_Type;


-- Stored Procedures
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_test_get_many_contact_form;
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_get_many_contact_form;
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_test_save_contact_form;
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_save_contact_form;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_clear_calc_user;
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_calc_user;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_clear_split_key_value_pair_csv;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_split_key_value_pair_csv;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_clear_split;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_split;
DROP PROCEDURE IF EXISTS partsltd_prod.p_clear_split_key_value_pair_csv;
DROP PROCEDURE IF EXISTS partsltd_prod.p_split_key_value_pair_csv;
DROP PROCEDURE IF EXISTS partsltd_prod.p_clear_split;
DROP PROCEDURE IF EXISTS partsltd_prod.p_split;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_debug_timing_reporting;
DROP PROCEDURE IF EXISTS partsltd_prod.p_debug_timing_reporting;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_validate_guid;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_validate_guid_test;

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'CORE_Msg_Error_Type'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.CORE_Msg_Error_Type (
	  id_type INT NOT NULL AUTO_INCREMENT PRIMARY KEY
	, code VARCHAR(50) NOT NULL
	, name VARCHAR(500) NOT NULL
	, description VARCHAR(1000)
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'CORE_Split_Temp'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.CORE_Split_Temp (
	  guid BINARY(36) NOT NULL
    , display_order INT NOT NULL
	, substring VARCHAR(4000) NOT NULL
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'CORE_Split_Key_Value_Pair_Csv_Temp'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.CORE_Split_Key_Value_Pair_Csv_Temp (
	  guid BINARY(36) NOT NULL
    , id INT NOT NULL
	, key_column VARCHAR(4000) NULL
    , value_column VARCHAR(4000) NULL
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User_Change_Set'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User_Change_Set (
	  id_change_set INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , comment VARCHAR(500)
    , updated_last_on DATETIME
    , id_user_updated_last_by INT
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Access_Level'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Access_Level (
      id_access_level INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , code VARCHAR(50)
    , name VARCHAR(255)
    , priority INT NOT NULL
    , display_order INT NOT NULL
    , active BIT NOT NULL DEFAULT 1
);



USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Permission_Group'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Permission_Group (
      id_group INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , code VARCHAR(50)
    , name VARCHAR(255)
    , display_order INT NOT NULL
    , active BIT NOT NULL DEFAULT 1
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Permission'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Permission (
      id_permission INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , code VARCHAR(50)
    , name VARCHAR(255)
    , id_permission_group INT NOT NULL
    , CONSTRAINT FK_PH_Permission_id_permission_group
		FOREIGN KEY (id_permission_group)
        REFERENCES partsltd_prod.PH_Permission_Group(id_group)
    , id_access_level_required INT NOT NULL
    , CONSTRAINT FK_PH_Permission_id_access_level_required
		FOREIGN KEY (id_access_level_required)
        REFERENCES partsltd_prod.PH_Access_Level(id_access_level)
    , display_order INT NOT NULL
    , active BIT NOT NULL DEFAULT 1
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User (
      id_user INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_user_auth0 VARCHAR(200)
    , firstname VARCHAR(255)
    , surname VARCHAR(255)
    , email VARCHAR(254)
    , is_email_verified BIT NOT NULL DEFAULT 0
    , is_super_user BIT NOT NULL DEFAULT 0
    , active BIT NOT NULL DEFAULT 1
    , created_on DATETIME
    , id_user_created_by INT
    , CONSTRAINT FK_PH_User_id_user_created_by
        FOREIGN KEY (id_user_created_by)
        REFERENCES partsltd_prod.PH_User(id_user)
    , id_change_set INT
    , CONSTRAINT FK_PH_User_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User_Audit'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User_Audit (
	  id_audit INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_user INT NOT NULL
    , CONSTRAINT FK_PH_User_Audit_id_user
		FOREIGN KEY (id_user)
        REFERENCES partsltd_prod.PH_User(id_user)
    , name_field VARCHAR(50) NOT NULL
    , value_prev VARCHAR(500)
    , value_new VARCHAR(500)
    , id_change_set INT NOT NULL
    , CONSTRAINT FK_PH_User_Audit_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User_Temp'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User_Temp (
      id_temp INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_user INT NOT NULL
    , id_user_auth0 VARCHAR(200)
    , firstname VARCHAR(255)
    , surname VARCHAR(255)
    , email VARCHAR(254)
    , is_email_verified BIT
    , is_super_user BIT
    , active BIT
    , guid BINARY(36) NOT NULL
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Role'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Role (
      id_role INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , code VARCHAR(50)
    , name VARCHAR(255)
    , display_order INT NOT NULL
    , active BIT NOT NULL DEFAULT 1
    , created_on DATETIME
    , id_user_created_by INT
    , CONSTRAINT FK_PH_Role_id_user_created_by
        FOREIGN KEY (id_user_created_by)
        REFERENCES partsltd_prod.PH_User(id_user)
    , id_change_set INT
    , CONSTRAINT FK_PH_Role_id_change_set
		FOREIGN KEY (id_change_set) 
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Role_Audit'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Role_Audit (
	  id_audit INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_role INT NOT NULL
    , CONSTRAINT FK_PH_Role_Audit_id_role
		FOREIGN KEY (id_role)
        REFERENCES partsltd_prod.PH_Role(id_role)
    , name_field VARCHAR(50) NOT NULL
    , value_prev VARCHAR(500)
    , value_new VARCHAR(500)
    , id_change_set INT NOT NULL
    , CONSTRAINT FK_PH_Role_Audit_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Role_Permission_Link'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Role_Permission_Link (
      id_link INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_role INT NOT NULL
    , CONSTRAINT FK_PH_Role_Permission_Link_id_role
		FOREIGN KEY (id_role)
        REFERENCES partsltd_prod.PH_Role(id_role)
    , id_permission INT NOT NULL
    , CONSTRAINT FK_PH_Role_Permission_Link_id_permission
		FOREIGN KEY (id_permission)
        REFERENCES partsltd_prod.PH_Permission(id_permission)
    , id_access_level INT NOT NULL
    , CONSTRAINT FK_PH_Role_Permission_Link_id_access_level
		FOREIGN KEY (id_access_level)
        REFERENCES partsltd_prod.PH_Access_Level(id_access_level)
    , active BIT NOT NULL DEFAULT 1
    , created_on DATETIME
    , id_user_created_by INT
    , CONSTRAINT FK_PH_Role_Permission_Link_id_user_created_by
        FOREIGN KEY (id_user_created_by)
        REFERENCES partsltd_prod.PH_User(id_user)
    , id_change_set INT
    , CONSTRAINT FK_PH_Role_Permission_Link_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Role_Permission_Link_Audit';

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Role_Permission_Link_Audit (
	  id_audit INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_link INT NOT NULL
    , CONSTRAINT FK_PH_Role_Permission_Link_Audit_id_link
		FOREIGN KEY (id_link)
        REFERENCES partsltd_prod.PH_Role_Permission_Link(id_link)
    , name_field VARCHAR(50) NOT NULL
    , value_prev VARCHAR(500)
    , value_new VARCHAR(500)
    , id_change_set INT NOT NULL
    , CONSTRAINT FK_PH_Role_Permission_Link_Audit_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User_Role_Link'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User_Role_Link (
      id_link INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_user INT NOT NULL
    , CONSTRAINT FK_PH_User_Role_Link_id_user
		FOREIGN KEY (id_user)
        REFERENCES partsltd_prod.PH_User(id_user)
    , id_role INT NOT NULL
    , CONSTRAINT FK_PH_User_Role_Link_id_role
		FOREIGN KEY (id_role)
        REFERENCES partsltd_prod.PH_Role(id_role)
    , active BIT NOT NULL DEFAULT 1
    , created_on DATETIME
    , id_user_created_by INT
    , CONSTRAINT FK_PH_User_Role_Link_id_user_created_by
        FOREIGN KEY (id_user_created_by)
        REFERENCES partsltd_prod.PH_User(id_user)
    , id_change_set INT
    , CONSTRAINT FK_PH_User_Role_Link_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_User_Role_Link_Audit'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_User_Role_Link_Audit (
	  id_audit INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id_link INT NOT NULL
    , CONSTRAINT FK_PH_User_Role_Link_Audit_id_link
		FOREIGN KEY (id_link)
        REFERENCES partsltd_prod.PH_User_Role_Link(id_link)
    , name_field VARCHAR(50) NOT NULL
    , value_prev VARCHAR(500)
    , value_new VARCHAR(500)
    , id_change_set INT NOT NULL
    , CONSTRAINT FK_PH_User_Role_Link_Audit_id_change_set
		FOREIGN KEY (id_change_set)
        REFERENCES partsltd_prod.PH_User_Change_Set(id_change_set)
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Calc_User_Temp'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Calc_User_Temp (
	  guid BINARY(36) NOT NULL
	, id_user INT
	, id_permission_required INT NOT NULL
	, CONSTRAINT FK_PH_Calc_User_Temp_id_permission_required
		FOREIGN KEY (id_permission_required)
		REFERENCES partsltd_prod.PH_Permission (id_permission)
	, priority_access_level_required INT NOT NULL
	, is_super_user BIT
	, priority_access_level_user INT
	, has_access BIT
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Contact_Form_Change_Set'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Contact_Form_Change_Set (
	  id_change_set INT NOT NULL AUTO_INCREMENT PRIMARY KEY
	, comment VARCHAR(500)
	, updated_last_on DATETIME
    , id_user_updated_last_by INT
    , CONSTRAINT FK_PH_Role_id_user_updated_last_by
        FOREIGN KEY (id_user_updated_last_by)
        REFERENCES partsltd_prod.PH_User(id_user)
);
USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Contact_Form'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Contact_Form (
	  id_contact_form INT NOT NULL AUTO_INCREMENT PRIMARY KEY
	, email VARCHAR(255) NOT NULL
	, name_contact VARCHAR(255) NOT NULL
	, name_company VARCHAR(255) NOT NULL
	, message TEXT NOT NULL
    , receive_marketing_communications BIT NOT NULL DEFAULT 0
	, active BIT NOT NULL DEFAULT 1
	, created_on DATETIME
    , id_user_created_by INT
    , CONSTRAINT FK_PH_Contact_Form_id_user_created_by
        FOREIGN KEY (id_user_created_by)
        REFERENCES partsltd_prod.PH_User(id_user)
	, id_change_set INT
	, CONSTRAINT FK_PH_Contact_Form_id_change_set
		FOREIGN KEY (id_change_set) 
		REFERENCES partsltd_prod.PH_Contact_Form_Change_Set(id_change_set)
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Contact_Form_Audit'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Contact_Form_Audit (
	  id_audit INT NOT NULL AUTO_INCREMENT PRIMARY KEY
	, id_contact_form INT NOT NULL
	, CONSTRAINT FK_PH_Contact_Form_Audit_id_contact_form
		FOREIGN KEY (id_contact_form)
		REFERENCES partsltd_prod.PH_Contact_Form(id_contact_form)
	, name_field VARCHAR(50) NOT NULL
	, value_prev TEXT
	, value_new TEXT
	, id_change_set INT NOT NULL
	, CONSTRAINT FK_PH_Contact_Form_Audit_id_change_set
		FOREIGN KEY (id_change_set) 
		REFERENCES partsltd_prod.PH_Contact_Form_Change_Set(id_change_set)
);

USE partsltd_prod;

SELECT CONCAT('WARNING: Table ', TABLE_SCHEMA, '.', TABLE_NAME, ' already exists.') AS msg_warning 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
        TABLE_SCHEMA = 'partsltd_prod' 
    AND TABLE_NAME = 'PH_Contact_Form_Temp'
;

CREATE TABLE IF NOT EXISTS partsltd_prod.PH_Contact_Form_Temp (
	  id_temp INT NOT NULL PRIMARY KEY AUTO_INCREMENT
	, id_contact_form INT
	, email VARCHAR(255)
	, name_contact VARCHAR(255)
	, name_company VARCHAR(255)
	, message TEXT
    , receive_marketing_communications BIT
	, active BIT
    , guid BINARY(36)
);
USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_User_Change_Set;

DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_User_Change_Set
BEFORE INSERT ON partsltd_prod.PH_User_Change_Set
FOR EACH ROW
BEGIN
	IF NEW.updated_last_on <=> NULL THEN
		SET NEW.updated_last_on = NOW();
	END IF;
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_User_Change_Set
BEFORE UPDATE ON partsltd_prod.PH_User_Change_Set
FOR EACH ROW
BEGIN
	IF NOT EXISTS(SELECT * FROM partsltd_prod.PH_User WHERE id_user = NEW.id_user_updated_last_by) THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New Updated-Last-By User ID must be provided.';
	END IF;
END //
DELIMITER ;

USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_User;
DROP TRIGGER IF EXISTS partsltd_prod.before_update_PH_User;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_User
BEFORE INSERT ON partsltd_prod.PH_User
FOR EACH ROW
BEGIN
	SET NEW.created_on := IFNULL(NEW.created_on, NOW());
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_User
BEFORE UPDATE ON partsltd_prod.PH_User
FOR EACH ROW
BEGIN
	IF OLD.id_change_set <=> NEW.id_change_set THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New change Set ID must be provided.';
    END IF;
    
    INSERT INTO partsltd_prod.PH_User_Audit (
		id_user,
        name_field,
        value_prev,
        value_new,
        id_change_set
    )
    -- Changed id_user_auth0
	SELECT NEW.id_user, 'id_user_auth0', OLD.id_user_auth0, NEW.id_user_auth0, NEW.id_change_set
		WHERE NOT (OLD.id_user_auth0 <=> NEW.id_user_auth0)
	UNION
    -- Changed firstname
	SELECT NEW.id_user, 'firstname', OLD.firstname, NEW.firstname, NEW.id_change_set
		WHERE NOT (OLD.firstname <=> NEW.firstname)
	UNION
    -- Changed surname
	SELECT NEW.id_user, 'surname', OLD.surname, NEW.surname, NEW.id_change_set
		WHERE NOT (OLD.surname <=> NEW.surname)
	UNION 
	-- Changed email
	SELECT NEW.id_user, 'email', OLD.email, NEW.email, NEW.id_change_set
		WHERE NOT (OLD.email <=> NEW.email)
	UNION
	-- Changed is_email_verified
	SELECT NEW.id_user, 'is_email_verified', CONVERT(CONVERT(OLD.is_email_verified, SIGNED), CHAR), CONVERT(CONVERT(NEW.is_email_verified, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.is_email_verified <=> NEW.is_email_verified)
	UNION
	-- Changed is_super_user
	SELECT NEW.id_user, 'is_super_user', CONVERT(CONVERT(OLD.is_super_user, SIGNED), CHAR), CONVERT(CONVERT(NEW.is_super_user, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.is_super_user <=> NEW.is_super_user)
	UNION
    -- Changed active
	SELECT NEW.id_user, 'active', CONVERT(CONVERT(OLD.active, SIGNED), CHAR), CONVERT(CONVERT(NEW.active, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.active <=> NEW.active)
    ;
END //
DELIMITER ;
USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_Role;
DROP TRIGGER IF EXISTS partsltd_prod.before_update_PH_Role;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_Role
BEFORE INSERT ON partsltd_prod.PH_Role
FOR EACH ROW
BEGIN
	SET NEW.created_on := IFNULL(NEW.created_on, NOW());
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_Role
BEFORE UPDATE ON partsltd_prod.PH_Role
FOR EACH ROW
BEGIN
	IF OLD.id_change_set <=> NEW.id_change_set THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New change Set ID must be provided.';
    END IF;
    
    INSERT INTO partsltd_prod.PH_Role_Audit (
		id_role,
        name_field,
        value_prev,
        value_new,
        id_change_set
    )
    -- Changed code
	SELECT NEW.id_role, 'code', OLD.code, NEW.code, NEW.id_change_set
		WHERE NOT OLD.code <=> NEW.code
	UNION
	-- Changed name
	SELECT NEW.id_role, 'name', OLD.name, NEW.name, NEW.id_change_set
		WHERE NOT OLD.name <=> NEW.name
	UNION
	-- Changed active
	SELECT NEW.id_role, 'active', CONVERT(CONVERT(OLD.active, SIGNED), CHAR), CONVERT(CONVERT(NEW.active, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.active <=> NEW.active)
    UNION
    -- Changed display_order
	SELECT NEW.id_role, 'display_order', CONVERT(OLD.display_order, CHAR), CONVERT(NEW.display_order, CHAR), NEW.id_change_set
		WHERE NOT OLD.display_order <=> NEW.display_order
    ;
END //
DELIMITER ;

USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_Role_Permission_Link;
DROP TRIGGER IF EXISTS partsltd_prod.before_update_PH_Role_Permission_Link;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_Role_Permission_Link
BEFORE INSERT ON partsltd_prod.PH_Role_Permission_Link
FOR EACH ROW
BEGIN
	SET NEW.created_on := IFNULL(NEW.created_on, NOW());
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_Role_Permission_Link
BEFORE UPDATE ON partsltd_prod.PH_Role_Permission_Link
FOR EACH ROW
BEGIN
	IF OLD.id_change_set <=> NEW.id_change_set THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New change Set ID must be provided.';
    END IF;
    
    INSERT INTO partsltd_prod.PH_Role_Permission_Link_Audit (
		id_link,
        name_field,
        value_prev,
        value_new,
        id_change_set
    )
    -- Changed id_role
	SELECT NEW.id_link, 'id_role', CONVERT(OLD.id_role, CHAR), CONVERT(NEW.id_role, CHAR), NEW.id_change_set
		WHERE NOT OLD.id_role <=> NEW.id_role
	UNION
	-- Changed id_permission
	SELECT NEW.id_link, 'id_permission', CONVERT(OLD.id_permission, CHAR), CONVERT(NEW.id_permission, CHAR), NEW.id_change_set
		WHERE NOT OLD.id_permission <=> NEW.id_permission
    UNION
    -- Changed id_access_level
	SELECT NEW.id_link, 'id_access_level', CONVERT(OLD.id_access_level, CHAR), CONVERT(NEW.id_access_level, CHAR), NEW.id_change_set
		WHERE NOT OLD.id_access_level <=> NEW.id_access_level
	UNION
	-- Changed active
	SELECT NEW.id_link, 'active', CONVERT(CONVERT(OLD.active, SIGNED), CHAR), CONVERT(CONVERT(NEW.active, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.active <=> NEW.active)
    ;
END //
DELIMITER ;

USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_User_Role_Link;
DROP TRIGGER IF EXISTS partsltd_prod.before_update_PH_User_Role_Link;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_User_Role_Link
BEFORE INSERT ON partsltd_prod.PH_User_Role_Link
FOR EACH ROW
BEGIN
	SET NEW.created_on := IFNULL(NEW.created_on, NOW());
END //
DELIMITER ;


DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_User_Role_Link
BEFORE UPDATE ON partsltd_prod.PH_User_Role_Link
FOR EACH ROW
BEGIN
	IF OLD.id_change_set <=> NEW.id_change_set THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New change Set ID must be provided.';
    END IF;
    
    INSERT INTO partsltd_prod.PH_User_Role_Link_Audit (
		id_link,
        name_field,
        value_prev,
        value_new,
        id_change_set
    )
	-- Changed id_user
	SELECT NEW.id_link, 'id_user', CONVERT(OLD.id_user, CHAR), CONVERT(NEW.id_user, CHAR), NEW.id_change_set
		WHERE NOT OLD.id_user <=> NEW.id_user
    UNION
    -- Changed id_role
	SELECT NEW.id_link, 'id_role', CONVERT(OLD.id_role, CHAR), CONVERT(NEW.id_role, CHAR), NEW.id_change_set
		WHERE NOT OLD.id_role <=> NEW.id_role
	UNION
	-- Changed active
	SELECT NEW.id_link, 'active', CONVERT(CONVERT(OLD.active, SIGNED), CHAR), CONVERT(CONVERT(NEW.active, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.active <=> NEW.active)
    ;
END //
DELIMITER ;USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_Contact_Form_Change_Set;

DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_Contact_Form_Change_Set
BEFORE INSERT ON partsltd_prod.PH_Contact_Form_Change_Set
FOR EACH ROW
BEGIN
	IF NEW.updated_last_on <=> NULL THEN
		SET NEW.updated_last_on = NOW();
	END IF;
END //
DELIMITER ;

USE partsltd_prod;

DROP TRIGGER IF EXISTS partsltd_prod.before_insert_PH_Contact_Form;
DROP TRIGGER IF EXISTS partsltd_prod.before_update_PH_Contact_Form;

DELIMITER //
CREATE TRIGGER partsltd_prod.before_insert_PH_Contact_Form
BEFORE INSERT ON partsltd_prod.PH_Contact_Form
FOR EACH ROW
BEGIN
	SET NEW.created_on := IFNULL(NEW.created_on, NOW());
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER partsltd_prod.before_update_PH_Contact_Form
BEFORE UPDATE ON partsltd_prod.PH_Contact_Form
FOR EACH ROW
BEGIN
	IF OLD.id_change_set <=> NEW.id_change_set THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'New change Set ID must be provided.';
    END IF;
    
    INSERT INTO partsltd_prod.PH_Contact_Form_Audit (
		id_contact_form,
        name_field,
        value_prev,
        value_new,
        id_change_set
    )
    -- Changed email
	SELECT NEW.id_contact_form, 'email', OLD.email, NEW.email, NEW.id_change_set
		WHERE NOT OLD.email <=> NEW.email
	UNION
    -- Changed name_contact
	SELECT NEW.id_contact_form, 'name_contact', OLD.name_contact, NEW.name_contact, NEW.id_change_set
		WHERE NOT OLD.name_contact <=> NEW.name_contact
	UNION
    -- Changed name_company
	SELECT NEW.id_contact_form, 'name_company', OLD.name_company, NEW.name_company, NEW.id_change_set
		WHERE NOT OLD.name_company <=> NEW.name_company
	UNION
	-- Changed message
	SELECT NEW.id_contact_form, 'message', OLD.message, NEW.message, NEW.id_change_set
		WHERE NOT OLD.message <=> NEW.message
	UNION
	-- Changed receive_marketing_communications
	SELECT NEW.id_contact_form, 'receive_marketing_communications', CONVERT(CONVERT(OLD.receive_marketing_communications, SIGNED), CHAR), CONVERT(CONVERT(NEW.receive_marketing_communications, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.receive_marketing_communications <=> NEW.receive_marketing_communications)
	UNION
	-- Changed active
	SELECT NEW.id_contact_form, 'active', CONVERT(CONVERT(OLD.active, SIGNED), CHAR), CONVERT(CONVERT(NEW.active, SIGNED), CHAR), NEW.id_change_set
		WHERE NOT (OLD.active <=> NEW.active)
    ;
END //
DELIMITER ;

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_validate_guid;
DROP PROCEDURE IF EXISTS partsltd_prod.p_core_validate_guid_test;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_validate_guid (
	IN a_guid BINARY(36)
)
BEGIN
    IF ISNULL(a_guid) THEN
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'GUID is required.'
        ;
	END IF;
END //
DELIMITER ;


DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_validate_guid_test ()
BEGIN
    CALL partsltd_prod.p_core_validate_guid ( 'nips' );
	CALL partsltd_prod.p_core_validate_guid ( NULL );
END //
DELIMITER ;

-- CALL partsltd_prod.p_core_validate_guid_test();
USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_debug_timing_reporting;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_debug_timing_reporting (
	IN a_time_start TIMESTAMP(6)
)
BEGIN
/*
	PROCEDURE partsltd_prod.p_core_debug_timing_reporting
	Shared method timing reporting
*/
    DECLARE v_time_end TIMESTAMP(6);

	SET v_time_end := CURRENT_TIMESTAMP(6);
	SELECT
		a_time_start
		, UNIX_TIMESTAMP(a_time_start)
		, MICROSECOND(a_time_start) / 1000
		, v_time_end
		, UNIX_TIMESTAMP(v_time_end)
		, MICROSECOND(v_time_end) / 1000
		, v_time_end - a_time_start AS timestamp_delta
		, UNIX_TIMESTAMP(v_time_end - a_time_start) AS UNIX_TIMESTAMP_timestamp_delta
		, MICROSECOND(v_time_end - a_time_start) AS MICROSECOND_timestamp_delta
		-- , TIME_FORMAT(TIMEDIFF(v_time_end, a_time_start), '%H:%i:%s') AS time_difference
		, CONCAT(
			TIME_FORMAT(TIMEDIFF(v_time_end, a_time_start), '%H hours, %i minutes, %s seconds'),
			', ',
			TIMESTAMPDIFF(MICROSECOND, a_time_start, v_time_end) % 1000000 / 1000, ' milliseconds'
		) AS time_difference
	;
	
END //
DELIMITER ;

/*
CALL partsltd_prod.p_core_debug_timing_reporting (
	CURRENT_TIMESTAMP(6)
);
*/
USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_split;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_split (
	  IN a_guid BINARY(36)
	, IN a_string LONGTEXT
	, IN a_separator VARCHAR(1000)
    -- IN a_allow_empty BIT
    , IN a_debug BIT
)
BEGIN
    DECLARE v_has_string BIT;
    DECLARE v_has_separator BIT;
    DECLARE v_i_separator INT;
    DECLARE v_i_start INT;
    DECLARE v_i_end INT;
    DECLARE v_current_item VARCHAR(4000);
    DECLARE v_time_start TIMESTAMP(6);
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET a_string := IFNULL(a_string, '');
    SET a_separator := IFNULL(a_separator, '');
    SET a_debug := IFNULL(a_debug, 0);
    
    IF a_debug = 1 THEN
		SELECT
              a_guid
            , a_string
            , a_separator
            , a_debug
		;
    END IF;
    
    CALL partsltd_prod.p_core_validate_guid ( a_guid );
    
    DROP TABLE IF EXISTS tmp_Split_Split;
    
    CREATE TEMPORARY TABLE tmp_Split_Split (
        display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
		substring VARCHAR(4000) NOT NULL
    );
    
    SET v_has_string = CASE WHEN a_string = '' THEN 0 ELSE 1 END;
    SET v_has_separator = CASE WHEN a_separator = '' THEN 0 ELSE 1 END;
    
    IF v_has_string THEN

		SET v_i_start = 1;
		SET v_i_end = LOCATE(',', a_string);

		WHILE v_i_end > 0 DO
			SET v_current_item = SUBSTRING(a_string, v_i_start, v_i_end - v_i_start);
			INSERT INTO tmp_Split_Split (substring) VALUES (v_current_item);

			SET v_i_start = v_i_end + 1;
			SET v_i_end = LOCATE(',', a_string, v_i_start);
		END WHILE;

		SET v_current_item = SUBSTRING(a_string, v_i_start);
		INSERT INTO tmp_Split_Split (substring) VALUES (TRIM(v_current_item));
	END IF;

	IF EXISTS (SELECT * FROM tmp_Split_Split LIMIT 1) THEN
		START TRANSACTION;
			INSERT INTO partsltd_prod.CORE_Split_Temp (
                  guid
                , display_order
                , substring
			)
            SELECT
				  a_guid
				, display_order
                , substring
			FROM tmp_Split_Split
            ;
		COMMIT;
    END IF;
    
    DROP TABLE IF EXISTS tmp_Split_Split;
    
    IF a_debug = 1 THEN
		CALL partsltd_prod.p_core_debug_timing_reporting ( v_time_start );
    END IF;
END //
DELIMITER ;


/*
CALL partsltd_prod.p_core_split (
	  'nips'
	, 'noods, cheese ' -- a_string
	, ',' -- a_separator
	-- '0', -- a_allow_empty
    , 1
);

SELECT *
FROM partsltd_prod.CORE_Split_Temp
WHERE GUID = 'nips';

CALL partsltd_prod.p_core_clear_split_temp( 'nips' );
*/

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_clear_split;


DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_clear_split (
	IN a_guid BINARY(36)
)
BEGIN
	CALL partsltd_prod.p_core_validate_guid( a_guid );

	START TRANSACTION; 
	
	-- DROP TABLE IF EXISTS 
    DELETE FROM partsltd_prod.CORE_Split_Temp
    WHERE GUID = a_guid
    ;
	
	COMMIT;
END //
DELIMITER ;

/*

CALL partsltd_prod.p_core_clear_split ( 'nips' );


*/

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_split_key_value_pair_csv;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_split_key_value_pair_csv (
	IN a_guid BINARY(36)
	, IN a_string LONGTEXT
    , IN a_debug BIT
)
BEGIN
    DECLARE v_has_string BIT;
    DECLARE v_current_item VARCHAR(4000);
    DECLARE v_time_start TIMESTAMP(6);
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET a_string := IFNULL(a_string, '');
    SET a_debug := IFNULL(a_debug, 0);
    
    SET v_has_string = CASE WHEN a_string = '' THEN 0 ELSE 1 END;
    
    IF a_debug = 1 THEN
		SELECT
			a_guid
            , a_string
            , a_debug
		;
    END IF;
    
    CALL partsltd_prod.p_core_validate_guid ( a_guid );
    
    DROP TABLE IF EXISTS tmp_Input_Split_Key_Value_Pair_Csv;
    DROP TABLE IF EXISTS tmp_Split_Split_Key_Value_Pair_Csv;

    CREATE TEMPORARY TABLE tmp_Input_Split_Key_Value_Pair_Csv (
        input_string TEXT
    );

    CREATE TEMPORARY TABLE tmp_Split_Split_Key_Value_Pair_Csv (
        id INT AUTO_INCREMENT PRIMARY KEY
        , key_column VARCHAR(4000)
        , value_column VARCHAR(4000)
    );

    INSERT INTO tmp_Input_Split_Key_Value_Pair_Csv (
        input_string
    ) 
    VALUES (
        a_string
    );

    INSERT INTO tmp_Split_Split_Key_Value_Pair_Csv (key_column, value_column)
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(t.pair, ':', 1), ',', -1) AS key_column,
        SUBSTRING_INDEX(t.pair, ':', -1) AS value_column
    FROM (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(input_string, ',', n), ',', -1) pair
        FROM temp_input
        CROSS JOIN (
            SELECT a.N + b.N * 10 + 1 n
            FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
            CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) numbers
        WHERE n <= 1 + (LENGTH(input_string) - LENGTH(REPLACE(input_string, ',', '')))
    ) t;

	IF EXISTS (SELECT * FROM tmp_Split_Split_Key_Value_Pair_Csv LIMIT 1) THEN
		START TRANSACTION;
			INSERT INTO partsltd_prod.CORE_Split_Key_Value_Pair_Csv_Temp (
				guid
				, id
                , key_column
                , value_column
			)
            SELECT
				a_guid
				, id
                , key_column
                , value_column
			FROM tmp_Split_Split_Key_Value_Pair_Csv
            ;
		COMMIT;
    END IF;
    
    DROP TEMPORARY TABLE IF EXISTS tmp_Input_Split_Key_Value_Pair_Csv;
    DROP TEMPORARY TABLE IF EXISTS tmp_Split_Split_Key_Value_Pair_Csv;
    
    IF a_debug = 1 THEN
		CALL partsltd_prod.p_core_debug_timing_reporting ( v_time_start );
    END IF;
END //
DELIMITER ;


/*
CALL partsltd_prod.p_core_split_key_value_pair_csv (
	'nipsnipsnipsnipsnipsnipsnipsnipsnips'
	, '1:100,2:200,3:300,4:400' -- a_string
    , 1
);

SELECT *
FROM partsltd_prod.CORE_Split_key_value_pair_csv_Temp
WHERE GUID = 'nipsnipsnipsnipsnipsnipsnipsnipsnips';

CALL partsltd_prod.p_core_clear_split_key_value_pair_csv_temp( 'nipsnipsnipsnipsnipsnipsnipsnipsnips' );
*/

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_core_clear_split_key_value_pair_csv;


DELIMITER //
CREATE PROCEDURE partsltd_prod.p_core_clear_split_key_value_pair_csv (
	IN a_guid BINARY(36)
)
BEGIN
	CALL partsltd_prod.p_core_validate_guid( a_guid );

	START TRANSACTION; 
	
	-- DROP TABLE IF EXISTS 
    DELETE FROM partsltd_prod.CORE_Split_Key_Value_Pair_Csv_Temp
    WHERE GUID = a_guid
    ;
	
	COMMIT;
END //
DELIMITER ;

/*

CALL partsltd_prod.p_core_clear_split_key_value_pair_csv ( 'nips' );


*/

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_user_eval;
DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_calc_user;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_ph_calc_user (
	  IN a_guid BINARY(36)
    , IN a_ids_user TEXT
    , IN a_get_inactive_user BIT
    , IN a_ids_permission VARCHAR(4000)
    , IN a_debug BIT
)
BEGIN
    DECLARE v_has_filter_permission BIT;
    DECLARE v_has_filter_user BIT;
    DECLARE v_id_permission INT;
    DECLARE v_time_start TIMESTAMP(6);
	DECLARE v_ids_row_delete VARCHAR(500);
    DECLARE v_code_type_error_bad_data VARCHAR(200);
    DECLARE v_id_type_error_bad_data INT;
    DECLARE v_code_error_permission VARCHAR(200);
    DECLARE v_id_permission_required INT;
    DECLARE v_priority_access_level_required INT;
    DECLARE v_priority_access_level_view INT;
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET v_code_type_error_bad_data := (SELECT code FROM partsltd_prod.CORE_Msg_Error_Type WHERE code = 'BAD_DATA');
    SET v_id_type_error_bad_data := (SELECT id_type FROM partsltd_prod.CORE_Msg_Error_Type WHERE code = v_code_type_error_bad_data);
    SET v_code_error_permission := (SELECT code FROM partsltd_prod.CORE_Msg_Error_Type WHERE id_type = 2);
    SET v_priority_access_level_view := (SELECT priority FROM partsltd_prod.PH_Access_Level WHERE code = 'VIEW' LIMIT 1);
    
    CALL partsltd_prod.p_core_validate_guid ( a_guid );
    SET a_ids_user := TRIM(IFNULL(a_ids_user, ''));
    SET a_get_inactive_user := IFNULL(a_get_inactive_user, 0);
    SET a_ids_permission := TRIM(IFNULL(a_ids_permission, ''));
    SET a_debug := IFNULL(a_debug, 0);
    
    IF a_debug = 1 THEN
		SELECT
			  a_guid
            , a_ids_user
            , a_get_inactive_user
            , a_ids_permission
            , a_debug
		;
    END IF;
    
	DROP TABLE IF EXISTS tmp_Calc_User;
	DROP TABLE IF EXISTS tmp_User_Calc_User;
	DROP TABLE IF EXISTS tmp_Split_Calc_User;
    
	CREATE TEMPORARY TABLE tmp_Calc_User (
		  id_row INT PRIMARY KEY AUTO_INCREMENT NOT NULL
		, id_user INT NULL
		, id_permission_required INT NOT NULL
		, priority_access_level_required INT NOT NULL
        , is_super_user BIT NULL
		, priority_access_level_user INT NULL
		, has_access BIT
	);

	CREATE TEMPORARY TABLE tmp_User_Calc_User (
		id_user INT NOT NULL
        , is_super_user BIT NOT NULL
        -- , id_access_level INT
        , priority_access_level INT NOT NULL
	);
    
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error (
		  display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT
		, id_type INT NULL
        , code VARCHAR(50) NOT NULL
        , msg VARCHAR(4000) NOT NULL
	);
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Split_Calc_User (
		substring VARCHAR(4000) NOT NULL
        , as_int INT NULL
	);
    DELETE FROM tmp_Split_Calc_User;
	
    SET v_has_filter_user = CASE WHEN a_ids_user = '' THEN 0 ELSE 1 END;
	SET a_ids_permission = REPLACE(a_ids_permission, '|', ',');
    SET v_has_filter_permission = CASE WHEN a_ids_permission = '' THEN 0 ELSE 1 END;
    -- SET v_id_access_level_view = (SELECT id_access_level FROM partsltd_prod.PH_Access_Level WHERE code = 'VIEW' LIMIT 1);


    -- Permission IDs
	IF NOT v_has_filter_permission THEN
        INSERT INTO tmp_Msg_Error (
              id_type
            , code
            , msg
        )
        SELECT
              v_id_type_error_bad_data
            , v_code_type_error_bad_data
            , 'Permission ID required.'
        ;
    ELSE
		CALL partsltd_prod.p_core_split(a_guid, a_ids_permission, ',', a_debug);
		
		INSERT INTO tmp_Split_Calc_User (
			  substring
			, as_int
		)
		SELECT 
			  substring
			, CONVERT(substring, DECIMAL(10,0)) AS as_int
		FROM partsltd_prod.CORE_Split_Temp
		WHERE 
                GUID = a_guid
			AND NOT ISNULL(substring)
			AND substring != ''
		;
		
		CALL partsltd_prod.p_core_clear_split( a_guid );
		
		-- Invalid or inactive
		IF EXISTS (
            SELECT PERM.id_permission 
            FROM tmp_Split_Calc_User t_S 
            LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission 
            WHERE 
                    ISNULL(t_S.as_int) 
                OR  ISNULL(PERM.id_permission) 
                OR  PERM.active = 0
        ) THEN
			INSERT INTO tmp_Msg_Error (
				  id_type
				, code
				, msg
			)
			SELECT
				  v_id_type_error_bad_data
				, v_code_type_error_bad_data
				, CONCAT('Invalid or inactive permission IDs: ', IFNULL(GROUP_CONCAT(t_S.substring SEPARATOR ', '), 'NULL'))
			FROM tmp_Split_Calc_User t_S
			LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission 
			WHERE 
				    ISNULL(t_S.as_int)
				OR  ISNULL(PERM.id_permission)
				OR  PERM.active = 0
			;
		ELSE 
            SELECT
                  PERM.id_permission
                , PERM.id_access_level_required
            INTO 
                  v_id_permission_required
                , v_priority_access_level_required
            FROM tmp_Split_Calc_User t_S 
            LEFT JOIN partsltd_prod.PH_Permission PERM ON t_S.as_int = PERM.id_permission 
            INNER JOIN partsltd_prod.PH_Access_Level AL ON PERM.id_access_level_required = AL.id_access_level
            ORDER BY AL.priority ASC
            LIMIT 1
            ;

            IF ISNULL(v_id_permission_required) THEN
                INSERT INTO tmp_Msg_Error (
                    id_type
                    , code
                    , msg
                )
                SELECT
                    v_id_type_error_bad_data
                    , v_code_type_error_bad_data
                    , 'Valid Permission ID required.'
                ;
            END IF;
		END IF;
	END IF;
    DELETE FROM tmp_Split_Calc_User;
    
	-- Users
	CALL partsltd_prod.p_core_split(a_guid, a_ids_user, ',', a_debug);
	
	INSERT INTO tmp_Split_Calc_User (
		  substring
		, as_int
	)
	SELECT 
		substring
		, CONVERT(substring, DECIMAL(10,0)) AS as_int
	FROM partsltd_prod.CORE_Split_Temp
	WHERE 
            GUID = a_guid
		AND NOT ISNULL(substring)
		AND substring != ''
	;
	
	CALL partsltd_prod.p_core_clear_split( a_guid );
	
	-- Invalid or inactive
	IF EXISTS (SELECT U.id_user FROM tmp_Split_Calc_User t_S LEFT JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user WHERE ISNULL(t_S.as_int) OR ISNULL(U.id_user) OR (a_get_inactive_user = 0 AND U.active = 0)) THEN
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		SELECT
			  v_id_type_error_bad_data
			, v_code_type_error_bad_data
			, CONCAT('Invalid or inactive user IDs: ', IFNULL(GROUP_CONCAT(t_S.substring SEPARATOR ', '), 'NULL'))
		FROM tmp_Split_Calc_User t_S
		LEFT JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user 
		WHERE 
			    ISNULL(t_S.as_int) 
			OR  ISNULL(U.id_user)
			OR  (
				    a_get_inactive_user = 0
                AND U.active = 0
			)
		;
	ELSE
		IF NOT EXISTS (SELECT * FROM tmp_Split_Calc_User) THEN
			INSERT INTO tmp_Split_Calc_User (substring, as_int)
			VALUES ( '', NULL );
		END IF;
		
		IF a_debug = 1 THEN
			SELECT *
			FROM tmp_Split_Calc_User;
		END IF;
		
        INSERT INTO tmp_User_Calc_User (
			  id_user
            , is_super_user
            , priority_access_level
		)
        SELECT
			  U.id_user
            , IFNULL(U.is_super_user, 0) AS is_super_user
            , IFNULL(MIN(AL_U.priority), v_priority_access_level_view) AS priority_access_level
		FROM tmp_Split_Calc_User t_S
        INNER JOIN partsltd_prod.PH_User U ON t_S.as_int = U.id_user
		LEFT JOIN partsltd_prod.PH_User_Role_Link URL
			ON U.id_user = URL.id_user
			AND URL.active
		LEFT JOIN partsltd_prod.PH_Role_Permission_Link RPL
			ON URL.id_role = RPL.id_role
			AND RPL.active
		LEFT JOIN partsltd_prod.PH_Access_Level AL_U
			ON RPL.id_access_level = AL_U.id_access_level
			AND AL_U.active
		GROUP BY U.id_user
        ;
        			
		INSERT INTO tmp_Calc_User (
			  id_user
			, id_permission_required
			, priority_access_level_required
			, priority_access_level_user
			, is_super_user
		)
		SELECT 
			  t_UCU.id_user
			, v_id_permission_required
			, v_priority_access_level_required
			, t_UCU.priority_access_level AS priority_access_level_user
			, t_UCU.is_super_user AS is_super_user
		FROM tmp_User_Calc_User t_UCU
        ;
	END IF;    
    DELETE FROM tmp_Split_Calc_User;

    
    -- Calculated fields
	UPDATE tmp_Calc_User t_CU
	SET
		  t_CU.has_access = (
                (t_CU.is_super_user = 1)
            OR  (t_CU.priority_access_level_user <= t_CU.priority_access_level_required)
        )
	;
    
    -- Export data to staging table
    IF NOT EXISTS (SELECT * FROM tmp_Msg_Error) THEN
        START TRANSACTION;
			INSERT INTO partsltd_prod.PH_Calc_User_Temp (
				  guid
				, id_user
				, id_permission_required
				, priority_access_level_required
				, priority_access_level_user
				, is_super_user
				, has_access
			)
			SELECT
				  a_guid
				, t_CU.id_user
				, t_CU.id_permission_required
				, t_CU.priority_access_level_required
				, t_CU.priority_access_level_user
				, t_CU.is_super_user
                , t_CU.has_access
			FROM tmp_Calc_User t_CU
			;
		COMMIT;
    END IF;
    
    IF a_debug = 1 THEN
		SELECT * FROM tmp_Msg_Error;
		SELECT * FROM tmp_Calc_User;
        SELECT * FROM tmp_User_Calc_User;
		SELECT * FROM partsltd_prod.PH_Calc_User_Temp WHERE GUID = a_guid;
		CALL partsltd_prod.p_ph_clear_calc_user ( a_guid, a_debug );
    END IF;
    
    -- Clean up
	DROP TABLE IF EXISTS tmp_Calc_User;
	DROP TABLE IF EXISTS tmp_User_Calc_User;
    DELETE FROM tmp_Split_Calc_User;
    
    IF a_debug = 1 THEN
        CALL partsltd_prod.p_debug_timing_reporting( v_time_start );
    END IF;
END //
DELIMITER ;

/*

CALL partsltd_prod.p_ph_calc_user (
	'chips                               '  -- a_guid
	, 1										-- a_ids_user
	, 0										-- a_get_inactive_user
	, '2'									-- a_ids_permission
	, '1'									-- a_ids_access_level
    , 0										-- a_debug
);
CALL partsltd_prod.p_ph_calc_user (
	'chips                               '  -- a_guid
	, 1 									-- a_ids_user
	, 0 									-- a_get_inactive_user
	, '2'									-- a_ids_permission
	, '1'									-- a_ids_access_level
    , 0										-- a_debug
);
*/
USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_clear_calc_user;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_ph_clear_calc_user (
	  IN a_guid BINARY(36)
    , IN a_debug BIT
)
BEGIN
	DECLARE v_time_start TIMESTAMP(6);
    SET v_time_start := CURRENT_TIMESTAMP(6);
    
    CALL partsltd_prod.p_core_validate_guid ( a_guid );
    
	START TRANSACTION;
		
		DELETE FROM partsltd_prod.PH_Calc_User_Temp
		WHERE GUID = a_guid
		;
	
	COMMIT;
    
    IF a_debug = 1 THEN
		CALL partsltd_prod.p_debug_timing_reporting( v_time_start );
    END IF;
END //
DELIMITER ;

/*

CALL partsltd_prod.p_ph_clear_calc_user (
	'chips                               ' -- a_guid
    , 1 -- debug
);

SELECT *
FROM partsltd_prod.PH_Calc_User_Temp
WHERE GUID = 'chips                               '
;

*/

USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_save_contact_form;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_ph_save_contact_form (
    IN a_comment VARCHAR(500),
	IN a_guid BINARY(36),
    IN a_id_user INT,
    IN a_debug BIT
)
BEGIN
	DECLARE v_code_type_error_bad_data VARCHAR(100);
    DECLARE v_id_type_error_bad_data INT;
    DECLARE v_id_permission_contact_form_admin INT;
    DECLARE v_id_permission_contact_form_new INT;
    DECLARE v_id_change_set INT;
    DECLARE v_time_start TIMESTAMP(6);
    DECLARE v_can_admin BIT;
    DECLARE v_can_create BIT;
    
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @sqlstate = RETURNED_SQLSTATE
            , @errno = MYSQL_ERRNO
            , @text = MESSAGE_TEXT
		;
        
        ROLLBACK;
        
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error (
			  display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT
			, id_type INT NULL
			, code VARCHAR(50) NOT NULL
			, msg VARCHAR(4000) NOT NULL
		);

        INSERT INTO tmp_Msg_Error (
			  id_type
            , code
            , msg
		)
        SELECT 
			  MET.id_type
            , @errno
            , @text
		FROM partsltd_prod.CORE_Msg_Error_Type MET
        WHERE MET.code = 'MYSQL_ERROR'
		;
        
        SELECT *
        FROM tmp_Msg_Error;
		DROP TABLE IF EXISTS tmp_Msg_Error
        ;
    END;
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET v_code_type_error_bad_data := 'BAD_DATA';
    SET v_id_type_error_bad_data := (SELECT id_type FROM partsltd_prod.CORE_Msg_Error_Type WHERE code = v_code_type_error_bad_data LIMIT 1);
    SET v_id_permission_contact_form_admin := (SELECT id_permission FROM partsltd_prod.PH_Permission P WHERE P.code = 'CONTACT_FORM_ADMIN' LIMIT 1);
    SET v_id_permission_contact_form_new := (SELECT id_permission FROM partsltd_prod.PH_Permission P WHERE P.code = 'CONTACT_FORM_NEW' LIMIT 1);
    
    CALL partsltd_prod.p_core_validate_guid ( a_guid );
    
    DROP TABLE IF EXISTS tmp_Contact_Form;
    
    CREATE TEMPORARY TABLE tmp_Contact_Form (
		  id_contact_form INT NOT NULL
        , email VARCHAR(255) NOT NULL
        , name_contact VARCHAR(255) NOT NULL
        , name_company VARCHAR(255) NOT NULL
        , message TEXT NOT NULL
        , receive_marketing_communications BIT NOT NULL
        , active BIT NOT NULL
        , name_error VARCHAR(255)
        , is_new BIT NOT NULL
    );
        
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error (
		  display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT
		, id_type INT NULL
        , code VARCHAR(50) NOT NULL
        , msg VARCHAR(4000) NOT NULL
	);
        
    
    -- Get data from Temp table
    INSERT INTO tmp_Contact_Form (
		  id_contact_form
        , email
        , name_contact
        , name_company
        , message
        , receive_marketing_communications
        , active
        , is_new
	)
    SELECT 
		  CF_T.id_contact_form AS id_contact_form
        , IFNULL(CF_T.email, CF.email) AS code
        , IFNULL(CF_T.name_contact, CF.name_contact) AS name_contact
        , IFNULL(CF_T.name_company, CF.name_company) AS name_company
        , IFNULL(CF_T.message, CF.message) AS message
        , COALESCE(CF_T.receive_marketing_communications, CF.receive_marketing_communications, 0) AS receive_marketing_communications
        , COALESCE(CF_T.active, CF.active, 1) AS active
        , CASE WHEN IFNULL(CF_T.id_contact_form, 0) < 1 THEN 1 ELSE 0 END AS is_new
	FROM partsltd_prod.PH_Contact_Form_Temp CF_T
    LEFT JOIN partsltd_prod.PH_Contact_Form CF ON CF_T.id_contact_form = CF.id_contact_form
    WHERE CF_T.guid = a_guid
    ;

    UPDATE tmp_Contact_Form t_CF
    SET name_error = COALESCE(t_CF.email, t_CF.name_company, t_CF.name_contact, t_CF.message, '(No Contact Form)')
    ;
    
    -- Validation
    -- Missing mandatory fields
    -- email
    IF EXISTS (SELECT * FROM tmp_Contact_Form t_CF WHERE ISNULL(t_CF.email) LIMIT 1) THEN
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		SELECT
			  v_id_type_error_bad_data
			, v_code_type_error_bad_data
			, CONCAT('The following Contact Form(s) do not have an Email: ', GROUP_CONCAT(t_CF.name_error SEPARATOR ', ')) AS msg
		FROM tmp_Contact_Form t_CF
		WHERE ISNULL(t_CF.email)
		;
    END IF;
    -- name_contact
    IF EXISTS (SELECT * FROM tmp_Contact_Form t_CF WHERE ISNULL(t_CF.name_contact) LIMIT 1) THEN
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		SELECT
			  v_id_type_error_bad_data
			, v_code_type_error_bad_data
			, CONCAT('The following Contact Form(s) do not have a Contact Name: ', GROUP_CONCAT(t_CF.name_error SEPARATOR ', ')) AS msg
		FROM tmp_Contact_Form t_CF
		WHERE ISNULL(t_CF.name_contact)
		;
    END IF;
    -- name_company
    IF EXISTS (SELECT * FROM tmp_Contact_Form t_CF WHERE ISNULL(t_CF.name_company) LIMIT 1) THEN
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		SELECT
			  v_id_type_error_bad_data
			, v_code_type_error_bad_data
			, CONCAT('The following Contact Form(s) do not have a Company Name: ', GROUP_CONCAT(t_CF.name_error SEPARATOR ', ')) AS msg
		FROM tmp_Contact_Form t_CF
		WHERE ISNULL(t_CF.name)
		;
    END IF;
    -- message
    IF EXISTS (SELECT * FROM tmp_Contact_Form t_CF WHERE ISNULL(t_CF.message) LIMIT 1) THEN
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		SELECT
			  v_id_type_error_bad_data
			, v_code_type_error_bad_data
			, CONCAT('The following Contact Form(s) do not have a Message: ', GROUP_CONCAT(t_CF.name_error SEPARATOR ', ')) AS msg
		FROM tmp_Contact_Form t_CF
		WHERE ISNULL(t_CF.message)
		;
    END IF;
    
    -- Permissions
    IF a_debug = 1 THEN
		SELECT 
			  a_guid
			, a_id_user
			, FALSE -- a_get_inactive_user
			, v_id_permission_contact_form_admin
			, v_id_permission_contact_form_new
			, 0 -- a_debug
        ;
    END IF;
    
	CALL partsltd_prod.p_ph_calc_user(
          a_guid
        , a_id_user
        , FALSE -- a_get_inactive_user
        , v_id_permission_contact_form_admin
        , 0 -- a_debug
    );
	
    SELECT
          IFNULL(CU_T.has_access, 0)
    INTO
          v_can_admin
    FROM partsltd_prod.PH_Calc_User_Temp CU_T
    WHERE CU_T.GUID = a_guid
    LIMIT 1
    ;
	
	CALL partsltd_prod.p_ph_clear_calc_user(
		  a_guid
        , 0 -- a_debug
	);
    
	CALL partsltd_prod.p_ph_calc_user(
          a_guid
        , a_id_user
        , FALSE -- a_get_inactive_user
        , v_id_permission_contact_form_new
        , 0 -- a_debug
    );
	
    SELECT
          IFNULL(CU_T.has_access, 0)
    INTO
          v_can_create
    FROM partsltd_prod.PH_Calc_User_Temp CU_T
    WHERE CU_T.GUID = a_guid
    LIMIT 1
    ;
	
	CALL partsltd_prod.p_ph_clear_calc_user(
		  a_guid
        , 0 -- a_debug
	);
    
    IF (v_can_create = 0 AND EXISTS(SELECT * FROM tmp_Contact_Form WHERE is_new = 1)) THEN
		DELETE t_ME
        FROM tmp_Msg_Error t_ME
        WHERE t_ME.id_type <> v_id_type_error_no_permission
        ;
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		VALUES (
			  v_id_type_error_no_permission
			, v_code_type_error_no_permission
			, 'You do not have permission to create new Contact Forms.'
		)
        ;
    END IF;
    
    IF (v_can_admin = 0 AND EXISTS(SELECT * FROM tmp_Contact_Form WHERE is_new = 0)) THEN
		DELETE t_ME
        FROM tmp_Msg_Error t_ME
        WHERE t_ME.id_type <> v_id_type_error_no_permission
        ;
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		VALUES (
			  v_id_type_error_no_permission
			, v_code_type_error_no_permission
			, 'You do not have permission to admin Contact Forms.'
		)
        ;
    END IF;
    
    IF EXISTS (SELECT * FROM tmp_Msg_Error LIMIT 1) THEN
    	IF a_debug = 1 THEN
	    	SELECT * from tmp_Contact_Form;
	    END IF;

        DELETE FROM tmp_Contact_Form;
    END IF;

    IF NOT EXISTS (SELECT * FROM tmp_Msg_Error LIMIT 1) THEN
		START TRANSACTION;
		
			INSERT INTO partsltd_prod.PH_Contact_Form_Change_Set ( 
                  comment
                , id_user_updated_last_by
                , updated_last_on
            )
			VALUES ( 
                  a_comment
                , a_id_user
                , v_time_start
            )
			;
			
			SET v_id_change_set := LAST_INSERT_ID();
			
			UPDATE partsltd_prod.PH_Contact_Form CF
			INNER JOIN tmp_Contact_Form t_CF 
				ON  CF.id_contact_form = t_CF.id_contact_form
				AND t_CF.is_new = 0
			SET 
				  CF.email = t_CF.email
				, CF.name_contact = t_CF.name_contact
				, CF.name_company = t_CF.name_company
				, CF.message = t_CF.message
                , CF.receive_marketing_communications = t_CF.receive_marketing_communications
				, CF.active = t_CF.active
				, CF.id_change_set = v_id_change_set
			;
			
			INSERT INTO partsltd_prod.PH_Contact_Form (
				  email
				, name_contact
				, name_company
				, message
                , receive_marketing_communications
				, active
				, id_user_created_by
				, created_on
			)
			SELECT
				  t_CF.email                            AS email
				, t_CF.name_contact                     AS name_contact
				, t_CF.name_company                     AS name_company
				, t_CF.message                          AS message
                , t_CF.receive_marketing_communications AS receive_marketing_communications
				, t_CF.active                           AS active
				, a_id_user                             AS created_by
				, v_time_start                          AS created_on
			FROM tmp_Contact_Form t_CF
			WHERE 
				    t_CF.is_new = 1
				AND t_CF.active = 1
			;
			
		COMMIT;
    END IF;
	
	START TRANSACTION;

		DELETE FROM partsltd_prod.PH_Contact_Form_Temp
		WHERE GUID = a_guid
        ;
		
	COMMIT;
    
    -- Errors
    SELECT *
    FROM tmp_Msg_Error t_ME
	INNER JOIN partsltd_prod.CORE_Msg_Error_Type MET ON t_ME.id_type = MET.id_type
	;
    
	IF a_debug = 1 THEN
		SELECT * from tmp_Contact_Form;
	END IF;

    DROP TEMPORARY TABLE tmp_Contact_Form;
    DROP TEMPORARY TABLE tmp_Msg_Error;
    
	IF a_debug = 1 THEN
		CALL partsltd_prod.p_core_debug_timing_reporting ( v_time_start );
	END IF;
END //
DELIMITER ;

/*
select 
	*
    -- COUNT(*)
-- delete
from partsltd_prod.PH_Contact_Form_Temp
;


CALL partsltd_prod.p_ph_save_product_CFategory (
	'nipples'
    , (SELECT GUID FROM partsltd_prod.PH_Contact_Form_Temp ORDER BY id_temp DESC LIMIT 1)
    , 1
    , 1
);

select 
	*
    -- COUNT(*)
-- delete
from partsltd_prod.PH_Contact_Form_Temp
;

*/
USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_test_save_contact_form;


DELIMITER //
CREATE PROCEDURE partsltd_prod.p_ph_test_save_contact_form ()
BEGIN
	
	DECLARE v_guid BINARY(36);
	DECLARE v_time_start TIMESTAMP(6);
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
	SET v_guid := 'nipple_ripple_chipple_spittle_pickle'; -- 123456789012345678901234567890123456

    SELECT 'Start of Test';

	SELECT *
	FROM partsltd_prod.PH_Contact_Form
	;
	SELECT *
	FROM partsltd_prod.PH_Contact_Form_Temp
	;

	START TRANSACTION;
		
		INSERT INTO partsltd_prod.PH_Contact_Form_Temp (
			  id_contact_form
            , email
            , name_contact
            , name_company
            , message
            , guid
            , active
		)
        /*
		VALUES (
			  -1 -- id_contact_form
			, 'edward.middletonsmith@gmail.com' -- email
			, 'Teddy' -- name_contact
			, 'PARTS Ltd' -- name_company
			, 'Sa dude' -- message
			, v_guid
		)
        */
		VALUES (
			  -1 -- id_contact_form
			, 'edward.middletonsmith@gmail.com' -- email
			, 'Teddy' -- name_contact
			, 'PARTS Ltd' -- name_company
			, 'hegrodorf is good' -- message
			, v_guid
            , 1 -- active
		)
        ;
		
	COMMIT;
    
	SELECT *
	FROM partsltd_prod.PH_Contact_Form_Temp
	-- WHERE GUID = v_guid
    ;
    
    CALL partsltd_prod.p_ph_save_contact_form ( 
		  'Test save Contact Form' -- comment
        , v_guid -- guid
        , 3 -- 1 -- id_user
		, 1 -- debug
    );
    
	SELECT *
	FROM partsltd_prod.PH_Contact_Form
	;
	SELECT *
	FROM partsltd_prod.PH_Contact_Form_Temp
	;
    
	CALL partsltd_prod.p_debug_timing_reporting ( v_time_start );
END //
DELIMITER ;


/*
SELECT 'Before Test';
SELECT *
FROM partsltd_prod.PH_Contact_Form
;
SELECT *
FROM partsltd_prod.PH_Contact_Form_Temp
;


CALL partsltd_prod.p_ph_test_save_contact_form ();

SELECT 'After Test';
SELECT *
FROM partsltd_prod.PH_Contact_Form
;
SELECT *
FROM partsltd_prod.PH_Contact_Form_Temp
;

DELETE FROM partsltd_prod.PH_Contact_Form_Temp;

DROP TABLE IF EXISTS tmp_Msg_Error;


*/
 
USE partsltd_prod;

DROP PROCEDURE IF EXISTS partsltd_prod.p_ph_get_many_contact_form;

DELIMITER //
CREATE PROCEDURE partsltd_prod.p_ph_get_many_contact_form (
	  IN a_id_user INT
    , IN a_get_all_contact_form BIT
	, IN a_get_inactive_contact_form BIT
	, IN a_ids_contact_form VARCHAR(500)
    , IN a_debug BIT
)
BEGIN
    DECLARE v_has_filter_contact_form BIT;
    DECLARE v_guid BINARY(36);
    DECLARE v_id_permission_contact_form_view INT;
    DECLARE v_id_minimum INT;
    DECLARE v_time_start TIMESTAMP(6);
    DECLARE v_can_view BIT;
    
    SET v_time_start := CURRENT_TIMESTAMP(6);
    SET v_guid := UUID();
    SET v_id_permission_contact_form_view := (SELECT id_permission FROM partsltd_prod.PH_Permission WHERE code = 'CONTACT_FORM_ADMIN' LIMIT 1);
    
    SET a_id_user := IFNULL(a_id_user, 0);
    SET a_get_all_contact_form := IFNULL(a_get_all_contact_form, 0);
    SET a_get_inactive_contact_form := IFNULL(a_get_inactive_contact_form, 0);
    SET a_ids_contact_form := TRIM(IFNULL(a_ids_contact_form, ''));
    SET a_debug := IFNULL(a_debug, 0);
    
    IF a_debug = 1 THEN
		SELECT 
			  a_id_user
            , a_get_all_contact_form
            , a_get_inactive_contact_form
            , a_ids_contact_form
            , a_debug
		;
    END IF;

    DROP TEMPORARY TABLE IF EXISTS tmp_Split;
    DROP TEMPORARY TABLE IF EXISTS tmp_Contact_Form;
    
    CREATE TEMPORARY TABLE tmp_Contact_Form (
		id_contact_form INT NOT NULL
    );
    
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Msg_Error (
          display_order INT NOT NULL PRIMARY KEY AUTO_INCREMENT
		, id_type INT NULL
        , code VARCHAR(50) NOT NULL
        , msg VARCHAR(4000) NOT NULL
	);
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Split (
		  substring VARCHAR(4000) NOT NULL
        , as_int INT NULL
	);
    DELETE FROM tmp_Split;
    
    CALL partsltd_prod.p_core_validate_guid ( v_guid );
    
    SET v_has_filter_contact_form = CASE WHEN a_ids_contact_form = '' THEN 0 ELSE 1 END;
    
    -- Contact Forms
    IF v_has_filter_contact_form = 1 THEN
		CALL partsltd_prod.p_split(v_guid, a_ids_contact_form, ',', a_debug);
		
		INSERT INTO tmp_Split (
			  substring
			, as_int
		)
		SELECT 
			  substring
			, CONVERT(substring, DECIMAL(10,0)) AS as_int
		FROM partsltd_prod.CORE_Split_Temp
		WHERE 
                GUID = v_guid
			AND NOT ISNULL(substring)
			AND substring != ''
		;
		
		CALL partsltd_prod.p_clear_split_temp( v_guid );
	END IF;
    
    IF NOT EXISTS (SELECT * FROM tmp_Msg_Error LIMIT 1) THEN
		IF EXISTS (
			SELECT * 
            FROM tmp_Split t_S 
            LEFT JOIN partsltd_prod.PH_Contact_Form CF ON t_S.as_int = CF.id_contact_form 
			WHERE 
				    ISNULL(t_S.as_int) 
                OR  ISNULL(CF.id_contact_form)
				OR  (
					CF.active = 0
					AND a_get_inactive_contact_form = 0
				)
		) THEN
			INSERT INTO tmp_Msg_Error (
				  id_type
				, code
				, msg
			)
			SELECT
				  v_id_type_error_bad_data
				, v_code_type_error_bad_data
				, CONCAT('Invalid or inactive Contact Form IDs: ', IFNULL(GROUP_CONCAT(t_S.substring SEPARATOR ', '), 'NULL'))
			FROM tmp_Split t_S
			LEFT JOIN partsltd_prod.PH_Contact_Form CF ON t_S.as_int = CF.id_contact_form
			WHERE 
				    ISNULL(t_S.as_int) 
				OR  ISNULL(CF.id_contact_form)
				OR  (
					CF.active = 0
					AND a_get_inactive_contact_form = 0
				)
			;
		ELSE
			INSERT INTO tmp_Contact_Form (
				id_contact_form
			)
			SELECT 
				CF.id_contact_form
			FROM tmp_Split t_S
			RIGHT JOIN partsltd_prod.PH_Contact_Form CF ON t_S.as_int = CF.id_contact_form
			WHERE 
                (
					    a_get_all_contact_form = 1
					OR  (
						    v_has_filter_contact_form = 1
						AND NOT ISNULL(t_S.as_int)
					)
				)
				AND (
					    a_get_inactive_contact_form = 1
					OR  CF.active = 1
				)
			;
		END IF;
	END IF;
    
    DELETE FROM tmp_Split;
    

    -- Permissions
    IF a_debug = 1 THEN
		SELECT 
			  v_guid
			, a_id_user
			, FALSE -- a_get_inactive_user
			, v_id_permission_contact_form_view
			, 0 -- a_debug
        ;
    END IF;
    
	CALL partsltd_prod.p_ph_calc_user(
          v_guid
        , a_id_user
        , FALSE -- a_get_inactive_user
        , v_id_permission_contact_form_view
        , 0 -- a_debug
    );
	
    SELECT
          IFNULL(CU_T.has_access, 0)
    INTO
          v_can_view
    FROM partsltd_prod.PH_Calc_User_Temp CU_T
    WHERE CU_T.GUID = v_guid
    LIMIT 1
    ;
    
    IF (v_can_view = 0) THEN
		DELETE t_ME
        FROM tmp_Msg_Error t_ME
        WHERE t_ME.id_type <> v_id_type_error_no_permission
        ;
		INSERT INTO tmp_Msg_Error (
			  id_type
			, code
			, msg
		)
		VALUES (
			v_id_type_error_no_permission
			, v_code_type_error_no_permission
			, 'You do not have permission to view Contact Forms.'
		)
        ;
    END IF;
	
	CALL partsltd_prod.p_ph_clear_calc_user(
		v_guid
        , 0 -- a_debug
	);
    
    IF v_can_view = 0 THEN
        IF a_debug = 1 THEN
            SELECT * FROM tmp_Contact_Form;
        END IF;

        DELETE FROM tmp_Contact_Form;
    END IF;

    -- Outputs
    -- Contact Forms
    SELECT 
          t_CF.id_contact_form
        , CF.email
        , CF.name_contact
        , CF.name_company
        , CF.message
        , CF.active
        , v_can_view
    FROM tmp_Contact_Form t_CF
    INNER JOIN partsltd_prod.PH_Contact_Form CF ON t_CF.id_contact_form = CF.id_contact_form
	GROUP BY t_CF.id_contact_form
	ORDER BY CF.created_on DESC
	;
    
    -- Errors
    SELECT *
    FROM tmp_Msg_Error t_ME
    INNER JOIN partsltd_prod.CORE_Msg_Error_Type MET ON t_ME.id_type = MET.id_type
    ;
    
    IF a_debug = 1 AND v_can_view = 1 THEN
		SELECT * FROM tmp_Contact_Form;
    END IF;
    
    DROP TEMPORARY TABLE IF EXISTS tmp_Split;
    DROP TEMPORARY TABLE IF EXISTS tmp_Contact_Form;
    
    IF a_debug = 1 THEN
		CALL partsltd_prod.p_core_debug_timing_reporting ( v_time_start );
    END IF;
END //
DELIMITER ;


/*

CALL partsltd_prod.p_ph_get_many_contact_form (
	  1 -- 'auth0|6582b95c895d09a70ba10fef',	-- a_id_user
    , 1                                     	-- a_get_all_contact_form
	, 0                                     	-- a_get_inactive_contact_form
	, ''                                    	-- a_ids_contact_form
    , 0                                     	-- a_debug
);

*/

USE partsltd_prod;


-- Error Message Types
INSERT INTO partsltd_prod.CORE_Msg_Error_Type (
	  code
    , name
    , description
)
VALUES 
	(
          'BAD_DATA'
        , 'Invalid data'
        , 'Rubbish data'
    )
	, (
          'NO_PERMISSION'
        , 'No permission'
        , 'Not authorised'
    )
    , (
          'MYSQL_ERROR'
        , 'MySQL error'
        , 'MySQL execution error.'
    )
    , (
          'WARNING'
        , 'Warning'
        , 'Non-breaking error.'
    )
;

-- Access Levels
INSERT INTO partsltd_prod.PH_Access_Level (
	  display_order
    , code
    , name
    , priority
)
VALUES 
	(
          1
        , 'VIEW'
        , 'View'
        , 3
    )
	, (
          2
        , 'EDIT'
        , 'Edit'
        , 2
    )
	, (
          3
        , 'ADMIN'
        , 'Admin'
        , 1
    )
;

-- Permission Groups
INSERT INTO partsltd_prod.PH_Permission_Group (
	display_order
    , code
    , name
)
VALUES 
	(
          0
        , 'CONTACT_FORM'
        , 'Contact Form'
    )
	, (
          1
        , 'USER'
        , 'Admin User'
    )
;

-- Permissions
INSERT INTO partsltd_prod.PH_Permission (
	  display_order
    , code
    , name
    , id_permission_group
    , id_access_level_required
)
VALUES 
	(
          1
        , 'CONTACT_FORM_NEW'
        , 'New Contact Form'
        , 1
        , 3
    )
	, (
          1
        , 'CONTACT_FORM_ADMIN'
        , 'Admin Contact Form'
        , 1
        , 3
    )
	, (
          1
        , 'CONTACT_FORM_CREATE'
        , 'Create Contact Form'
        , 1
        , 1
    )
;

-- Users
INSERT INTO partsltd_prod.PH_User (
	  id_user_auth0
	, firstname
    , surname
	, email
	, is_super_user
    , active
)
VALUES 
	(
		  'auth0|6582b95c895d09a70ba10fef' -- id_user_auth0
		, 'Teddy' -- firstname
        , 'Middleton-Smith' -- surname
        , 'edward.middletonsmith@gmail.com' -- email
        , 1 -- is_super_user
		, 1 -- active
	)
    , (
		'auth0|672659014296b7f94a9bab45' -- id_user_auth0
		, 'Tierney' -- firstname
		, 'Gullen' -- surname
		, 'tierneybailey13@gmail.com' -- email
		, 1 -- is_super_user
		, 1 -- active
	)
    , (
          NULL -- id_user_auth0
        , 'Contact Form Bot' -- firstname
        , 'Bot' -- surname
        , 'teddy@partsltd.co.uk' -- email
        , 0 -- is_super_user
        , 1 -- active
    )
;

-- Roles
INSERT INTO partsltd_prod.PH_Role (
	  display_order
	, code
	, name
    , id_user_created_by
)
VALUES 
	  (
          1
        , 'DIRECTOR'
        , 'Director'
        , 1
    )
	, (
          2
        , 'USER'
        , 'User'
        , 1
    )
;

-- Role Permission link
INSERT INTO partsltd_prod.PH_Role_Permission_Link (
	  id_role
    , id_permission
    , id_access_level
    , id_user_created_by
)
VALUES 
	  (
          1
        , 1
        , 3
        , 1
    )
	, (
          1
        , 2
        , 3
        , 1
    )
	, (
          1
        , 3
        , 3
        , 1
    )
	, (
          2
        , 1
        , 1
        , 1
    )
	, (
          2
        , 2
        , 1
        , 1
    )
	, (
          2
        , 3
        , 1
        , 1
    )
;

-- User Role link
INSERT INTO partsltd_prod.PH_User_Role_Link (
	  id_user
    , id_role
    , id_user_created_by
)
VALUES 
	(
          1
        , 1
        , 1
    )
	, (
          2
        , 2
        , 1
    )
	, (
          3
        , 2
        , 1
    )
;


INSERT INTO partsltd_prod.PH_Contact_Form (
      email
    , name_contact
    , name_company
    , message
    , id_user_created_by
)
VALUES 
      (
          'edward.middleton-smith@gmail.com'
        , 'Teddy Middleton-Smith'
        , 'PARTS Ltd'
        , 'Hello, I would like to enquire about your services.'
        , 1
    )
;

-- Error Message type
SELECT * FROM partsltd_prod.CORE_Msg_Error_Type;

-- User Change Sets
SELECT * FROM partsltd_prod.PH_User_Change_Set;

-- Access Levels
SELECT * FROM partsltd_prod.PH_Access_Level;

-- Permission Groups
SELECT * FROM partsltd_prod.PH_Permission_Group;

-- Permissions
SELECT * FROM partsltd_prod.PH_Permission;

-- Users
SELECT * FROM partsltd_prod.PH_User;
SELECT * FROM partsltd_prod.PH_User_Audit;

-- Roles
SELECT * FROM partsltd_prod.PH_Role;
SELECT * FROM partsltd_prod.PH_Role_Audit;

-- Role Permission link
SELECT * FROM partsltd_prod.PH_Role_Permission_Link;
SELECT * FROM partsltd_prod.PH_Role_Permission_Link_Audit;

-- User Role link
SELECT * FROM partsltd_prod.PH_User_Role_Link;
SELECT * FROM partsltd_prod.PH_User_Role_Link_Audit;

-- Contact Form Change Sets
SELECT * FROM partsltd_prod.PH_Contact_Form_Change_Set;

-- Contact Forms
SELECT * FROM partsltd_prod.PH_Contact_Form;
SELECT * FROM partsltd_prod.PH_Contact_Form_Audit;
SELECT * FROM partsltd_prod.PH_Contact_Form_Temp;