-- =====================================================================================
-- 商城分析模块增量升级脚本
-- 包含: 埋点事件表、分析用 RPC 函数
-- =====================================================================================

-- =====================================================================================
-- 1. 创建用户行为事件表 (埋点)
-- =====================================================================================

CREATE TABLE IF NOT EXISTS public.analytics_events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_type VARCHAR(50) NOT NULL,  -- e.g., page_view, product_view, add_to_cart, checkout_start, payment_success
    user_id UUID REFERENCES public.ak_users(id) ON DELETE SET NULL,
    session_id VARCHAR(100),
    device_id VARCHAR(100),
    page_url TEXT,
    referrer TEXT,
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    ip_address INET,
    user_agent TEXT,
    screen_resolution VARCHAR(20),
    device_type VARCHAR(50),
    os VARCHAR(50),
    browser VARCHAR(50),
    country VARCHAR(50),
    region VARCHAR(50),
    city VARCHAR(50),
    event_props JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE public.analytics_events IS '用户行为事件（埋点）表';
COMMENT ON COLUMN public.analytics_events.event_type IS '事件类型，如 page_view, product_view, add_to_cart 等';

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_analytics_events_user_id ON public.analytics_events(user_id);
CREATE INDEX IF NOT EXISTS idx_analytics_events_event_type ON public.analytics_events(event_type);
CREATE INDEX IF NOT EXISTS idx_analytics_events_created_at ON public.analytics_events(created_at);
CREATE INDEX IF NOT EXISTS idx_analytics_events_session_id ON public.analytics_events(session_id);


-- =====================================================================================
-- 2. 创建分析所需的 RPC 函数
-- =====================================================================================

-- -------------------------------------------------------------------------------------
-- 函数: rpc_analytics_user_kpis
-- 描述: 获取用户分析核心KPI指标
-- -------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.rpc_analytics_user_kpis(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    total_users BIGINT,
    user_growth FLOAT,
    new_users BIGINT,
    new_user_growth FLOAT,
    active_users BIGINT,
    active_growth FLOAT,
    ordering_users BIGINT,
    ordering_growth FLOAT,
    paid_users BIGINT,
    paid_growth FLOAT,
    new_user_conversion_rate FLOAT,
    repurchase_rate FLOAT,
    repurchase_growth FLOAT
)
LANGUAGE plpgsql
AS $$
DECLARE
    prev_start_date DATE;
    prev_end_date DATE;
    period_days INT;
