-- 迁移：给 cfg_area_category 加 sort_order 字段 + 创建 app.v_cfg_area_category 视图
-- 关联：R3 修复（项目类型筛选接口重建）
-- 日期：2026-03-20
-- 回滚：见文件末尾

-- ============================================================
-- 1. 给 cfg_area_category 加 sort_order 字段
-- ============================================================
ALTER TABLE dws.cfg_area_category
    ADD COLUMN IF NOT EXISTS sort_order INTEGER DEFAULT 100 NOT NULL;

COMMENT ON COLUMN dws.cfg_area_category.sort_order
    IS '前端筛选器显示排序（值越小越靠前）';

-- 按业务优先级设置排序值
UPDATE dws.cfg_area_category SET sort_order = 10  WHERE category_code = 'BILLIARD';
UPDATE dws.cfg_area_category SET sort_order = 20  WHERE category_code = 'SNOOKER';
UPDATE dws.cfg_area_category SET sort_order = 30  WHERE category_code = 'MAHJONG';
UPDATE dws.cfg_area_category SET sort_order = 40  WHERE category_code = 'KTV';
UPDATE dws.cfg_area_category SET sort_order = 900 WHERE category_code = 'SPECIAL';
UPDATE dws.cfg_area_category SET sort_order = 999 WHERE category_code = 'OTHER';

-- ============================================================
-- 2. 创建 app.v_cfg_area_category 视图
--    去重到 category 级别，排除 SPECIAL/OTHER（不在前端筛选器显示）
-- ============================================================
CREATE OR REPLACE VIEW app.v_cfg_area_category AS
SELECT DISTINCT
    category_code,
    category_name,
    display_name,
    short_name,
    sort_order
FROM dws.cfg_area_category
WHERE is_active = TRUE
  AND category_code NOT IN ('SPECIAL', 'OTHER')
ORDER BY sort_order;

GRANT SELECT ON app.v_cfg_area_category TO app_reader;

COMMENT ON VIEW app.v_cfg_area_category
    IS '项目类型筛选器配置视图（去重到 category 级别，排除 SPECIAL/OTHER）';

-- ============================================================
-- 回滚
-- ============================================================
-- DROP VIEW IF EXISTS app.v_cfg_area_category;
-- ALTER TABLE dws.cfg_area_category DROP COLUMN IF EXISTS sort_order;
