-- Alternative SQL without explicit collation (will use database default)
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;

-- Insert some sample data (optional)
-- Note: Make sure user ID 1 exists in your user table before running this
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');
