ALTER DATABASE `stackscom` COLLATE utf8mb4_general_ci;
USE `stackscom`;
ALTER TABLE `stacktasks` ADD COLUMN `done_at` datetime DEFAULT NULL AFTER `done`, ADD COLUMN `as_template` tinyint NOT NULL DEFAULT '0', ADD COLUMN `due_date` datetime DEFAULT NULL, ADD COLUMN `estimation_minutes` int unsigned DEFAULT NULL, ADD COLUMN `deleted` tinyint(1) NOT NULL DEFAULT '0', ADD COLUMN `workspace_id` int DEFAULT NULL, ADD KEY `idx_deleted` (`deleted`), ADD KEY `fk_stacktasks_workspace` (`workspace_id`);
CREATE TABLE `sliders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `slider_from` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `slider_to` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `sorting` int NOT NULL,
  `created_at` datetime NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `workspaces` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sorting` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_workspaces_user` (`user_id`),
  CONSTRAINT `fk_workspaces_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_tag` (`user_id`,`name`),
  CONSTRAINT `fk_tags_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `task_tags` (
  `task_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`task_id`,`tag_id`),
  KEY `idx_tag_id` (`tag_id`),
  CONSTRAINT `fk_tasktags_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tasktags_task` FOREIGN KEY (`task_id`) REFERENCES `stacktasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `stacktasks` ADD CONSTRAINT `fk_stacktasks_workspace` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE SET NULL;
