-- Migration: Add help_commands table
-- Date: 2025-10-10
-- Description: Creates table for Discord bot custom help system

-- Create help_commands table
CREATE TABLE IF NOT EXISTS help_commands (
    id SERIAL PRIMARY KEY,
    name VARCHAR(32) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(50),
    created_by_discord_id TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    last_modified_by TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    view_count INTEGER DEFAULT 0,
    display_order INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_help_commands_name
ON help_commands(name);

CREATE INDEX IF NOT EXISTS idx_help_commands_category
ON help_commands(category);

CREATE INDEX IF NOT EXISTS idx_help_commands_is_active
ON help_commands(is_active);

CREATE INDEX IF NOT EXISTS idx_help_commands_display_order
ON help_commands(display_order);