-- =====================================================================================
-- 商城系统数据库状态检查脚本
-- 分析现有数据库结构，生成个性化ALTER建议
-- =====================================================================================

-- =====================================================================================
-- 1. 检查现有表结构
-- =====================================================================================

-- 检查 ak_users 表字段情况
DO $$
DECLARE
    missing_fields TEXT[] := ARRAY[]::TEXT[];
    existing_fields TEXT[] := ARRAY[]::TEXT[];
    field_name TEXT;
    field_names TEXT[] := ARRAY['mall_status', 'mall_type', 'last_login_ip', 'total_orders', 'total_spent', 'user_level', 'points', 'verified_status'];
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '检查 ak_users 表字段状态';
    RAISE NOTICE '=======================================================';
    
    FOREACH field_name IN ARRAY field_names LOOP
        IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = field_name) THEN
            existing_fields := array_append(existing_fields, field_name);
            RAISE NOTICE '✓ 字段已存在: %', field_name;
        ELSE
            missing_fields := array_append(missing_fields, field_name);
            RAISE NOTICE '✗ 字段缺失: %', field_name;
        END IF;
    END LOOP;
    
    RAISE NOTICE '-------------------------------------------------------';
    RAISE NOTICE '已存在字段数量: %', array_length(existing_fields, 1);
    RAISE NOTICE '缺失字段数量: %', array_length(missing_fields, 1);
    
    IF array_length(missing_fields, 1) > 0 THEN
        RAISE NOTICE '需要添加的字段: %', array_to_string(missing_fields, ', ');
    ELSE
        RAISE NOTICE 'ak_users 表所有商城字段均已存在';
    END IF;
END $$;

-- 检查商城表存在情况
DO $$
DECLARE
    table_name TEXT;
    table_names TEXT[] := ARRAY['ml_user_profiles', 'ml_user_addresses', 'ml_categories', 'ml_brands', 'ml_products', 'ml_product_skus', 'ml_shops', 'ml_orders', 'ml_shopping_cart'];
    existing_tables TEXT[] := ARRAY[]::TEXT[];
    missing_tables TEXT[] := ARRAY[]::TEXT[];
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '检查商城核心表存在情况';
    RAISE NOTICE '=======================================================';
    
    FOREACH table_name IN ARRAY table_names LOOP
        IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = table_name) THEN
            existing_tables := array_append(existing_tables, table_name);
            RAISE NOTICE '✓ 表已存在: %', table_name;
        ELSE
            missing_tables := array_append(missing_tables, table_name);
            RAISE NOTICE '✗ 表缺失: %', table_name;
        END IF;
    END LOOP;
    
    RAISE NOTICE '-------------------------------------------------------';
    RAISE NOTICE '已存在表数量: %', array_length(existing_tables, 1);
    RAISE NOTICE '缺失表数量: %', array_length(missing_tables, 1);
    
    IF array_length(missing_tables, 1) > 0 THEN
        RAISE NOTICE '需要创建的表: %', array_to_string(missing_tables, ', ');
    ELSE
        RAISE NOTICE '所有商城核心表均已存在';
    END IF;
END $$;

-- =====================================================================================
-- 2. 检查现有索引情况
-- =====================================================================================

-- 检查重要索引存在情况
DO $$
DECLARE
    index_info RECORD;
    missing_indexes TEXT[] := ARRAY[]::TEXT[];
    existing_indexes TEXT[] := ARRAY[]::TEXT[];
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '检查重要索引存在情况';
    RAISE NOTICE '=======================================================';
    
    -- 定义重要索引列表
    FOR index_info IN 
        SELECT * FROM (VALUES
            ('idx_ak_users_mall_status', 'ak_users', 'mall_status'),
            ('idx_ak_users_mall_type', 'ak_users', 'mall_type'),
            ('idx_ak_users_total_orders', 'ak_users', 'total_orders'),
            ('idx_ml_products_cid', 'ml_products', 'cid'),
            ('idx_ml_products_slug', 'ml_products', 'slug'),
            ('idx_ml_categories_cid', 'ml_categories', 'cid'),
            ('idx_ml_orders_cid', 'ml_orders', 'cid'),
            ('idx_ml_shops_cid', 'ml_shops', 'cid')
        ) AS t(index_name, table_name, column_name)
    LOOP
        -- 检查表是否存在
        IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = index_info.table_name) THEN
            -- 检查索引是否存在
            IF EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = index_info.index_name) THEN
                existing_indexes := array_append(existing_indexes, index_info.index_name);
                RAISE NOTICE '✓ 索引已存在: % (表: %)', index_info.index_name, index_info.table_name;
            ELSE
                missing_indexes := array_append(missing_indexes, index_info.index_name);
                RAISE NOTICE '✗ 索引缺失: % (表: %)', index_info.index_name, index_info.table_name;
            END IF;
        ELSE
            RAISE NOTICE '○ 表不存在，跳过索引检查: % (表: %)', index_info.index_name, index_info.table_name;
        END IF;
    END LOOP;
    
    RAISE NOTICE '-------------------------------------------------------';
    RAISE NOTICE '已存在索引数量: %', array_length(existing_indexes, 1);
    RAISE NOTICE '缺失索引数量: %', array_length(missing_indexes, 1);