BEGIN
    -- 计算上一周期日期范围
    period_days := p_end_date - p_start_date + 1;
    prev_start_date := p_start_date - period_days;
    prev_end_date := p_start_date - 1;

    RETURN QUERY
    WITH
    -- 当前周期数据
    current_period AS (
        SELECT
            (SELECT COUNT(*) FROM public.ak_users WHERE created_at <= p_end_date::timestamp) AS total_users,
            COUNT(DISTINCT CASE WHEN u.created_at::DATE BETWEEN p_start_date AND p_end_date THEN u.id END)::BIGINT AS new_users,
            COUNT(DISTINCT e.user_id)::BIGINT AS active_users,
            COUNT(DISTINCT o.user_id)::BIGINT AS ordering_users,
            COUNT(DISTINCT CASE WHEN o.payment_status = 2 THEN o.user_id END)::BIGINT AS paid_users,
            (
                SELECT COUNT(DISTINCT user_id)
                FROM public.ml_orders
                WHERE created_at::DATE BETWEEN p_start_date AND p_end_date
                AND user_id IN (SELECT id FROM public.ak_users WHERE created_at::DATE BETWEEN p_start_date AND p_end_date)
            ) * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN u.created_at::DATE BETWEEN p_start_date AND p_end_date THEN u.id END), 0) AS new_user_conversion_rate,
            COUNT(DISTINCT CASE WHEN user_orders.order_count > 1 THEN user_orders.user_id END) * 100.0 / NULLIF(COUNT(DISTINCT user_orders.user_id), 0) AS repurchase_rate
        FROM public.ak_users u
        LEFT JOIN public.analytics_events e ON e.user_id = u.id AND e.created_at::DATE BETWEEN p_start_date AND p_end_date
        LEFT JOIN public.ml_orders o ON o.user_id = u.id AND o.created_at::DATE BETWEEN p_start_date AND p_end_date
        LEFT JOIN (
            SELECT user_id, COUNT(*) as order_count
            FROM public.ml_orders
            WHERE created_at::DATE <= p_end_date AND payment_status = 2
            GROUP BY user_id
        ) user_orders ON user_orders.user_id = o.user_id AND o.payment_status = 2
    ),
    -- 上一周期数据
    previous_period AS (
        SELECT
            (SELECT COUNT(*) FROM public.ak_users WHERE created_at <= prev_end_date::timestamp) AS total_users,
            COUNT(DISTINCT CASE WHEN u.created_at::DATE BETWEEN prev_start_date AND prev_end_date THEN u.id END)::BIGINT AS new_users,
            COUNT(DISTINCT e.user_id)::BIGINT AS active_users,
            COUNT(DISTINCT o.user_id)::BIGINT AS ordering_users,
            COUNT(DISTINCT CASE WHEN o.payment_status = 2 THEN o.user_id END)::BIGINT AS paid_users,
            COUNT(DISTINCT CASE WHEN user_orders.order_count > 1 THEN user_orders.user_id END) * 100.0 / NULLIF(COUNT(DISTINCT user_orders.user_id), 0) AS repurchase_rate
        FROM public.ak_users u
        LEFT JOIN public.analytics_events e ON e.user_id = u.id AND e.created_at::DATE BETWEEN prev_start_date AND prev_end_date
        LEFT JOIN public.ml_orders o ON o.user_id = u.id AND o.created_at::DATE BETWEEN prev_start_date AND prev_end_date
        LEFT JOIN (
            SELECT user_id, COUNT(*) as order_count
            FROM public.ml_orders
            WHERE created_at::DATE <= prev_end_date AND payment_status = 2
            GROUP BY user_id
        ) user_orders ON user_orders.user_id = o.user_id AND o.payment_status = 2
    )
    SELECT
        cp.total_users,
        ROUND(((cp.total_users - pp.total_users) * 100.0 / NULLIF(pp.total_users, 1))::numeric, 2)::FLOAT AS user_growth,
        cp.new_users,
        ROUND(((cp.new_users - pp.new_users) * 100.0 / NULLIF(pp.new_users, 1))::numeric, 2)::FLOAT AS new_user_growth,
        cp.active_users,
        ROUND(((cp.active_users - pp.active_users) * 100.0 / NULLIF(pp.active_users, 1))::numeric, 2)::FLOAT AS active_growth,
        cp.ordering_users,
        ROUND(((cp.ordering_users - pp.ordering_users) * 100.0 / NULLIF(pp.ordering_users, 1))::numeric, 2)::FLOAT AS ordering_growth,
        cp.paid_users,
        ROUND(((cp.paid_users - pp.paid_users) * 100.0 / NULLIF(pp.paid_users, 1))::numeric, 2)::FLOAT AS paid_growth,
        ROUND(cp.new_user_conversion_rate::numeric, 2)::FLOAT,
        ROUND(cp.repurchase_rate::numeric, 2)::FLOAT AS repurchase_rate,
        ROUND((cp.repurchase_rate - pp.repurchase_rate)::numeric, 2)::FLOAT AS repurchase_growth
    FROM current_period cp, previous_period pp;
END;
$$;

-- -------------------------------------------------------------------------------------
-- 函数: rpc_analytics_user_growth_trend
-- 描述: 获取用户增长趋势（日维度）
-- -------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.rpc_analytics_user_growth_trend(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    date DATE,
    new_users BIGINT,
    active_users BIGINT
)
LANGUAGE sql
AS $$
    WITH date_series AS (
        SELECT generate_series(p_start_date, p_end_date, '1 day'::interval)::DATE AS date
    )
    SELECT
        ds.date,
        COUNT(DISTINCT CASE WHEN u.created_at::DATE = ds.date THEN u.id END)::BIGINT AS new_users,
        COUNT(DISTINCT e.user_id)::BIGINT AS active_users
    FROM date_series ds
    LEFT JOIN public.ak_users u ON u.created_at::DATE = ds.date
    LEFT JOIN public.analytics_events e ON e.created_at::DATE = ds.date
    GROUP BY ds.date
    ORDER BY ds.date;
