RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / sqls /
Filename : new_task_reward_permission_relataed_tables.sql
back
CREATE TABLE `task_visibility` (
  `task_visibility_id` int(11) NOT NULL,
  `task_settings_id` int(11) NOT NULL,
  `entity_type` enum('ZIPCODE','USER') NOT NULL,
  `entity_value` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `permission_user_entity` (
  `permission_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `entity_type` enum('TASK','REWARD') NOT NULL,
  `entity_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `permission_team_entity` (
  `permission_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `entity_type` enum('TASK','REWARD') NOT NULL,
  `entity_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `reward_visibility` (
  `reward_visibility_id` int(11) NOT NULL,
  `reward_settings_id` int(11) NOT NULL,
  `entity_type` enum('ZIPCODE','USER') NOT NULL,
  `entity_value` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `change_log` (
  `change_log_id` int(11) NOT NULL,
  `entity_type` enum('TASK','REWARD') NOT NULL,
  `entity_id` int(11) NOT NULL,
  `changed_by_user_id` int(11) NOT NULL,
  `change_operation` text DEFAULT NULL,
  `field_values_json_old` text DEFAULT NULL,
  `field_values_json_new` text DEFAULT NULL,
  `user_remarks` varchar(255) DEFAULT NULL,
  `system_remarks` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `task_review_allowed` (
  `task_id` int(11) NOT NULL,
  `allowed_to_type` enum('ADMIN','PARENTS_OR_FOSTER_AGENT','USERS') NOT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `task_images` (
  `task_image_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `image_name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `users_address` (
  `users_address_id` int(11) NOT NULL,
  `address_line_1` text DEFAULT NULL,
  `address_line_2` text DEFAULT NULL,
  `address_line_3` text DEFAULT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `zipcode` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `product_pending_wishlist` (
  `product_wished_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `product_pending_wishlist_images` (
  `image_id` int(11) NOT NULL,
  `product_wished_id` int(11) NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `task_settings` (
  `task_settings_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `visible_to` enum('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD','ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD','SELECTED_PARENT') NOT NULL,
  `review_required` tinyint(1) NOT NULL DEFAULT 1,
  `repeate_after_days` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `reward_settings` (
  `reward_settings_id` int(11) NOT NULL,
  `reward_id` int(11) NOT NULL,
  `visible_to` enum('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD','ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD','SELECTED_PARENT') NOT NULL,
  `requirement_for_claim` enum('SP','TASK','SP_OR_TASK','SP_AND_TASK') NOT NULL,
  `repeate_after_days` int(11) NOT NULL,
  `max_claim_allowed_total` int(11) NOT NULL,
  `max_claim_allowed_per_user` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `tasks` ADD `is_featured` BOOLEAN NOT NULL DEFAULT FALSE AFTER `task_status`;

ALTER TABLE `tasks` CHANGE `group_id` `group_id` INT(11) NULL DEFAULT NULL;

ALTER TABLE `tasks` ADD `parent_task_id` INT NULL DEFAULT NULL AFTER `task_id`;

ALTER TABLE `products` ADD `app_share_link` VARCHAR(255) NULL DEFAULT NULL AFTER `status`, ADD `android_share_link` VARCHAR(255) NULL DEFAULT NULL AFTER `app_share_link`, ADD `ios_share_link` VARCHAR(255) NULL DEFAULT NULL AFTER `android_share_link`;


ALTER TABLE `task_visibility`
  ADD PRIMARY KEY (`task_visibility_id`);

ALTER TABLE `task_visibility`
  MODIFY `task_visibility_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `permission_user_entity`
  ADD PRIMARY KEY (`permission_id`);

ALTER TABLE `permission_user_entity`
  MODIFY `permission_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `permission_team_entity`
  ADD PRIMARY KEY (`permission_id`);

ALTER TABLE `permission_team_entity`
  MODIFY `permission_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `reward_visibility`
  ADD PRIMARY KEY (`reward_visibility_id`);

ALTER TABLE `reward_visibility`
  MODIFY `reward_visibility_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `change_log`
  ADD PRIMARY KEY (`change_log_id`);

ALTER TABLE `change_log`
  MODIFY `change_log_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `task_review_allowed`
  ADD PRIMARY KEY (`task_id`);

ALTER TABLE `task_review_allowed`
  MODIFY `task_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `task_images`
  ADD PRIMARY KEY (`task_image_id`);

ALTER TABLE `task_images`
  MODIFY `task_image_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `users_address`
  ADD PRIMARY KEY (`users_address_id`);

ALTER TABLE `users_address`
  MODIFY `users_address_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `product_pending_wishlist`
  ADD PRIMARY KEY (`product_wished_id`);

ALTER TABLE `product_pending_wishlist`
  MODIFY `product_wished_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `product_pending_wishlist_images`
  ADD PRIMARY KEY (`image_id`);

ALTER TABLE `product_pending_wishlist_images`
  MODIFY `image_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `task_settings`
  ADD PRIMARY KEY (`task_settings_id`);

ALTER TABLE `task_settings`
  MODIFY `task_settings_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

ALTER TABLE `reward_settings`
  ADD PRIMARY KEY (`reward_settings_id`);

ALTER TABLE `reward_settings`
  MODIFY `reward_settings_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

DROP TABLE `permission_user_entity`;

CREATE TABLE `permission_user_entity` (
  `id` int NOT NULL AUTO_INCREMENT,
  `permission_id` int NOT NULL,
  `user_id` int NOT NULL,
  `team_id` int NOT NULL,
  `entity_type` enum('TASK','REWARD') NOT NULL,
  `entity_id` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE task_review_allowed;

ALTER TABLE task_settings ADD COLUMN task_review_allowed_to_type enum('ADMIN','PARENTS_OR_FOSTER_AGENT','USER') AFTER repeate_after_days;

ALTER TABLE reward_settings ADD COLUMN visible_in_family_store BOOLEAN DEFAULT TRUE AFTER visible_to;

ALTER TABLE product_images ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

CREATE TABLE `all_claims` (
  `claim_id` bigint NOT NULL AUTO_INCREMENT,
  `claim_entity_type` enum('TASK','PRODUCT','TASK_AND_PRODUCT') DEFAULT NULL,
  `task_id` bigint DEFAULT NULL,
  `product_id` bigint DEFAULT NULL,
  `task_assigned_id` bigint DEFAULT NULL,
  `product_claimed_id` bigint DEFAULT NULL,
  `store_id` bigint DEFAULT NULL,
  `group_id` bigint DEFAULT NULL,
  `claimed_by_user_id` bigint DEFAULT NULL,
  `status` enum('PENDING','PENDING_APPROVAL','APPROVED','REJECTED','REDEEMED') DEFAULT NULL,
  `reviewed_by_user_id` bigint DEFAULT NULL,
  `updated_by_id` bigint DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`claim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE users_address ADD COLUMN user_id INT AFTER users_address_id;

ALTER TABLE users_address MODIFY COLUMN  `city` varchar(255) NULL, MODIFY COLUMN `state` varchar(255) NULL, MODIFY COLUMN `zipcode` varchar(255) NULL;

ALTER TABLE products ADD COLUMN parent_product_id INT DEFAULT NULL NULL AFTER product_id;

ALTER TABLE group_members ADD COLUMN role_id INT DEFAULT 4 AFTER email;

INSERT INTO roles (name, display_name, description) VALUES('OWNER', 'Owner', 'Owner of the group');

ALTER TABLE store_product ADD COLUMN `deleted_at` datetime DEFAULT NULL;

ALTER TABLE task_assigned ADD COLUMN `group_id` int DEFAULT NULL AFTER task_id;

ALTER TABLE task_assigned ADD COLUMN `deleted_at` datetime DEFAULT NULL;

UPDATE task_assigned AS ta JOIN tasks AS t ON t.task_id = ta.task_id SET ta.group_id = t.group_id WHERE t.created_by IS NOT NULL;



------------------------------ Added By Chandrakant (23-03-24) ---------------------------------

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