-- Staff TODO System Database Setup
-- Run this in phpMyAdmin to create the TODO system tables

-- Create todo_categories table
CREATE TABLE IF NOT EXISTS `todo_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `color` varchar(7) DEFAULT '#1976d2',
  `allowed_roles` JSON DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alternative: If table already exists, just add the allowed_roles column
-- ALTER TABLE `todo_categories` ADD COLUMN `allowed_roles` JSON DEFAULT NULL;

-- Insert default categories
INSERT INTO `todo_categories` (`name`, `description`, `color`) VALUES
('General', 'General tasks and activities', '#1976d2'),
('Bug Fix', 'Bug fixes and technical issues', '#d32f2f'),
('Feature', 'New feature development', '#388e3c'),
('Documentation', 'Documentation and guides', '#f57c00'),
('Testing', 'Testing and quality assurance', '#7b1fa2');

-- Create todo_items table
CREATE TABLE IF NOT EXISTS `todo_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `category_id` int(11) NOT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `status` enum('pending','in_progress','completed','cancelled') DEFAULT 'pending',
  `assigned_role` varchar(50),
  `assigned_user` varchar(100),
  `assigned_user_id` int(11),
  `created_by` varchar(100) NOT NULL,
  `due_date` datetime,
  `completed_at` datetime,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `assigned_user_id` (`assigned_user_id`),
  KEY `status` (`status`),
  KEY `priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create todo_comments table
CREATE TABLE IF NOT EXISTS `todo_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `todo_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `comment` text NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `todo_id` (`todo_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
