-- 分类表 (支持多系统/模块使用，支持多级分类，使用 alias 字段)
CREATE TABLE categories (
    -- 分类的唯一标识符，全局唯一
    id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::varchar,

    -- 标识分类所属的系统、模块或服务
    system_id TEXT NOT NULL,

    -- 分类名称
    name TEXT NOT NULL,

    -- 用于 URL 或程序内部识别的别名
    alias TEXT,

    -- 父级分类的 ID
    -- 如果是顶级分类，此字段为 NULL
    parent_id VARCHAR(36) REFERENCES categories(id) ON DELETE SET NULL,

    -- 可选字段：分类的描述
    description TEXT,

    -- 记录创建时间
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),

    -- 记录最后更新时间（可选）
    updated_at TIMESTAMP WITH TIME ZONE,

    -- 复合唯一约束：确保在同一个 system_id + parent_id 下，分类名称不重复
    -- 这允许不同父级下的同名子分类，也允许不同系统下的同名顶级分类 (parent_id IS NULL)
    CONSTRAINT uix_categories_system_parent_name UNIQUE (system_id, parent_id, name),

    -- 复合唯一约束：确保在同一个 system_id + parent_id 下，分类 alias 不重复 (如果使用 alias 字段)
    CONSTRAINT uix_categories_system_parent_alias UNIQUE (system_id, parent_id, alias),

    -- 外键约束：parent_id 引用回 categories 表自身的 id
    CONSTRAINT fk_categories_parent
        FOREIGN KEY (parent_id)
        REFERENCES categories (id)
        ON DELETE CASCADE -- 或 ON DELETE SET NULL，根据业务决定删除父分类时如何处理子分类
        -- ON DELETE CASCADE: 删除父分类时，所有子分类也会被删除
        -- ON DELETE SET NULL: 删除父分类时，所有子分类的 parent_id 设为 NULL，使其成为顶级分类
);

-- 为 system_id, parent_id, name 添加索引，用于按系统、父级和名称快速查找分类
CREATE INDEX idx_categories_system_parent_name ON categories (system_id, parent_id, name);

-- 如果经常按 system_id, parent_id 和 alias 查询分类
CREATE INDEX idx_categories_system_parent_alias ON categories (system_id, parent_id, alias);

-- 为 parent_id 添加索引，用于查找某个父级下的所有子分类
CREATE INDEX idx_categories_parent_id ON categories (parent_id);

-- 如果经常需要查找某个系统下的所有分类 (包括顶级和子级)
CREATE INDEX idx_categories_system_id ON categories (system_id);

-- 原有的 system_id + name/alias 索引已包含在新的复合索引中，通常不需要单独创建
-- CREATE INDEX idx_categories_system_id_name ON categories (system_id, name);
-- CREATE INDEX idx_categories_system_id_alias ON categories (system_id, alias);