-- 商城系统用户兼容性实施方案
-- 基于混合方案：复用 ak_users 主表 + 商城扩展表

-- 1. 商城用户扩展表
CREATE TABLE public.mall_user_profiles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE,
    user_type INTEGER DEFAULT 1, -- 1:消费者 2:商家 3:配送员 4:客服 5:管理员
    status INTEGER DEFAULT 1, -- 1:正常 2:冻结 3:注销 4:待审核
    real_name VARCHAR(64), -- 真实姓名(商家认证、配送员必填)
    id_card VARCHAR(32), -- 身份证号(商家认证、配送员必填)
    credit_score INTEGER DEFAULT 100, -- 信用分数 0-1000
    mall_role VARCHAR(32) DEFAULT 'consumer', -- 商城角色标识
    verification_status INTEGER DEFAULT 0, -- 认证状态 0:未认证 1:已认证 2:认证失败
    verification_data JSONB, -- 认证相关数据
    business_license VARCHAR(128), -- 营业执照号(商家)
    shop_category VARCHAR(64), -- 店铺类别(商家)
    service_areas JSONB, -- 服务区域(配送员)
    emergency_contact VARCHAR(128), -- 紧急联系人(配送员)
    preferences JSONB, -- 用户偏好设置
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

COMMENT ON TABLE public.mall_user_profiles IS '商城用户扩展信息表';
COMMENT ON COLUMN public.mall_user_profiles.user_id IS '关联ak_users表的用户ID';
COMMENT ON COLUMN public.mall_user_profiles.user_type IS '用户类型：1消费者 2商家 3配送员 4客服 5管理员';
COMMENT ON COLUMN public.mall_user_profiles.status IS '用户状态：1正常 2冻结 3注销 4待审核';
COMMENT ON COLUMN public.mall_user_profiles.credit_score IS '信用分数，影响交易权限';
COMMENT ON COLUMN public.mall_user_profiles.verification_status IS '认证状态：0未认证 1已认证 2认证失败';

-- 创建索引
CREATE INDEX idx_mall_user_profiles_user_id ON public.mall_user_profiles(user_id);
CREATE INDEX idx_mall_user_profiles_user_type ON public.mall_user_profiles(user_type);
CREATE INDEX idx_mall_user_profiles_status ON public.mall_user_profiles(status);
CREATE INDEX idx_mall_user_profiles_mall_role ON public.mall_user_profiles(mall_role);

