RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin_new2 / sqls /
Filename : v2_1_data_import_changes.sql
back
CREATE TABLE import_profiles (
    import_profile_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    sample_file_path VARCHAR(250) NULL,
    created_at DATETIME NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME
);

CREATE TABLE import_queue_items (
    import_queue_item_id INT AUTO_INCREMENT PRIMARY KEY,
    import_profile_id INT NOT NULL,
    uploaded_file_name VARCHAR(100) NOT NULL,
    temp_table_name VARCHAR(250) NOT NULL,
    original_file_handle VARCHAR(250) NOT NULL,
    error_file_handle VARCHAR(250),
    current_status ENUM('UPLOADED','QUEUED','VALIDATION_INPROGRESS','VALIDATION_SUCCESS','VALIDATION_FAILURE','IMPORT_INPROGRESS','IMPORT_SUCCESS','IMPORT_FAILURE') NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME,
    started_at DATETIME,
    finished_at DATETIME,
    scheduled_by INT,
    FOREIGN KEY (import_profile_id) REFERENCES import_profiles(import_profile_id)
);

CREATE TABLE import_error_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    import_profile_id INT NOT NULL,
    import_queue_item_id INT NOT NULL,
    error_code VARCHAR(250),
    error_message TEXT,
    error_record_count INT,
    created_at DATETIME NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME,
    FOREIGN KEY (import_profile_id) REFERENCES import_profiles(import_profile_id),
    FOREIGN KEY (import_queue_item_id) REFERENCES import_queue_items(import_queue_item_id)
);

CREATE TABLE import_additional_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    import_profile_id INT NOT NULL,
    import_queue_item_id INT NOT NULL,
    total_records INT,
    validation_failed_records INT,
    validation_passed_records INT,
    import_failed_records INT,
    import_passed_records INT,
    created_at DATETIME NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME,
    file_size_bytes DECIMAL(15,3),
    FOREIGN KEY (import_profile_id) REFERENCES import_profiles(import_profile_id),
    FOREIGN KEY (import_queue_item_id) REFERENCES import_queue_items(import_queue_item_id)
);

ALTER TABLE import_profiles ADD COLUMN uuid CHAR(36) AFTER import_profile_id;

INSERT INTO import_profiles (name, uuid, created_at, updated_at)
VALUES
('Users', UUID(), NOW(), NOW()),
('Groups', UUID(), NOW(), NOW()),
('Tasks', UUID(), NOW(), NOW()),
('Products', UUID(), NOW(), NOW());

CREATE TABLE `files` (
    `file_id` INT AUTO_INCREMENT PRIMARY KEY,
    `file_handle` CHAR(36) NOT NULL,
    `original_file_name` VARCHAR(255) NOT NULL,
    `storage_location` ENUM('local', 's3') NOT NULL,
    `namespace` VARCHAR(100) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME NULL
);

ALTER TABLE `groups`
ADD COLUMN `uuid` VARCHAR(100) NULL UNIQUE AFTER group_id;

UPDATE `groups`
SET uuid = UUID()
WHERE uuid IS NULL;

CREATE TABLE `files` (
  `file_id` int NOT NULL AUTO_INCREMENT,
  `file_handle` char(36) NOT NULL,
  `original_file_name` varchar(255) NOT NULL,
  `mime_type` varchar(255) NOT NULL,
  `file_size_bytes` DECIMAL(15,3) NOT NULL,
  `storage_location` enum('local','s3') NOT NULL,
  `namespace` varchar(100) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`file_id`)
) ENGINE=InnoDB;

ALTER TABLE import_profiles ADD COLUMN sample_file_path VARCHAR(250) NULL AFTER `name`;
UPDATE import_profiles SET sample_file_path = 'user_data.csv' WHERE name = 'Users';


ALTER TABLE users ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER ios_notify_invite_done;
ALTER TABLE users_address ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER zipcode;
ALTER TABLE user_settings ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER last_viewed_activity_history_id;
ALTER TABLE group_members ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER is_active;
ALTER TABLE sp_wallet ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER wallet_uid;
ALTER TABLE tuit_wallet ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER wallet_uid;
ALTER TABLE role_user ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER team_id;

-- ALTER TABLE tuit_wallet CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
UPDATE import_profiles SET sample_file_path = 'group_data.csv' WHERE name = 'Groups';
ALTER TABLE `groups` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER group_image;
ALTER TABLE `teams` ADD COLUMN uuid VARCHAR(100) DEFAULT NULL AFTER id;
ALTER TABLE `teams` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER `description`;
UPDATE `teams`
SET uuid = UUID()
WHERE uuid IS NULL;

ALTER TABLE `tasks` ADD COLUMN uuid VARCHAR(100) DEFAULT NULL AFTER task_id;
UPDATE `tasks`
SET uuid = UUID()
WHERE uuid IS NULL;

ALTER TABLE `tasks` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER is_repeated;
ALTER TABLE `task_images` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER image_name;
ALTER TABLE `task_settings` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER task_review_allowed_to_type;

UPDATE import_profiles SET sample_file_path = 'tasks_data.csv' WHERE name = 'Tasks';

ALTER TABLE `products` ADD COLUMN uuid VARCHAR(100) DEFAULT NULL AFTER product_id;
UPDATE `products`
SET uuid = UUID()
WHERE uuid IS NULL;

ALTER TABLE `products` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER longitude;
ALTER TABLE `product_images` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER image_name;
ALTER TABLE `reward_settings` ADD COLUMN import_queue_item_id INT DEFAULT NULL AFTER max_claim_allowed_per_user;
UPDATE import_profiles SET sample_file_path = 'products_data.csv' WHERE name = 'Products';