catalog/upload/task_manager.sql

Вот содержимое файла catalog/upload/task_manager.sql:

-- ================================================================
-- SQL script for creating the necessary database tables for Task Manager module
-- ================================================================

-- Create table for tasks
CREATE TABLE `oc_task_manager` (
  `task_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `group_id` INT(11) DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `priority` INT(11) NOT NULL DEFAULT 1,  -- 1: Low, 2: Medium, 3: High
  `status` INT(1) NOT NULL DEFAULT 0,    -- 0: Pending, 1: In Progress, 2: Completed
  `due_date` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for task categories
CREATE TABLE `oc_task_categories` (
  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for task tags
CREATE TABLE `oc_task_tags` (
  `tag_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table to link tasks and categories
CREATE TABLE `oc_task_task_category` (
  `task_id` INT(11) NOT NULL,
  `category_id` INT(11) NOT NULL,
  PRIMARY KEY (`task_id`, `category_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `oc_task_categories` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table to link tasks and tags
CREATE TABLE `oc_task_task_tag` (
  `task_id` INT(11) NOT NULL,
  `tag_id` INT(11) NOT NULL,
  PRIMARY KEY (`task_id`, `tag_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE,
  FOREIGN KEY (`tag_id`) REFERENCES `oc_task_tags` (`tag_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for task logs
CREATE TABLE `oc_task_logs` (
  `log_id` INT(11) NOT NULL AUTO_INCREMENT,
  `task_id` INT(11) NOT NULL,
  `message` TEXT,
  `status` INT(1) NOT NULL, -- 1: Success, 0: Failure
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`log_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for notifications
CREATE TABLE `oc_task_notifications` (
  `notification_id` INT(11) NOT NULL AUTO_INCREMENT,
  `task_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `message` TEXT NOT NULL,
  `status` INT(1) NOT NULL, -- 0: Unread, 1: Read
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`notification_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `oc_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for task comments
CREATE TABLE `oc_task_comments` (
  `comment_id` INT(11) NOT NULL AUTO_INCREMENT,
  `task_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `comment` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`comment_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `oc_user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for task status history
CREATE TABLE `oc_task_status_history` (
  `history_id` INT(11) NOT NULL AUTO_INCREMENT,
  `task_id` INT(11) NOT NULL,
  `old_status` INT(1) NOT NULL, -- 0: Pending, 1: In Progress, 2: Completed
  `new_status` INT(1) NOT NULL, -- 0: Pending, 1: In Progress, 2: Completed
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`history_id`),
  FOREIGN KEY (`task_id`) REFERENCES `oc_task_manager` (`task_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create table for cron job management
CREATE TABLE `oc_task_cron_jobs` (
  `cron_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `status` INT(1) NOT NULL, -- 0: Disabled, 1: Enabled
  `interval` INT(11) NOT NULL, -- Cron interval in minutes
  `last_run` DATETIME DEFAULT NULL,
  `next_run` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`cron_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert default task categories
INSERT INTO `oc_task_categories` (`name`, `created_at`) VALUES
('General', NOW()),
('Urgent', NOW()),
('Long Term', NOW());

-- Insert default task tags
INSERT INTO `oc_task_tags` (`name`, `created_at`) VALUES
('High Priority', NOW()),
('Medium Priority', NOW()),
('Low Priority', NOW());

-- ================================================================
-- End of SQL script
-- ================================================================

Объяснение структуры базы данных:

1. oc_task_manager: Хранит основные данные о задачах (задачи, описание, приоритет, статус и т.д.).

2. oc_task_categories: Категории задач (например, "Общие", "Срочные", "Долгосрочные").

3. oc_task_tags: Теги для задач (например, "Высокий приоритет", "Средний приоритет").

4. oc_task_task_category: Связь задач с категориями.

5. oc_task_task_tag: Связь задач с тегами.

6. oc_task_logs: Логирование статуса выполнения задач (успех или ошибка).

7. oc_task_notifications: Уведомления для пользователей о задачах.

8. oc_task_comments: Комментарии к задачам.

9. oc_task_status_history: История изменений статуса задачи.

10. oc_task_cron_jobs: Хранит информацию о CRON задачах для автоматизации.

Также в базе данных создаются некоторые начальные данные, такие как категории и теги задач.

Это базовый скрипт, который позволит создать таблицы для работы модуля "Менеджер задач".