END $$;

-- =====================================================================================
-- 3. 检查扩展和函数
-- =====================================================================================

-- 检查必要的PostgreSQL扩展
DO $$
DECLARE
    ext_name TEXT;
    extensions TEXT[] := ARRAY['uuid-ossp', 'btree_gin'];
    existing_ext TEXT[] := ARRAY[]::TEXT[];
    missing_ext TEXT[] := ARRAY[]::TEXT[];
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '检查PostgreSQL扩展';
    RAISE NOTICE '=======================================================';
    
    FOREACH ext_name IN ARRAY extensions LOOP
        IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = ext_name) THEN
            existing_ext := array_append(existing_ext, ext_name);
            RAISE NOTICE '✓ 扩展已安装: %', ext_name;
        ELSE
            missing_ext := array_append(missing_ext, ext_name);
            RAISE NOTICE '✗ 扩展缺失: %', ext_name;
        END IF;
    END LOOP;
    
    IF array_length(missing_ext, 1) > 0 THEN
        RAISE NOTICE '需要安装的扩展: %', array_to_string(missing_ext, ', ');
    END IF;
END $$;

-- 检查商城相关函数
DO $$
DECLARE
    func_name TEXT;
    functions TEXT[] := ARRAY['generate_order_no', 'calculate_cart_total', 'update_user_mall_stats'];
    existing_funcs TEXT[] := ARRAY[]::TEXT[];
    missing_funcs TEXT[] := ARRAY[]::TEXT[];
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '检查商城相关函数';
    RAISE NOTICE '=======================================================';
    
    FOREACH func_name IN ARRAY functions LOOP
        IF EXISTS (SELECT 1 FROM pg_proc WHERE proname = func_name) THEN
            existing_funcs := array_append(existing_funcs, func_name);
            RAISE NOTICE '✓ 函数已存在: %', func_name;
        ELSE
            missing_funcs := array_append(missing_funcs, func_name);
            RAISE NOTICE '✗ 函数缺失: %', func_name;
        END IF;
    END LOOP;
    
    IF array_length(missing_funcs, 1) > 0 THEN
        RAISE NOTICE '需要创建的函数: %', array_to_string(missing_funcs, ', ');
    END IF;
END $$;

-- =====================================================================================
-- 4. 生成个性化建议
-- =====================================================================================

DO $$
DECLARE
    ak_users_missing INTEGER := 0;
    mall_tables_missing INTEGER := 0;
    suggestion TEXT := '';
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '个性化升级建议';
    RAISE NOTICE '=======================================================';
    
    -- 统计ak_users缺失字段
    SELECT COUNT(*) INTO ak_users_missing
    FROM (VALUES ('mall_status'), ('mall_type'), ('total_orders'), ('total_spent')) AS t(field)
    WHERE NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'ak_users' AND column_name = t.field
    );
    
    -- 统计商城表缺失情况
    SELECT COUNT(*) INTO mall_tables_missing
    FROM (VALUES ('ml_products'), ('ml_categories'), ('ml_orders'), ('ml_shops')) AS t(table_name)
    WHERE NOT EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_name = t.table_name
    );
    
    -- 生成建议
    IF ak_users_missing > 0 AND mall_tables_missing > 0 THEN
        suggestion := '建议使用 mall_alter_upgrade.sql（完整升级脚本）';
    ELSIF ak_users_missing > 0 AND mall_tables_missing = 0 THEN
        suggestion := '建议使用 mall_fields_only_upgrade.sql（仅字段升级脚本）';
    ELSIF ak_users_missing = 0 AND mall_tables_missing > 0 THEN
        suggestion := '建议使用 mall_migration.sql（表结构创建脚本）';
    ELSE
        suggestion := '数据库结构已完整，建议检查数据完整性和权限配置';
    END IF;
    
    RAISE NOTICE '根据您的数据库状态分析：';
    RAISE NOTICE '• ak_users 表缺失字段数: %', ak_users_missing;
    RAISE NOTICE '• 缺失商城核心表数: %', mall_tables_missing;
    RAISE NOTICE '';
    RAISE NOTICE '推荐执行方案: %', suggestion;
    
    -- 详细建议
    RAISE NOTICE '';
    RAISE NOTICE '详细执行步骤：';
    IF ak_users_missing > 0 THEN
        RAISE NOTICE '1. 先执行字段升级脚本为ak_users表添加商城字段';
    END IF;
    IF mall_tables_missing > 0 THEN
        RAISE NOTICE '2. 执行表结构创建脚本建立商城核心表';
    END IF;
    RAISE NOTICE '3. 执行SEO和安全策略脚本（mall_seo_security.sql）';
    RAISE NOTICE '4. 根据需要执行模拟数据插入脚本进行测试';
