SET search_path TO atlas, public;

-- 精确坐标按水印在预览区域内的可移动范围归一化；旧九宫格数据保留原位置。
ALTER TABLE watermark_template_versions
    ADD COLUMN IF NOT EXISTS position_x NUMERIC(5,4),
    ADD COLUMN IF NOT EXISTS position_y NUMERIC(5,4);

-- 迁移角色受强制 RLS 限制，回填期间临时关闭并在同一事务内恢复。
ALTER TABLE watermark_template_versions NO FORCE ROW LEVEL SECURITY;
ALTER TABLE watermark_template_versions DISABLE ROW LEVEL SECURITY;

UPDATE watermark_template_versions
SET position_x = CASE
        WHEN position IN ('top_left', 'middle_left', 'bottom_left') THEN 0
        WHEN position IN ('top_center', 'center', 'bottom_center') THEN 0.5
        ELSE 1
    END,
    position_y = CASE
        WHEN position IN ('top_left', 'top_center', 'top_right') THEN 0
        WHEN position IN ('middle_left', 'center', 'middle_right') THEN 0.5
        ELSE 1
    END
WHERE position_x IS NULL OR position_y IS NULL;

ALTER TABLE watermark_template_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE watermark_template_versions FORCE ROW LEVEL SECURITY;

ALTER TABLE watermark_template_versions
    ALTER COLUMN position_x SET NOT NULL,
    ALTER COLUMN position_y SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conrelid = 'atlas.watermark_template_versions'::regclass
          AND conname = 'watermark_template_versions_position_coordinates_check'
    ) THEN
        ALTER TABLE watermark_template_versions
            ADD CONSTRAINT watermark_template_versions_position_coordinates_check
            CHECK (position_x BETWEEN 0 AND 1 AND position_y BETWEEN 0 AND 1);
    END IF;
END
$$;