RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / sqls /
Filename : v3_1_attendence_changes.sql
back
CREATE TABLE `attendances` (
    `attendance_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT NOT NULL,
    `group_id` BIGINT NULL,
    `date` DATE NOT NULL,
    `present` BOOLEAN NOT NULL,
    `holiday` BOOLEAN NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME
);

CREATE TABLE `calendars` (
    `calendar_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(250) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `group_id` BIGINT NULL,
    `is_default` BOOLEAN NOT NULL DEFAULT FALSE,
    `created_at` DATETIME NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME
);

CREATE TABLE `holidays` (
    `holiday_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `calendar_id` BIGINT  NOT NULL,
    `date` DATE NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME
);

-- Create table for broadcast messages
CREATE TABLE `broadcast_messages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `type` ENUM('SCHEDULED_MAINTENANCE', 'NOTIFICATION'),
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create table for broadcast messages
CREATE TABLE `user_additional_info` (
    `user_additional_info_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` VARCHAR(255) NOT NULL,
    `last_android_brodcast_time` DATETIME DEFAULT NULL,
    `last_ios_brodcast_time` DATETIME DEFAULT NULL,
    `created_at` DATETIME,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE users ADD COLUMN preferred_college_id INT DEFAULT NULL AFTER import_queue_item_id;
ALTER TABLE products ADD COLUMN task_id INT DEFAULT NULL AFTER product_type;

SELECT p.product_id AS p_product_id, p.task_id AS p_task_id, t.product_id AS t_product_id, t.task_id AS t_task_id FROM `products` AS p
JOIN tasks AS t
ON t.product_id = p.product_id
WHERE t.task_id IS NOT NULL AND p.task_id IS NULL;

UPDATE `products` AS p
JOIN tasks AS t
ON t.product_id = p.product_id
SET p.task_id = t.task_id
WHERE t.task_id IS NOT NULL AND p.task_id IS NULL;

ALTER TABLE `task_settings` CHANGE `visible_to` `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') NULL;
ALTER TABLE `attendances` ADD COLUMN `attendance_updated_by_type` ENUM('PARENT', 'CHILD', 'ADMIN') DEFAULT 'PARENT' AFTER holiday, ADD COLUMN `attendance_updated_by_id` INT DEFAULT NULL AFTER holiday;