-- ============================================
-- TJWater Server 时区统一迁移脚本
-- 将历史无时区时间列升级为 TIMESTAMP WITH TIME ZONE
-- 约定：历史无时区值按 UTC 解释
-- ============================================

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'users'
          AND column_name = 'created_at'
          AND data_type = 'timestamp without time zone'
    ) THEN
        EXECUTE 'ALTER TABLE public.users
                 ALTER COLUMN created_at TYPE TIMESTAMP WITH TIME ZONE
                 USING created_at AT TIME ZONE ''UTC''';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'users'
          AND column_name = 'updated_at'
          AND data_type = 'timestamp without time zone'
    ) THEN
        EXECUTE 'ALTER TABLE public.users
                 ALTER COLUMN updated_at TYPE TIMESTAMP WITH TIME ZONE
                 USING updated_at AT TIME ZONE ''UTC''';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'audit_logs'
          AND column_name = 'timestamp'
          AND data_type = 'timestamp without time zone'
    ) THEN
        EXECUTE 'ALTER TABLE public.audit_logs
                 ALTER COLUMN timestamp TYPE TIMESTAMP WITH TIME ZONE
                 USING "timestamp" AT TIME ZONE ''UTC''';
    END IF;
END $$;