-- 2. 用户地址表
CREATE TABLE public.ak_user_addresses (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    receiver_name VARCHAR(64) NOT NULL, -- 收货人姓名
    receiver_phone VARCHAR(32) NOT NULL, -- 收货人手机
    province VARCHAR(64) NOT NULL, -- 省份
    city VARCHAR(64) NOT NULL, -- 城市
    district VARCHAR(64) NOT NULL, -- 区县
    address_detail TEXT NOT NULL, -- 详细地址
    postal_code VARCHAR(16), -- 邮编
    is_default BOOLEAN DEFAULT false, -- 是否默认地址
    label VARCHAR(32), -- 地址标签：home/office/school/other
    coordinates POINT, -- 经纬度坐标，用于配送距离计算
    delivery_instructions TEXT, -- 配送说明
    business_hours VARCHAR(128), -- 可配送时间(如：9:00-18:00)
    status INTEGER DEFAULT 1, -- 地址状态：1正常 2禁用
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

COMMENT ON TABLE public.ak_user_addresses IS '用户地址表';
COMMENT ON COLUMN public.ak_user_addresses.coordinates IS '经纬度坐标，格式：POINT(longitude latitude)';
COMMENT ON COLUMN public.ak_user_addresses.label IS '地址标签：home家 office公司 school学校 other其他';

-- 创建索引
CREATE INDEX idx_user_addresses_user_id ON public.ak_user_addresses(user_id);
CREATE INDEX idx_user_addresses_city ON public.ak_user_addresses(city);
CREATE INDEX idx_user_addresses_district ON public.ak_user_addresses(district);
CREATE INDEX idx_user_addresses_is_default ON public.ak_user_addresses(is_default);

-- 创建地理位置索引(用于附近配送查询)
CREATE INDEX idx_user_addresses_coordinates ON public.ak_user_addresses USING GIST(coordinates);

-- 3. 用户收藏表
CREATE TABLE public.mall_user_favorites (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    target_type VARCHAR(32) NOT NULL, -- 收藏类型：product/shop
    target_id uuid NOT NULL, -- 目标ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

COMMENT ON TABLE public.mall_user_favorites IS '用户收藏表';
COMMENT ON COLUMN public.mall_user_favorites.target_type IS '收藏类型：product商品 shop店铺';

-- 创建索引和唯一约束
CREATE INDEX idx_mall_user_favorites_user_id ON public.mall_user_favorites(user_id);
CREATE INDEX idx_mall_user_favorites_target ON public.mall_user_favorites(target_type, target_id);
CREATE UNIQUE INDEX idx_mall_user_favorites_unique ON public.mall_user_favorites(user_id, target_type, target_id);

-- 4. 用户搜索历史表
CREATE TABLE public.mall_user_search_history (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    keyword VARCHAR(256) NOT NULL, -- 搜索关键词
    search_count INTEGER DEFAULT 1, -- 搜索次数
    last_search_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

COMMENT ON TABLE public.mall_user_search_history IS '用户搜索历史表';

-- 创建索引
CREATE INDEX idx_mall_search_history_user_id ON public.mall_user_search_history(user_id);
CREATE INDEX idx_mall_search_history_keyword ON public.mall_user_search_history(keyword);
CREATE UNIQUE INDEX idx_mall_search_history_unique ON public.mall_user_search_history(user_id, keyword);

-- 5. 用户浏览历史表
CREATE TABLE public.mall_user_browse_history (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
    product_id uuid NOT NULL, -- 浏览的商品ID
    browse_count INTEGER DEFAULT 1, -- 浏览次数
    browse_duration INTEGER DEFAULT 0, -- 浏览时长(秒)
    last_browse_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

COMMENT ON TABLE public.mall_user_browse_history IS '用户浏览历史表';

-- 创建索引
CREATE INDEX idx_mall_browse_history_user_id ON public.mall_user_browse_history(user_id);
CREATE INDEX idx_mall_browse_history_product_id ON public.mall_user_browse_history(product_id);
CREATE INDEX idx_mall_browse_history_last_browse ON public.mall_user_browse_history(last_browse_at);
CREATE UNIQUE INDEX idx_mall_browse_history_unique ON public.mall_user_browse_history(user_id, product_id);

-- 6. 触发器：确保每个用户只有一个默认地址
CREATE OR REPLACE FUNCTION ensure_single_default_address()
RETURNS TRIGGER AS $$
BEGIN
    -- 如果新插入/更新的地址设为默认
    IF NEW.is_default = true THEN
        -- 将该用户的其他地址的默认状态设为false
        UPDATE public.ak_user_addresses 
        SET is_default = false 
        WHERE user_id = NEW.user_id AND id != NEW.id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_ensure_single_default_address
    BEFORE INSERT OR UPDATE ON public.ak_user_addresses
    FOR EACH ROW
    EXECUTE FUNCTION ensure_single_default_address();

-- 7. 触发器：自动更新 updated_at 字段
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为相关表创建更新时间触发器
CREATE TRIGGER trigger_mall_user_profiles_updated_at
    BEFORE UPDATE ON public.mall_user_profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_user_addresses_updated_at
    BEFORE UPDATE ON public.ak_user_addresses
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 8. 数据迁移：为现有 ak_users 用户创建默认商城档案
INSERT INTO public.mall_user_profiles (user_id, user_type, status, mall_role)
SELECT 
    id,
    1, -- 默认为消费者
    1, -- 默认状态正常
    'consumer' -- 默认角色消费者
FROM public.ak_users
WHERE id NOT IN (SELECT user_id FROM public.mall_user_profiles WHERE user_id IS NOT NULL);

-- 9. 创建视图：商城用户完整信息视图
CREATE VIEW public.mall_users_view AS
SELECT 
    u.id,
    u.username,
    u.email,
    u.phone,
    u.avatar_url,
    u.gender,
    u.birthday,
    u.bio,
    u.created_at as user_created_at,
    u.updated_at as user_updated_at,
    mp.user_type,
    mp.status,
    mp.real_name,
    mp.credit_score,
    mp.mall_role,
    mp.verification_status,
    mp.created_at as profile_created_at,
    mp.updated_at as profile_updated_at
FROM public.ak_users u
INNER JOIN public.mall_user_profiles mp ON u.id = mp.user_id;

COMMENT ON VIEW public.mall_users_view IS '商城用户完整信息视图';

-- 10. 权限设置(根据实际需要调整)
-- 创建商城相关的RLS策略
ALTER TABLE public.mall_user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ak_user_addresses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.mall_user_favorites ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.mall_user_search_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.mall_user_browse_history ENABLE ROW LEVEL SECURITY;

-- 用户只能访问自己的数据
CREATE POLICY mall_user_profiles_policy ON public.mall_user_profiles
    FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));

CREATE POLICY user_addresses_policy ON public.ak_user_addresses
    FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));

CREATE POLICY mall_user_favorites_policy ON public.mall_user_favorites
    FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));

CREATE POLICY mall_user_search_history_policy ON public.mall_user_search_history
    FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));

CREATE POLICY mall_user_browse_history_policy ON public.mall_user_browse_history
    FOR ALL USING (auth.uid()::text = (SELECT auth_id::text FROM public.ak_users WHERE id = user_id));

-- 11. 示例查询函数
-- 获取用户默认地址
CREATE OR REPLACE FUNCTION get_user_default_address(p_user_id uuid)
RETURNS TABLE (
    id uuid,
    receiver_name varchar,
    receiver_phone varchar,
    full_address text,
    coordinates point
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id,
        a.receiver_name,
        a.receiver_phone,
        (a.province || a.city || a.district || a.address_detail) as full_address,
        a.coordinates
    FROM public.ak_user_addresses a
    WHERE a.user_id = p_user_id AND a.is_default = true AND a.status = 1
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 检查用户是否为商城认证商家
CREATE OR REPLACE FUNCTION is_verified_merchant(p_user_id uuid)
RETURNS boolean AS $$
DECLARE
    result boolean := false;
BEGIN
    SELECT (user_type = 2 AND verification_status = 1) INTO result
    FROM public.mall_user_profiles
    WHERE user_id = p_user_id;
    
    RETURN COALESCE(result, false);
END;
$$ LANGUAGE plpgsql;

-- 12. 完成提示
DO $$
BEGIN
    RAISE NOTICE '商城用户兼容性方案部署完成！';
    RAISE NOTICE '已创建表：mall_user_profiles, ak_user_addresses, mall_user_favorites, mall_user_search_history, mall_user_browse_history';
    RAISE NOTICE '已创建视图：mall_users_view';
    RAISE NOTICE '已设置触发器和RLS策略';
    RAISE NOTICE '已为现有用户创建默认商城档案';
END $$;