-- Add Todo table to NBRP AEGIS database
-- Run this SQL in your MySQL database management tool (phpMyAdmin, MySQL Workbench, etc.)

-- Create todo table
CREATE TABLE `todo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `status` enum('PENDING','IN_PROGRESS','COMPLETED') NOT NULL DEFAULT 'PENDING',
  `priority` enum('LOW','MEDIUM','HIGH','URGENT') NOT NULL DEFAULT 'MEDIUM',
  `createdBy` int DEFAULT NULL,
  `assignedTo` int DEFAULT NULL,
  `dueDate` datetime DEFAULT NULL,
  `completedAt` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_todo_created_by` (`createdBy`),
  KEY `idx_todo_assigned_to` (`assignedTo`),
  KEY `idx_todo_status` (`status`),
  KEY `idx_todo_priority` (`priority`),
  KEY `idx_todo_due_date` (`dueDate`),
  KEY `idx_todo_created_at` (`createdAt`),
  CONSTRAINT `todo_createdBy_fkey` FOREIGN KEY (`createdBy`) REFERENCES `user` (`id`) ON DELETE SET NULL,
  CONSTRAINT `todo_assignedTo_fkey` FOREIGN KEY (`assignedTo`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert some sample data (optional)
-- First, let's check what users exist and then insert sample data
-- Run this query first to see available user IDs: SELECT id, firstName, lastName FROM user LIMIT 5;

-- Sample data - UPDATE THE USER IDs BELOW TO MATCH YOUR ACTUAL USER IDs
-- Comment out or modify these lines based on your actual user IDs
/*
INSERT INTO `todo` (`title`, `description`, `status`, `priority`, `createdBy`, `assignedTo`, `dueDate`) VALUES
('Test Todo', 'Dette er en test todo-oppgave', 'PENDING', 'MEDIUM', 1, NULL, '2025-08-30 12:00:00'),
('Høy prioritet oppgave', 'En viktig oppgave som må gjøres snart', 'IN_PROGRESS', 'HIGH', 1, 1, '2025-08-26 09:00:00');
*/

-- Additional SQL to handle existing todos if you need to modify an existing todo table:
-- (Only run this if you already have a todo table and need to update it)
/*
ALTER TABLE `todo` 
MODIFY COLUMN `createdBy` int DEFAULT NULL,
DROP FOREIGN KEY `todo_createdBy_fkey`,
ADD CONSTRAINT `todo_createdBy_fkey` FOREIGN KEY (`createdBy`) REFERENCES `user` (`id`) ON DELETE SET NULL;
*/
