RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / sqls /
Filename : v2_1_changes.sql
back
ALTER TABLE task_settings MODIFY COLUMN `visible_to` enum('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD','ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD','SELECTED_PARENT', 'COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD') NOT NULL;
ALTER TABLE reward_settings MODIFY COLUMN `visible_to` enum('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD','ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD','SELECTED_PARENT', 'COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD') COLLATE utf8mb4_general_ci DEFAULT NULL;

ALTER TABLE task_visibility MODIFY COLUMN entity_type enum('ZIPCODE','USER','COUNTRY') NOT NULL;
ALTER TABLE reward_visibility MODIFY COLUMN entity_type enum('ZIPCODE','USER','COUNTRY') NOT NULL;

ALTER TABLE users_address ADD COLUMN country VARCHAR(250) DEFAULT NULL AFTER `state`;

CREATE TABLE custom_fields (
    custom_field_id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(255) NOT NULL,
    entity_id INT NOT NULL,
    field_type VARCHAR(255) NOT NULL,
    field_name VARCHAR(255) NOT NULL,
    field_label VARCHAR(255) NOT NULL,
    field_meta TEXT,
    field_help_text TEXT,
    field_data_regex_validation VARCHAR(255),
    data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE tuit_allocation_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    parent_id INT,
    action VARCHAR(100),
    description VARCHAR(255),
    tuit_points DECIMAL(18, 8),
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME
);

INSERT INTO tuit_allocation_config (parent_id, action, description, tuit_points, created_at, updated_at) VALUES
(NULL, 'CREATION_OF_CUSTOM_TASKS', 'Allocation for creating custom tasks', 0.100, NOW(), NOW()),
(NULL, 'CREATION_OF_CUSTOM_REWARDS', 'Allocation for creating custom rewards', 0.100, NOW(), NOW()),
(NULL, 'COMPLETION_OF_TASKS_BY_CHILDREN_IN_GROUP', 'Allocation for completion of tasks by children in group', 0.100, NOW(), NOW()),
(NULL, 'CLAIM_REVIEW_BY_PARENT', 'Allocation for claim review by parent (approve/reject)', 0.100, NOW(), NOW()),
(NULL, 'PARENT_SIGN_UP', 'Allocation for signing up by parent', 10.000, NOW(), NOW());

ALTER TABLE tuit_wallet_transactions
MODIFY COLUMN tuit DECIMAL(18,8) NOT NULL DEFAULT '0.00',
MODIFY COLUMN tuit_credit DECIMAL(18,8) NOT NULL DEFAULT '0.00',
MODIFY COLUMN tuit_debit DECIMAL(18,8) NOT NULL DEFAULT '0.00',
MODIFY COLUMN balance DECIMAL(18,8) NOT NULL DEFAULT '0.00';

DELIMITER $$
CREATE TRIGGER `Create TUIT Wallet` AFTER INSERT ON `users` FOR EACH ROW INSERT INTO `tuit_wallet`(user_id, wallet_uid, wallet_name, created_at) VALUES (NEW.user_id, UPPER(SUBSTRING(MD5(RAND()) FROM 1 FOR 10)), 'TUIT Wallet', CURRENT_TIMESTAMP)
$$
DELIMITER ;

UPDATE tuit_wallet_transactions SET tuit_credit = CASE WHEN tuit_credit = 0 THEN tuit_debit ELSE tuit_credit END, tuit_debit = 0 WHERE transaction_type = 'CREDIT';

UPDATE tuit_wallet_transactions SET tuit_debit = CASE WHEN tuit_debit = 0 THEN tuit_credit ELSE tuit_debit END, tuit_credit = 0 WHERE transaction_type = 'DEBIT';

CREATE TABLE user_activity_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    activity_details TEXT,
    activity_sub_details TEXT,
    side_effect_details TEXT,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME
);

ALTER TABLE `users`
ADD COLUMN `uuid` VARCHAR(100) NULL UNIQUE AFTER user_id;

UPDATE `users`
SET uuid = FLOOR(RAND() * 8999999999) + 1000000000
WHERE uuid IS NULL;

ALTER TABLE `users` ADD `ios_notify_invite_done` BOOLEAN NOT NULL DEFAULT FALSE AFTER `is_active`;

CREATE TABLE dynamic_contents (
    dynamic_content_id INT AUTO_INCREMENT PRIMARY KEY,
    parent_dynamic_content_id INT,
    content_key VARCHAR(100),
    content_value TEXT,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME
);

ALTER TABLE `user_activity_history`
ADD COLUMN `group_id` INT NULL AFTER user_id;

UPDATE user_activity_history SET group_id = -1 WHERE group_id IS NULL;

ALTER TABLE `user_settings`
ADD COLUMN `last_viewed_activity_history_id` INT NULL AFTER recent_goal_id;

ALTER TABLE `supported_cities`
ADD COLUMN `country_code` VARCHAR(22) DEFAULT NULL,
ADD COLUMN `country_name` VARCHAR(255) DEFAULT NULL,
ADD COLUMN `raw_postal_code` VARCHAR(22) DEFAULT NULL;


-- mysqldump -u root -p --complete-insert beforffv_coins_for_college supported_cities > supported_cities.sql

ALTER TABLE `supported_cities` ADD INDEX(`zip`);
ALTER TABLE `supported_cities` ADD INDEX(`country_name`, `zip`);
ALTER TABLE `supported_cities` ADD INDEX(`country_code`, `zip`);
ALTER TABLE `supported_cities` ADD INDEX(`country_name`, `city`, `zip`);
ALTER TABLE `supported_cities` ADD INDEX(`country_code`, `city`, `zip`);

ALTER TABLE `countries` ADD INDEX(`country_name`);
ALTER TABLE `countries` ADD INDEX(`country_code`);

UPDATE supported_cities AS sc JOIN countries AS c ON c.country_code = sc.country_code SET sc.country_name = c.country_name;

ALTER TABLE `users_address` CHANGE `country` `country_name` VARCHAR(250) NULL DEFAULT NULL;

CREATE TABLE `task_review_allowed` (
    `task_review_allowed_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `task_id` BIGINT NOT NULL,
    `task_settings_id` BIGINT NOT NULL,
    `review_allowed_to_type` ENUM('ADMIN', 'PARENTS_OR_FOSTER_AGENT', 'USER') NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL
);

INSERT INTO task_review_allowed(task_id, task_settings_id, review_allowed_to_type)
SELECT task_id, task_settings_id, task_review_allowed_to_type
FROM task_settings WHERE task_review_allowed_to_type IS NOT NULL
AND task_review_allowed_to_type != 'USER';

ALTER TABLE `task_visibility` CHANGE `entity_type` `entity_type` ENUM('ZIPCODE','USER','COUNTRY','COUNTRY_SINGLE') NOT NULL;
ALTER TABLE `reward_visibility` CHANGE `entity_type` `entity_type` ENUM('ZIPCODE','USER','COUNTRY','COUNTRY_SINGLE') NOT NULL;