-- Migration: Add task categories table
-- Date: 2025-01-XX
-- Description: Creates task_categories table for company-specific task categories and adds category column to project_tasks

-- ===== Task Categories =====
CREATE TABLE IF NOT EXISTS task_categories (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    color VARCHAR(7) DEFAULT '#667eea',
    order_index INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company (company_id),
    INDEX idx_company_order (company_id, order_index),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Add category column to project_tasks =====
-- Check if column exists first, if not add it
ALTER TABLE project_tasks 
ADD COLUMN category VARCHAR(255) DEFAULT NULL AFTER notes;

-- Add index for category
ALTER TABLE project_tasks 
ADD INDEX idx_category (category);

