-- Migration: Create project_todos table
-- Date: 2025-01-XX
-- Description: Creates table for project todos (subtasks) linked to project_tasks

CREATE TABLE IF NOT EXISTS project_todos (
    id VARCHAR(36) PRIMARY KEY,
    project_task_id VARCHAR(36) NOT NULL,
    todo_name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'completed') DEFAULT 'pending',
    order_index INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    INDEX idx_project_task (project_task_id),
    INDEX idx_status (status),
    INDEX idx_order (project_task_id, order_index)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

