-- =============================================================================
-- 迁移：P16 调度任务最小运行间隔 — scheduled_tasks 新增字段
-- 日期：2026-03-22
-- 关联 SPEC：admin-web-enhancement
-- 需求：B1.1, B1.2
-- 说明：
--   为 public.scheduled_tasks 表新增 3 个字段：
--   1. min_run_interval_value  — 最小间隔数值（0=无限制，向后兼容）
--   2. min_run_interval_unit   — 间隔单位（minutes/hours/days）
--   3. last_success_at         — 最后一次成功执行的时间
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. 新增字段
-- ---------------------------------------------------------------------------

-- 最小间隔数值（0=无限制，与现有行为完全一致）
ALTER TABLE scheduled_tasks
    ADD COLUMN IF NOT EXISTS min_run_interval_value INTEGER NOT NULL DEFAULT 0;

-- 间隔单位：minutes/hours/days
ALTER TABLE scheduled_tasks
    ADD COLUMN IF NOT EXISTS min_run_interval_unit VARCHAR(20) NOT NULL DEFAULT 'minutes';

-- 最后一次成功执行的时间（NULL=从未成功执行）
ALTER TABLE scheduled_tasks
    ADD COLUMN IF NOT EXISTS last_success_at TIMESTAMPTZ;

-- ---------------------------------------------------------------------------
-- 2. 字段注释
-- ---------------------------------------------------------------------------

COMMENT ON COLUMN scheduled_tasks.min_run_interval_value IS '最小间隔数值（0=无限制）';
COMMENT ON COLUMN scheduled_tasks.min_run_interval_unit IS '间隔单位：minutes/hours/days';
COMMENT ON COLUMN scheduled_tasks.last_success_at IS '最后一次成功执行的时间';

-- =============================================================================
-- === ROLLBACK ===
-- ALTER TABLE scheduled_tasks DROP COLUMN IF EXISTS min_run_interval_value;
-- ALTER TABLE scheduled_tasks DROP COLUMN IF EXISTS min_run_interval_unit;
-- ALTER TABLE scheduled_tasks DROP COLUMN IF EXISTS last_success_at;
-- =============================================================================

-- =============================================================================
-- 验证
-- =============================================================================
-- 1) 新增字段存在性
-- SELECT column_name, data_type, is_nullable, column_default
-- FROM information_schema.columns
-- WHERE table_schema = 'public' AND table_name = 'scheduled_tasks'
--   AND column_name IN ('min_run_interval_value', 'min_run_interval_unit', 'last_success_at')
-- ORDER BY ordinal_position;
-- 预期：3 行

-- 2) 默认值验证
-- SELECT column_name, column_default
-- FROM information_schema.columns
-- WHERE table_schema = 'public' AND table_name = 'scheduled_tasks'
--   AND column_name IN ('min_run_interval_value', 'min_run_interval_unit')
-- ORDER BY column_name;
-- 预期：min_run_interval_unit → 'minutes'::character varying, min_run_interval_value → 0

-- 3) 向后兼容验证 — 已有行的新字段值应为默认值
-- SELECT id, min_run_interval_value, min_run_interval_unit, last_success_at
-- FROM scheduled_tasks
-- LIMIT 5;
-- 预期：min_run_interval_value=0, min_run_interval_unit='minutes', last_success_at=NULL

-- 4) 字段注释验证
-- SELECT a.attname, d.description
-- FROM pg_catalog.pg_attribute a
-- JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
-- JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
-- LEFT JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum
-- WHERE n.nspname = 'public' AND c.relname = 'scheduled_tasks'
--   AND a.attname IN ('min_run_interval_value', 'min_run_interval_unit', 'last_success_at')
-- ORDER BY a.attnum;
-- 预期：3 行，每行 description 非 NULL
