-- 创建互动记录表
-- 如果表已存在则先删除
DROP TABLE IF EXISTS public.ak_interactions CASCADE;

-- 互动记录表
CREATE TABLE public.ak_interactions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 互动ID
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 用户ID (发起互动的用户)
    target_type VARCHAR(32) NOT NULL, -- 目标类型：training_record, assignment_submission, teaching_resource等
    target_id uuid NOT NULL, -- 目标ID (被互动的对象ID)
    interaction_type VARCHAR(32) NOT NULL, -- 互动类型：comment(评论), like(点赞), share(分享)等
    content TEXT, -- 互动内容 (如评论内容，点赞时可为空)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
);

-- 添加表注释
COMMENT ON TABLE public.ak_interactions IS '互动记录表';
COMMENT ON COLUMN public.ak_interactions.target_type IS '目标类型：training_record(训练记录), assignment_submission(作业提交), teaching_resource(教学资源)等';
COMMENT ON COLUMN public.ak_interactions.interaction_type IS '互动类型：comment(评论), like(点赞), share(分享)等';

-- 创建索引
CREATE INDEX idx_interactions_user_id ON public.ak_interactions(user_id);
CREATE INDEX idx_interactions_target ON public.ak_interactions(target_type, target_id);
CREATE INDEX idx_interactions_type ON public.ak_interactions(interaction_type);

-- 验证表是否创建成功
SELECT 'ak_interactions table created successfully' as result;