END $$;

-- =====================================================================================
-- 5. 生成具体的ALTER语句（可选）
-- =====================================================================================

-- 生成ak_users表缺失字段的ALTER语句
DO $$
DECLARE
    alter_statements TEXT := '';
    field_name TEXT;
    field_configs TEXT[] := ARRAY[
        'mall_status INTEGER DEFAULT 1 CHECK (mall_status IN (1,2))',
        'mall_type INTEGER DEFAULT 1 CHECK (mall_type IN (1,2,3))',
        'total_orders INTEGER DEFAULT 0 CHECK (total_orders >= 0)',
        'total_spent DECIMAL(12,2) DEFAULT 0.00 CHECK (total_spent >= 0)',
        'user_level INTEGER DEFAULT 1 CHECK (user_level >= 1 AND user_level <= 10)',
        'points INTEGER DEFAULT 0 CHECK (points >= 0)',
        'verified_status INTEGER DEFAULT 0 CHECK (verified_status IN (0,1,2))'
    ];
    field_names TEXT[] := ARRAY['mall_status', 'mall_type', 'total_orders', 'total_spent', 'user_level', 'points', 'verified_status'];
    i INTEGER;
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '生成ak_users表ALTER语句';
    RAISE NOTICE '=======================================================';
    
    FOR i IN 1..array_length(field_names, 1) LOOP
        field_name := field_names[i];
        IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'ak_users' AND column_name = field_name) THEN
            alter_statements := alter_statements || format('ALTER TABLE public.ak_users ADD COLUMN %s;' || chr(10), field_configs[i]);
            RAISE NOTICE '需要执行: ALTER TABLE public.ak_users ADD COLUMN %;', field_configs[i];
        END IF;
    END LOOP;
    
    IF alter_statements = '' THEN
        RAISE NOTICE 'ak_users表无需添加字段';
    ELSE
        RAISE NOTICE '';
        RAISE NOTICE '完整ALTER脚本：';
        RAISE NOTICE '%', alter_statements;
    END IF;
END $$;

-- =====================================================================================
-- 6. 数据完整性检查
-- =====================================================================================

DO $$
DECLARE
    users_count INTEGER;
    profiles_count INTEGER;
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '数据完整性检查';
    RAISE NOTICE '=======================================================';
    
    -- 检查用户表数据
    SELECT COUNT(*) INTO users_count FROM public.ak_users;
    RAISE NOTICE 'ak_users 表用户数量: %', users_count;
    
    -- 检查用户档案表（如果存在）
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ml_user_profiles') THEN
        SELECT COUNT(*) INTO profiles_count FROM public.ml_user_profiles;
        RAISE NOTICE 'ml_user_profiles 表档案数量: %', profiles_count;
        
        IF users_count > profiles_count THEN
            RAISE NOTICE '注意: 有 % 个用户缺少商城档案，建议执行档案补充脚本', users_count - profiles_count;
        END IF;
    ELSE
        RAISE NOTICE 'ml_user_profiles 表不存在';
    END IF;
END $$;

-- =====================================================================================
-- 完成提示
-- =====================================================================================

DO $$
BEGIN
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '数据库状态检查完成！';
    RAISE NOTICE '=======================================================';
    RAISE NOTICE '请根据上述分析结果选择合适的升级脚本：';
    RAISE NOTICE '';
    RAISE NOTICE '• mall_alter_upgrade.sql - 完整升级（表+字段+索引+函数）';
    RAISE NOTICE '• mall_fields_only_upgrade.sql - 仅字段升级（最小化修改）';
    RAISE NOTICE '• mall_migration.sql - 完整建表（全新部署）';
    RAISE NOTICE '• mall_seo_security.sql - SEO优化和安全策略';
    RAISE NOTICE '';
    RAISE NOTICE '建议在生产环境执行前先在测试环境验证！';
    RAISE NOTICE '=======================================================';
END $$;