-- Neo4j 图数据导出 - 完整版（含用户和视频详情）
-- 节点: User(mid), Video(vid), Share(shareid)
-- 关系: CREATED(User→Share), OF(Share→Video), CLICKED(User→Share)
-- 使用 fetch_daily.py 按天获取
--
-- 字段说明：
--   action_type: share=分享事件, click=点击事件
--   actor_mid: 执行动作的用户（share时是分享者，click时是点击者）
--   sharer_mid: 创建 shareid 的用户（从 shareid 提取）

WITH user_info AS (
    SELECT uid, mid, nick_name, gender, country, province, city
    FROM videoods.dim_user
    LATERAL VIEW explode(split(mids, ',')) tmp AS mid
),
share_log AS (
    SELECT
        topic as action_type,
        shareid,
        rootshareid,
        sharedepth,
        CASE
            WHEN topic = 'share' THEN shareobjectid
            WHEN topic = 'click' THEN clickobjectid
        END as vid,
        machinecode as actor_mid,
        split(shareid, '-')[0] as sharer_mid,
        clienttimestamp as action_ts
    FROM loghubods.user_share_log
    WHERE dt = '${dt}'
        AND topic IN ('share', 'click')
        AND shareid IS NOT NULL
)
SELECT
    t.action_type,
    t.shareid,
    t.rootshareid,
    t.sharedepth,
    t.vid,
    t.actor_mid,
    t.sharer_mid,
    t.action_ts,
    -- 动作执行者详情（share时=分享者，click时=点击者）
    actor.uid as actor_uid,
    actor.nick_name as actor_nick,
    actor.gender as actor_gender,
    actor.country as actor_country,
    actor.province as actor_province,
    actor.city as actor_city,
    -- 分享者详情（click时有意义，share时与actor相同）
    sharer.uid as sharer_uid,
    sharer.nick_name as sharer_nick,
    sharer.gender as sharer_gender,
    sharer.country as sharer_country,
    sharer.province as sharer_province,
    sharer.city as sharer_city,
    -- 视频详情
    v.title as video_title,
    v.cover_img_path as video_cover
FROM share_log t
LEFT JOIN user_info actor ON t.actor_mid = actor.mid
LEFT JOIN user_info sharer ON t.sharer_mid = sharer.mid
LEFT JOIN videoods.wx_video v ON t.vid = CAST(v.id AS STRING)
