-- ============================================
-- 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;

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'scheme_list'
          AND column_name = 'scheme_start_time'
          AND data_type IN ('character varying', 'text')
    ) THEN
        EXECUTE 'ALTER TABLE public.scheme_list
                 ALTER COLUMN scheme_start_time TYPE TIMESTAMP WITH TIME ZONE
                 USING CASE
                     WHEN scheme_start_time ~ ''(Z|[+-][0-9]{2}:[0-9]{2})$'' THEN scheme_start_time::timestamptz
                     ELSE scheme_start_time::timestamp AT TIME ZONE ''UTC''
                 END';
    END IF;
END $$;