$$;

-- -------------------------------------------------------------------------------------
-- 函数: rpc_analytics_user_segments
-- 描述: 获取用户分群（基于新老客、复购、回流的简单模型）
-- -------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.rpc_analytics_user_segments(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    name TEXT,
    value BIGINT
)
LANGUAGE sql
AS $$
    WITH user_orders AS (
        SELECT
            user_id,
            MIN(created_at) as first_order_time,
            COUNT(id) as total_orders
        FROM public.ml_orders
        WHERE payment_status = 2
        GROUP BY user_id
    )
    SELECT
        segment as name,
        COUNT(user_id) as value
    FROM (
        SELECT
            u.id as user_id,
            CASE
                WHEN uo.first_order_time::DATE BETWEEN p_start_date AND p_end_date THEN '新客'
                WHEN uo.total_orders > 1 AND EXISTS (SELECT 1 FROM public.ml_orders WHERE user_id = u.id AND created_at::DATE BETWEEN p_start_date AND p_end_date) THEN '复购客户'
                WHEN uo.total_orders >= 1 AND EXISTS (SELECT 1 FROM public.ml_orders WHERE user_id = u.id AND created_at::DATE BETWEEN p_start_date AND p_end_date) THEN '回流客户'
                ELSE '老客'
            END as segment
        FROM public.ak_users u
        JOIN user_orders uo ON u.id = uo.user_id
    ) segments
    GROUP BY segment;
$$;

-- -------------------------------------------------------------------------------------
-- 函数: rpc_analytics_traffic_sources
-- 描述: 获取流量来源分布
-- -------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.rpc_analytics_traffic_sources(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    name TEXT,
    value BIGINT
)
LANGUAGE sql
AS $$
    SELECT
        COALESCE(
            NULLIF(utm_source, ''),
            CASE
                WHEN referrer IS NULL OR referrer = '' THEN '直接访问'
                WHEN referrer ILIKE '%baidu.com%' THEN '百度搜索'
                WHEN referrer ILIKE '%google.com%' THEN '谷歌搜索'
                WHEN referrer ILIKE '%bing.com%' THEN '必应搜索'
                WHEN referrer ILIKE '%sogou.com%' THEN '搜狗搜索'
                WHEN referrer ILIKE '%toutiao.com%' OR referrer ILIKE '%douyin.com%' THEN '字节系'
                WHEN referrer ILIKE '%weixin.qq.com%' THEN '微信'
                ELSE '其他推荐'
            END
        ) AS name,
        COUNT(DISTINCT session_id)::BIGINT AS value
    FROM public.analytics_events
    WHERE
        created_at::DATE BETWEEN p_start_date AND p_end_date
        AND event_type = 'page_view'
    GROUP BY name
    ORDER BY value DESC;
$$;

-- =====================================================================================
-- 3. 完成提示
-- =====================================================================================

DO $$
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '商城分析模块增量升级完成！';
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '✓ 埋点事件表(analytics_events)创建完成';
    RAISE NOTICE '✓ RPC 函数创建完成:';
    RAISE NOTICE '  - rpc_analytics_user_kpis';
    RAISE NOTICE '  - rpc_analytics_user_growth_trend';
    RAISE NOTICE '  - rpc_analytics_user_segments';
    RAISE NOTICE '  - rpc_analytics_traffic_sources';
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '下一步:';
    RAISE NOTICE '1. 在应用中集成前端埋点，将数据写入 analytics_events 表';
    RAISE NOTICE '2. 在前端分析页面调用以上 RPC 函数获取数据';
    RAISE NOTICE '=======================================================';
END $$;